title: vsto excel workbook project: How to write HUGE datatable into a excel sheet fairly quickly
body: <p>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. </p>
  
  <p>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.</p>
  
  <p>Is it possible to write 20K rows x 10K columns data to an excel sheet fairly quickly in less than a minute or &lt; 5 minutes? What is the best technique to complete this task fast.</p>
  
  <p>Environment: Visual studio 2010, VSTO excel workbook project, .net framework 4.0, excel 2010/2007</p>
  
  <p>EDIT:</p>
  
  <p>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. </p>
  
  <p>Using this Code to write datatable to an excel sheet:</p>
  
  <pre><code>Excel.Range oRange;
                  var oSheet = Globals.Sheet3;
                  int rowCount = 1;
                  foreach (DataRow dr in resultsDataTable.Rows)
                  {
                      rowCount += 1;
                      for (int i = 1; i &lt; 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();
  </code></pre>
  
  <p>Final Solution:
  Used a 2D Object array instead of datatable and wrote it to the range. </p>