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();
}
/// <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);
}
public ExcelReader(string excelfile, MSOfficeVersion version = MSOfficeVersion.Office2003)
{
Load(excelfile, version);
}