I have a windows application in C#. One of the function it has is to extract data from the Database and save it in excel so It can be sent to headquarters to be imported into their database.
The program works fine and does as is required, However it takes a long time to save the data to excel, It extracts from the Database just fine and loads the data into a data grid view, but saving from the datagridview to Excel takes long, sometimes up to 7 minutes. How can I speed up this process ?
Also, why does the Excel process keep running ? Isn't EXL.Quit() as in my code, supposed to kill the process ?
Below is the code I am using to save from DataGridView to Excel.
private void btnSavetoExcel_Click(object sender, EventArgs e)
{
if (gridExtractReport.RowCount < 1)
{
MessageBox.Show("No Data to Save.\r\nPlease Try Again",
"Distributor Sales Management System", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
ExcelSpreadsheet.Application EXL = new ExcelSpreadsheet.Application();
DataSet dset = new DataSet();
dset.Tables.Add();
for (int x = 0; x <= (gridExtractReport.ColumnCount - 1); x++)
{
dset.Tables[0].Columns.Add(gridExtractReport.Columns[x].HeaderText);
}
DataRow drow1;
for (int x = 0; x <= (gridExtractReport.RowCount - 1); x++)
{
drow1 = dset.Tables[0].NewRow();
for (int y = 0; y <= (gridExtractReport.ColumnCount - 1); y++)
{
drow1[y] = gridExtractReport.Rows[x].Cells[y].Value;
}
dset.Tables[0].Rows.Add(drow1);
}
ExcelSpreadsheet.Workbook Wbook;
Wbook = EXL.Workbooks.Add(ExcelSpreadsheet.XlWBATemplate.xlWBATWorksheet);
ExcelSpreadsheet.Worksheet WSheet = (ExcelSpreadsheet.Worksheet)Wbook.ActiveSheet;
WSheet.Cells.Font.Size = 10;
WSheet.Cells.Font.Name = "Arial";
DataTable dt = dset.Tables[0];
DataColumn dc = new DataColumn();
int colIndex = 0, rowIndex = 0;
foreach (DataColumn dcol in dt.Columns)
{
colIndex = colIndex + 1;
EXL.Cells[1, colIndex] = dcol.ColumnName;
}
foreach (DataRow drow in dt.Rows)
{
rowIndex = rowIndex + 1;
colIndex = 0;
foreach (DataColumn dcol in dt.Columns)
{
colIndex = colIndex + 1;
EXL.Cells[rowIndex + 1, colIndex] = drow[dcol.ColumnName];
}
}
saveReport.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
saveReport.Filter = "Microsoft Excel | *.xls";
saveReport.FileName = "DSDPD2extract";
WSheet.get_Range("A1:Q1", Type.Missing).BorderAround(ExcelSpreadsheet.XlLineStyle.xlContinuous,ExcelSpreadsheet.XlBorderWeight.xlThin,ExcelSpreadsheet.XlColorIndex.xlColorIndexAutomatic,System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black));
WSheet.get_Range("A1:Q1", Type.Missing).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
WSheet.get_Range("A1:Q1", Type.Missing).ColumnWidth = 13.29;
WSheet.get_Range("A1:Q1", Type.Missing).HorizontalAlignment = ExcelSpreadsheet.XlHAlign.xlHAlignCenter;
if (saveReport.ShowDialog() == DialogResult.OK)
{
try
{
WSheet.Name = "DSDPD2extract";
WSheet.get_Range("A1:Q1", Type.Missing).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(192, 192, 192));
EXL.ActiveWorkbook.SaveAs(saveReport.FileName, ExcelSpreadsheet.XlFileFormat.xlExcel8, Type.Missing, Type.Missing, false, false, ExcelSpreadsheet.XlSaveAsAccessMode.xlShared, ExcelSpreadsheet.XlSaveConflictResolution.xlLocalSessionChanges, false, Type.Missing, Type.Missing, false);
}
catch (Exception)
{
MessageBox.Show("Error Exporting Report.\r\nPlease Try Again", "Distributor Sales Management System", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
MessageBox.Show("Report Succesfully Saved !", "Distributor Sales Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
EXL.ActiveWorkbook.Close(false, null, null);
EXL.Quit();
}