Display Microsoft Excel File in ASP.NET GridView

You are currently viewing Display Microsoft Excel File in ASP.NET GridView

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.

READ ALSO:  Connection String Encryption in Web.config using VB.NET

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.

Tags: ,

This Post Has 12 Comments

  1. Ketki June 22, 2015 Reply

    Very Helpful..!!! 🙂 Keep it Up…

  2. naseema May 6, 2015 Reply

    hi sir
    how to display excel sheet in pm monitor(hardware)
    by using .net

  3. kavitha December 25, 2013 Reply

    wonderfull.. make it easy to understand,,,

  4. Naman Mehra September 17, 2013 Reply

    Simple and Effective .. Brilliant

  5. kiran April 2, 2012 Reply

    great

  6. Ajay April 8, 2011 Reply

    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,,

  7. yalon January 31, 2011 Reply

    god bless you

  8. sravan September 1, 2010 Reply

    very nice…

  9. kalpana April 30, 2010 Reply

    sir, plz let me know how to hyperlink a pdf document based on gridview search results in asp.net with C#
    thanx

  10. waqas ahmad June 2, 2009 Reply

    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

  11. sapclassic.com June 1, 2009 Reply

    Very informative article. Keep it up.

  12. Rafaquat Butt May 31, 2009 Reply

    sir your website is very knowledgeable for us
    kindly tell me how can insert images in data base through page and retrieve

Leave a Reply