Coding Defined

a blog for those who code

Thursday, 28 July 2016

Inserting Data to Excel file using OLEDB in C#

Home csharp Excel OLEDB Inserting Data to Excel file using OLEDB in C#
July 28, 2016 , ,
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.


Related articles

Read more
by at
Tags , ,

4 comments:

  1. Unknown15 September 2017 at 17:22

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

    Regards

    ReplyDelete
  2. Anonymous28 January 2018 at 08:29

    insert your data

    ReplyDelete
  3. Anonymous26 February 2018 at 10:43

    Choose the appropriate wallpaper on the mood.

    ReplyDelete
  4. Constantine14 September 2021 at 22:24

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

    ReplyDelete