Sometimes the client wants to get the data from the multiple sheets of Excel file and show that the data on the page uss C# in ASP.Net so this article explains how to do that.
Initially I need to create an Excel file to get the data, so there is a file named "MyExcel.xlsx" with 3 sheets with some data like as in the following.
Data of Employees in Employee Sheet with Employee ID and Name Columns.
Data of Students in Student Sheet with Roll Number and Name Columns.
Data of Teachers in Teacher Sheet with Teacher ID and Name Columns.
Now to get the data from the preceding Excel file, I need to work on a page as in the following:
- Add the "FileUpload" Control to upload the file
- Add 3 Gridviews to show the data of all 3 sheets
- Add a button with click event
- Write some code on button click event in the code file
To learn more about this, check the following procedure.
Step 1Add a new "Website" named "Website1".
Add some controls to the default page named "Defaut.aspx".
- Add the "FileUpload" Control to upload the file
- Add 3 Gridviews to show the data of all 3 sheets
- Add a button with click event
- <asp: FileUpload ID = "FileUpload1"
- runat = "server" / > < asp: Button ID = "Button1"
- runat = "server"
- Text = "Load Excel"
- OnClick = "Button1_Click" / > < asp: GridView ID = "GridView1"
- runat = "server" > < /asp:GridView>
- <asp:GridView ID="GridView2" runat="server"></asp: GridView > < asp: GridView ID = "GridView3"
- runat = "server" > < /asp:GridView>
It will look as in the following page.
Step 2
Add 2 namespaces to the top of the code file.
- using System.IO;
- using System.Data.OleDb;
- using System.Data;
- "System.IO" is used for the "File" and "Path" classes to access the Excel file.
- "System.Data.OleDb" is used for the "OleDbConnection" and "OleDbConnection" classes to connect with an Excel file.
- "System.Data" is used for the "DataTable" class.
Note
Microsoft Excel is like a database and OleDb is used to connect with many kinds of databases.
Add the following code to the button Click event.
- protected void Button1_Click(object sender, EventArgs e)
- {
-
- if (Request.Files["FileUpload1"].ContentLength <= 0)
- {
- return;
- }
-
-
- string fileExtension = Path.GetExtension(Request.Files["FileUpload1"].FileName);
-
-
- if (fileExtension != ".xls" && fileExtension != ".xlsx")
- {
- return;
- }
-
-
- string fileLocation = Server.MapPath("\\") + Request.Files["FileUpload1"].FileName;
-
-
- if (File.Exists(fileLocation))
- {
- File.Delete(fileLocation);
- }
-
- Request.Files["FileUpload1"].SaveAs(fileLocation);
-
-
- string strConn = "";
- switch (fileExtension)
- {
- case ".xls":
-
- strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
- break;
- case ".xlsx":
-
- strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0 xml;HDR=Yes;IMEX=1\"";
- break;
- }
-
- BindData(strConn);
-
-
- File.Delete(fileLocation);
- }
Here is the "BindData()" method that will get the sheets data and bind that to the grids.
- private void BindData(string strConn)
{
- OleDbConnection objConn = new OleDbConnection(strConn);
- objConn.Open();
-
-
- DataTable dt = null;
- dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
- objConn.Close();
-
- if (dt.Rows.Count > 0)
{
- int i = 0;
-
-
- foreach(DataRow row in dt.Rows)
{
- DataTable dt_sheet = null;
- dt_sheet = getSheetData(strConn, row["TABLE_NAME"].ToString());
- switch (i)
{
- case 0:
- GridView1.DataSource = dt_sheet;
- GridView1.DataBind();
- break;
- case 1:
- GridView2.DataSource = dt_sheet;
- GridView2.DataBind();
- break;
- case 2:
- GridView3.DataSource = dt_sheet;
- GridView3.DataBind();
- break;
- }
- i++;
- }
- }
- }
Note
The row["TABLE_NAME"] column in the datatable stores the sheet's names sorted alphabetically.
Here is the "getSheetData ()" method that will get the sheet name and return the datatable.
- private DataTable getSheetData(string strConn, string sheet)
{
- string query = "select * from [" + sheet + "]";
- OleDbConnection objConn;
- OleDbDataAdapter oleDA;
- DataTable dt = new DataTable();
- objConn = new OleDbConnection(strConn);
- objConn.Open();
- oleDA = new OleDbDataAdapter(query, objConn);
- oleDA.Fill(dt);
- objConn.Close();
- oleDA.Dispose();
- objConn.Dispose();
- return dt;
- }
Step 3Run the page.
Select the Excel file that I have created first and click on the "Load Excel" Button. Here is the result.