Improve performance of Excel file creation

Question

I have an application with a TabControl. It gets some TabPages, all having a DataGridView that gets filled with a DataTable.
Once the TabControl is filled, I want to be able to export all of the DataGridViews (or rather their DataSources, which are all DataTables) into one Excel file.
I have the following code for that. It works but takes almost a minute.

Button gets clicked:

private void exportBtn_Click(object sender, EventArgs e)
{
    var result = new List<DataTable>();
    foreach (TabPage page in tabControl1.TabPages)
    {
        var dgv = page.Controls[0] as DataGridView;
        if (dgv == null) continue;
        
        var dt = dgv.DataSource as DataTable;
        if (dt == null) continue;
        dt.TableName = page.Text;

        result.Add(dt);
    }

    ExportServices.ToExcel(result);
}

ExportServices looks like this:

internal static class ExportServices
{
    public static void ToExcel(List<DataTable> tables)
    {
        var excelApp = new Microsoft.Office.Interop.Excel.Application();
        excelApp.Workbooks.Add();

        foreach (var table in tables)
        {
            table.AddSheetToExcelApp(excelApp);
        }
        excelApp.Visible = true;
    }
}

The extension method for DataTable, taken from this question:

public static void AddSheetToExcelApp(this DataTable Tbl, Microsoft.Office.Interop.Excel.Application excelApp)
{
    try
    {
        if (Tbl == null || Tbl.Columns.Count == 0)
            throw new Exception("ExportToExcel: Null or empty input table!\n");

        // single worksheet
        _Worksheet workSheet = (_Worksheet)excelApp.Sheets.Add();
        workSheet.Name = Tbl.TableName.Remove(5,1);

        // column headings
        for (int i = 0; i < Tbl.Columns.Count; i++)
        {
            workSheet.Cells[1, (i + 1)] = Tbl.Columns[i].ColumnName;
        }
        // rows
        for (int i = 0; i < Tbl.Rows.Count; i++)
        {
            for (int j = 0; j < Tbl.Columns.Count; j++)
            {
                workSheet.Cells[(i + 2), (j + 1)] = Tbl.Rows[i][j];
            }
        }
    }
    catch (Exception ex)
    {
        throw new Exception("ExportToExcel: \n" + ex.Message);
    }
}

As I said, this code works. But it takes forever to do so. Pausing the execution of the program during random times showed me that most of the time it's doing work in the loop below // rows.
Any way to accelerate this? It would really be not much fun for the user to wait for a minute for just one Excel file.

EDIT: Forgot to mention I can't use any other libraries than the ones that I have installed. Our company is working with very sensitive data so we are not allowed to run any code from the "outside world".

from my experience with `excel interop` it is faster to work with cell ranges instead of individual cells. i suggest to try this answer http://stackoverflow.com/a/21079709/1506454 from the linked question; if range is too large, try to create ranges for each row, for example — ASh, Jan 22 '16 at 12:04

Alex Butenko · Accepted Answer · 2019-05-30T06:28:59.007

2

To set cells one by one is very inefficient. I advise you to set the whole table at once:

object[,] array = new object[Tbl.Rows.Count,Tbl.Columns.Count]
// Fill the array with values then

workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[Tbl.Rows.Count, Tbl.Columns.Count]].Value = array;

Or, at least, to use bigger pieces.

edited May 30 '19 at 06:28

answered Jan 22 '16 at 12:17

Alex Butenko

3,664
3
35
54

Time went down from 56 seconds to <2 seconds. Thanks. – Wilsu Jan 26 '16 at 13:28

score 1 · Answer 2 · answered Jan 22 '16 at 12:11

It is normal that using COM dll to Excel it's take time to do this

Try this. You don't neeed excel on machine. I tested and I used this approach to export data to excel 1 000 000 rows

https://github.com/jsegarra1971/SejExcelExport

Full example how to use it:

http://www.codeproject.com/Tips/829389/Fast-Excel-Import-and-Export

score 0 · Answer 3 · answered Jan 22 '16 at 12:12

I have used three methods over time:

1) write out a csv file, then import that into the excel sheet.

2) put the data into the clipboard, separating values with tabs, then pasting it into Excel (probably using paste special -> no formatting). Whether you can do this obviously depends a lot on the environment in which the programs are running.

3) learn about and use OpenXmlWriter. This is far more flexible than any of the other options, but also has quite a learning curve to get right.

score 0 · Answer 4 · answered Jan 22 '16 at 12:16

0

You can generate the Excel file without executing Excel Application You can use EPPlus library. It's a mature library with lots of functionality

answered Jan 22 '16 at 12:16

mnieto

3,744
4
21
37

Thanks, when looking for a way to export I came across this, too. But as I mentioned in my edit, I can't use third party libraries. – Wilsu Jan 22 '16 at 13:10
I have seen later your restriction. In that case, the best solution is from @AlexButenko. But I allways have set the rows as a tab separated string – mnieto Jan 25 '16 at 09:21

Improve performance of Excel file creation

4 Answers4