Display Excel data in ASP.NET Web page

This article will describe you how to show Microsoft Excel data to ASP.NET Web page. This tutorial will read from excel which has data from Northwind database's Products table. This excel sheet has multiple sheets named with Product Category Name. And each sheet will have products depending on 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 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\"

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

Excel sheet names


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

Northwind Product Details in ASP.NET GridView


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.

Speak your mind :
Leave a comment for this article on dotnetbloogers.com
User profile picture on dotnetmentors.com

by stefany at 10/10/2013 3:08:00 PM
Try this .NET Excel API : http://www.aspose.com/.net/excel-component.aspx for converting excel file to html page so you can view your data in webpage.
post comment on dotnetmentors.com