Example #1
0
        public void Load(string path, MSOfficeVersion versin = MSOfficeVersion.Office2003)
        {
            IExcelDataReader excelReader = versin == MSOfficeVersion.Office2003 ?
                                           ExcelReaderFactory.CreateBinaryReader(File.Open(path, FileMode.Open, FileAccess.Read)) :
                                           ExcelReaderFactory.CreateOpenXmlReader(File.Open(path, FileMode.Open, FileAccess.Read));
            DataSet ds = excelReader.AsDataSet();

            Tables = new DataTable[ds.Tables.Count];
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                Tables[i] = ds.Tables[i];
            }

            excelReader.Close();
        }
Example #2
0
        /// <summary>
        /// 从Excel加载数据表,数据以DataSet集合的形式返回。
        /// </summary>
        /// <param name="path"></param>
        /// <param name="version"></param>
        /// <returns></returns>
        public DataTable[] LoadSheetsFromExcel(string path, MSOfficeVersion version = MSOfficeVersion.Office2007)
        {
            if (!File.Exists(path))
            {
                return(new DataTable[0]);
            }

            string provider = version == MSOfficeVersion.Office2003 ?
                              "Microsoft.Jet.OleDb.4.0" :                                      // offce  199x -- 2000
                              "Microsoft.Ace.OLEDB.12.0";                                      // office 2003 -- 2016
            string strConn = string.Format("Provider={0};Data Source={1};Extended Properties='Excel 12.0;HDR=NO;IMEX=1';", provider, path);

            Console.WriteLine(strConn);
            try
            {
                OleDbConnection cn = new OleDbConnection(strConn);
                cn.Open();

                List <DataTable> dts = new List <DataTable>();
                DataTable        dt  = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                foreach (DataRow dr in dt.Rows)
                {
                    string           sheetName = dr["TABLE_NAME"].ToString();
                    string           strExcel  = "select * from [" + sheetName + "]";
                    DataSet          ds        = new DataSet();
                    OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
                    myCommand.Fill(ds, sheetName);
                    dts.Add(ds.Tables[0]);
                }

                cn.Close();

                return(dts.ToArray());
            }
            catch (Exception e1)
            {
                ErrorMessage = e1.Message;
            }

            return(null);
        }
Example #3
0
 public ExcelReader(string excelfile, MSOfficeVersion version = MSOfficeVersion.Office2003)
 {
     Load(excelfile, version);
 }