private void button1_Click(object sender, EventArgs e)
{
//int columnWidth = 0;
// Daten speichern
try
{
// creating Excel Application
Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
// creating new WorkBook within Excel application
Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
app.Visible = false;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Worksheets.get_Item(1);
worksheet = workbook.ActiveSheet;
// Set Orientation to Landscape
worksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
// changing the name of active sheet
worksheet.Name = "Jahresrangliste";
// storing title in Excel
worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, dataGridView1.Columns.Count]].Merge(false);
worksheet.Cells[1, 1] = label4.Text;
worksheet.Cells[1, 1].Font.Bold = true;
worksheet.Cells[1, 1].Font.Size = 20;
worksheet.Cells[1, 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[1, 1].Interior.Color = ColorTranslator.ToOle(Color.Gray);
worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, dataGridView1.Columns.Count]].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
// storing header part in Excel
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
if (i > 3 && i < dataGridView1.Columns.Count)
{
worksheet.Cells[3, i] = "Lauf " + (i - 3).ToString();
}
worksheet.Cells[2, i].Font.Bold = true;
worksheet.Cells[2, i].Font.Size = 12;
worksheet.Cells[2, i].Interior.Color = ColorTranslator.ToOle(Color.LightGray);
worksheet.Cells[2, i].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
if (i > 3 && i < dataGridView1.Columns.Count)
{
worksheet.Cells[2, i] = dataGridView1.Columns[i - 1].HeaderText;
}
else
{
worksheet.Cells[3, i] = dataGridView1.Columns[i - 1].HeaderText;
}
worksheet.Cells[3, i].Font.Bold = true;
worksheet.Cells[3, i].Interior.Color = ColorTranslator.ToOle(Color.LightGray);
worksheet.Cells[3, i].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
try
{
worksheet.Cells[i + 4, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
catch //Fängt Ausnahmen ab wenn Zellen leer sind
{
}
worksheet.Cells[i + 4, j + 1].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
}
}
worksheet.Range[worksheet.Cells[dataGridView1.Rows.Count + 4, 1], worksheet.Cells[dataGridView1.Rows.Count + 4, dataGridView1.Columns.Count]].Merge(false);
worksheet.Cells[dataGridView1.Rows.Count + 4, 1] = DateTime.Today.ToString("d");
worksheet.Cells[dataGridView1.Rows.Count + 4, 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
// Set column width
worksheet.UsedRange.Columns.AutoFit();
// for(int j = 1; j < dataGridView1.Columns.Count; j++)
// {
// if(columnWidth < worksheet.Cells[3,j].width) columnWidth = (int)worksheet.Cells[3, j].width;
// }
//worksheet.UsedRange.ColumnWidth = 15;
// save the application
workbook.SaveAs(path + "\\" + jahr + "_" + klasse + "_0_Jahresrangliste" + ".xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// export PDF
worksheet.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, path + "\\" + jahr + "_" + klasse + "_0_Jahresrangliste" + ".pdf");
// Exit from the application
app.Quit();
}
catch
{
}
}
