How to get the sheet name assigned to dataset from dropdownlist

See more:

dataset

DropDownList

Hi I have requirement : where i need to import data from excel sheet and display it in Gridview. I am using dataset/datables to bind to the grid view. I am able to do it up-to here . However I am unable achieve the below requirement

When I upload the excel sheet the..All the sheets present in the workbook should be popluted in dropdown list and i will select which sheet i need and then save it. Now what is happening by default data table consider the last edited sheet and saves it to the database.

protected void btnUpload_Click(object sender, EventArgs e)
   {
       if (FileUpload1.HasFile)
       {
           string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
           string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
           string FolderPath = ConfigurationManager.AppSettings["FolderPath"];

           string FilePath = Server.MapPath(FolderPath + FileName);
           FileUpload1.SaveAs(FilePath);
           Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
       }
   }

   private void Import_To_Grid(string FilePath, string Extension, string isHDR)
   {
       string conStr = "";
       switch (Extension)
       {
           case ".xls": //Excel 97-03
               conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]
                        .ConnectionString;
               break;
           case ".xlsx": //Excel 07
               conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]
                         .ConnectionString;
               break;
       }
       conStr = String.Format(conStr, FilePath, isHDR);
       OleDbConnection connExcel = new OleDbConnection(conStr);
       OleDbCommand cmdExcel = new OleDbCommand();
       OleDbDataAdapter oda = new OleDbDataAdapter();
       DataTable dt = new DataTable();
       ViewState["dt"] = dt;
       cmdExcel.Connection = connExcel;

       //Get the name of First Sheet
       connExcel.Open();
       DataTable dtExcelSchema;
       dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
       int sheetCount = (int)dtExcelSchema.Rows.Count;

       for (int i = 0; i < sheetCount; i++)
       {
           string sheetn = dtExcelSchema.Rows[i]["TABLE_NAME"].ToString();
           ddlSheetName.Items.Add(sheetn);

        //string TABLE_NAME = ddlSheetName.SelectedValue.ToString();

       }

   string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();



       ViewState["tblname"] = SheetName;
       connExcel.Close();


       //Read Data from First Sheet
       connExcel.Open();
       cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
       oda.SelectCommand = cmdExcel;
       oda.Fill(dt);
       connExcel.Close();
       GridView1.Caption = Path.GetFileName(FilePath);
       GridView1.DataSource = dt;
       GridView1.DataBind();


       ViewState["countnoofcolumns"] = dt.Columns.Count;

       colValues = Convert.ToInt16(ViewState["countnoofcolumns"]);


       columnNames = (from dc in dt.Columns.Cast<DataColumn>()
                      select dc.ColumnName).ToArray();

       ViewState["columnNamesstore"] = columnNames;

   }