CODEWITHSUNDEEP
c#-4.0vsto
Ken
Ken

Reputation: 744

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

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.

Upvotes: 4

Views: 3808

Answers (3)

Mathias
Mathias

Reputation: 15391

I can't speak about using a datatable for the job, but if you want to use Interop, you definitely want to avoid writing cell by cell. Instead, create a 2-d array, and write it at once to a range, which will give you a very significant performance improvement.

Another option you should consider is avoiding interop altogether, and using OpenXML. If you are working with Excel 2007 or above, this is typically a better approach to manipulate files.

Upvotes: 5

Peter Majeed
Peter Majeed

Reputation: 5352

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;

Upvotes: 5

Brijesh Mishra
Brijesh Mishra

Reputation: 2748

VSTO is always gonna take its time, the best tip I can share with you is disable sheet refresh when you populate data, one way to do this is pop up a "Modal" progress dialog box and refresh your sheet in background, this will give you 50-70% better performance. Another thing you can do is update VS to sp1, it helps.

Upvotes: 1

Related Questions