读取Excel返回DataSet

最新推荐文章于 2020-11-12 15:12:02 发布

QQ58758

最新推荐文章于 2020-11-12 15:12:02 发布

阅读量695

点赞数

分类专栏： .net 文章标签： dataset excel properties exception string path

本文链接：https://blog.csdn.net/qq58758/article/details/6697452

版权

.net 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

#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