Fastest way to read and write to Excel

Here we’ll discuss efficient ways to write large volumes of data to Excel with the help of some easy tricks.

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 volune 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 :)).