Hosting Article
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.
Open Visual Studio
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.
Right Click on the project
Add New Item
Add 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:

Html code

Step 5: Now design your page as you want and suppose we have a page with 5 Text-Boxes and 1 Button control.
  1. 1st Text-Box For Name.
  2. 2nd Text-Box For Email.
  3. 3rd Text-Box For Mobile No.
  4. 4th Text-Box For Location.
  5. 5th Text-Box For Qualification.
For designing this you need to do the following:

designing

designing code
Step 6: For styling purposes here we have taken some CSS style so I am putting these CSS Styles inside the head tag.

CSS Style inside the head tag

CSS Style
Step 7: Now click on the design.

Aspx design form

Step 8: Now after clicking on the design you will see the design of the page as in the following:
design Form
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:
excel sheet
Step 10: Now double-click on the Submit button and fire the click event of this button.
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:
  1. using System.Data.OleDb;
  2. using System.Data;
Step 11: Now on the click event of the Submit button write the following code.
  1. protected void Button1_Click(object sender, EventArgs e)
  2. {
  3.     string ConStr = “”;
  4.     //getting the path of the file     
  5.     string path = Server.MapPath(“InsertDataExcel.xlsx”);
  6.     //connection string for that file which extantion is .xlsx    
  7.     ConStr = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + path + “;Extended Properties=\”Excel 12.0;ReadOnly=False;HDR=Yes;\””;
  8.     //making query    
  9.     string query = “INSERT INTO [Sheet1$] ([Name], [Email], [MobileNo], [Location], [Qualification]) VALUES(‘” + TextBox1.Text + “‘,'” + TextBox2.Text + “‘,'” + TextBox3.Text + “‘,'” + TextBox4.Text + “‘,'” + TextBox5.Text + “‘)”;
  10.     //Providing connection    
  11.     OleDbConnection conn = new OleDbConnection(ConStr);
  12.     //checking that connection state is closed or not if closed the     
  13.     //open the connection    
  14.     if (conn.State == ConnectionState.Closed)
  15.     {
  16.         conn.Open();
  17.     }
  18.     //create command object    
  19.     OleDbCommand cmd = new OleDbCommand(query, conn);
  20.     int result = cmd.ExecuteNonQuery();
  21.     if (result > 0)
  22.     {
  23.         Response.Write(“<script>alert(‘Sucessfully Data Inserted Into Excel’)</script>”);
  24.     }
  25.     else
  26.     {
  27.         Response.Write(“<script>alert(‘Sorry!\n Insertion Failed’)</script>”);
  28.     }
  29.     conn.Close();
  30. }
Step 12: Now build and run the project and fill in some data.
run the project
And after clicking on “Submit”.
click 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:
open excel file and see
error: Content is protected !!