读取Excel返回DataSet

来源：互联网发布：网络对战游戏编辑：程序博客网时间：2024/06/05 11:50

#region 读取excel path文件路径
        public DataSet ExcelToDataSet(string path)
        {
            try
            {

                DataSet ds = new DataSet();

                string strCon = string.Empty;
                int strIndex = path.LastIndexOf('.');
                string lastStr = path.Substring(path.LastIndexOf('.'), path.Length - strIndex);

               //根据后缀名判断Excel文件版本取得不同的连接字符串
                if (lastStr == ".xls") //Excel 2003
                    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1';" + "data source=" + path;
                else if (lastStr == ".xlsx") //Excel 2007
                    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Extended Properties='Excel 12.0 Xml;HDR=YES';" + "Data Source=" + path;

                OleDbConnection myConn = new OleDbConnection(strCon);
                myConn.Open();
                var sheetName = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,   //检索excel的架构信息
                                                                  new[] { null, null, null, "Table" });
                string excelName = "Sheet1$";
                for (int i = 0; i < sheetName.Rows.Count; i++)
                {
                    excelName = sheetName.Rows[i]["TABLE_NAME"].ToString();
                }

                OleDbDataAdapter myCommand = new OleDbDataAdapter(string.Format("select * from [{0}]", excelName), myConn);
                myCommand.Fill(ds);
                myConn.Close();
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("读取数据失败,请检查工作表名是否正确");
            }
        }
        #endregion