I have a dataGridView in a WindowsForms. I want a button to export this dataGridView to an Excel Worksheet.
using Excel = Microsoft.Office.Interop.Excel;
Excel.Application ExcelApp;
Excel.Workbook ExcelWorkBook;
Excel.Worksheet ExcelWorkSheet;
ExcelApp = new Excel.Application();
ExcelWorkBook = ExcelApp.Workbooks.Add(Missing.Value);
ExcelWorkSheet = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
try
{
for (int i = 0; i <= dataGridView1.RowCount - 1; i)
{
for (int j = 0; j <= dataGridView1.ColumnCount - 1; j)
{
DataGridViewCell cell = dataGridView1[j, i];
ExcelWorkSheet.Cells[i 1, j 1] = cell.Value;
}
}
} catch (Exception ex) { /*somestuff*/ }
// save ExcelWorkbook
This code works. But unfortunately the time complexity is bad. So I'm forced to implement a progressbar. If i wouldn't do it, the user would be thinking the program crashed, while exporting a big datagridview. Needless to say, this will of course slow down the export even more. (progressbar code is not included in this question)
I wonder, if there is a method to export a datagrid to an excel faster.
CodePudding user response:
Thanks for the hints to use another third-party library. I was giving EPPLus a chance.
Here is the code:
private void ExportWithEPPlus(string filepath)
{
using (ExcelPackage excelPackage = new ExcelPackage())
{
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet 1");
try
{
for (int i = 0; i <= dataGridView1.RowCount - 1; i)
{
for (int j = 0; j <= dataGridView1.ColumnCount - 1; j)
{
DataGridViewCell cell = dataGridView1[j, i];
worksheet.Cells[i 1, j 1].Value = cell.Value;
}
}
catch (Exception ex) { /*somestuff*/ }
finally
{
FileInfo fi = new FileInfo(@filepath);
excelPackage.SaveAs(fi);
}
}
}
It's fast like hell. The drawback is: It's memory hungry.