Microsoft Excel is still very powerful and widely used tool in almost all the companies. Many people use it extensively to store and analyze tabular data without learning a relational database management system such as SQL Server or Oracle. If you are creating ASP.NET website, there are chances that the data is made available to you in an Excel file and reading and displaying Excel data in ASP.NET page can make your applications more powerful for analysis. In this tutorial I will show you how to read Excel file and displaying data in ASP.NET GridView control.
For the purpose of this tutorial, I have created a sample Excel sheet “SearchEngines.xls” that holds the records of different search engines. Make sure you have saved this file in your ASP.NET website special folder named App_Data so that we can interact with it from ASP.NET page without worrying about its absolute path.
Next I have created ASP.NET page which is very straight forward with only one Button and GridView control on it. Following is the HTML source for the (.aspx) file.
<form id="form1" runat="server">
<asp:button id="Button1" runat="server" onclick="Button1_Click" text="Display Excel Data" />
<br>
<br>
<asp:gridview id="GridView1" runat="server" cellpadding="6" gridlines="None"
bordercolor="#336699" borderstyle="Solid" borderwidth="1px">
<headerstyle backcolor="#336699" font-bold="True" forecolor="White" />
</asp:gridview>
</form>
In the C# code of the button Click event I will connect and read Excel file and then I will bind the data to GridView control. To connect Excel file you need to use Microsoft Jet OLEDB data provider as you can see from the following connection string. The path of the excel file is provided by using ASP.NET DataDirectory feature which points to the App_Data folder in your Website.
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|SearchEngines.xls;Extended Properties=’Excel 8.0;HDR=Yes;’You can make sure of the OLEDB data provider to treat the Excel file as a data source. Where Excel Workbook can be assumed as the Database and the sheets in the Workbook are considered as tables. In this way, you can fire SQL queries to the Excel file to retrieve data from the sheets. Following code shows you how to connect and read data from Excel using OleDbDataAdapter class available in System.Data.OleDb namespace.
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|SearchEngines.xls;Extended Properties='Excel 8.0;HDR=Yes;'";
string query = "SELECT * FROM [Sheet1$]";
DataSet excelDataSet = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(query, strConn);
da.Fill(excelDataSet);
GridView1.DataSource = excelDataSet;
GridView1.DataBind();
The output of the above code can be shown below.
I hope this tutorial will help many of you specially those who are new in ASP.NET and want to connect Excel from ASP.NET page.
Very Helpful..!!! 🙂 Keep it Up…
hi sir
how to display excel sheet in pm monitor(hardware)
by using .net
wonderfull.. make it easy to understand,,,
Simple and Effective .. Brilliant
great
hi,
tried above code, but this give a error about “Cannot update. Database or object is read-only. “
I check my file proparty but it not read only,
can u help me,,
god bless you
very nice…
sir, plz let me know how to hyperlink a pdf document based on gridview search results in asp.net with C#
thanx
Sir where this code needed to be inserted.
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|SearchEngines.xls;Extended Properties=’Excel 8.0;HDR=Yes;’
Regards,
Waqas Ahmad
Very informative article. Keep it up.
sir your website is very knowledgeable for us
kindly tell me how can insert images in data base through page and retrieve