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.
-
- string filename = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + "\\Detail.xlsx";
-
- 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();
-
- 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();
-
- System.Data.OleDb.OleDbDataAdapter cmd2;
- cmd2 = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", con);
-
- DataSet ds = new System.Data.DataSet();
- cmd2.Fill(ds);
-
- 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.