//private static readonly int m_maxSheelSize = 65000;
#region 公用静态方法
#region 从Excel读数据
/// <summary>
/// 从Excel读数据
/// </summary>
/// <param name="filePath">excel文档路径</param>
/// <param name="excelVersion">文档版本</param>
/// <param name="pHDR">第一行是否标题</param>
/// <param name="bMerge">
/// 如果有多页，是否合并数据，合并时必须保证多页的表结构一致
/// </param>
/// <returns>DataTable集</returns>
public static DataTable[] GetExcelData(string filePath, ExcelVersion excelVersion, HeadRowType pHDR, bool bMerge)
{
List<DataTable> dtResult = new List<DataTable>();
string connectionString = string.Format(GetConnectionString(excelVersion, ImportOrExportType.Import),
filePath, pHDR);
using (OleDbConnection con = new OleDbConnection(connectionString))
{
con.Open();
string[] sheels = GetExcelWorkSheets(filePath, excelVersion);
foreach (string sheelName in sheels)
{
try
{
DataTable dtExcel = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from [" + sheelName + "$]", con);
adapter.FillSchema(dtExcel, SchemaType.Mapped);
adapter.Fill(dtExcel);
dtExcel.TableName = sheelName;
dtResult.Add(dtExcel);
}
catch
{
//容错处理：取不到时，不报错，结果集为空即可。
}
}
//如果需要合并数据，则合并到第一张表
if (bMerge)
{
for (int i = 1; i < dtResult.Count; i++)
{
//如果不为空才合并
if (dtResult[0].Columns.Count == dtResult[i].Columns.Count &&
dtResult[i].Rows.Count > 0)
{
dtResult[0].Load(dtResult[i].CreateDataReader());
}
}
}
}
return dtResult.ToArray();
}
//private static readonly int m_maxSheelSize = 65000;
#region 公用静态方法
#region 从Excel读数据
/// <summary>
/// 从Excel读数据
/// </summary>
/// <param name="filePath">excel文档路径</param>
/// <param name="excelVersion">文档版本</param>
/// <param name="pHDR">第一行是否标题</param>
/// <param name="bMerge">
/// 如果有多页，是否合并数据，合并时必须保证多页的表结构一致
/// </param>
/// <returns>DataTable集</returns>
public static DataTable[] GetExcelData(string filePath, ExcelVersion excelVersion, HeadRowType pHDR, bool bMerge)
{
List <DataTable> dtResult = new List <DataTable>();
string connectionString = string.Format(GetConnectionString(excelVersion, ImportOrExportType.Import),
filePath, pHDR);
using (OleDbConnection con = new OleDbConnection(connectionString))
{
con.Open();
string[] sheels = GetExcelWorkSheets(filePath, excelVersion);
foreach (string sheelName in sheels)
{
try
{
DataTable dtExcel = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from [" + sheelName + "$]", con);
adapter.FillSchema(dtExcel, SchemaType.Mapped);
adapter.Fill(dtExcel);
dtExcel.TableName = sheelName;
dtResult.Add(dtExcel);
}
catch
{
//容错处理：取不到时，不报错，结果集为空即可。
}
}
//如果需要合并数据，则合并到第一张表
if (bMerge)
{
for (int i = 1; i < dtResult.Count; i++)
{
//如果不为空才合并
if (dtResult[0].Columns.Count == dtResult[i].Columns.Count &&
dtResult[i].Rows.Count > 0)
{
dtResult[0].Load(dtResult[i].CreateDataReader());
}
}
}
}
return(dtResult.ToArray());
}