im attempting to export the datagridview to excel(.xls) in a winforms application using visual studio 2010 in C#, the problem being it is taking forever to save, so far i have 4220 rows and 20 columns. Is there a faster way to do this. NOTE: I am populating the datagridview from the saved excel file. I appreciate your help….my save code is as follows:
private void btnSave_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// Get the Header from dataGridView
for (int h = 1; h < dataGridView1.Columns.Count + 1; h++)
{
xlWorkSheet.Cells[1, h] = dataGridView1.Columns[h - 1].HeaderText;
}
// Get the Cell Values
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
DataGridViewCell cell = dataGridView1[j, i];
xlWorkSheet.Cells[i + 2, j + 1] = cell.Value;
}
}
//xlWorkBook.SaveCopyAs("\\FORM TEST.xlsx");
xlWorkBook.SaveAs("\\GB STOCK.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
xlApp = null;
xlWorkBook = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
Best Answer
After having read through various searches and through the answers above i came across this code, it works extremely faster (almost instantaneous) as compared with my orginal where it took just under 2 minutes. I am very grateful for your answers above and i will look into these especially the copy and paste method which was an interesting read. I am a relatively new to this (new hobby) and am only beginning to understand some concepts with regards to exporting datasets etc. I know this is by no means the best way of acheiving what i set out to but it does what i want it to at the moment. Once again thanks to all who have helped, i am learning alot.