Insert Data Into Excel Using OLEDB In Windows Application Using C#

Today, I am going to explain how to insert a data into Excel, using OLEDB in Windows Application and how to display the data, using DataGridView.

Before we begin, let us check the following prerequisites.

Step 1

Add "Microsoft.Office.Interpol.Excel" references to your project, basically, it is the .DLL file located at C:\Program Files (x86)\Microsoft Visual Studio 12.0\Visual Studio Tools for Office\PIA\Office15



Step 2

Add the mentioned namespace given below.

using System.Data.OleDb;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;

Now, in my scenario, I am just going to enter four textbox values into an Excel, so I have created four textboxes with label names and one DataGridView to display the entered items, as shown below.

 

Create one Excel file with the header and add this Excel file in your project, as shown shown below.

 

 

Now, it's time to write button event coding to add the item into an Excel file; just double click the button and write the code, mentioned below.

// Declare the File name
string filename = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + "\\Detail.xlsx";
// Connection String // Here i am using HDR = YES which means excel first row consider as header
string con = String.Format(@ "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=0;READONLY=FALSE'", filename);
using(OleDbConnection cn = new OleDbConnection(con)) {
    cn.Open();
    // Insert the textbox value to the excel using insert Query
    OleDbCommand cmd1 = new OleDbCommand("INSERT INTO [Sheet1$] " + "([Name],[Age],[Emp ID],[Designation]) " + "VALUES(@value1,@value2,@value3,@values4)", cn);
    cmd1.Parameters.AddWithValue("@value1", textBox1.Text);
    cmd1.Parameters.AddWithValue("@value2", textBox2.Text);
    cmd1.Parameters.AddWithValue("@value3", textBox3.Text);
    cmd1.Parameters.AddWithValue("@value4", textBox4.Text);
    cmd1.ExecuteNonQuery();
    // Get the entered value using  OleDbAdapter
    System.Data.OleDb.OleDbDataAdapter cmd2;
    cmd2 = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", con);
    // copy the Excel value to the DataSet
    DataSet ds = new System.Data.DataSet();
    cmd2.Fill(ds);
    // Finally display the entered Item using dataGriedView
    dataGridView1.DataSource = ds.Tables[0];
}

Just execute the program and enter the textbox value, click the Add button and you will see the result, as shown below.

 

If you get the error mentioned below, while executing the program, just right click an Excel file and go to Properties.

 

Just change the Build Action from None to Content and change the option copy to output directory to always copy and re-excute the program and you will get the expected output.