/// <summary>
/// 读取excel模板，初始化excel数据
/// </summary>
/// <param name="HeaderRowIndex"></param>
/// <returns></returns>
private List <ExcelModel> RenderDataTableFromExcel(int HeaderRowIndex)
{
ISheet sheet = hssfworkbook.GetSheetAt(0);
IRow headerRow = sheet.GetRow(HeaderRowIndex);
cellCount = headerRow.LastCellNum;
//这种取法暂时有问题，未做调整，实际应该取的是包含列最多的行
int rowCount = sheet.LastRowNum;
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
if (i == HeaderRowIndex)
{
continue;
}
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
for (int j = row.FirstCellNum; j < cellCount; j++)
{
ExcelModel model = new ExcelModel();
if (row.GetCell(j) != null)
{
string value = row.GetCell(j).StringCellValue.Trim();
if (string.IsNullOrEmpty(value))
{
continue;
}
if (value.StartsWith("#"))
{
model.ColumnName = value.Substring(value.IndexOf('#') + 1, value.Length - 2);
model.ColumnType = row.GetCell(j).CellType;
model.ColumnIndex = row.GetCell(j).ColumnIndex;
model.ColumnStyle = row.GetCell(j).CellStyle;
model.RowIndex = row.RowNum;
if (value.EndsWith("$"))//行循环
{
model.SetValueType = 1;
}
else if (value.EndsWith("~"))//列循环
{
model.SetValueType = 2;
}
else if (value.EndsWith("#"))
{
model.SetValueType = 0;
}
list.Add(model);
}
}
}
}
return(list);
}
/// <summary>
/// 解析采集报表模板 Author:彭皓 2017-04-14
/// </summary>
/// <param name="indexRow">开始行</param>
public Dictionary <int, List <ExcelModel> > RenderDataTableFromExcel2(int indexRow)
{
Dictionary <int, List <ExcelModel> > map = new Dictionary <int, List <ExcelModel> >();
ISheet sheet = hssfworkbook.GetSheetAt(0);
hssfworkbook.SetActiveSheet(0);
sheet.IsSelected = true;
IRow headerRow = sheet.GetRow(indexRow);
cellCount = headerRow.LastCellNum;
int rowCount = sheet.LastRowNum;
//XSSFFormulaEvaluator eval = new XSSFFormulaEvaluator((XSSFWorkbook) hssfworkbook);
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
for (int j = row.FirstCellNum; j < cellCount; j++)
{
ExcelModel model = new ExcelModel();
if (row.GetCell(j) != null)
{
string value = "";
if (row.GetCell(j).CellType == CellType.Numeric)
{
value = row.GetCell(j).NumericCellValue.ToString().Trim();
}
/*else if (row.GetCell(j).CellType == CellType.Formula)
* {
* value = row.GetCell(j).CellFormula;
* value = eval.EvaluateFormulaCell(row.GetCell(j)).ToString();
* }*/
else
{
value = row.GetCell(j).StringCellValue.Trim();
}
if (string.IsNullOrEmpty(value))
{
continue;
}
if (value.StartsWith("#"))
{
model.ColumnName = value.Substring(value.IndexOf('#') + 1, value.IndexOf('}') - 3);
string index = value.Substring(value.IndexOf('{') + 1, 1);
model.DataIndex = int.Parse(index);
model.ColumnType = row.GetCell(j).CellType;
model.ColumnIndex = row.GetCell(j).ColumnIndex;
model.ColumnStyle = row.GetCell(j).CellStyle;
model.RowIndex = row.RowNum;
//FileLog.WriteLog(model.ColumnName + ":第" + model.DataIndex+"行数据,Row="+ model.RowIndex + ",Column" + model.ColumnIndex);
if (value.EndsWith("$"))//行循环
{
model.SetValueType = 1;
}
else if (value.EndsWith("~"))//列循环
{
model.SetValueType = 2;
}
else if (value.EndsWith("#"))
{
model.SetValueType = 0;
}
if (!map.ContainsKey(model.DataIndex))
{
map.Add(model.DataIndex, new List <ExcelModel>());
}
map[model.DataIndex].Add(model);
}
}
}
}
return(map);
}
private List <ExcelModel> RenderDataTableFromExcel(int sheetIndex, string a)
{
ISheet sheet = hssfworkbook.GetSheetAt(sheetIndex);
hssfworkbook.SetActiveSheet(sheetIndex);
sheet.IsSelected = true;
IRow headerRow = null;
// modify by luqy
if (sheetIndex == 7 || sheetIndex == 8)
{
headerRow = sheet.GetRow(1);
}
else
{
headerRow = sheet.GetRow(2);
}
cellCount = headerRow.LastCellNum;
//这种取法暂时有问题，未做调整，实际应该取的是包含列最多的行
for (int n = 0; n < sheet.LastRowNum; n++)
{
headerRow = sheet.GetRow(n);
if (headerRow != null)
{
if (headerRow.LastCellNum > cellCount)
{
cellCount = headerRow.LastCellNum;
}
}
}
int rowCount = sheet.LastRowNum;
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
for (int j = row.FirstCellNum; j < cellCount; j++)
{
ExcelModel model = new ExcelModel();
if (row.GetCell(j) != null)
{
string value = "";
if (row.GetCell(j).CellType == CellType.Numeric)
{
value = row.GetCell(j).NumericCellValue.ToString().Trim();
}
else
{
value = row.GetCell(j).StringCellValue.Trim();
}
if (string.IsNullOrEmpty(value))
{
continue;
}
if (value.StartsWith("#"))
{
model.ColumnName = value.Substring(value.IndexOf('#') + 1, value.Length - 2);
model.ColumnType = row.GetCell(j).CellType;
model.ColumnIndex = row.GetCell(j).ColumnIndex;
model.ColumnStyle = row.GetCell(j).CellStyle;
model.RowIndex = row.RowNum;
if (value.EndsWith("$"))//行循环
{
model.SetValueType = 1;
}
else if (value.EndsWith("~"))//列循环
{
model.SetValueType = 2;
}
else if (value.EndsWith("#"))
{
model.SetValueType = 0;
}
list.Add(model);
}
}
}
}
return(list);
}