Notes

private void cmdExceleAktar_Click(object sender, EventArgs e)
{
Yazdir = false;

if (MessageBox.Show("Excel Çıktısı Kaydedilecektir, emin misiniz?", "Excel Kaydet", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) == System.Windows.Forms.DialogResult.No) return;

this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
ExcelPlanYaz();
this.Cursor = System.Windows.Forms.Cursors.Arrow;
}

int OncekiyleAyni = 0;
void ExcelPlanYaz()
{


SaveFileDialog saveAs = new SaveFileDialog();
saveAs.Filter = "Excel File|*.xls";
saveAs.Title = "Kayıt Yolu Seçiniz";
saveAs.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
saveAs.ShowDialog();

DataSet ds = new DataSet();
ds = dyg.MakinaPlanDetayYAZDIR(cboMakinaGrup.Text, cboMakina.Text);

if (ds == null) { MessageBox.Show("Database ile iletişimde sorun oluştu tekrar deneyiniz"); return; }
Workbook wb = new Workbook();
//Eklenen workbook için Özet Rapor isimli workSheet ekler
Worksheet sahife = wb.Worksheets.Add("Makina Planı");

//En üstteki 30 cell'i birleştir ve cell e 'Tarih ile beraber Makina Plan Rapor' yaz.
WorksheetMergedCellsRegion RaporBaslik = sahife.MergedCellsRegions.Add(0, 1, 0, 30);
wb.Worksheets[0].Rows[0].Height = 330;
RaporBaslik.Value = DateTime.Now.ToShortDateString() + " Tarihli Makina Plan Raporu";
RaporBaslik.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
CellBorderKoy(RaporBaslik.CellFormat, Color.Black, Color.LightGreen);


KolonBasliklariniYaz(ds, sahife,1);

int[] MergeEdilecekSiralar = new int[0];

int ExcelRowSay = 2;
int MergeSay = 0;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
int SıraTut = ds.Tables[0].Rows[i]["SIRA"].ToInt32();
string MakinaIsmiTut = ds.Tables[0].Rows[i]["Makina"].ToString();

DataRow[] drSira = ds.Tables[0].Select("SIRA='" + SıraTut + "' AND Makina Like '"+MakinaIsmiTut.Substring(0,4)+"'");

WorksheetCell hucre = sahife.Rows[ExcelRowSay].Cells[1];
for (int k = 0; k < drSira.Length; k++)
{
#region MergeEt
WorksheetMergedCellsRegion SiraMerge = sahife.MergedCellsRegions.Add(ExcelRowSay, 1, ExcelRowSay + drSira.Length - 1, 1);
SiraMerge.Value = drSira[MergeSay]["SIRA"].ToInt32();
SiraMerge.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
CellBorderKoy(SiraMerge.CellFormat, Color.Black, Color.Silver);

WorksheetMergedCellsRegion MakinaIsmiMerge = sahife.MergedCellsRegions.Add(ExcelRowSay, 2, ExcelRowSay + drSira.Length - 1, 2);
MakinaIsmiMerge.Value = drSira[MergeSay]["Makina"].ToString();
MakinaIsmiMerge.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
CellBorderKoy(MakinaIsmiMerge.CellFormat, Color.Black, Color.Silver);

WorksheetMergedCellsRegion Kapasite = sahife.MergedCellsRegions.Add(ExcelRowSay, 3, ExcelRowSay + drSira.Length - 1, 3);
Kapasite.Value = drSira[MergeSay]["Kapasite"].ToInt32();
Kapasite.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
CellBorderKoy(Kapasite.CellFormat, Color.Black, Color.Silver);

WorksheetMergedCellsRegion SarjKg = sahife.MergedCellsRegions.Add(ExcelRowSay, 4, ExcelRowSay + drSira.Length - 1, 4);
SarjKg.Value = drSira[MergeSay]["SarjKG"].ToDouble();
SarjKg.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
CellBorderKoy(SarjKg.CellFormat, Color.Black, Color.Silver);
#endregion

#region Hücreleri Doldur
for (int m = 0; m < drSira.Length; m++)
{
for (int j = 5; j < ds.Tables[0].Columns.Count - 1; j++)
{
if (sahife.Rows[1].Cells[j].Value != null)
{
switch (sahife.Rows[1].Cells[j].Value.ToString())
{
case "Giriş Tarihi":
hucre = sahife.Rows[ExcelRowSay].Cells[j];
hucre.Value = drSira[m][j].ToString().Substring(0, 10);
break;
case "Teslim Tarihi":
hucre = sahife.Rows[ExcelRowSay].Cells[j];
hucre.Value = drSira[m][j].ToString() == "" ? "" : drSira[m][j].ToString().Substring(0, 10);
break;
case "PlanNotu":
hucre = sahife.Rows[ExcelRowSay].Cells[j];
hucre.Value = drSira[m][j].ToString() == "" ? String.Empty : drSira[m][j].ToString();
break;
case "MT":
hucre = sahife.Rows[ExcelRowSay].Cells[j];
hucre.Value = drSira[m][j].ToDouble();
break;
case "KG":
hucre = sahife.Rows[ExcelRowSay].Cells[j];
hucre.Value = drSira[m][j].ToDouble();
break;
case "Sipariş No":
hucre = sahife.Rows[ExcelRowSay].Cells[j];
hucre.CellFormat.Alignment = HorizontalCellAlignment.Left;
hucre.Value = drSira[m][j].ToString();
break;
case "Sipariş MT":
hucre = sahife.Rows[ExcelRowSay].Cells[j];
hucre.CellFormat.Alignment = HorizontalCellAlignment.Left;
hucre.Value = drSira[m][j].ToDouble();
break;
default:
hucre = sahife.Rows[ExcelRowSay].Cells[j];
hucre.Value = drSira[m][j].ToString();
break;
}
if (hucre.Value.ToString() == "acillll")
{
CellBorderKoy(hucre.CellFormat, Color.Black, Color.Red);
}
else
CellBorderKoy(hucre.CellFormat, Color.Black, Color.White);

}

}

ExcelRowSay++;


}
#endregion

#region Doldurduğun Veriyi Datasetten Sil
for (int j = ds.Tables[0].Rows.Count - 1; j >= 0; j--)
{
if (ds.Tables[0].Rows[j]["SIRA"].ToString() == SıraTut.ToString() && ds.Tables[0].Rows[j]["Makina"].ToString().Substring(0,4) == MakinaIsmiTut.Substring(0,4))
{
ds.Tables[0].Rows[j].Delete();

}
}
ds.AcceptChanges();

i = -1;

if (ds.Tables[0].Rows.Count > i + 1)
{
if (ds.Tables[0].Rows[i + 1]["SIRA"].ToInt32() != SıraTut && MakinaIsmiTut.Substring(0, 4) != ds.Tables[0].Rows[i + 1]["Makina"].ToString().Substring(0, 4))
{
ExcelRowSay += 2;
KolonBasliklariniYaz(ds, sahife, ExcelRowSay);
}
}
#endregion

break;
}
}

BIFF8Writer.WriteWorkbookToFile(wb, saveAs.FileName);
}

private void KolonBasliklariniYaz(DataSet ds, Worksheet sahife, int ExcelRow)
{
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
WorksheetCell hucre = sahife.Rows[ExcelRow].Cells[i];
if (ds.Tables[0].Columns[i].Caption == "PlanID" || ds.Tables[0].Columns[i].Caption == "Renklendir") continue;
switch (ds.Tables[0].Columns[i].Caption)
{
case "FasonFirma":
hucre.Value = "Fason Firma";
sahife.Columns[i].Width = 5000;
break;
case "GirisTRH":
hucre.Value = "Giriş Tarihi";
sahife.Columns[i].Width = 2700;
break;
case "TeslimTRH":
hucre.Value = "Teslim Tarihi";
sahife.Columns[i].Width = 2700;
break;
case "Musterisi":
hucre.Value = "Müşterisi";
sahife.Columns[i].Width = 7000;
sahife.Columns[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
break;
case "Durum":
hucre.Value = "Durum";
sahife.Columns[i].Width = 5000;
sahife.Columns[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
break;
case "IsEmriNo":
hucre.Value = "İş Emri No";
sahife.Columns[i].Width = 4000;
sahife.Columns[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
break;
case "RenkKod":
hucre.Value = "Renk Kodu";
sahife.Columns[i].Width = 3500;
sahife.Columns[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
break;
case "SiparisNo":
hucre.Value = "Sipariş No";
sahife.Columns[i].Width = 3700;
sahife.Columns[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
break;
case "DesenNo":
hucre.Value = "Desen No";
sahife.Columns[i].Width = 3000;
sahife.Columns[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
break;
case "Kompozisyon":
hucre.Value = "Kompozisyon";
sahife.Columns[i].Width = 7500;
sahife.Columns[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
break;
case "RotaAciklama":
hucre.Value = "Rota Açıklama";
sahife.Columns[i].Width = 20000;
sahife.Columns[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
break;
case "Lot":
hucre.Value = "Lot";
sahife.Columns[i].Width = 3000;
sahife.Columns[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
break;
case "SipMT":
hucre.Value = "Sipariş MT";
sahife.Columns[i].Width = 2500;
sahife.Columns[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
break;
case "PlanTarihi":
hucre.Value = "Plan Tarihi";
sahife.Columns[i].Width = 3500;
sahife.Columns[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
break;
default://
hucre.Value = ds.Tables[0].Columns[i].Caption;
sahife.Columns[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
break;
}

CellBorderKoy(hucre.CellFormat, Color.LightGray, Color.LightSalmon);
}
}

private void CellBorderKoy(IWorksheetCellFormat cell, Color BorderRenk, Color CellRenk)
{
cell.BottomBorderColor = BorderRenk;
cell.BottomBorderStyle = CellBorderLineStyle.Thin;

cell.LeftBorderColor = BorderRenk;
cell.LeftBorderStyle = CellBorderLineStyle.Thin;

cell.RightBorderColor = BorderRenk;
cell.RightBorderStyle = CellBorderLineStyle.Thin;

cell.TopBorderColor = BorderRenk;
cell.TopBorderStyle = CellBorderLineStyle.Thin;


cell.FillPattern = FillPatternStyle.Solid;
cell.FillPatternForegroundColor = CellRenk;

cell.Alignment = HorizontalCellAlignment.Center;
cell.VerticalAlignment = VerticalCellAlignment.Center;
}