title: Why does writing to an Excel range take much longer than expected in C# .NET?
body: <p>I've built a script in C# that takes a large data file in CSV format and splits in into two output files in Excel 2007+ format.  I have fully working code that meets all the requirements, but it takes ~15 seconds to run my <code>save_files()</code> method on a relatively small source file.  I would like to know if there is a faster way to do what I'm doing.</p>
  
  <p>The first output ends up writing up to 180 columns of 125,000 points each into an excel file.  (The 15 second run of the code used only 20 columns).  <code>output1_temp_array</code> is a <code>List&lt;string[,]&gt;</code> with each list item containing a string array with the 125k data points.  (It's defined as a 2D array because Excel's <code>Range.Value2</code> expects a 2D array, but really it's 125k x 1 item wide.)</p>
  
  <p>The second output ends up writing 195 columns by 1 row.  The way <code>data_temp_array</code> is populated (it's also a <code>List&lt;string[,]</code>), I have to transpose it into <code>temp_array</code>, and then write that temp_array into the Excel range.  </p>
  
  <p>Here's some of the code:</p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.ComponentModel;
  using System.Data;
  using System.Drawing;
  using System.Linq;
  using System.Text;
  using System.Threading.Tasks;
  using System.Windows.Forms;
  using System.IO;
  using System.Runtime.InteropServices;
  using Microsoft.Office.Interop.Excel;
  using MyExcel = Microsoft.Office.Interop.Excel;
  
  namespace TransposeAThing
  {
      public partial class Form1 : Form
      {
          private void save_files()
          {
              MyExcel.Application excelApp = null;
              MyExcel.Workbook excelWorkbook = null;
              MyExcel.Worksheet worksheet = null;
              MyExcel.Range range = null;
              excelApp = new MyExcel.Application();
              excelApp.DefaultSaveFormat = XlFileFormat.xlOpenXMLWorkbook;
              excelApp.Visible = false;
  
              //Write data into first output file
              excelWorkbook = excelApp.Workbooks.Open(xlsOutput1_Filename, 1, false, 5, "", "", false, MyExcel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
              worksheet = excelWorkbook.Worksheets.get_Item("Output Data");
  
              for (int i = 0; i &lt; output1_temp_array.Count(); i++)
              {
                  range = worksheet.get_Range((MyExcel.Range)worksheet.Cells[1, i + 2], (MyExcel.Range)worksheet.Cells[output1_temp_array[i].Length, i + 2]);
                  range.set_Value(Type.Missing,output1_temp_array[i]);
              }
              excelWorkbook.Save();
              excelWorkbook.Close();
  
              //Write data into second output file
              string[,] temp_array;
              temp_array = new string[1,data_temp_array.Count()];
  
              for (int i = 0; i &lt; data_temp_array.Count(); i++)
              {
                  temp_array[0,i] = data_temp_array[i][0,0];
              }
  
              excelWorkbook = excelApp.Workbooks.Open(xlsData_Filename, 1, false, 5, "", "", false, MyExcel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
              worksheet = excelWorkbook.Worksheets.get_Item("Aggregate Data");
  
              int start_row = worksheet.UsedRange.Rows.Count + 1;
              range = worksheet.get_Range((MyExcel.Range)worksheet.Cells[start_row, 1], (MyExcel.Range)worksheet.Cells[start_row, worksheet.UsedRange.Columns.Count]);
  
              range.Value2 = temp_array;
              excelWorkbook.Save();
  
              GC.Collect();
              GC.WaitForPendingFinalizers();
  
              if (worksheet != null)
              {
                  Marshal.FinalReleaseComObject(worksheet);
              }
              if (excelWorkbook != null)
              {
                  excelWorkbook.Close(true, Type.Missing, Type.Missing);
                  Marshal.FinalReleaseComObject(excelWorkbook);
              }
              if (excelApp != null)
              {
                  excelApp.Quit();
                  Marshal.FinalReleaseComObject(excelApp);
              }
          }
      }
  }
  </code></pre>
  
  <p>Any ideas why it takes so long to complete this process?  Any tips on how to speed it up?</p>
  
  <p>For reference, a Python script I wrote to do the same thing takes about 2.3 seconds on the same data, so I know this could be faster than it is now.</p>