private void GenerateExcel(bool isPDF, SaveFileDialog sfd) { CopyAlltoClipboard(); object misValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application xlexcel = new Microsoft.Office.Interop.Excel.Application(); xlexcel.DisplayAlerts = false; Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int colStart = 1; int addCol = 0; int logoCellLeft = 0; if (cboReports.SelectedValue.ToString() == "Absentees Report" || cboReports.SelectedValue.ToString() == "No Time Out Report") { addCol = 2; colStart += addCol; logoCellLeft = 100; } else if (cboReports.SelectedValue.ToString() == "Attendance Report") { logoCellLeft = 12; } Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[8, colStart]; xlWorkSheet.Shapes.AddPicture(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location) + @LOGO_PATH, MsoTriState.msoFalse, MsoTriState.msoCTrue, logoCellLeft, 0, 90, 90); xlWorkSheet.get_Range("A1", "A1").Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.get_Range("A1", "A1").Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); CR.Rows.AutoFit(); CR.get_Range("A1").Columns.EntireColumn.AutoFit(); CR.get_Range("C1", "K1").Columns.EntireColumn.AutoFit(); CR.get_Range("B1").Columns.ColumnWidth = 35; if (cboReports.SelectedValue.ToString().Trim() == "Attendance Report") { CR.get_Range("D1", "K1").Columns.EntireColumn.AutoFit(); CR.get_Range("C1").Columns.ColumnWidth = 20; } CR.get_Range("B1").Cells.Style.WrapText = true; xlWorkSheet.Cells[3, 3 + addCol - 1] = "' " + cboReports.SelectedValue.ToString(); xlWorkSheet.Cells[3, 3 + addCol - 1].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.Cells[4, 3 + addCol - 1] = "' " + dtFrom.SelectedDate.Value.ToString("dd MMMMM yyyy") + " to " + dtTo.SelectedDate.Value.ToString("dd MMMM yyyy"); xlWorkSheet.Cells[4, 3 + addCol - 1].Cells.Style.WrapText = false; xlWorkSheet.Cells[4, 3 + addCol - 1].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; xlWorkSheet.Cells[5, 3 + addCol - 1] = "' " + GetFilterUsed().Replace("_", ""); xlWorkSheet.Cells[5, 3 + addCol - 1].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; int ctr = 1; while (ctr <= dgResults.Columns.Count) { ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[8, ctr + addCol]).Interior.Color = ColorTranslator.ToOle(Color.AliceBlue); ctr++; } for (int ctrC = 1; ctrC <= dgResults.Columns.Count; ctrC++) { for (int ctrR = 8; ctrR <= dgResults.Items.Count + 8; ctrR++) { ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[ctrR, ctrC + addCol]).BorderAround(LineStyle.Thin, Microsoft.Office.Interop.Excel.XlBorderWeight.xlHairline, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, ColorTranslator.ToOle(Color.AliceBlue)); } } Microsoft.Office.Interop.Excel.Range rg = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, colStart]; rg.EntireColumn.NumberFormat = "yyyy/MM/dd"; if (cboReports.SelectedValue.ToString() == "Consolidated Report") { rg.EntireColumn.NumberFormat = "@"; } xlWorkBook.SaveAs(sfd.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlexcel.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); ReleaseObject(xlWorkSheet); ReleaseObject(xlWorkBook); ReleaseObject(xlexcel); dgResults.UnselectAllCells(); Clipboard.Clear(); if (File.Exists(sfd.FileName) && !isPDF) { CheckIfFileIsOpen(sfd); //System.Diagnostics.Process.Start(sfd.FileName); System.Diagnostics.Process.Start(Path.GetDirectoryName(sfd.FileName)); } }