/// <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>
/// 根据Value值，查找字典Sheet中对应的Key值
/// Key值在第一列， Value值在第二列
/// </summary>
/// <param name="workbook"></param>
/// <param name="dictionarySheetName"></param>
/// <param name="value"></param>
/// <returns></returns>
private object GetDictionaryKeyByValue(IWorkbook workbook, string dictionarySheetName, ColumnMd columnMd, string value)
{
if (dictionarySheetName.IsMissing())
{
return(null);
}
ISheet sheet = workbook.GetSheet(dictionarySheetName);
if (sheet == null)
{
return(null);
}
IRow row = null;
ICell cell = null;
for (int i = columnMd.StartRowIndex - 1; i < columnMd.EndRowIndex; i++)
{
row = sheet.GetRow(i);
cell = row.GetCell(1);
object obj = ExcelUtils.GetCellValue(cell);
if (obj != null && obj.ToString().Trim() == value)
{
return(ExcelUtils.GetCellValue(row.GetCell(0)));
}
}
return("");
}
/// <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);
}
