/// <summary>
/// 拓展方法,生成EXECL
/// </summary>
/// <param name="info">EXECL相关信息</param>
/// <param name="token">用户认证令牌</param>
/// <returns>Execl路径</returns>
public static MemoryStream ExportExeclStream(this ExcelInfo info, string token)
{
//1.获取列表对应数据
DataTable dt = GetGirdData(info, token);
//2.创建文档
MemoryStream ms = null;
switch (info.FileFormat)
{
case ExportFileFormat.Excel:
//导出Excel
ms = NPOIHelper.Export(dt, info);
break;
case ExportFileFormat.Word:
//导出Word
//ms = WordHelper.Export(dt, info, guid);
break;
case ExportFileFormat.Pdf:
//导出Pdf
//ms = PdfHelper.Export(dt, info, guid);
break;
}
return(ms);
}
/// <summary>
/// 校验数据是否正常
/// </summary>
/// <param name="dt">数据集</param>
/// <param name="extraInfo"></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, UserInfo userInfo, 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 + 1];
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);
}
}
}
//行数据整体校验
arrErrorMsg[columnCount] = RowVerify(row, extraInfo);
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 = copyfont.FontHeight;
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);
}
/// <summary>
///从上传文件流中读取数据 保存为datatable
/// </summary>
/// <param name="ins">输入流</param>
/// <param name="datasheet">数据得sheet表格</param>
/// <returns>数据</returns>
public virtual DataTable GetDataFromExcel(Stream ins, out ISheet datasheet)
{
return(NPOIHelper.GetDataFromExcel(ins, out datasheet, StartRowIndex));
}