Display Excel data in ASP.NET Web page

Follow the below steps to show Products excel data to ASP.NET Web page.

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\"

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.

<div>
    <asp:DropDownList ID="ddlCategories" runat="server"
            AutoPostBack="true"
            OnSelectedIndexChanged="ddlCategories_SelectedIndexChanged">
    </asp:DropDownList>
</div>
    
            

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();
        oconn.Open();
        dtCategories = oconn.GetOleDbSchemaTable(
                    OleDbSchemaGuid.Tables, null);
        oconn.Close();

        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;
        ddlCategories.DataBind();
    }
}
    
            

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.

 
<div>
    <asp:GridView ID="gvProducts" runat="server" BackColor="#DEBA84"
            BorderColor="#DEBA84"
        BorderStyle="None" BorderWidth="1px" CellPadding="3" 
            CellSpacing="2">
        <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" />
    </asp:GridView>
</div>       

    

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();
    dtProducts.Columns.Add("CategoryName");
    dtProducts.Columns.Add("ProductID");
    dtProducts.Columns.Add("ProductName");
    dtProducts.Columns.Add("SupplierID");
    dtProducts.Columns.Add("QuantityPerUnit");
    dtProducts.Columns.Add("UnitPrice");
    dtProducts.Columns.Add("UnitsInStock");
    dtProducts.Columns.Add("UnitsOnOrder");
    dtProducts.Columns.Add("ReorderLevel");
    dtProducts.Columns.Add("Discontinued");

    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 + "$]",
                                oconn);

    oconn.Open();
    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));
        dtProducts.Rows.Add(drProducts);  
    }

    oconn.Close();

    gvProducts.DataSource = dtProducts;
    gvProducts.DataBind();  
}
    
        

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(); 
}
    
    

Source Code on Github