Showing posts with label Excel automation. Show all posts
Showing posts with label Excel automation. Show all posts

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();
}
}