// excel所有单元格数据验证
public UploadExcelFileResult ValidateExcel()
{
var result = new UploadExcelFileResult();
result.Success = true;
_rowCount = new Dictionary <int, int>();
Stream fileStream = new FileStream(_filePath, FileMode.Open);
int edition = this.GetExcelEdition(_filePath);
if (edition != 0)
{
IWorkbook workbook = this.CreateWorkBook(edition, fileStream);
int sheetCount = _list.Find(e => e.HeaderRegular != null).HeaderRegular["sheetCount"];
for (int i = 0; i < sheetCount; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
Dictionary <int, string> dict = this.GetExcelHeaders(sheet, ref result, _list);
if (result.Success)
{
_rowCount.Add(i, sheet.LastRowNum);
result = this.CheckExcelDatasEnableNull(sheet, _list, dict, _rowCount[i]);
}
else
{
break;
}
}
}
else
{
result.Success = false;
result.Message = "文件类型错误!";
}
fileStream.Close();
return(result);
}
// 解析excel数据到DTO
public List <TableDTO> Import <TableDTO>()
{
var uploadExcelFileResult = new UploadExcelFileResult();
var resultList = new List <TableDTO>();
Stream fileStream = new FileStream(_filePath, FileMode.Open);
int edition = this.GetExcelEdition(_filePath);
IWorkbook workbook = this.CreateWorkBook(edition, fileStream);
int sheetCount = _list.Find(e => e.HeaderRegular != null).HeaderRegular["sheetCount"];
ValidateExcel();
for (int i = 0; i < sheetCount; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
string sheetName = sheet.SheetName;
Dictionary <int, string> dict = this.GetExcelHeaders(sheet, ref uploadExcelFileResult, _list);
var sheetLists = this.GetExcelDatas <TableDTO>(sheet, sheetName, _list, dict, _rowCount[i]);
resultList.AddRange(sheetLists);
}
fileStream.Close();
return(resultList);
}
/// <summary>
/// 解析并检查EXCEL表头数据
/// </summary>
/// <param name="sheet"></param>
/// <param name="uploadExcelFileResult"></param>
/// <param name="list"></param>
/// <returns></returns>
public Dictionary <int, string> GetExcelHeaders(ISheet sheet, ref UploadExcelFileResult uploadExcelFileResult,
List <Regular> list)
{
int firstHeaderRowIndex = list.Find(e => e.HeaderRegular != null).HeaderRegular["firstHeaderRow"];
int lastHeaderRowIndex = list.Find(e => e.HeaderRegular != null).HeaderRegular["lastHeaderRow"];
var dict = new Dictionary <int, string>();
try
{
// 循环获得表头
for (int i = firstHeaderRowIndex - 1; i < lastHeaderRowIndex; i++)
{
IRow headerRow = sheet.GetRow(i);
int cellCount = headerRow.LastCellNum;
for (int j = headerRow.FirstCellNum; j < cellCount; j++)
{
if (!string.IsNullOrEmpty(headerRow.GetCell(j).StringCellValue.Trim()))
{
// 根据 键－值 是否已存在做不同处理
//TODO 代码待重构！！！
try
{
string oldValue = dict[j];
dict.Remove(j);
dict.Add(j, oldValue + headerRow.GetCell(j).StringCellValue.Trim());
}
catch (Exception)
{
dict.Add(j, headerRow.GetCell(j).StringCellValue.Trim());
}
}
}
}
// 遍历表头字典，消除空格
for (int i = 0; i < dict.Count; i++)
{
var value = dict[i];
this.ReplaceSpace(ref value);
dict[i] = value;
}
// 检查表头模板是否被修改
for (int count = 0; count < dict.Count; count++)
{
Regular header = list.Find(h => h.HeaderText == dict[count]);
if (header == null)
{
uploadExcelFileResult.Success = false;
uploadExcelFileResult.Message = "读取EXCEL表头模板时发生错误，可能造成原因是：EXCEL模板被修改！请下载最新EXCEL模板！";
}
}
}
catch (Exception e)
{
uploadExcelFileResult.Success = false;
uploadExcelFileResult.Message = "读取EXCEL表头模板时发生错误，可能造成原因是：EXCEL模板被修改！请下载最新EXCEL模板！";
}
return(dict);
}
/// <summary>
/// 检查excel数据
/// </summary>
/// <param name="sheet">excel工作表</param>
/// <param name="list">规则集</param>
/// <param name="dict">表头</param>
/// <param name="rowCount">总数据行数</param>
/// <returns>检查结果</returns>
public UploadExcelFileResult CheckExcelDatasEnableNull(ISheet sheet, List <Regular> list, Dictionary <int, string> dict, int rowCount)
{
var result = new UploadExcelFileResult();
result.Success = true;
// 记录单个sheet所有错误信息
var sheetErrors = new List <ExcelFileErrorPosition>();
// 表头结束行
int lastHeaderRowIndex = list.Find(e => e.HeaderRegular != null).HeaderRegular["lastHeaderRow"];
// 循环行数据
for (int i = lastHeaderRowIndex; i <= rowCount; i++)
{
// 标注该行是否出错
bool isrowfalse = false;
// 记录该行数据临时对象
var rowDatas = new List <string>();
// 记录该行错误列
var rowErrorCell = new List <int>();
// 记录该行错误列具体错误信息
var rowErrorMessages = new List <string>();
// 记录该行空值数
int nullcount = 0;
IRow dataRow = sheet.GetRow(i);
int cellCount = dict.Count;
// 循环列数据
for (int j = dataRow.FirstCellNum; j < cellCount; j++)
{
string value = "";
Regular header = list.Find(h => h.HeaderText == dict[j]);
//value = dataRow.GetCell(j).ToString();
switch (dataRow.GetCell(j).CellType)
{
case CellType.Formula:
value = dataRow.GetCell(j).StringCellValue.ToString();
break;
default:
value = dataRow.GetCell(j).ToString();
break;
}
// 记录可能出错数据
rowDatas.Add(value);
// 检查空值
if (!this.CheckNull(value, ref nullcount))
{
// 检查类型
if (!this.CheckDataType(header.DataType, value))
{
isrowfalse = true;
result.Success = false;
// 记录该行错误信息
rowErrorCell.Add(j + 1);
rowErrorMessages.Add("读取EXCEL数据时发生数据格式错误，请检查该行该列数据格式！");
}
else
{
if (header.DataType == "System.string" || header.DataType == "System.String")
{
this.ReplaceSpace(ref value);
}
}
}
}
// 报错处理(空行不报错)
if (isrowfalse && nullcount < cellCount)
{
sheetErrors.Add(new ExcelFileErrorPosition
{
RowContent = rowDatas,
RowIndex = i + 1,
CellIndex = rowErrorCell,
ErrorMessage = rowErrorMessages
});
}
}
result.ExcelFileErrorPositions = sheetErrors;
return(result);
}