/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="path">Excel文件路径</param>
/// <param name="objectProps">转换规则</param>
/// <param name="worksheetIndex">获取工作表Index 默认获取第一张工作表内容( Index = 0 )</param>
/// <param name="isContainLieDingYi">是否含有列定义行， 默认true</param>
/// <param name="lieDingYi_RowIndex">首位列定义 RowIndex</param>
/// <param name="lieDingYi_ColumnIndex">首位列定义 ColumnIndex</param>
/// <returns></returns>
public List <T> WorkSheet2List <T>(
string path,
List <PropertyColumn> objectProps,
int worksheetIndex = 0,
bool isContainLieDingYi = true,
int lieDingYi_RowIndex = 0, int lieDingYi_ColumnIndex = 0,
bool ignoreRepeatColumnName = false
) where T : class, new()
{
#region 制需要导入的文件到 \exe目录\Temp\ExcelFiles， 用于解决文件占用问题
string copyToTempDirectory = System.IO.Path.Combine(Environment.CurrentDirectory, "Temp", "ExcelFiles");
if (System.IO.Directory.Exists(copyToTempDirectory) == false)
{
System.IO.Directory.CreateDirectory(copyToTempDirectory);
}
string copyToTempPath = System.IO.Path.Combine(copyToTempDirectory, "{0}.{1}".FormatWith(Guid.NewGuid().ToString(), path.Substring(path.LastIndexOf(".") + 1)));
System.IO.File.Copy(path, copyToTempPath, true);
#endregion 制需要导入的文件到 \exe目录\Temp\ExcelFiles， 用于解决文件占用问题
Workbook workbook = new Aspose.Cells.Workbook(copyToTempPath);
Worksheet worksheet = workbook.Worksheets[worksheetIndex]; // 默认取第一张工作表
var maxRowIndex = worksheet.Cells.MaxDataRow; // 读取工作表中最大的行指针 ( 由0开始 )
var maxColumnIndex = worksheet.Cells.MaxDataColumn; // 读取工作表中最大的列指针 ( 由0开始 )
var rowsCount = maxRowIndex + 1; // 总行数
var columnsCount = maxColumnIndex + 1; // // 总列数
StringBuilder columnNotExist = new StringBuilder();
#region Columns
List <PropertyColumn> header = null;
// 若已定义 Header Index, 则跳过下面方法
if (objectProps.Exists(i => i.ColumnIndex < 0) == true) // 获取工作表列头信息
{
header = this.GetHeader
(
worksheet: worksheet,
columnCount: columnsCount,
lieDingYi_RowIndex: lieDingYi_RowIndex,
lieDingYi_ColumnIndex: lieDingYi_ColumnIndex,
ignoreRepeatColumnName: ignoreRepeatColumnName
);
for (int i = 0; i < objectProps.Count; i++)
{
var matchObjectProps = header.FirstOrDefault(h => h.ExcelColumn == objectProps[i].ExcelColumn);
if (matchObjectProps == null)
{
columnNotExist.AppendLine("找不到列【{0}】;".FormatWith(objectProps[i].ExcelColumn));
}
else
{
objectProps[i].ColumnIndex = matchObjectProps.ColumnIndex;
objectProps[i].ColumnIndexName = Util.Excel.ExcelCommonMethod.ToExcelColumnName(objectProps[i].ColumnIndex);
}
}
}
else if (isContainLieDingYi == false && objectProps.Exists(i => i.ColumnIndex < 0) == true)
{
columnNotExist.Append("无列定义行，且程序员未指定固定的列位置与对象的属性关联。");
}
#endregion
string errorMsg = columnNotExist.ToString();
if (errorMsg.IsNullOrEmpty() == false)
{
workbook = null;
GC.Collect();
throw new Exception(errorMsg);
}
List <T> result = new List <T>();
#region Rows
Type type = typeof(T);
// 若 T 包含 ExcelRowNumber属性 (int)，将 Excel行号赋值给 ExcelRowNumber属性
System.Reflection.PropertyInfo prop_ExcelRowNumber = type.GetProperty("ExcelRowNumber"); // Excel 行号
// 若 T 包含 ExcelRowErrorInfo (string)，将 Excel 每行转换时遇到的问题赋值到本属性
System.Reflection.PropertyInfo prop_ExcelRowErrorInfo = type.GetProperty("ExcelRowErrorInfo"); // 记录读取 Excel 的异常信息
StringBuilder errorInfoStringBuilder = new StringBuilder(); // 每行转换时遇到的问题记录
// 若为无单头, for ( lieDingYi_RowIndex + 1 == > for ( lieDingYi_RowIndex + 0
if (isContainLieDingYi == false)
{
lieDingYi_RowIndex = lieDingYi_RowIndex - 1;
}
for (int rowIndex = lieDingYi_RowIndex + 1; rowIndex <= worksheet.Cells.MaxDataRow; rowIndex++)
{
T item = new T();
if (prop_ExcelRowNumber != null)
{
prop_ExcelRowNumber.SetValue(item, rowIndex + 1, null); // RowNumber = 指针 + 1
}
errorInfoStringBuilder.Clear();
foreach (PropertyColumn itemColumn in objectProps)
{
Cell cell = worksheet.Cells[rowIndex, itemColumn.ColumnIndex];
System.Reflection.PropertyInfo propInfo = type.GetProperty(itemColumn.PropertyName);
if (propInfo == null)
{
throw new Exception("返回结果List<{0}>，{0} 不包含有属性【{1}】。".FormatWith(type.Name, itemColumn.PropertyName));
}
object value = GetValueType(cell);
try
{
PropertyInfoSetValue(propInfo, item, value);
}
catch (Exception ex)
{
if (propInfo.PropertyType.FullName.ToString().IndexOf("System.Nullable") == 0) // 可空属性
{
propInfo.SetValue(item, null, null);
}
else
{
errorInfoStringBuilder.AppendLine("读取【{0}】发生错误（{1}）；".FormatWith(itemColumn.ExcelColumn, ex.Message));
}
}
}
string errorInfo = errorInfoStringBuilder.ToString();
if (prop_ExcelRowErrorInfo != null && errorInfo.IsNullOrEmpty() == false)
{
prop_ExcelRowErrorInfo.SetValue(item, errorInfo, null);
}
result.Add(item);
}
#endregion
workbook = null;
GC.Collect();
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);
}