/// <summary>
///返回对应的导出模版数据
/// </summary>
/// <param name="ins">导入文件流</param>
/// <param name="fileName">文件名</param>
/// <param name="userInfo">用户信息</param>
/// <returns>ImportResult</returns>
public virtual ImportResult ImportTemplate(Stream ins, string fileName)
{
if (DictFields == null)
{
throw new ArgumentNullException("Excel字段映射及校验缓存字典DictFields空异常");
}
//1.读取数据
ISheet datasheet = null;
DataTable dt = GetDataFromExcel(ins, out datasheet);
//2.校验列是否正确
//相同列数
int equalCount = (from p in GetColumnList(dt)
join q in DictFields.Keys
on p equals q
select p).Count();
if (equalCount < DictFields.Keys.Count)
{
throw new Exception(string.Format("模版列和规定的不一致,正确的列为（{0}）", string.Join(",", DictFields.Keys)));
}
//2.改变列名为英文字段名
ImportVerify objVerify = null;
List <string> columns = new List <string>();
List <string> removeColumns = new List <string>();
foreach (DataColumn dc in dt.Columns)
{
if (DictFields.TryGetValue(dc.ColumnName, out objVerify))
{
if (objVerify != null)
{
dc.ColumnName = objVerify.FieldName;
columns.Add(objVerify.FieldName);
continue;
}
}
removeColumns.Add(dc.ColumnName);
}
//3.删除无效列
foreach (string remove in removeColumns)
{
dt.Columns.Remove(remove);
}
//4.获取校验所需额外参数
Dictionary <string, object> extraInfo = GetExtraInfo(columns, dt);
// 英文字段名到中文列名映射关系
Dictionary <string, ImportVerify> DictColumnFields = DictFields.Values.ToDictionary(e => e.FieldName, e => e);
//5.开始校验
ImportResult result = Verify(dt, datasheet, extraInfo, fileName, DictColumnFields);
if (result.IsSuccess)
{
//校验完成后进行数据类型转换
ImportVerify iv = null;
Type columnType = null;
DataTable dtNew = dt.Clone();
foreach (DataColumn dc in dtNew.Columns)
{
if (DictColumnFields != null && DictColumnFields.TryGetValue(dc.ColumnName, out iv))
{
if (iv.DataType != null)
{
columnType = iv.DataType;
}
else
{
columnType = dc.DataType;
}
}
else
{
columnType = typeof(string);
}
dc.DataType = columnType;
}
//复制数据到克隆的datatable里
try
{
foreach (DataRow dr in dt.Rows)
{
dtNew.ImportRow(dr);
}
}
catch { }
//3.保存数据
result.ExtraInfo = SaveImportData(dtNew, extraInfo);
result.Message = string.Format("成功导入{0}条数据", dtNew.Rows.Count);
}
return(result);
}
/// <summary>
/// 校验数据是否正常
/// </summary>
/// <param name="dt">数据集</param>
/// <param name="outputStream">输出流</param>
/// <param name="sheet">数据sheet</param>
/// <param name="userInfo">用户信息</param>
/// <param name="fileName">文件名称</param>
/// <param name="DictColumnFields">英文字段名到中文列名映射关系</param>
/// <returns>ImportResult</returns>
public virtual ImportResult Verify(DataTable dt, ISheet sheet, Dictionary <string, object> extraInfo, string fileName, Dictionary <string, ImportVerify> DictColumnFields)
{
IWorkbook wb = sheet.Workbook;
ImportResult result = new ImportResult();
string[] arrErrorMsg = null;
string errorMsg = string.Empty;
int columnCount = dt.Columns.Count;
string columnName = string.Empty;
ImportVerify objVerify = null;
ImportVerifyParam objVerifyParam = new ImportVerifyParam {
DTExcel = dt, CellValue = null, ColName = columnName, ColumnIndex = 0, RowIndex = 0
};
DataRow row = null;
object objExtra = null;
bool isCorrect = true;
//错误数据行样式
var cellErrorStyle = NPOIHelper.GetErrorCellStyle(wb);
ICell errorCell = null;
IRow sheetRow = null;
for (int i = 0, rLength = dt.Rows.Count; i < rLength; i++)
{
row = dt.Rows[i];
arrErrorMsg = new string[columnCount];
for (int j = 0; j < columnCount; j++)
{
columnName = dt.Columns[j].ColumnName;
if (DictColumnFields.TryGetValue(columnName, out objVerify))
{
if (objVerify.VerifyFunc != null)
{
objVerifyParam.CellValue = row[j];
objVerifyParam.ColumnIndex = j;
objVerifyParam.RowIndex = i;
objVerifyParam.ColName = objVerify.ColumnName;
if (extraInfo != null)
{
extraInfo.TryGetValue(columnName, out objExtra);
}
arrErrorMsg[j] = objVerify.VerifyFunc(objVerifyParam, objExtra);
}
}
}
errorMsg = string.Join("，", arrErrorMsg.Where(e => !string.IsNullOrEmpty(e)));
if (!string.IsNullOrEmpty(errorMsg))
{
isCorrect = false;
//设置错误信息
sheetRow = sheet.GetRow(StartRowIndex + 1 + i);
errorCell = sheetRow.GetCell(columnCount);
if (errorCell == null)
{
errorCell = sheetRow.CreateCell(columnCount);
}
errorCell.CellStyle = cellErrorStyle;
errorCell.SetCellValue(errorMsg);
}
}
//输出错误信息模版
if (!isCorrect)
{
sheetRow = sheet.GetRow(StartRowIndex);
errorCell = sheetRow.GetCell(columnCount);
if (errorCell == null)
{
errorCell = sheetRow.CreateCell(columnCount);
}
ICellStyle copyStyle = sheetRow.GetCell(columnCount - 1).CellStyle;
ICellStyle style = NPOIHelper.GetErrorHeadCellStyle(wb);
IFont font = style.GetFont(wb);
IFont copyfont = copyStyle.GetFont(wb);
font.FontHeight = 22;
font.FontName = copyfont.FontName;
style.FillForegroundColor = copyStyle.FillForegroundColor;
style.BorderBottom = copyStyle.BorderBottom;
style.BorderLeft = copyStyle.BorderLeft;
style.BorderRight = copyStyle.BorderRight;
style.BorderTop = copyStyle.BorderTop;
errorCell.CellStyle = style;
errorCell.SetCellValue("错误信息");
//自适应列宽度
sheet.AutoSizeColumn(columnCount);
int width = sheet.GetColumnWidth(columnCount) + 2560;
sheet.SetColumnWidth(columnCount, width > NPOIHelper.MAX_COLUMN_WIDTH ? NPOIHelper.MAX_COLUMN_WIDTH : width);
result.Message = ExcelImportHelper.GetErrorExcel(wb, fileName);
}
else
{
result.IsSuccess = true;
}
return(result);
}