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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With