a blog for those who code

Thursday 28 July 2016

Inserting Data to Excel file using OLEDB in C#

In this post we will be discussing about inserting data to excel file using OLEDB in C#. We will be using Microsoft Excel 12.0 Object Library for inserting to the Excel file. For this operation we do not have to use Excel Object, we can directly use OLEDB for inserting into excel.

At first we need to create a connection to our Excel File as shown below

System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""D:\WeightData.xlsx"";Extended Properties=""Excel 12.0;HDR=YES;"";");

If the connection is not correct you might get an error like "Could not find installable ISAM" while opening the connection. To solve this error you need to check that your system has the exact OLEDB version which you are using in the connection string. Other things which people forget is to enclose Extended Properties inside quotes.


After that we will create Oledb Command and execute the query as shoen below

System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = "Insert into [Sheet1$] ([name],[date],weight,privacy) values('Coding Defined', '27/6/2016', '74', 'yes')";
myCommand.ExecuteNonQuery();

While doing this you might encounter an error like "Syntax error in INSERT INTO statement" while executing the query. This is because if you use one or more fields which is a reserved you need to enclose that field in square brackets. In our case it is name and date, so we need to enclose it inside square brackets.


Full Code to Insert data into Excel File is

System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""D:\WeightData.xlsx"";Extended Properties=""Excel 12.0;HDR=YES;"";");

try
{
  myConnection.Open();
  System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
  myCommand.Connection = myConnection;
  myCommand.CommandText = "Insert into [Sheet1$] (name,date,weight,privacy) values('Coding Defined', '27/6/2016', '74', 'no')";
  myCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
  Console.WriteLine(ex.ToString());
}
finally
{
  myConnection.Close();
}

Please Like and Share the CodingDefined.com Blog, if you find it interesting and helpful.


4 comments:

  1. If wanted to insert your data in row 7, how would you do that?

    Regards

    ReplyDelete
  2. insert your data

    ReplyDelete
  3. Choose the appropriate wallpaper on the mood.

    ReplyDelete
  4. getting syntax error in insert into statement exception on cmd.ExecuteNonQuery();

    ReplyDelete