public ExcelModule()
: base("Excel")
{
//任务列表
Post["/GridExport"] = r =>
{
string excelParam = Request.Form["excelParam"];
if (string.IsNullOrEmpty(excelParam))
{
return "";
}
ExcelInfo info = JsonConvert.DeserializeObject<ExcelInfo>(excelParam);
if (string.IsNullOrEmpty(info.FileName))
{
info.FileName = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + ".xls";
}
else
{
if (!info.FileName.EndsWith(".xls"))
{
info.FileName = info.FileName + ".xls";
}
}
MemoryStream ms = info.ExportExeclStream();
byte[] msbyte = ms.GetBuffer();
ms.Dispose();
ms = null;
return new Response()
{
Contents = stream => { stream.Write(msbyte, 0, msbyte.Length); },
ContentType = "application/msexcel",
StatusCode = HttpStatusCode.OK,
Headers = new Dictionary<string, string> {
{ "Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpUtility.UrlPathEncode(info.FileName)) },
{"Content-Length", msbyte.Length.ToString()}
}
};
};
/// <summary>
/// 导出Excel模版
/// </summary>
/// <param name="type">业务类型</param>
/// <param name="FunctionCode">对应功能模块Code</param>
/// <returns></returns>
Get["/DownLoadTemplate"] = r =>
{
if (AllImports == null)
{
AllImports = MefConfig.ResolveMany<ExcelImport>();
}
string strType = Request.Query["type"];
ExcelImportType type = EnumHelper.StringToEnum<ExcelImportType>(strType);
var handler = AllImports.FirstOrDefault(e => e.Type == type);
if (handler == null)
{
throw new Exception("未找到“" + type.ToString() + "”相应处理模块");
}
string path = ExcelImporMapper.GetTemplatePath(type);
if (File.Exists(path))
{
try
{
string FileName = Path.GetFileName(path);
return new Response()
{
Contents = stream => { handler.GetExportTemplate(path, stream); },
ContentType = MimeHelper.GetMineType(path),
StatusCode = HttpStatusCode.OK,
Headers = new Dictionary<string, string> {
{ "Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpUtility.UrlPathEncode(FileName)) }
}
};
}
catch (Exception ex)
{
throw ex;
}
}
else
{
throw new Exception("未找到“" + type.ToString() + "”对应模版文件");
}
};
/// <summary>
/// 导出Excel模版
/// </summary>
/// <returns></returns>
Post["/ImportTemplate"] = r =>
{
ImportResult result = new ImportResult();
try
{
if (AllImports == null)
{
AllImports = MefConfig.ResolveMany<ExcelImport>();
}
string ywType = Request.Query["type"];
if (string.IsNullOrEmpty(ywType))
{
throw new ArgumentNullException("ywType");
}
//业务类型
ExcelImportType type = EnumHelper.StringToEnum<ExcelImportType>(ywType);
//文件
HttpFile file = Request.Files.First();
var handler = AllImports.FirstOrDefault(e => e.Type == type);
if (handler == null)
{
throw new Exception("未找到“" + type.ToString() + "”相应处理模块");
}
result = handler.ImportTemplate(file.Value, file.Name, null);
if (result.IsSuccess)
{
//是否获取详细数据，决定后台是否返回 result.ExtraInfo
string ReturnDetailData = Request.Query["ReturnDetailData"];
if (string.IsNullOrEmpty(ReturnDetailData) || ReturnDetailData != "1")
{
result.ExtraInfo = null;
}
}
else
{
//设置错误模版http路径
result.Message = Request.Url.SiteBase + result.Message;
}
}
catch (Exception ex)
{
result.IsSuccess = false;
result.Message = ex.Message;
LogHelper.WriteLog("Excel导入异常", ex);
}
return Response.AsJson(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, 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];
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 = 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>
///返回对应的导出模版数据
/// </summary>
/// <param name="ins">导入文件流</param>
/// <param name="fileName">文件名</param>
/// <param name="userInfo">用户信息</param>
/// <returns>ImportResult</returns>
public virtual ImportResult ImportTemplate(Stream ins, string fileName, UserInfo userInfo)
{
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, userInfo, 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, userInfo);
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, 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];
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 = 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;
}
