Update excel data using ado.net

If we want to update the excel sheet means we have to give IMEX=2 in ado connection string, other jet engine throw the error like "query is not update-able statement".

ExcelFileNameWithPath=@"C:\Test.xls";
.strConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=2"";", ExcelFileNameWithPath)


Sample code in VB.Net

Public Function UpdateExcelData(ByVal Name As String, ByVal DOB_DATE As String, ByVal ExcelFileNameWithPath As String) As Boolean
Dim bResult As Boolean = False
Dim strQuery As String = String.Format("UPDATE [Sheet1$] SET DOB= '{0}' where NAME_OF_EMP = '{1}' ", DOB_DATE, Name )
Dim strConnectionString As String = String.Empty
Dim cmd As System.Data.OleDb.OleDbCommand = Nothing
Dim ErrorMessage As String
Dim i As Integer
Try

'IMEX Values
'0 is Export mode
'1 is Import mode
'2 is Linked mode (full update capabilities)

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

If (Not System.IO.File.Exists(ExcelFileNameWithPath)) Then
Throw New System.Exception("File not found, source file does not exit!")
End If


Using con As New System.Data.OleDb.OleDbConnection(strConnectionString)
con.Open()
cmd = New System.Data.OleDb.OleDbCommand()

cmd.Connection = con
cmd.CommandType = CommandType.Text
cmd.CommandText = strQuery
i = cmd.ExecuteNonQuery()
If i > 0 then
bResult = True
Else
bResult = False
End If
End Using
Catch exp As Exception
bResult = False
Finally
If (Not cmd Is Nothing And cmd.Connection.State = ConnectionState.Open) Then
cmd.Connection.Close()
End If
End Try

Return bResult
End Function

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/

Convert Dataset value into excel(Xls) file in C#

There was several way to write XLS file, here I have given below one of the simple to way.
Steps:
1) Write all data set value into flat file (comma separated value) format.
2) With the help of com component ("Microsoft excel library") open the flat file and save as excel format.
Write into flat File: