title: How to speed up dumping a DataTable into an Excel worksheet?
body: <p>I have the following routine that dumps a DataTable into an Excel worksheet.  </p>
  
  <pre><code>    private void RenderDataTableOnXlSheet(DataTable dt, Excel.Worksheet xlWk, 
                                      string [] columnNames, string [] fieldNames)
      {
          // render the column names (e.g. headers)
          for (int i = 0; i &lt; columnNames.Length; i++)
              xlWk.Cells[1, i + 1] = columnNames[i];
  
          // render the data 
          for (int i = 0; i &lt; fieldNames.Length; i++)
          {
              for (int j = 0; j &lt; dt.Rows.Count; j++)
              {
                  xlWk.Cells[j + 2, i + 1] = dt.Rows[j][fieldNames[i]].ToString();
              }
          }
      }
  </code></pre>
  
  <p>For whatever reason, dumping DataTable of 25 columns and 400 rows takes about 10-15 seconds on my relatively modern PC.  Takes even longer testers' machines.  </p>
  
  <p>Is there anything I can do to speed up this code?  Or is interop just inherently slow?</p>
  
  <p>SOLUTION:  Based on suggestions from Helen Toomik, I've modified the method and it should now work for several common data types (int32, double, datetime, string).  Feel free to extend it.  The speed for processing my dataset went from 15 seconds to under 1.</p>
  
  <pre><code>    private void RenderDataTableOnXlSheet(DataTable dt, Excel.Worksheet xlWk, string [] columnNames, string [] fieldNames)
      {
          Excel.Range rngExcel = null;
          Excel.Range headerRange = null;
  
          try
          {
              // render the column names (e.g. headers)
              for (int i = 0; i &lt; columnNames.Length; i++)
                  xlWk.Cells[1, i + 1] = columnNames[i];
  
              // for each column, create an array and set the array 
              // to the excel range for that column.
              for (int i = 0; i &lt; fieldNames.Length; i++)
              {
                  string[,] clnDataString = new string[dt.Rows.Count, 1];
                  int[,] clnDataInt = new int[dt.Rows.Count, 1];
                  double[,] clnDataDouble = new double[dt.Rows.Count, 1];
  
                  string columnLetter = char.ConvertFromUtf32("A".ToCharArray()[0] + i);
                  rngExcel = xlWk.get_Range(columnLetter + "2", Missing.Value);
                  rngExcel = rngExcel.get_Resize(dt.Rows.Count, 1);
  
                  string dataTypeName = dt.Columns[fieldNames[i]].DataType.Name;
  
                  for (int j = 0; j &lt; dt.Rows.Count; j++)
                  {
                      if (fieldNames[i].Length &gt; 0)
                      {
                          switch (dataTypeName)
                          {
                              case "Int32":
                                  clnDataInt[j, 0] = Convert.ToInt32(dt.Rows[j][fieldNames[i]]);
                                  break;
                              case "Double":
                                  clnDataDouble[j, 0] = Convert.ToDouble(dt.Rows[j][fieldNames[i]]);
                                  break;
                              case "DateTime":
                                  if (fieldNames[i].ToLower().Contains("time"))
                                      clnDataString[j, 0] = Convert.ToDateTime(dt.Rows[j][fieldNames[i]]).ToShortTimeString();
                                  else if (fieldNames[i].ToLower().Contains("date"))
                                      clnDataString[j, 0] = Convert.ToDateTime(dt.Rows[j][fieldNames[i]]).ToShortDateString();
                                  else 
                                      clnDataString[j, 0] = Convert.ToDateTime(dt.Rows[j][fieldNames[i]]).ToString();
  
                                  break;
                              default:
                                  clnDataString[j, 0] = dt.Rows[j][fieldNames[i]].ToString();
                                  break;
                          }
                      }
                      else
                          clnDataString[j, 0] = string.Empty;
                  }
  
                  // set values in the sheet wholesale.
                  if (dataTypeName == "Int32") 
                      rngExcel.set_Value(Missing.Value, clnDataInt);
                  else if (dataTypeName == "Double")
                      rngExcel.set_Value(Missing.Value, clnDataDouble);                             
                  else
                      rngExcel.set_Value(Missing.Value, clnDataString);
              }
  
  
              // figure out the letter of the last column (supports 1 letter column names)
              string lastColumn = char.ConvertFromUtf32("A".ToCharArray()[0] + columnNames.Length - 1);
  
              // make the header range bold
              headerRange = xlWk.get_Range("A1", lastColumn + "1");
              headerRange.Font.Bold = true;
  
              // autofit for better view
              xlWk.Columns.AutoFit();
  
          }
          finally
          {
              ReleaseObject(headerRange);
              ReleaseObject(rngExcel);
          }
      }
  
      private void ReleaseObject(object obj)
      {
          try
          {
              System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
              obj = null;
          }
          catch
          {
              obj = null;
          }
          finally
          {
              GC.Collect();
          }
      }
  </code></pre>