Hi friends, In this tutorial, I will show you how to Import Excel Data to GridView In ASP.NET. then store it into a dataset stored as DataSets data to be shown in a GridView.

How to Import Excel Data to GridView:

Step 1: Open Visual Studio and Create an ASP.NET Project.

Step 2: Add a WebForm called Default.aspx.

Step 3: Add a Folder called Upload.

Step 4: Add the following file in the Upload folder.

Employee.xlsx:

Step 5: Replace the following code in Default.aspx page.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
 <title>Import Excel Data to GridView</title>
</head>
<body>
 <form id="form1" runat="server">
 Import Excel File: 
 <asp:FileUpload ID="FileUpload1" runat="server" /> 
 <br /> 
 <br /> 
 <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" /> 
 <br /> 
 <br /> 
 <asp:Label ID="Label1" runat="server"></asp:Label> 
 <br /> 
 <asp:GridView ID="gvExcelFile" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None"> 
 <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> 
 <EditRowStyle BackColor="#999999" /> 
 <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> 
 <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> 
 <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> 
 <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> 
 <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> 
 <SortedAscendingCellStyle BackColor="#E9E7E2" /> 
 <SortedAscendingHeaderStyle BackColor="#506C8C" /> 
 <SortedDescendingCellStyle BackColor="#FFFDF8" /> 
 <SortedDescendingHeaderStyle BackColor="#6F8DAE" /> 
 </asp:GridView> 
 </form>
</body>
</html>

Step 6: Open the Code Behind file and add the following namespaces.

using System.Data;
using System.Data.OleDb;
using System.IO;

Step 7: Add the following code Page Load Event.

if (!Page.IsPostBack)
 {
 //Coneection String by default empty 
 string sConnectionString = "";

string sFileExtension = Path.GetExtension("Employee.xlsx").ToLower();
 //getting the path of the file 
 string sPath = Server.MapPath("~/Upload/Employee.xlsx");
 Label1.Text = "Employee.xlsx";
 //saving the file inside the MyFolder of the server

//checking that extantion is .xls or .xlsx 
 if (sFileExtension.Trim() == ".xls")
 {
 //connection string for that file which extantion is .xls 
 sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
 }
 else if (sFileExtension.Trim() == ".xlsx")
 {
 //connection string for that file which extantion is .xlsx 
 sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
 }
 //making query 
 string sQuery = "SELECT * FROM [Sheet1$]";
 //Providing connection 
 OleDbConnection objDbCon = new OleDbConnection(sConnectionString);
 //checking that connection state is closed or not if closed the 
 //open the connection 
 if (objDbCon.State == ConnectionState.Closed)
 {
 objDbCon.Open();
 }

OleDbCommand objCommand = new OleDbCommand(sQuery, objDbCon);
 OleDbDataAdapter objEmployeeDA = new OleDbDataAdapter(objCommand);
 DataSet objEmployeeDS= new DataSet();

objEmployeeDA.Fill(objEmployeeDS);

gvExcelFile.DataSource = objEmployeeDS.Tables[0];
 //binding the gridview 
 gvExcelFile.DataBind();

objDbCon.Close();
 }

Step 8: Double Click the button and add the following Code on Button Click event.

 string sConnectionString = ""; 
 string sExtension = Path.GetExtension(FileUpload1.FileName).ToLower(); 
 string sPath = Server.MapPath("~/Upload/" + FileUpload1.FileName); 
 FileUpload1.SaveAs(sPath);
 Label1.Text = FileUpload1.FileName + "\'s Data showing into the GridView"; 
 if (sExtension.Trim() == ".xls")
 { 
 sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
 }
 else if (sExtension.Trim() == ".xlsx")
 {
 
 sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
 } 
 string sQuery = "SELECT * FROM [Sheet1$]";
 
 OleDbConnection objCon = new OleDbConnection(sConnectionString);
 
 if (objCon.State == ConnectionState.Closed)
 {
 objCon.Open();
 }
 
 OleDbCommand objEmployeeCmd = new OleDbCommand(sQuery, objCon);
 
 OleDbDataAdapter objEmployeeDA = new OleDbDataAdapter(objEmployeeCmd);
 DataSet objEmployeeDS = new DataSet();
 
 objEmployeeDA.Fill(objEmployeeDS); 
 gvExcelFile.DataSource = objEmployeeDS.Tables[0]; 
 gvExcelFile.DataBind(); 
 objCon.Close();

Complete code For Default.aspx.cs file:

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Web.UI;

public partial class _Default : System.Web.UI.Page
{
 protected void Page_Load(object sender, EventArgs e)
 {
 if (!Page.IsPostBack)
 {
 //Coneection String by default empty 
 string sConnectionString = "";

string sFileExtension = Path.GetExtension("Employee.xlsx").ToLower();
 //getting the path of the file 
 string sPath = Server.MapPath("~/Upload/Employee.xlsx");
 Label1.Text = "Employee.xlsx";
 //saving the file inside the MyFolder of the server

//checking that extantion is .xls or .xlsx 
 if (sFileExtension.Trim() == ".xls")
 {
 //connection string for that file which extantion is .xls 
 sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
 }
 else if (sFileExtension.Trim() == ".xlsx")
 {
 //connection string for that file which extantion is .xlsx 
 sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
 }
 //making query 
 string sQuery = "SELECT * FROM [Sheet1$]";
 //Providing connection 
 OleDbConnection objDbCon = new OleDbConnection(sConnectionString);
 //checking that connection state is closed or not if closed the 
 //open the connection 
 if (objDbCon.State == ConnectionState.Closed)
 {
 objDbCon.Open();
 }

OleDbCommand objCommand = new OleDbCommand(sQuery, objDbCon);
 OleDbDataAdapter objEmployeeDA = new OleDbDataAdapter(objCommand);
 DataSet objEmployeeDS= new DataSet();

objEmployeeDA.Fill(objEmployeeDS);

gvExcelFile.DataSource = objEmployeeDS.Tables[0];
 //binding the gridview 
 gvExcelFile.DataBind();

objDbCon.Close();
 }
 }

protected void btnUpload_Click(object sender, EventArgs e)
 {
 
 string sConnectionString = ""; 
 string sExtension = Path.GetExtension(FileUpload1.FileName).ToLower(); 
 string sPath = Server.MapPath("~/Upload/" + FileUpload1.FileName); 
 FileUpload1.SaveAs(sPath);
 Label1.Text = FileUpload1.FileName + "\'s Data showing into the GridView"; 
 if (sExtension.Trim() == ".xls")
 { 
 sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
 }
 else if (sExtension.Trim() == ".xlsx")
 {
 
 sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
 } 
 string sQuery = "SELECT * FROM [Sheet1$]";
 
 OleDbConnection objCon = new OleDbConnection(sConnectionString);
 
 if (objCon.State == ConnectionState.Closed)
 {
 objCon.Open();
 }
 
 OleDbCommand objEmployeeCmd = new OleDbCommand(sQuery, objCon);
 
 OleDbDataAdapter objEmployeeDA = new OleDbDataAdapter(objEmployeeCmd);
 DataSet objEmployeeDS = new DataSet();
 
 objEmployeeDA.Fill(objEmployeeDS); 
 gvExcelFile.DataSource = objEmployeeDS.Tables[0]; 
 gvExcelFile.DataBind(); 
 objCon.Close();
 }
}

Output:

Import Excel Data

View More:

Conclusion:

I hope you would love this post about Import Excel Data To GridView. Please feel free to comment for any technical help. Your feedback and comment are important for me.

Thank You.

Leave a Comment