Fastest way to read and write to Excel

Fastest way to write to Excel

Many times I have to work on generating an Excel sheet and there was a time when the user reported that the generation of Excel reports was quite slow. While researching that I tried some Google searches and discovered that there's a way to write a large volume of data to excel very quickly. After I did the R&D on the code I thought to share it.

Here we'll not discuss how to create an Excel sheet programmatically but we'll see how we can write to Excel a large amount of data in the quickest manner possible. Use the trick mentioned below and impress your management.
Here's goes the code to create an Excel workbook and worksheet:

Microsoft.Office.Interop.Excel.Application xlApp = null;
   Workbook xlWorkbook = null;
   Sheets xlSheets = null;
   Worksheet xlNewSheet = null;
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkbook = xlApp.Workbooks.Open(sourcefile, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing,
               false, XlPlatform.xlWindows, Type.Missing,
               true, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
   // xlWorkbook = xlApp.Workbooks.Add(Type.Missing);
         xlSheets = xlWorkbook.Sheets as Sheets;
 
   // The first argument below inserts the new worksheet as the first one
         xlNewSheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
xlNewSheet.Name= "DummySheet";

Now here's the discussion of what to do after you have accomplished the preceding to create a sheet.

Most of the time we write a loop and then write the data cell by cell. And this makes the application go slower.

If you have your data in a collection then your best method is to write the data using a range.

var startCell = (Range)xlNewSheet.Cells[2, 1];
      var endCell = new object();
      endCell = (Range)xlNewSheet.Cells[numbers.Items.Count, 6];
      var writeRange = xlNewSheet.get_Range(startCell, endCell);
      writeRange.set_Value(Type.Missing, retList);

and after that release everything and quit so you don't loose your data.

xlWorkbook.Save();
      xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
      xlApp.Quit();
and finally release your COM objects.

finally
  { 
      if(xlNewSheet!=null)
      Marshal.ReleaseComObject(xlNewSheet);
      if (xlSheets != null)
      Marshal.ReleaseComObject(xlSheets);
      if (xlWorkbook != null)
      Marshal.ReleaseComObject(xlWorkbook);
      if (xlApp != null)
      Marshal.ReleaseComObject(xlApp);
      xlApp = null;
      KillExcel();
  }

Happy Reporting :)).