/// <summary>
/// 读取EXCEL XML配置信息集
/// </summary>
/// <param name="xmlpath">xml文件路径</param>
/// <returns></returns>
public override List <Regular> GetXMLInfo(string xmlpath)
{
var reader = new XmlTextReader(xmlpath);
var doc = new XmlDocument();
doc.Load(reader);
var headerList = new List <Regular>();
foreach (XmlNode node in doc.DocumentElement.ChildNodes)
{
var header = new Regular();
if (node.Attributes["firstHeaderRow"] != null)
{
header.HeaderRegular.Add("firstHeaderRow", int.Parse(node.Attributes["firstHeaderRow"].Value));
}
if (node.Attributes["lastHeaderRow"] != null)
{
header.HeaderRegular.Add("lastHeaderRow", int.Parse(node.Attributes["lastHeaderRow"].Value));
}
if (node.Attributes["sheetCount"] != null)
{
header.HeaderRegular.Add("sheetCount", int.Parse(node.Attributes["sheetCount"].Value));
}
if (node.Attributes["headerText"] != null)
{
header.HeaderText = node.Attributes["headerText"].Value;
}
if (node.Attributes["propertyName"] != null)
{
header.PropertyName = node.Attributes["propertyName"].Value;
}
if (node.Attributes["dataType"] != null)
{
header.DataType = node.Attributes["dataType"].Value;
}
headerList.Add(header);
}
return(headerList);
}
/// <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);
}
/// <summary>
/// 读取EXCEL数据
/// </summary>
/// <typeparam name="TableDTO">数据对象</typeparam>
/// <param name="sheet">工作簿对应工作表</param>
/// <param name="sheetName">excel工作表名称</param>
/// <param name="list">该excel规则集</param>
/// <param name="dict">表头字典</param>
/// <param name="rowCount">总数据行数</param>
/// <returns>解析后的Excel数据集</returns>
public List <TableDTO> GetExcelDatas <TableDTO>(ISheet sheet, string sheetName, List <Regular> list,
Dictionary <int, string> dict, int rowCount)
{
// 返回数据对象集合
var resultList = new List <TableDTO>();
// 表头结束行
int lastHeaderRowIndex = list.Find(e => e.HeaderRegular != null).HeaderRegular["lastHeaderRow"];
// 循环行数据
for (int i = lastHeaderRowIndex; i <= rowCount; i++)
{
// 产生一个新的泛型对象
var model = Activator.CreateInstance <TableDTO>();
// 记录该行空值数
int nullcount = 0;
IRow dataRow = sheet.GetRow(i);
int cellCount = dict.Count;
if (dataRow != null)
{
// 循环列数据
for (int j = dataRow.FirstCellNum; j < cellCount; j++)
{
string value = "";
Regular header = list.Find(h => h.HeaderText == dict[j]);
PropertyInfo prop = model.GetType().GetProperty(header.PropertyName);
//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;
}
// 去除空值
this.ReplaceSpace(ref value);
if (value == "")
{
nullcount++;
}
// 赋值
switch (header.DataType)
{
case "System.double":
double valueDecimal;
if (double.TryParse(value, out valueDecimal))
{
prop.SetValue(model, valueDecimal, null);
}
break;
case "System.Int16":
short valueInt16;
if (Int16.TryParse(value, out valueInt16))
{
prop.SetValue(model, valueInt16, null);
}
break;
case "System.Int32":
int valueInt32;
if (Int32.TryParse(value, out valueInt32))
{
prop.SetValue(model, valueInt32, null);
}
break;
case "System.Boolean":
bool valueBoolean;
if (Boolean.TryParse(value, out valueBoolean))
{
prop.SetValue(model, valueBoolean, null);
}
break;
case "System.DateTime":
DateTime valueDateTime;
if (DateTime.TryParse(value, out valueDateTime))
{
prop.SetValue(model, valueDateTime, null);
}
break;
default:
prop.SetValue(model, value, null);
break;
}
}
}
// 添加非空行数据到DTO
if (nullcount < cellCount)
{
resultList.Add(model);
}
}
return(resultList);
}