Copy DataTable to Excel

Question

I have a DataTable with size m x n and want to copy over all the contents(including column headers) to an excel file that is already open. I have the reference to the Excel.WorkBook and it is known which WorkSheet will the data be copied to.

I know the easiest(and dirtiest way) is:

Excel.WorkSheet outSheet; //set to desired worksheet
int rowIdx = 1;
int colIdx = 1;
//add header row
foreach (DataColumn dc in dt.Columns)
{
    outSheet.Cells[rowIdx, colIdx++] = dc.ColumnName;
}

colIdx = 1; //reset to Cell 1

//add rest of rows
foreach (DataRow dr in dt.Rows)
{
    colIdx = 0;
    foreach (DataColumn dc in dt.Columns)
    {
        outSheet.Cells[rowIdx + 1, colIdx + 1] = dr[colIdx].ToString();
        colIdx++;
    }
    rowIdx++;
}

This works but unfortunately incurs a huge time cost as it needs to access and paste data cell by cell. Is there a better way to accomplish this?

Answer 1

I wrote a small example for you. tl;dr you can assign an array of values to an Excel range. But this one must meet some specifications. credits go to Eric Carter

    Stopwatch sw = new Stopwatch();
    sw.Start();
    Application xlApp = new Application();
    Workbook xlBook = xlApp.Workbooks.Open(@"E:\Temp\StackOverflow\COM_Interop_CS\bin\Debug\demo.xlsx");
    Worksheet wrkSheet = xlBook.Worksheets[1];            

    try
    {
        /// credits go to: 
        /// http://blogs.msdn.com/b/eric_carter/archive/2004/05/04/126190.aspx
        /// 
        /// [cite] when you want to set a range of values to an array, you must declare that array as a 2 
        /// dimensional array where the left-most dimension is the number of rows you are going to set and 
        /// the right-most dimension is the number of columns you are going to set.  
        /// 
        /// Even if you are just setting one column, you can’t create a 1 dimensional array and have it work[/cite]

        Excel.Range range = wrkSheet.Range["A1", "Z100000"];
        int maxRows = 100000, maxCols = 26;
        object[,] values = new object[maxRows, maxCols];

        int counter = 0;
        for (int row = 0; row < maxRows; row++)
        {
            for (int col = 0; col < maxCols; col++)
            {
                values[row, col] = counter++;
            }
        }
        range.Value2 = values;                
    }
    catch (Exception ex)
    {
        Debug.WriteLine(ex.Message);
    }
    xlApp.Visible = true;
    sw.Stop();
    Console.WriteLine("Elapsed: {0}", sw.Elapsed);

I added 100.000 rows and 26 cols in less than 10 seconds. I hope this is appropriate for you!