Read Excel Sheet Data and Bind With ASP.NET GridView

Sourabh Somani

5y315.5k13100Article

13 Comments

Join the conversation! Your thoughts help the community grow.

Ashokkumar B6y ago
Having "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine." error. But, same code is working in winforms. How to resolve?
Sreekanth Vangara7y ago
Will it read Objects in excel ?
Mukund Malpani7y ago
String ss1 = ""; string path=""; string connectionString = ""; if (FileUpload1.HasFile) { string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName); string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName); string fileLocation = Server.MapPath("~/ProfilePic/" + fileName); FileUpload1.SaveAs(fileLocation); //Check whether file extension is xls or xslx if (fileExtension == ".xls") { connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } else if (fileExtension == ".xlsx") { connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; } //Create OleDB Connection and OleDb Command OleDbConnection con = new OleDbConnection(connectionString); OleDbCommand cmd = new OleDbCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = con; OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd); DataTable dtExcelRecords = new DataTable(); con.Open(); DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString(); cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]"; dAdapter.SelectCommand = cmd; dAdapter.Fill(dtExcelRecords); con.Close(); GridView1.DataSource = dtExcelRecords; TemplateField tfield = new TemplateField(); tfield = new TemplateField(); tfield.HeaderText = "View"; GridView1.Columns.Add(tfield); GridView1.DataBind(); }
zeenat ali8y ago
I have multiple sheets in my Excel Workbook. I want to get the selected sheet in my Gridview. How do I do that ?
yashu v9y ago
How to add pagination to the above code.
Purnima Saini9y ago
Thank you .. Your code helps me alot and its the best code i have found on net.. i have searched the code for excel on youtube and so many websites but those are not working.. your code helps me alot..
Bhupendra Gupta9y ago
I want to in this gridview show how the Excel sheet name fornt of data
Shervin Cyril10y ago
tqu bro..Your codes are working & usefull...:)
- Sourabh Somani10y ago
  Welcome :)
Tahir Aziz10y ago
include using system.io; for path error #Vignesh Madeshwaram
Vignesh Madeshwaran11y ago
getting error in path help me
kshitija mahamuni11y ago
getting an error near 'path ' does not exist in the current context
waqas qazi11y ago
how to define my columns header text programatically when there is no column header text in excel file on data is present to import

Introduction

This article explains how to retrieve data from Excel using ADO.NET then store it into a dataset stored as DataSets data to be shown in a GridView.

I am showing you it step-by-step:

Step 1: Open Visual Studio and create a new ASP.NET project and create a webform.

Step 2: Now on that webform use one FileUploadControl. Using this we will upload an Excel file from our system. Create one button to upload the data to the server, one label to show you what file's data is showing in the grid view, and use one GridView control in which we will show our Excel sheet's data.

So my design aspx page is like; this you can just copy and paste the aspx page given below:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ImportExelDataInGridView.aspx.cs" Inherits="ReadDataFromExcel.ImportExelDataInGridView" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
Import Excel File:
<asp:FileUpload ID="FileUpload1" runat="server" />
<br />
<br />
<asp:Button ID="btnUpload" runat="server" OnClick="btnUpload_Click" Text="Upload" />
<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>
</div>
</form>
</body>
</html>

Using the code above you will get output of the page such as the following:

Step 3: Now on the code behind of this aspx page, use the following two namespaces:

System.Data;
System.Data.OleDb;

Step 4: For the click event of the upload button you should write the following code:

protected void btnUpload_Click(object sender, EventArgs e)
{
//Coneection String by default empty
string ConStr = "";
//Extantion of the file upload control saving into ext because
//there are two types of extation .xls and .xlsx of Excel
string ext = Path.GetExtension(FileUpload1.FileName).ToLower();
//getting the path of the file
string path = Server.MapPath("~/MyFolder/"+FileUpload1.FileName);
//saving the file inside the MyFolder of the server
FileUpload1.SaveAs(path);
Label1.Text = FileUpload1.FileName + "\'s Data showing into the GridView";
//checking that extantion is .xls or .xlsx
if (ext.Trim() == ".xls")
{
//connection string for that file which extantion is .xls
ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (ext.Trim() == ".xlsx")
{
//connection string for that file which extantion is .xlsx
ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//making query
string query = "SELECT * FROM [Sheet1$]";
//Providing connection
OleDbConnection conn = new OleDbConnection(ConStr);
//checking that connection state is closed or not if closed the
//open the connection
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
//create command object
OleDbCommand cmd = new OleDbCommand(query, conn);
// create a data adapter and get the data into dataadapter
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
//fill the Excel data to data set
da.Fill(ds);
//set data source of the grid view
gvExcelFile.DataSource = ds.Tables[0];
//binding the gridview
gvExcelFile.DataBind();
//close the connection
conn.Close();
}