Post
Ask Question

Insert Data Into Excel Using ASP.Net

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 
 
If you want to see how to read data from the Excel file using ADO .NET, see my previous article:


Similar Articles