ASP.NET - how to load excel file sheet name and column name to dropdownlist in asp.net C#?

Asked By prakash rg on 08-May-13 05:40 AM
Actually, I want to bind the data from excel to gridview, In those cases, Load sheet name of excel to one dropdown list and column name of sheet to another dropdownlist. Below coding works properly. But i want to bind data based on dropdownlist. 

Ex: If i select first dropdown (SHEET1$)- Automatically ,second dropdown shows the column name of (SHEET1$).
And finally bind the gridview deponds on dropdownlist selection.

please help me if u can.....

Default.aspx:

<table>
            <tr>
                <td colspan="1">
                    <asp:Label ID="lblFile" runat="server" Text="Select File"></asp:Label></td>
                <td colspan="2">
                    <asp:FileUpload ID="FileUpload1" runat="server" /></td>
            </tr>
            <tr>
                <td>
                </td>
                <td colspan="2">
                    <asp:Button ID="btnImportExcel" runat="server" OnClick="btnImportExcel_Click" Text="Upload" /></td>
            </tr>
             </table>
              <asp:Label ID="lwarning" runat="server"></asp:Label>
             <br />
        <asp:Label ID="Label1" runat="server" Text="SheetName"></asp:Label>
        <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" 
            onselectedindexchanged="DropDownList1_SelectedIndexChanged">
        </asp:DropDownList>
        <asp:Label ID="Label2" runat="server" Text="FieldName"></asp:Label>
        <asp:DropDownList ID="DropDownList2" runat="server">
        </asp:DropDownList>
        <asp:Button ID="Button1" runat="server" Text="bind" onclick="Button1_Click" />
      
                    <asp:GridView ID="GridView1" runat="server" >
                        <%--<Columns>
                            <asp:BoundField DataField="Id" HeaderText="ID" />
                            <asp:BoundField DataField="EmpName" HeaderText="EMP NAME" />
                            <asp:BoundField DataField="EmpAddress" HeaderText="EMP ADDRESS" />
                            <asp:BoundField DataField="EmpCity" HeaderText="EMP CITY" />
                        </Columns>--%>


                    </asp:GridView>
            
                   
       Default.aspx.cs:

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.Services;


public partial class _Default : System.Web.UI.Page 
{


    protected void Page_Load(object sender, EventArgs e)
    {
        
    }


    protected void btnImportExcel_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string filelocation = Server.MapPath("~/" + filename);
            FileUpload1.SaveAs(filelocation);
            GetExcelSheets(filelocation, extension);
            lwarning.Text = "File Uploaded Successfully";
        }
        else
            lwarning.Text = "Please select a file";
    }

    private void GetExcelSheets(string filelocation, string extension)
    {
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " + "Data Source='" + FileUpload1.PostedFile.FileName.ToString() + "';" + " Extended Properties=Excel 8.0;";
        strConn = String.Format(strConn, filelocation);
        OleDbConnection connExcel = new OleDbConnection(strConn);
        OleDbCommand cmdExcel = new OleDbCommand();
        cmdExcel.Connection = connExcel;
        connExcel.Open();

        //Bind the Sheets to DropDownList
        DropDownList1.Items.Clear();
        DropDownList1.DataSource = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        DropDownList1.DataTextField = "TABLE_NAME";
        DropDownList1.DataValueField = "TABLE_NAME";
        DropDownList1.DataBind();
        connExcel.Close();


        connExcel.Open();
        DropDownList2.Items.Clear();
       DropDownList2 .DataSource =connExcel .GetOleDbSchemaTable (OleDbSchemaGuid .Columns ,null );
        DropDownList2 .DataTextField ="COLUMN_NAME";
        DropDownList2 .DataValueField ="COLUMN_NAME";
        DropDownList2 .DataBind ();
        connExcel.Close();
    }