Vsto excel workbook project: How to quickly write a HUGE datatable to an excel sheet

Question

I have a complex object (tree structure) that I flatten it as a datatable in order to display it on an excel sheet. The datatable is huge and has about 20,000 rows and 10,000 columns.

Writing data to the excel cell one at a time was accepted forever. So, I convert the complex object to datatable and then write it to excel sheet using the following code.

Is it possible to write 20K rows x 10K columns per excel sheet quite quickly in less than a minute or, 5 minutes? What is the best way to quickly complete this task.

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

EDIT:

The source data source is the response of the recreation service in json format. Then I deserialize the json response into C # objects and finally flatten it into a datatable.

Using this code to write data 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 an array of 2D objects instead of a datatable and wrote it in a range.

Answer 1

In addition to freezing Excel animations, you can, given the data source from which this comes from, save yourself in a loop through the Excel.Range object, which should be a bottleneck, instead of writing to Datatable , write to string[,] , which Excel can use to write to Range immediately. A loop through string[,] much faster than a loop 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; 

Answer 2

I can't talk about using data to work, but if you want to use Interop, you definitely want to avoid writing cell by cell. Instead, create a 2-dimensional array and immediately write to the range , which will give you a very significant performance improvement.

Another option you should consider is to avoid interactions in general and use OpenXML . If you are working with Excel 2007 or higher, this is usually the best approach to file management.

Answer 3

VSTO will always waste its time, the best advice I can share with you is to disable sheet updating when filling out data, one way to do this is to open the "Modal" dialog box and update your sheet in the background, this will give you 50- 70% better performance. Another thing you can do is upgrade VS to sp1, this helps.