How to speed up dumping a DataTable into an Excel worksheet?

Posted by AngryHacker on Stack Overflow See other posts from Stack Overflow or by AngryHacker
Published on 2010-04-22T17:18:29Z Indexed on 2010/04/22 17:23 UTC
Read the original article Hit count: 217

Filed under:
|
|
|
|

I have the following routine that dumps a DataTable into an Excel worksheet.

    private void RenderDataTableOnXlSheet(DataTable dt, Excel.Worksheet xlWk, string [] columnNames, string [] fieldNames)
    {
        // render the column names (e.g. headers)
        for (int i = 0; i < columnNames.Length; i++)
            xlWk.Cells[1, i + 1] = columnNames[i];

        // render the data 
        for (int i = 0; i < fieldNames.Length; i++)
        {
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                xlWk.Cells[j + 2, i + 1] = dt.Rows[j][fieldNames[i]].ToString();
            }
        }
    }

For whatever reason, dumping DataTable of 25 columns and 400 rows takes about 10-15 seconds on my relatively modern PC. Takes even longer testers' machines.

Is there anything I can do to speed up this code? Or is interop just inherently slow?

© Stack Overflow or respective owner

Related posts about c#

Related posts about excel