/// <summary>
/// 读取Excel并转换为给定类型数组
/// </summary>
/// <param name="path">读取路径</param>
/// <param name="sheetName">工作表名称</param>
/// <param name="rowIndex">行索引</param>
/// <returns>给定类型数组</returns>
/// <remarks>默认读取第二行</remarks>
public static T[] ReadFile <T>(string path, string sheetName, int rowIndex = 1)
{
#region # 验证
if (string.IsNullOrWhiteSpace(path))
{
throw new ArgumentNullException(nameof(path), "文件路径不可为空！");
}
if (string.IsNullOrWhiteSpace(sheetName))
{
throw new ArgumentNullException(nameof(sheetName), "工作表名称不可为空！");
}
if (rowIndex < 0)
{
rowIndex = 0;
}
#endregion
//01.创建文件流
using (FileStream stream = File.OpenRead(path))
{
//02.创建工作薄
string extensionName = Path.GetExtension(path);
IWorkbook workbook = ExcelConductor.CreateWorkbook(extensionName, stream);
//03.读取给定工作表
ISheet sheet = workbook.GetSheet(sheetName);
//04.返回集合
return(SheetToArray <T>(sheet, rowIndex));
}
}
//Private
#region # 创建工作簿 —— static IWorkbook CreateWorkbook<T>(string extensionName...
/// <summary>
/// 创建工作簿
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="extensionName">扩展名</param>
/// <param name="array">对象数组</param>
/// <param name="titles">标题集</param>
/// <returns>工作簿</returns>
private static IWorkbook CreateWorkbook <T>(string extensionName, T[] array, string[] titles = null)
{
//01.创建工作簿
IWorkbook workbook = ExcelConductor.CreateWorkbook(extensionName);
//02.创建工作表
ISheet sheet = workbook.CreateSheet(typeof(T).Name);
#region //03.创建标题行
IRow rowTitle = sheet.CreateRow(0);
string[] defaultTitles = typeof(T).GetProperties().Select(x => x.Name).ToArray();
if (titles == null)
{
CreateTitleRow(defaultTitles, rowTitle);
}
else
{
#region # 验证
if (titles.Length != defaultTitles.Length)
{
throw new ArgumentOutOfRangeException(nameof(titles), "标题列数与数据列数不一致！");
}
#endregion
CreateTitleRow(titles, rowTitle);
}
#endregion
//04.创建数据行
CreateDataRows(array, sheet);
return(workbook);
}
/// <summary>
/// 读取每一行，并填充对象属性值
/// </summary>
/// <param name="sheet">工作表对象</param>
/// <param name="index">行索引</param>
/// <param name="properties">对象属性集合</param>
/// <param name="instance">对象实例</param>
private static void FillInstanceValue <T>(ISheet sheet, int index, PropertyInfo[] properties, T instance)
{
IFormulaEvaluator formulaEvaluator = ExcelConductor.CreateFormulaEvaluator(sheet.Workbook);
IRow row = sheet.GetRow(index);
#region # 验证
if (properties.Length != row.Cells.Count)
{
throw new InvalidOperationException($"模型与Excel表格不兼容：第{(index + 1)}行 列数不一致！");
}
#endregion
for (int i = 0; i < properties.Length; i++)
{
ICell cell = row.GetCell(i);
string cellValue = cell.ToString().Trim();
#region # 公式与日期时间处理
//公式
if (cell.CellType == CellType.Formula)
{
CellValue formulaValue = formulaEvaluator.Evaluate(cell);
switch (formulaValue.CellType)
{
case CellType.Numeric:
cellValue = formulaValue.NumberValue.ToString();
break;
case CellType.String:
cellValue = formulaValue.StringValue;
break;
case CellType.Boolean:
cellValue = formulaValue.BooleanValue.ToString();
break;
default:
cellValue = formulaValue.StringValue;
break;
}
}
//日期时间
if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
{
cellValue = cell.DateCellValue.ToString(CultureInfo.CurrentCulture);
}
#endregion
if (properties[i].PropertyType == typeof(double))
{
properties[i].SetValueInternal(instance, Convert.ToDouble(cellValue));
}
else if (properties[i].PropertyType == typeof(float))
{
properties[i].SetValueInternal(instance, Convert.ToSingle(cellValue));
}
else if (properties[i].PropertyType == typeof(decimal))
{
properties[i].SetValueInternal(instance, Convert.ToDecimal(cellValue));
}
else if (properties[i].PropertyType == typeof(byte))
{
properties[i].SetValueInternal(instance, Convert.ToByte(cellValue));
}
else if (properties[i].PropertyType == typeof(short))
{
properties[i].SetValueInternal(instance, Convert.ToInt16(cellValue));
}
else if (properties[i].PropertyType == typeof(int))
{
properties[i].SetValueInternal(instance, Convert.ToInt32(cellValue));
}
else if (properties[i].PropertyType == typeof(long))
{
properties[i].SetValueInternal(instance, Convert.ToInt64(cellValue));
}
else if (properties[i].PropertyType == typeof(bool))
{
properties[i].SetValueInternal(instance, Convert.ToBoolean(cellValue));
}
else if (properties[i].PropertyType == typeof(DateTime))
{
properties[i].SetValueInternal(instance, Convert.ToDateTime(cellValue));
}
else
{
properties[i].SetValueInternal(instance, cellValue);
}
}
}
