Home > Mobile >  Create an Excel File from a dataGridView
Create an Excel File from a dataGridView

Time:06-30

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.

  • Related