This article explains how to insert data into Excel using an OleDB Connection in an ASP.NET page.
Use the following procedure to create the sample.
Step 1: Open Visual Studio and create a new empty website.
Provide the location and name of the website and click on the "OK" button.
Step 2: Now go to the Solution Explorer and right-click on the project, Select Add and then click on Add New Item.
Step 3: Now one dialog box will be opened; from that select Web Form, provide the name of the web form that you want and click on Add.
Step 4: Now you will see the following code:
Step 5: Now design your page as you want and suppose we have a page with 5 Text-Boxes and 1 Button control.
- 1st Text-Box For Name.
- 2nd Text-Box For Email.
- 3rd Text-Box For Mobile No.
- 4th Text-Box For Location.
- 5th Text-Box For Qualification.
For designing this you need to do the following:
Step 6: For styling purposes here we have taken some CSS style so I am putting these CSS Styles inside the head tag.
Step 7: Now click on the design.
Step 8: Now after clicking on the design you will see the design of the page as in the following:
Step 9: Now create one Excel sheet and put it inside the Solution Explorer. As I have explained above, I am storing the Name, Email, Mobile Number, Location and Qualification so I am creating the Excel sheet as in the following:
Step 10: Now double-click on the Submit button and fire the click event of this button.
Step 10: Before writing the code inside the click event's function, we need to add two namespaces, so add those; the two namespaces are given below:
- using System.Data.OleDb;
- using System.Data;
Step 11: Now on the click event of the Submit button write the following code.
- protected void Button1_Click(object sender, EventArgs e)
- {
- string ConStr = "";
-
- string path = Server.MapPath("InsertDataExcel.xlsx");
-
- ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;ReadOnly=False;HDR=Yes;\"";
-
- string query = "INSERT INTO [Sheet1$] ([Name], [Email], [MobileNo], [Location], [Qualification]) VALUES('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "')";
-
- OleDbConnection conn = new OleDbConnection(ConStr);
-
-
- if (conn.State == ConnectionState.Closed)
- {
- conn.Open();
- }
-
- OleDbCommand cmd = new OleDbCommand(query, conn);
- int result = cmd.ExecuteNonQuery();
- if (result > 0)
- {
- Response.Write("<script>alert('Sucessfully Data Inserted Into Excel')</script>");
- }
- else
- {
- Response.Write("<script>alert('Sorry!\n Insertion Failed')</script>");
- }
- conn.Close();
- }
Step 12: Now build and run the project and fill in some data.
And after clicking on "Submit".
After inserting some data inside Excel, open the Excel file and see that the Excel file has some data that I inserted using my ASP.NET page as in the following:
If you want to see how to read data from the Excel file using ADO .NET, see my previous article:
Muhammad AliPosted May 27, 2021, 2:39 AM
The Microsoft Access database engine could not find the object 'Sheet1$'?
vikash kumarPosted Jan 14, 2020, 1:16 AM
How i will do in Sql Server
sha ismailPosted Nov 3, 2017, 2:29 AM
It's running now. Thank you!
sha ismailPosted Nov 3, 2017, 1:24 AM
Why my data, do not transfer into excel sheet? there is no error but, the data do not transfer into excel sheet
vicky bPosted Sep 7, 2015, 1:59 AM
it gives me error saying "textbox1" doesn't exist
Karipaka AvinashPosted Jul 30, 2015, 1:49 AM
Good Explanation
Kamlesh GuptaPosted Mar 25, 2015, 3:44 AM
Nice Article ,Thanks
vijay kumarPosted Oct 17, 2014, 2:27 AM
Thank you so much :)
Ajay JhaPosted Oct 10, 2014, 1:46 PM
Gud Job Bro ...........
Shaili DashoraPosted Feb 25, 2014, 4:51 AM
Nice Article.