Friday, May 22, 2009

Exporting datatable to an Excel Sheet

Simple function to export the data in a Datatable to an excel sheet.

#region Custom References
using System.Xml;
using System.IO;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
#endregion


public override bool GenerateOutput(System.Data.DataTable sourceData, string outputfilepath)
{
bool blnSuccess = false;
try
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;

xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlApp.DisplayAlerts = false;
xlApp.Visible = false;

// delete the sheet if already exists
try
{
xlWorkSheet = (Worksheet)xlWorkBook.Sheets["test"];
if (xlWorkSheet != null)
{
xlWorkSheet.Delete();
}
}
catch
{

}

xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xlWorkSheet.Name = "test";

xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item("test");

for (int rowCount = 1; rowCount <= sourceData.Rows.Count; rowCount++)
{
for (int readerColIndex = 1; readerColIndex <= sourceData.Columns.Count; readerColIndex++)
{
xlWorkSheet.get_Range(xlWorkSheet.Cells[rowCount, readerColIndex], xlWorkSheet.Cells[rowCount, readerColIndex]).Value2 = sourceData.Rows[rowCount - 1].ItemArray[readerColIndex - 1].ToString();
}
}

xlWorkBook.SaveAs(outputfilepath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

xlWorkBook.Close(true, misValue, misValue);
xlApp.DisplayAlerts = true;
xlApp.Quit();

releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);

blnSuccess = true;
}
catch (Exception ex)
{
blnSuccess = false;
throw new Exception(ex.ToString());
}

return blnSuccess;
}

public static void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
//MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}

No comments:

Post a Comment