C# GemBox.Spreadsheet ile birden fazla komutu birleştirme

[mfyildiz]

Aşağidaki̇ kodu nasil kisaltirim (for yada başka bi̇r döngü İle) bi̇r de excel kenar boşliklari çok fazala veri̇yor yazilar excel sayfasinin ortasinda kaliyor.

PROJE BURDA

LÜTFEN YARDIMCI OLUNUZ.


using GemBox.Spreadsheet;

int bbas;

private void button1_Click(object sender, EventArgs e)
{
string xlsfile = Application.StartupPath + "\\data.xls";
try { File.Delete(xlsfile); File.Copy(System.Windows.Forms.Application.StartupPath + "\\null.xls", xlsfile);
} catch { }

SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile ef = ExcelFile.Load(xlsfile);
ExcelWorksheet ws = ef.Worksheets.Add("BILL1");
ef.Worksheets.Remove(0);

// Code block that should be abbreviated by "For"

if (comboBox1.Text == "Yes")
{ bbas = 5; } // stabil number
else
{ bbas = 8; } // stabil number

ws.Rows[bbas].Height = 15 * 15; // seperator

int satir4 = bbas + 2;
int satir5 = bbas + 3;
int satir6 = bbas + 4;
int satir7 = bbas + 5;


try { if (dataGridView1.RowCount > 0) {
ws.Cells.GetSubrangeAbsolute(satir4, 0, satir4, 3).Merged = true;
ws.Cells.GetSubrangeAbsolute(satir4, 4, satir4, 9).Merged = true;
ws.Cells.GetSubrangeAbsolute(satir4, 10, satir4, 15).Merged = true;
ws.Cells.GetSubrangeAbsolute(satir4, 16, satir4, 20).Merged = true;
ws.Cells.GetSubrangeAbsolute(satir4, 21, satir4, 27).Merged = true;
ws.Cells[satir4, 0].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(0, 0, 0), LineStyle.Thin);
ws.Cells[satir4, 4].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(0, 0, 0), LineStyle.Thin);
ws.Cells[satir4, 10].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(0, 0, 0), LineStyle.Thin);
ws.Cells[satir4, 16].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(0, 0, 0), LineStyle.Thin);
ws.Cells[satir4, 21].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(0, 0, 0), LineStyle.Thin);
ws.Cells[satir4, 0].Value = dataGridView1.Rows[0].Cells[1].Value.ToString();
ws.Cells[satir4, 4].Value = dataGridView1.Rows[0].Cells[2].Value.ToString();
ws.Cells[satir4, 10].Value = dataGridView1.Rows[0].Cells[3].Value.ToString();
ws.Cells[satir4, 16].Value = dataGridView1.Rows[0].Cells[4].Value.ToString();
ws.Cells[satir4, 21].Value = dataGridView1.Rows[0].Cells[6].Value.ToString() + " - " + dataGridView1.Rows[0].Cells[7].Value.ToString();
} } catch { }

try { if (dataGridView1.RowCount > 1) {
ws.Cells.GetSubrangeAbsolute(satir5, 0, satir5, 3).Merged = true;
ws.Cells.GetSubrangeAbsolute(satir5, 4, satir5, 9).Merged = true;
ws.Cells.GetSubrangeAbsolute(satir5, 10, satir5, 15).Merged = true;
ws.Cells.GetSubrangeAbsolute(satir5, 16, satir5, 20).Merged = true;
ws.Cells.GetSubrangeAbsolute(satir5, 21, satir5, 27).Merged = true;
ws.Cells[satir5, 0].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(0, 0, 0), LineStyle.Thin);
ws.Cells[satir5, 4].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(0, 0, 0), LineStyle.Thin);
ws.Cells[satir5, 10].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(0, 0, 0), LineStyle.Thin);
ws.Cells[satir5, 16].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(0, 0, 0), LineStyle.Thin);
ws.Cells[satir5, 21].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(0, 0, 0), LineStyle.Thin);
ws.Cells[satir5, 0].Value = dataGridView1.Rows[1].Cells[1].Value.ToString();
ws.Cells[satir5, 4].Value = dataGridView1.Rows[1].Cells[2].Value.ToString();
ws.Cells[satir5, 10].Value = dataGridView1.Rows[1].Cells[3].Value.ToString();
ws.Cells[satir5, 16].Value = dataGridView1.Rows[1].Cells[4].Value.ToString();
ws.Cells[satir5, 21].Value = dataGridView1.Rows[1].Cells[6].Value.ToString() + " - " + dataGridView1.Rows[0].Cells[7].Value.ToString();
} } catch { }

try { if (dataGridView1.RowCount > 2) {
ws.Cells.GetSubrangeAbsolute(satir6, 0, satir6, 3).Merged = true;
ws.Cells.GetSubrangeAbsolute(satir6, 4, satir6, 9).Merged = true;
ws.Cells.GetSubrangeAbsolute(satir6, 10, satir6, 15).Merged = true;
ws.Cells.GetSubrangeAbsolute(satir6, 16, satir6, 20).Merged = true;
ws.Cells.GetSubrangeAbsolute(satir6, 21, satir6, 27).Merged = true;
ws.Cells[satir6, 0].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(0, 0, 0), LineStyle.Thin);
ws.Cells[satir6, 4].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(0, 0, 0), LineStyle.Thin);
ws.Cells[satir6, 10].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(0, 0, 0), LineStyle.Thin);
ws.Cells[satir6, 16].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(0, 0, 0), LineStyle.Thin);
ws.Cells[satir6, 21].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(0, 0, 0), LineStyle.Thin);
ws.Cells[satir6, 0].Value = dataGridView1.Rows[2].Cells[1].Value.ToString();
ws.Cells[satir6, 4].Value = dataGridView1.Rows[2].Cells[2].Value.ToString();
ws.Cells[satir6, 10].Value = dataGridView1.Rows[2].Cells[3].Value.ToString();
ws.Cells[satir6, 16].Value = dataGridView1.Rows[2].Cells[4].Value.ToString();
ws.Cells[satir6, 21].Value = dataGridView1.Rows[2].Cells[6].Value.ToString() + " - " + dataGridView1.Rows[0].Cells[7].Value.ToString();
} } catch { }

try { if (dataGridView1.RowCount > 3) {
ws.Cells.GetSubrangeAbsolute(satir7, 0, satir7, 3).Merged = true;
ws.Cells.GetSubrangeAbsolute(satir7, 4, satir7, 9).Merged = true;
ws.Cells.GetSubrangeAbsolute(satir7, 10, satir7, 15).Merged = true;
ws.Cells.GetSubrangeAbsolute(satir7, 16, satir7, 20).Merged = true;
ws.Cells.GetSubrangeAbsolute(satir7, 21, satir7, 27).Merged = true;
ws.Cells[satir7, 0].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(0, 0, 0), LineStyle.Thin);
ws.Cells[satir7, 4].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(0, 0, 0), LineStyle.Thin);
ws.Cells[satir7, 10].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(0, 0, 0), LineStyle.Thin);
ws.Cells[satir7, 16].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(0, 0, 0), LineStyle.Thin);
ws.Cells[satir7, 21].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(0, 0, 0), LineStyle.Thin);
ws.Cells[satir7, 0].Value = dataGridView1.Rows[4].Cells[1].Value.ToString();
ws.Cells[satir7, 4].Value = dataGridView1.Rows[4].Cells[2].Value.ToString();
ws.Cells[satir7, 10].Value = dataGridView1.Rows[4].Cells[3].Value.ToString();
ws.Cells[satir7, 16].Value = dataGridView1.Rows[4].Cells[4].Value.ToString();
ws.Cells[satir7, 21].Value = dataGridView1.Rows[4].Cells[6].Value.ToString() + " - " + dataGridView1.Rows[0].Cells[7].Value.ToString();
} } catch { }


// Code block that should be abbreviated by "For"
// #########

ef.Save(xlsfile);
System.Diagnostics.Process.Start(xlsfile);