// IWorkbook
// HSSFWorkbook -- XLS
// XSSFWorkbook -- XLSX
/// <summary>
/// 读取Excel文件
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="fileName">文件名称</param>
/// <returns>DataSet 结果集</returns>
public static DataSet Excel2DataSet(string filePath, ExcelReaderConfig config = null)
{
DataSet result = new DataSet();
IWorkbook iWorkbook = null;
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
if (filePath.EndsWith("xlsx"))
{
iWorkbook = new XSSFWorkbook(fs);
}
else if (filePath.EndsWith("xls"))
{
iWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
}
else
{
throw new Exception("必须保存为Excel标准后缀（.xls, .xlsx）。");
}
int sheetCount = iWorkbook.NumberOfSheets; // 工作表总数
for (int sheetNo = 0; sheetNo < sheetCount; sheetNo++)
{
SheetReadConfig matchReadConfig = null;
ISheet sheet = iWorkbook.GetSheetAt(sheetNo);
if (config != null && config.Config != null)
{
matchReadConfig = config.Config.FirstOrDefault(j => j.SheetName == sheet.SheetName || (j.SheetIndex.HasValue == true && j.SheetIndex == sheetNo));
if (matchReadConfig == null)
{
continue;
}
}
DataTable dt = new DataTable();
#region 表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
if (header == null)
{
continue;
}
List <int> columns = new List <int>();
for (int i = 0; i < header.LastCellNum; i++)
{
if (header.GetCell(i) != null)
{
object obj = NPOIHelper.GetValueType(header.GetCell(i) as ICell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
{
dt.Columns.Add(new DataColumn(obj.ToString()));
}
columns.Add(i);
}
}
#endregion
#region 数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool rowHasValue = false; // TODO : 遇到空行 计数问题未解决
// TODO : 计算行号
foreach (int j in columns)
{
if (sheet.GetRow(i) != null && sheet.GetRow(i).GetCell(j) != null)
{
if (matchReadConfig != null && matchReadConfig.CellReadRule != null && matchReadConfig.CellReadRule.ContainsKey(j))
{
CellType t = CellType.Blank;
if (matchReadConfig.CellReadRule.TryGetValue(j, out t))
{
try
{
switch (t)
{
case CellType.String:
{
dr[j] = sheet.GetRow(i).GetCell(j).StringCellValue;
if (rowHasValue != true && dr[j] != null && string.IsNullOrEmpty(dr[j].ToString()) == false)
{
rowHasValue = true;
}
}
break;
case CellType.Formula:
{
dr[j] = sheet.GetRow(i).GetCell(j).CellFormula;
if (rowHasValue != true && dr[j] != null && string.IsNullOrEmpty(dr[j].ToString()) == false)
{
rowHasValue = true;
}
}
break;
case CellType.DateTime:
{
dr[j] = sheet.GetRow(i).GetCell(j).DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff");
if (rowHasValue != true && dr[j].Equals(DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss.fff")) == false)
{
rowHasValue = true;
}
}
break;
case CellType.Blank:
dr[j] = string.Empty;
break;
default:
dr[j] = string.Empty;
break;
}
}
catch (Exception ex)
{
throw new Exception("NPOIHelperV3 - matchReadConfig.CellReadRule Swicth Throw Exception", ex);
}
}
else
{
dr[j] = NPOIHelper.GetValueType(sheet.GetRow(i).GetCell(j) as ICell);
if (rowHasValue != true && dr[j] != null && dr[j].ToString() != string.Empty)
{
rowHasValue = true;
}
}
}
else
{
// TODO : 读取日期Cell问题仍需解决
dr[j] = NPOIHelper.GetValueType(sheet.GetRow(i).GetCell(j) as ICell);
if (rowHasValue != true && dr[j] != null && dr[j].ToString() != string.Empty)
{
rowHasValue = true;
}
}
}
}
if (rowHasValue)
{
dt.Rows.Add(dr);
}
}
#endregion
result.Tables.Add(dt);
}
}
return(result);
}
/// <summary>
/// 读取Excel文件
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="fileName">文件名称</param>
/// <returns>DataSet 结果集</returns>
public static DataSet Excel2DataSet(string filePath, ExcelReaderConfig config = null)
{
// TO___DO 完善此方法读取数据部分
DataSet result = new DataSet();
Workbook iWorkbook = null;
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
if (filePath.EndsWith("xlsx"))
{
iWorkbook = new Workbook(fs);
}
else if (filePath.EndsWith("xls"))
{
iWorkbook = new Workbook(fs);
}
else
{
throw new Exception("必须保存为Excel标准后缀（.xls, .xlsx）。");
}
int sheetCount = iWorkbook.Worksheets.Count; // 工作表总数
for (int sheetNo = 0; sheetNo < sheetCount; sheetNo++)
{
SheetReadConfig matchReadConfig = null;
Worksheet sheet = iWorkbook.Worksheets[sheetNo];
var TestSheeName = sheet.Name;
if (config != null && config.Config != null) // 跳过不读取的Sheet (名称 或 顺序)
{
// matchReadConfig = config.Config.FirstOrDefault(j => j.SheetName == sheet.SheetName || j.SheetNo == sheetNo);
matchReadConfig = config.Config.FirstOrDefault(j => j.SheetName == sheet.Name || (j.SheetIndex.HasValue == true && j.SheetIndex == sheetNo));
if (matchReadConfig == null)
{
continue;
}
}
DataTable dt = new DataTable();
if (sheet.Cells.Rows.Count <= 0)
{
result.Tables.Add(dt);
continue;
}
bool isFirstRowDefineColumn = true; // TO___DO 加一个配置 定义第一行是否列定义行
#region 表头
if (isFirstRowDefineColumn == true)
{
int cellRow = 0;
int cellColumnCount = sheet.Cells.Columns.Count;
Row header = sheet.Cells.Rows[0];
if (header == null)
{
continue;
}
List <int> columns = new List <int>();
for (int cellColumn = 0; cellColumn < cellColumnCount; cellColumn++)
{
Cell cell = sheet.Cells[cellRow, cellColumn];
object obj = ExcelUtils_Aspose.GetValueType(cell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + cellColumn.ToString()));
}
else
{
dt.Columns.Add(new DataColumn(obj.ToString()));
}
columns.Add(cellColumn);
}
}
#endregion
#region 数据
//for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
//{
// DataRow dr = dt.NewRow();
// bool rowHasValue = false; // TODO : 遇到空行 计数问题未解决
// // TO__DO : 计算行号
// foreach (int j in columns)
// {
// if (sheet.GetRow(i) != null && sheet.GetRow(i).GetCell(j) != null)
// {
// if (matchReadConfig != null && matchReadConfig.CellReadRule != null && matchReadConfig.CellReadRule.ContainsKey(j))
// {
// NPOICellType t = NPOICellType.Blank;
// if (matchReadConfig.CellReadRule.TryGetValue(j, out t))
// {
// try
// {
// switch (t)
// {
// case NPOICellType.String:
// {
// dr[j] = sheet.GetRow(i).GetCell(j).StringCellValue;
// if (rowHasValue != true && dr[j] != null && string.IsNullOrEmpty(dr[j].ToString()) == false)
// {
// rowHasValue = true;
// }
// }
// break;
// case NPOICellType.Formula:
// {
// dr[j] = sheet.GetRow(i).GetCell(j).CellFormula;
// if (rowHasValue != true && dr[j] != null && string.IsNullOrEmpty(dr[j].ToString()) == false)
// {
// rowHasValue = true;
// }
// }
// break;
// case NPOICellType.DateTime:
// {
// dr[j] = sheet.GetRow(i).GetCell(j).DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff");
// if (rowHasValue != true && dr[j].Equals(DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss.fff")) == false)
// {
// rowHasValue = true;
// }
// }
// break;
// case NPOICellType.Blank:
// dr[j] = string.Empty;
// break;
// default:
// dr[j] = string.Empty;
// break;
// }
// }
// catch (Exception ex)
// {
// throw new Exception("NPOIHelperV3 - matchReadConfig.CellReadRule Swicth Throw Exception", ex);
// }
// }
// else
// {
// dr[j] = NPOIHelper.GetValueType(sheet.GetRow(i).GetCell(j) as ICell);
// if (rowHasValue != true && dr[j] != null && dr[j].ToString() != string.Empty)
// {
// rowHasValue = true;
// }
// }
// }
// else
// {
// // TO___DO : 读取日期Cell问题仍需解决
// dr[j] = NPOIHelper.GetValueType(sheet.GetRow(i).GetCell(j) as ICell);
// if (rowHasValue != true && dr[j] != null && dr[j].ToString() != string.Empty)
// {
// rowHasValue = true;
// }
// }
// }
// }
// if (rowHasValue)
// {
// dt.Rows.Add(dr);
// }
//}
#endregion
result.Tables.Add(dt);
}
}
return(result);
}