C#读取 excel中的表名sheet名不是默认的sheet1,shee2就取不到值了 c# 如何读取自定义的sheet值？c#读取excel最佳解决方案

亲测

 

              /// <summary>
        /// 不指定sheet
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable_sheet(string fileName,string sheet)
        {
            OleDbConnection conn = null;
            DataTable dt = new DataTable();
            string strCon = "";

            //string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + fileName;

            if (fileName.Substring(fileName.LastIndexOf(".") + 1).ToLower().Equals("xlsx"))
            {
                strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=No;IMEX=1';Data Source=" + fileName;
            }
            else if (fileName.Substring(fileName.LastIndexOf(".") + 1).ToLower().Equals("xls"))
            {
                strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=No;IMEX=1';Data Source=" + fileName;
            }
            else
            {
                MessageBox.Show("选择的文件格式不对！", "错误");
                return null;
            }

 

            try
            {
                string sheetname = "";
                List<string> sheetnamelist = new List<string>();

                conn = new OleDbConnection(strCon);
                conn.Open();
                System.Data.DataTable dt_excel = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                for (int i = 0; i < dt_excel.Rows.Count; i++)
                {//循环取得所有表名
                    sheetname = dt_excel.Rows[i]["TABLE_NAME"].ToString();

                    sheetnamelist.Add(sheetname);
                }
                string CurrSheet = sheetnamelist[0].ToString();//取得第一个表名

                string strCom = "Select * From   [" + CurrSheet + "]";
             
                OleDbDataAdapter adp = new OleDbDataAdapter(strCom, conn);
                adp.Fill(dt);
                return dt;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }