Export Array to Excel worksheet

There are several methods that can be used to export data from your application to excel. I will try to show you some of the most used in c# and VB .net. You need to have Excel installed on the system for this code to run properly. You can either reference the Excel object directly or use Microsoft Office Primary Interop Assemblies to open and manipulate Excel spreadsheets.

Either method you choose to use, you need to ensure you have the proper references to communicate with Excel.

C#

using Microsoft.Office.Interop;

Imports Microsoft.Office.Interop

After establishing the reference to the interop assembly, you can call the COM object just like any .NET object. In this case of Excel, simply instantiate a new Application, Workbook and Sheet object.

C#

.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
Excel._Workbook oWorkBook;
Excel._Worksheet oSheet;

Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
Excel._Workbook oWorkBook;
Excel._Worksheet oSheet;

GC.Collect();

oExcel = new Microsoft.Office.Interop.Excel.Application();

oExcel.Visible = false;
oWorkBook = (Microsoft.Office.Interop.Excel._Workbook)(oExcel.Workbooks.Add(Missing.Value));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWorkBook.ActiveSheet;
string[,] Data = new string[,] { { "", "Norway", "USA" }, { "Aug", "5", "6" }, { "Sep", "8", "9" } };
for (int j = 0; j < Data.GetLength(0); j++)
{
for (int k = 0; k < Data.GetLength(1); k++)
{
oSheet.Cells[j+1, k + 1] = Data.GetValue(j, k);
}
}
string strFile = System.DateTime.Now.Ticks.ToString() + ".xls";
oWorkBook.SaveAs("c:\\" + strFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, false, false, null, null, null);
oWorkBook.Close(null, null, null);
oExcel.Workbooks.Close();
oExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWorkBook);
oSheet = null;
oWorkBook = null;
oExcel = null;
GC.Collect();