예제 #1
0
        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
            {
            }
        }