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