/// <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); } } }