<div> <asp:DropDownList ID="ddlCategories" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlCategories_SelectedIndexChanged" Width="300px"> </div> <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" /> </div>
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { OleDbConnection oconn = null; string FilePath = "D:\\HBL Current Work\\HBLMBI\\Forms\\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(); } } protected void ddlCategories_SelectedIndexChanged(object sender, EventArgs e) { DataTable dtProducts = new DataTable(); OleDbConnection oconn = null; string FilePath = "D:\\HBL Current Work\\HBLMBI\\Forms\\dotnetmentors\\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(); // ocmd.ExecuteNonQuery(); OleDbDataAdapter dr = new OleDbDataAdapter(); dr.SelectCommand = ocmd; dr.Fill(dtProducts); oconn.Close(); gvProducts.DataSource = dtProducts; gvProducts.DataBind(); }
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)