Thursday, February 17, 2011
How to export the csv data in to EXCEL format with late binding
private void WriteIntoExcelFile()
{
//Define Missing Args
object misValue = Type.Missing;
int xlWorkbookNormal = -4143; //Excel 2000
int xlExcel8 = 56; //Excel 2003
int xlExcel9795 = 43; //Excel 2007
string SourceCSVFileName = @"C:\test.csv";
string ExcelFileName = @"C:\test.xls";
Object[] FileOpenArgs;
Object[] FileSaveArgs;
Object objVersion;
double Version;
Object ExcelApp;
if (System.IO.File.Exists(ExcelFileName))
System.IO.File.Delete(ExcelFileName);
try
{
Type ExAppType = Type.GetTypeFromProgID("Excel.Application");
//Get Excel Application
ExcelApp = Activator.CreateInstance(ExAppType);
//Get Version information
objVersion = ExcelApp.GetType().InvokeMember("Version", System.Reflection.BindingFlags.GetProperty, null, ExcelApp, null, null);
ExcelApp.GetType().InvokeMember("Visible", System.Reflection.BindingFlags.SetProperty, null, ExcelApp, new Object[] { true }, null);
//Argument Fillup based on version information
Version = Convert.ToDouble(objVersion);
if (Version < 9.0)
{
return;
}
else if (Version == 9.0)
{
FileOpenArgs = new Object[] { SourceCSVFileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue };
FileSaveArgs = new Object[] { ExcelFileName, xlWorkbookNormal, misValue, misValue, misValue, misValue, misValue, misValue, misValue };
}
else if (Version > 9.0 && Version < 12.0)
{
FileOpenArgs = new Object[] { SourceCSVFileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue };
FileSaveArgs = new Object[] { ExcelFileName, xlExcel9795, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue };
}
else
{
FileOpenArgs = new Object[] { SourceCSVFileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue };
FileSaveArgs = new Object[] { ExcelFileName, xlExcel8, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue };
}
//Get WorkBooks instance
Object objXlWbks = ExcelApp.GetType().InvokeMember("Workbooks", System.Reflection.BindingFlags.GetProperty, null, ExcelApp, null);
//Open Csv as Workbook and Get instance
Object objXlNewWbk = objXlWbks.GetType().InvokeMember("Open", System.Reflection.BindingFlags.InvokeMethod, null, objXlWbks, FileOpenArgs);
//Get WorkSheek Collection
Object objXlSheets = objXlNewWbk.GetType().InvokeMember("Worksheets", System.Reflection.BindingFlags.GetProperty, null, objXlNewWbk, null);
//Get First WorkSheet
Object[] args2 = new Object[1];
args2[0] = 1;
Object objXlSheet = objXlNewWbk.GetType().InvokeMember("Item", System.Reflection.BindingFlags.GetProperty, null, objXlSheets, args2);
objXlNewWbk.GetType().InvokeMember("Name", System.Reflection.BindingFlags.SetProperty, null, objXlSheet, new Object[] { "QueryData" });
//
Object objXlColumns = objXlNewWbk.GetType().InvokeMember("Columns", System.Reflection.BindingFlags.GetProperty, null, objXlSheet, null);
Object objXlEntireColumn = objXlNewWbk.GetType().InvokeMember("EntireColumn", System.Reflection.BindingFlags.GetProperty, null, objXlColumns, null);
Object objXl123 = objXlNewWbk.GetType().InvokeMember("AutoFit", System.Reflection.BindingFlags.InvokeMethod, null, objXlEntireColumn, null);
//Save into XLS file format
objXlSheet = objXlSheet.GetType().InvokeMember("SaveAs", System.Reflection.BindingFlags.InvokeMethod, null, objXlSheet, FileSaveArgs);
objXlNewWbk.GetType().InvokeMember("Close", System.Reflection.BindingFlags.InvokeMethod, null, objXlNewWbk, null);
ExcelApp = ExcelApp.GetType().InvokeMember("Quit", System.Reflection.BindingFlags.InvokeMethod, null, ExcelApp, null, null);
}
catch (Exception e)
{
Console.WriteLine("Error Stack {0}\n{1} ", e.Message, e.InnerException);
}
finally
{
//When this object is destroyed the Excel application will be closed
//So Sleep for sometime and see the excel application
//Relaese the object
GC.Collect();
}
Console.ReadLine();
}
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment