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();
}
}
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment