Display Excel data in ASP.NET Web page

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

Create 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:\dotnetmentors\"

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 Excel sheet names and add it 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 Products 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 System.Data.OleDb to create connection to Excel file. Add below code in Page_Load event of Default.aspx. Which creates OleDbConnection to "C:\\dotnetmentors\\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:\\dotnetmentors\\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 selected category.

Add below html code for your GridView which will set some default color, backcolor, 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 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 empty string.

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

    return val.ToString(); 
}
    
    

Export GridView to PDF

If you wish to export Product details from GridView to PDF check convert Repeater / Gridview to PDF using iTextSharp

Download source code.