Display Excel data in ASP.NET Web page

This article will describe to you how to show Microsoft Excel data to the ASP.NET Web page. This tutorial will read from excel which has data from the Northwind database's Products table. This excel sheet has multiple sheets named with Product Category Name. And each sheet will have products depending on the selected category.

All the sheet names will be shown in ASP.NET DropDownList control and on selection change of DropDownList value corresponding Products will display in GridView control.

Create an Excel sheet with Northwind.Products table using Microsoft.Interop.Excel

Create required excel sheet which has multiple sheets with category name and each sheet has corresponding Products. You can go through Export SQL data to excel using Microsoft.Interop.Office.Excel. Put the created excel in "C:\geeksarray\"

Display Products in Microsoft Excel

Create ReadFromExcel Solution

Open your visual studio and click on File -> New -> ASP.NET Web Application Give name as ReadFromExcel and click Ok.

Load all Excel sheet names

In this step, we will read all the Excel sheet names and add them to ASP.NET DropDownList control. On selection change of DropDownList or Category corresponding sheet products will be displayed in GridView control.

Open Default.aspx or any other page where you want to show Product data. Add ASP.NET DropDownList control to aspx page.

    <asp:DropDownList ID="ddlCategories" runat="server"

We will use the System.Data.OleDb to create a connection to Excel file. Add below code in Page_Load event of Default.aspx. Which creates OleDbConnection to "C:\geeksarray\products.xlsx" and read all sheet names to load ddlCategories.

protected void Page_Load(object sender, EventArgs e)
    if (!Page.IsPostBack)
        OleDbConnection oconn = null;
        string FilePath = "C:\geeksarray\Products.xlsx";
        oconn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
                Data Source=" + FilePath + ";
                        Extended Properties=Excel 8.0");

        DataTable dtCategories = new DataTable();
        dtCategories = oconn.GetOleDbSchemaTable(
                    OleDbSchemaGuid.Tables, null);

        List<ListItem> lstCategories = new List<ListItem>();

        string sheetName = string.Empty;
        foreach (DataRow dr in dtCategories.Rows)
            sheetName = dr["TABLE_NAME"].ToString();
            if (!sheetName.Contains("Sheet"))
                lstCategories.Add(new ListItem(
                            sheetName.Replace("$", "")));
        ddlCategories.DataSource = lstCategories;

Excel sheet names

Products GridView

Open Default.aspx html and add GridView control which will display all Products of the selected category.

Add below HTMK code for your GridView which will set some default color, back color, padding, etc.

    <asp:GridView ID="gvProducts" runat="server" BackColor="#DEBA84"
        BorderStyle="None" BorderWidth="1px" CellPadding="3" 
        <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
        <HeaderStyle BackColor="#A55129" Font-Bold="True" 
            ForeColor="White" />
        <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
        <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
        <SelectedRowStyle BackColor="#738A9C" Font-Bold="True"
                ForeColor="White" />
        <SortedAscendingCellStyle BackColor="#FFF1D4" />
        <SortedAscendingHeaderStyle BackColor="#B95C30" />
        <SortedDescendingCellStyle BackColor="#F1E5CE" />
        <SortedDescendingHeaderStyle BackColor="#93451F" />


Load Products in ASP.NET GridView

In this step, we will write code to ddlCategories ddlCategories_SelectedIndexChanged event. Which will select particular Category sheet and load its Products to gvProducts GridView

Add below code to Default.aspx.cs file which creates a DataTable object to hold Products excel sheet data and gvProducts uses DataTable as a data source.

protected void ddlCategories_SelectedIndexChanged(object sender,
                                                        EventArgs e)
    DataTable dtProducts = new DataTable();

    OleDbConnection oconn = null;
    string FilePath = "C:\\Products.xlsx";
    oconn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
            Data Source=" + FilePath + ";
                    Extended Properties=Excel 8.0");
    OleDbCommand ocmd = new OleDbCommand("
            select * from [" + ddlCategories.SelectedItem.Value + "$]",

    OleDbDataReader odr = ocmd.ExecuteReader();

    while (odr.Read())
        DataRow drProducts = dtProducts.NewRow();
        drProducts["CategoryName"] = HandleNull(odr.GetValue(0));
        drProducts["ProductID"] = HandleNull(odr.GetValue(1));
        drProducts["ProductName"] = HandleNull(odr.GetValue(2));
        drProducts["SupplierID"] = HandleNull(odr.GetValue(3));
        drProducts["QuantityPerUnit"] = HandleNull(odr.GetValue(4));
        drProducts["UnitPrice"] = HandleNull(odr.GetValue(5));
        drProducts["UnitsInStock"] = HandleNull(odr.GetValue(6));
        drProducts["UnitsOnOrder"] = HandleNull(odr.GetValue(7));
        drProducts["ReorderLevel"] = HandleNull(odr.GetValue(8));
        drProducts["Discontinued"] = HandleNull(odr.GetValue(9));


    gvProducts.DataSource = dtProducts;

Handle Excel Null Values

Add below function to replace Null values with an empty string.

private string HandleNull(object val)
    if (val == null)
        return string.Empty;

    return val.ToString(); 

Northwind Product Details in ASP.NET GridView

Source code on Git hub Source Code on Github

Speak your mind
Please login to post your comment!

Blog Search

If you like my content please feel free to buy me coffee. Buy Me A Coffee