/// <summary> /// 从Excel中获取数据 /// </summary> /// <param name="workbook"></param> /// <param name="sheet"></param> /// <param name="sheetColumnInfoList"></param> /// <returns></returns> private List <Dictionary <string, string> > GetDataListFromSheet(IWorkbook workbook, ISheet sheet, List <ColumnInfo> sheetColumnInfoList) { List <Dictionary <string, string> > data = null; if (sheet == null) { return(data); } data = new List <Dictionary <string, string> >(); int columnCount = sheetColumnInfoList.Count; int startRow = 2; int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; i++) { IRow row = sheet.GetRow(i); if (row == null) { continue; //没有数据的行默认是null } //一行的数据 Dictionary <string, string> rowData = new Dictionary <string, string>(); int cellCount = Math.Min(row.LastCellNum, columnCount); for (int j = 0; j < cellCount; ++j) { ColumnInfo columnInfo = sheetColumnInfoList[j]; string columnName = columnInfo.ColumnName; if (string.IsNullOrWhiteSpace(columnName)) { continue; } ColumnMd columMd = this.GetColumnMetadata(columnName); if (!rowData.ContainsKey(columnName)) { string value = ""; object cellValue = ExcelUtils.GetCellValue(row.GetCell(j)); if (cellValue is DateTime) { value = string.Format("{0:yyyy-MM-dd HH:mm:ss}", (DateTime)cellValue); } else { value = cellValue.ToStringOrEmpty(); } if (columnInfo.IsNeedDictionary) { //从字典页中读取所对应的Key值 value = this.GetDictionaryKeyByValue(workbook, columMd.DictionarySheetName, columMd, value).ToStringOrEmpty(); rowData.Add(columnName, value); } else { rowData.Add(columnName, value); } } } data.Add(rowData); } return(data); }
/// <summary> /// 从Excel中获取字段名 /// </summary> /// <param name="workbook"></param> /// <param name="sheet"></param> /// <returns></returns> private List <ColumnInfo> GetColumnNameListFromSheet(IWorkbook workbook, ISheet sheet, IEnumerable <FapColumn> entityColumnList) { List <ColumnInfo> columnInfoList = new List <ColumnInfo>(); if (sheet == null) { return(columnInfoList); } //获取标题行的列名 IRow columnTitleRow = sheet.GetRow(0); IRow columnNameRow = sheet.GetRow(1); IRow columnValidateRow = sheet.GetRow(2); int cellCount = columnNameRow.LastCellNum; //一行最后一个cell的编号 即总的列数 for (int i = columnNameRow.FirstCellNum; i < cellCount; ++i) { ICell cell = columnNameRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; //筛选字段名 if (!string.IsNullOrWhiteSpace(cellValue)) { string columnName = cellValue.Trim(); FapColumn column = entityColumnList.Where(c => c.ColName == columnName).FirstOrDefault(); ColumnMd columnMd = this.GetColumnMetadata(columnName); if (column != null) { ColumnInfo columnInfo = new ColumnInfo(); columnInfo.ColumnTitle = column.ColComment; columnInfo.ColumnName = column.ColName; columnInfo.ColumnType = column.ColType; if (column.CtrlType == FapColumn.CTRL_TYPE_REFERENCE || column.CtrlType == FapColumn.CTRL_TYPE_COMBOBOX) { columnInfo.IsNeedDictionary = true; if (columnMd != null) { columnInfo.DictionarySheetName = columnMd.DictionarySheetName; columnInfo.DictionaryExcelRange = columnMd.DictionarySheetRange; columnInfo.DictionaryRowRangeIndex = new Tuple <int, int>(columnMd.StartRowIndex, columnMd.EndRowIndex); //columnInfo.DictionaryRowRangeIndex = columnMd.DictionaryRowRangeIndex; } //ICell validateCell = columnValidateRow.GetCell(i); //if (validateCell != null) //{validateCell.CellFormula // CellRangeAddress dr = new CellRangeAddress(2, 2, i, i); //} } columnInfoList.Add(columnInfo); } else { //如果字段名不存在,则设置为空字符串,用于后面判断 } } } } return(columnInfoList); }