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.