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

How to read text/numeric data from Excel in .net

To read mixed mode like numeric and alphanumeric in same column means, oledb connection string should be as below, otherwise jet engine automatically take eighter numeric or text( data type value fill with NULL value).

Connection string: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

here,
HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

"IMEX=1;" tells the driver to always read "intermixed" data columns as text. Note that this option might affect excel sheet write access negative.

Sample Source:
bool GetExcelData(string ExcelFileNameWithPath, out DataSet ds, ref string ErrorMessage)
{
bool bResult = false;
string strQuery = String.Empty;
string strConnectionString = String.Empty;
System.Data.OleDb.OleDbCommand cmd = null;
System.Data.OleDb.OleDbConnection con = null;
System.Data.OleDb.OleDbDataAdapter da = null;

strQuery = "SELECT * FROM [Sheet1$] ";

ds = new DataSet();
try
{

strConnectionString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";", ExcelFileNameWithPath);

if (!System.IO.File.Exists(ExcelFileNameWithPath))
throw new System.Exception("File not found, source file does not exit!");


using (con = new OleDbConnection(strConnectionString))
{
con.Open();
cmd = new OleDbCommand();

cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQuery;

da = new OleDbDataAdapter(cmd);
da.Fill(ds, "RECORDS");

bResult = true;

}
}
catch (Exception exp)
{
//Log error Message
//String.Format("Error occured during data retrive from source, Msg={0}, Trace={1}", exp.Message, exp.StackTrace);
}
finally
{
if ( con != null && con.State ==ConnectionState.Open)
con.Close();
}

return bResult;
}

Source Ref : http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/0d754ec0-e642-44de-9aa2-1e3487db0a2c/