vsto excel workbook project: How to write HUGE datatable into a excel sheet fairly quickly

Question

I have a complex object(tree structure) which I am flattening it out into a datatable to display it on an excel sheet. Datatable is huge and has around 20000 rows and 10000 columns.

Writing the data onto an excel cell one at a time took forever. So, I am converting the complex object into a datatable and then writing it to the excel sheet using the code below.

Is it possible to write 20K rows x 10K columns data to an excel sheet fairly quickly in less than a minute or < 5 minutes? What is the best technique to complete this task fast.

Environment: Visual studio 2010, VSTO excel workbook project, .net framework 4.0, excel 2010/2007

EDIT:

Original source of data is a rest service response in json format. I am then deserializing json response into c# objects and finally flattening it into a datatable.

Using this Code to write datatable to an excel sheet:

Excel.Range oRange;
                var oSheet = Globals.Sheet3;
                int rowCount = 1;
                foreach (DataRow dr in resultsDataTable.Rows)
                {
                    rowCount += 1;
                    for (int i = 1; i < resultsDataTable.Columns.Count + 1; i++)
                    {
                        // Add the header the first time through 
                        if (rowCount == 2)
                        {
                            oSheet.Cells[1, i] = resultsDataTable.Columns[i - 1].ColumnName;
                        }
                        oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
                    }
                }

                // Resize the columns 
                oRange = oSheet.get_Range(oSheet.Cells[1, 1],
                                oSheet.Cells[rowCount, resultsDataTable.Columns.Count]);
                oRange.EntireColumn.AutoFit();

Final Solution: Used a 2D Object array instead of datatable and wrote it to the range.

Answer 1

In addition to freezing Excel's animation, you can, given the data source this is coming from, save yourself the looping through the Excel.Range object, which is bound to be a bottleneck, by instead of writing to a Datatable, write to a string[,], which Excel can use to write to a Range at once. Looping through a string[,] is much faster than looping through Excel cells.

string[,] importString = new string[yourJsonSource.Rows.Count, yourJsonSource.Columns.Count];
//populate the string[,] however you can
for (int r = 0; r < yourJsonSource.Rows.Count; r++)
{
    for (int c = 0; c < yourJsonSource.Columns.Count; c++)
    {
        importString[r, c] = yourJsonSource[r][c].ToString();
    }
}

var oSheet = Globals.Sheet3;
Excel.Range oRange = oSheet.get_Range(oSheet.Cells[1, 1],
            oSheet.Cells[yourJsonSource.Rows.Count, yourJsonSource.Columns.Count]);
oRange.Value = importString;