CODEWITHSUNDEEP
c#excelwinformsperformanceiteration
Wilsu
Wilsu

Reputation: 348

Improve performance of Excel file creation

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".

Upvotes: 1

Views: 1893

Answers (4)

Alex Butenko
Alex Butenko

Reputation: 3764

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.

Upvotes: 2

mnieto
mnieto

Reputation: 3874

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

Upvotes: 0

pCarsten
pCarsten

Reputation: 68

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.

Upvotes: 0

blogprogramisty.net
blogprogramisty.net

Reputation: 1772

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

Upvotes: 1

Related Questions