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