Excel Fast DataGrid Export

Question

Using a datagrid displaying lots of information, always more than 10k rows up to a million rows with 5 or 6 columns. I want to have a button on my WPF app that exports that data to excel file, keeping the same columns structure.

I am using the MicrosoftOffice - Interop =, however, this takes a long time to perform. Is there any faster way to achieve this?

Thanks,

My Excel Export Helper Class:

public static DataTable ConvertToDataTable<T>(IList<T> data)
{
    var properties = TypeDescriptor.GetProperties(typeof(T));
    DataTable table = new System.Data.DataTable();
    foreach (PropertyDescriptor prop in properties)
    {
        table.Columns.Add(prop.Name, 
            Nullable.GetUnderlyingType(prop.PropertyType) ?? 
            prop.PropertyType);
    }
    foreach (T item in data)
    {
        DataRow row = table.NewRow();
        foreach (PropertyDescriptor prop in properties)
        {
            row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
        }
        table.Rows.Add(row);
    }
    return table;
}

public static void ExportToExcel(DataTable tbl, ProgressDialogController dialogController, string excelFilePath = null)
{
    try
    {
        if (tbl == null || tbl.Columns.Count == 0)
            throw new Exception("ExportToExcel: Null or empty input table!\n");
        // load excel, and create a new workbook
        var excelApp = new Microsoft.Office.Interop.Excel.Application();
        excelApp.Workbooks.Add();
        // single worksheet
        Microsoft.Office.Interop.Excel._Worksheet workSheet = excelApp.ActiveSheet;
        // column headings
        for (var i = 0; i < tbl.Columns.Count; i++)
        {
            workSheet.Cells[1, i + 1] = tbl.Columns[i].ColumnName;
            if(dialogController.IsCanceled)
            {
                return;                        
            }
        }
        // rows
        for (var i = 0; i < tbl.Rows.Count; i++)
        {
            // to do: format datetime values before printing
            for (var j = 0; j < tbl.Columns.Count; j++)
            {
                workSheet.Cells[i + 2, j + 1] = tbl.Rows[i][j];
            }
            dialogController.SetProgress((double)i / tbl.Rows.Count);
            if (dialogController.IsCanceled)
            {
                return;
            }
        }
        // check file path
        if (!string.IsNullOrEmpty(excelFilePath))
        {
            try
            {
                // workSheet.SaveAs(excelFilePath);
                workSheet.SaveAs(excelFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Missing.Value,
                    Missing.Value, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                    Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true,
                    Missing.Value);
                excelApp.Quit();
            }
            catch (Exception ex)
            {
                throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n" + ex.Message);
            }
        }
        else
        { // no file path is given
            excelApp.Visible = true;
        }
    }
    catch (Exception ex)
    {
        throw new Exception("ExportToExcel: \n" + ex.Message);
    }
}

All this data is available on SQL Table. Would it be possible to code it to export it from the SQL TABLE to Excel directly? — Daniel Ferreira, Oct 04 '19 at 04:34
Do not try to do this cell-by-cell - that will take forever (as you've already seen...) Create a 2-d array and write that to the sheet in one operation. — Tim Williams, Oct 04 '19 at 04:42
https://stackoverflow.com/questions/536636/write-array-to-excel-range — Tim Williams, Oct 04 '19 at 04:43

score 0 · Answer 1 · answered Oct 04 '19 at 07:31

Instead of Microsoft Interop you could use Open XML SDK to create Excel files. Interop is slower and needs the Excel application to be installed. Open XML is faster and doesn't require the Excel application.

See these samples from Microsoft:

https://learn.microsoft.com/en-us/office/open-xml/how-to-create-a-spreadsheet-document-by-providing-a-file-name

https://learn.microsoft.com/en-us/office/open-xml/how-to-insert-text-into-a-cell-in-a-spreadsheet

score 0 · Answer 2 · answered Oct 11 '19 at 07:52

      //very simple method without external any DLL
      //dg is Datagrid 
      {
        string Destination = ".\.....location\_filename.xls";
        dg.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
        dg.SelectAllCells();


        ApplicationCommands.Copy.Execute(null, dg);
        String resultat = (string)Clipboard.GetData(DataFormats.CommaSeparatedValue);
        String result = (string)Clipboard.GetData(DataFormats.Text);
        //dg.UnselectAllCells();
        System.IO.StreamWriter file1 = new System.IO.StreamWriter(Destination);
        file1.WriteLine(result.Replace(',', ' '));
        file1.Close();
       }

score 0 · Answer 3 · answered Oct 14 '19 at 22:46

Appreciate all the answers guys!

Both solutions seem to work fine, and faster than my first solution. However, after some extra research I have found lots of people use EPPlus, not only its fast, but will work in any computer even if you do not have Excel installed. Plus much easier than the above solutions.

Cheers! https://github.com/JanKallman/EPPlus Export DataTable to Excel with EPPlus How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?

Excel Fast DataGrid Export

3 Answers3