如何从下拉列表中获取分配给数据集的工作表名称 [英] How to get the sheet name assigned to dataset from dropdownlist

问题描述

您好我有要求：我需要从Excel工作表导入数据并在Gridview中显示。我正在使用数据集/数据绑定到网格视图。我能够在这里做到这一点。但是我无法达到以下要求



当我上传Excel工作表时...工作簿中的所有工作表都应该在下拉列表中显示，我会选择我需要哪张纸然后保存。现在，默认数据表中发生的事情是考虑最后编辑的工作表并将其保存到数据库中。

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;

   }

推荐答案

这篇关于如何从下拉列表中获取分配给数据集的工作表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！