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));
}
}