批量导入功能对于大部分后台系统来说都是不可或缺的一部分，常见的场景-基础数据的录入(部门，用户)，用批量导入方便快捷。最近项目需要用到批量导入，决定花点时间写套比较通用的Excel导入功能。经过考虑，最终的实现需要达到
1.不同业务导入无需考虑npoi相关操作，只需要关注自己的业务逻辑，这里的业务逻辑最重要的两点(数据校验和数据保存)
2.导入异常(模板不匹配,数据填写错误...)，提醒信息准确精细，达到帮助用户修正数据的目地
在线体验地址：http://tm.myscloud.cn:9000，最终实现效果
导入成功
导入失败
阅读目录
设计流程图
本文使用的npoi版本：1.2.5，可以nuget下载相应包。系统相关流程和重要类的类图如下。
设计原则:
1.通用操作与业务无关代码在基类里面实现
2.对于个性化业务可以重写基类方法实现
开发流程：
1.制作Excel导入模版
2.添加继承ExcelImport业务导入类
3.在ExcelImportMapper中添加枚举和该业务枚举对应模版路径地址
4.业务导入类重写Type,DictFields，SavaImportData，根据模版决定是否重写GetExportTemplate方法
代码实现
1.返回导入模版
默认实现，直接根据模版文件路径返回到响应流中
/// <summary>
///返回对应的导出模版数据
/// </summary>
/// <param name="FilePath">模版的路径</param>
/// <param name="s">响应流</param>
/// <returns>模版MemoryStream</returns>
public virtual void GetExportTemplate(string FilePath, Stream s)
{
byte[] m_buffer = new byte[BUFFER_SIZE];
int count = ;
using (FileStream fs = File.OpenRead(FilePath))
{
do
{
count = fs.Read(m_buffer, , BUFFER_SIZE);
s.Write(m_buffer, , count);
} while (count == BUFFER_SIZE);
}
}
个性化实现，比如导出模版有下拉选项
/// <summary>
///返回对应的导出模版数据
/// </summary>
/// <param name="FilePath">模版的路径</param>
/// <param name="s">响应流</param>
/// <returns>模版MemoryStream</returns>
public override void GetExportTemplate(string FilePath, Stream s)
{
//写入下拉框值 任务状态
var sheet = NPOIHelper.GetFirstSheet(FilePath); string[] taskStatus = GetStatusDict().Keys.ToArray(); int dataRowIndex = StartRowIndex + ;
NPOIHelper.SetHSSFValidation(sheet, taskStatus, dataRowIndex, ); sheet.Workbook.Write(s);
}
2.导入模版
抽象类提供的导入流程
/// <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 { } //6.保存数据
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 = , RowIndex = };
DataRow row = null;
object objExtra = null;
bool isCorrect = true; //错误数据行样式
var cellErrorStyle = NPOIHelper.GetErrorCellStyle(wb);
ICell errorCell = null;
IRow sheetRow = null; for (int i = , rLength = dt.Rows.Count; i < rLength; i++)
{
row = dt.Rows[i];
arrErrorMsg = new string[columnCount];
for (int j = ; 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 + + 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 - ).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) + ;
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="dt">数据</param>
/// <param name="extraInfo">额外参数</param>
/// <param name="userInfo">用户信息</param>
public override object SaveImportData(DataTable dt, Dictionary<string, object> extraInfo, UserInfo userInfo)
{
string columnName = string.Empty;
object objExtra = null;
Dictionary<string, string> dict = null;
object objCellValue = null; List<string> listAssetsId = new List<string>();
string strAssetsId = string.Empty;
//下拉选项text转成Value
foreach (DataRow dr in dt.Rows)
{
foreach (DataColumn dc in dt.Columns)
{
columnName = dc.ColumnName;
if (extraInfo.TryGetValue(columnName, out objExtra))
{
dict = objExtra as Dictionary<string, string>;
if (dict != null)
{
objCellValue = dr[columnName];
if (!ExcelImportHelper.ObjectIsNullOrEmpty(objCellValue))
{
dr[columnName] = dict[objCellValue.ToString()];
}
}
}
}
} try
{
//保存任务数据
List<TaskUtil> list = dt.ToList<TaskUtil>();
foreach (var item in list)
{
TaskHelper.SaveTask(item);
}
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
3.前端代码封装
上传插件基于百度的webuploader插件，带进度条效果不错
模版下载方法
/*
* 功能： 根据业务类型下载导入数据得模版文件
* 参数： type：业务类型 取值参照 Ywdsoft.Utility.Excel.ExcelImportType 枚举
* 返回值： 无
* 创建人： 焰尾迭
* 创建时间：2016-08-19
*/
DownloadExcelTemplate: function (type) {
if (type == "undefined") {
return;
}
var param = { type: type };
$.download("/Excel/DownLoadTemplate", param, "get");
},
模版上传
/*
* 功能： 根据业务类型下载导入数据的模版文件
* 参数： options：
{
type:业务类型, 取值参照 Ywdsoft.Utility.Excel.ExcelImportType 枚举
Ext:可导入文件类型,
ReturnDetailData:是否返回详细数据
after:function(){}//回调函数
}
* 返回值： 无
* 创建人： 焰尾迭
* 创建时间：2016-08-22
*/
ImportExcelTemplate: function (options) {
if ($.isPlainObject(options)) {
var defaults = {
ReturnDetailData: 0
}; var param = $.extend({}, defaults, options); if (param.type != "undefined") {
//加载样式和js文件
$.loadFile("/Content/Css/plugins/webuploader/webuploader.css");
$.loadFile("/Content/Scripts/plugins/webuploader/webuploader.min.js");
if (!WebUploader.Uploader.support()) {
var error = "上传控件不支持您的浏览器！请尝试升级flash版本或者使用Chrome引擎的浏览器。<a target='_blank' href='http://www.chromeliulanqi.com'>下载页面</a>";
if (window.console) {
window.console.log(error);
}
return;
} var id = "ImportExcelTemplate{0}".format(param.type);
var modal = $("#" + id);
$(modal).remove();
var html =
'<div class="modal" id="{0}">'.format(id) +
'<div class="modal-dialog">' +
'<div class="modal-content">' +
'<div class="modal-header">' +
'<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>' +
'<h4 class="modal-title">Excel导入</h4>' +
'</div>' +
'<div class="modal-body">' +
'<div id="uploader" class="wu-example">' +
'<p style="font-weight:bold;">导入说明:</p><p class="pt5">导入文件为EXCEL格式，请先下载模板进行必要信息填写，模板下载<a href="javascript:;" onclick="$.DownloadExcelTemplate(\'{0}\')">请点击这里</a>！</p>'.format(param.type) +
'<div id="thelist" class="uploader-list"></div>' +
'<div class="uploader-wrap clearfix pb20">' +
'<input type="text" readonly class="form-control input-sm mr5 upload-file-name" style="width:300px;" />' +
'<div id="picker">选择文件</div>' +
'<button id="ctlBtn" class="btn btn-white btn-sm btn-start-uploader ml5" style="display:none;">开始上传</button>' +
'</div>'
'</div>' +
'</div></div></div></div>';
$(html).appendTo("body");
modal = $("#" + id);
var postData = { type: param.type, FunctionCode: param.FunctionCode, ReturnDetailData: param.ReturnDetailData };
var uploader = WebUploader.create({
swf: '/Content/Scripts/plugins/webuploader/Uploader.swf',
server: '/Excel/ImportTemplate?' + $.param(postData),
pick: '#picker',
accept: {
title: 'excel',
extensions: 'xls',
mimeTypes: 'application/msexcel'
},
resize: false,
fileSingleSizeLimit: 10 * 1024 * 1024,//10M
duplicate: true
}); $("#ctlBtn").on('click', function () {
uploader.upload();
}); // 当有文件被添加进队列的时候
uploader.on('fileQueued', function (file) {
$("#thelist").html('<div id="' + file.id + '" class="item">' +
'<div class="state"></div>' +
'</div>');
$(".upload-file-name").val(file.name);
$(".btn-start-uploader").show();
}); // 文件上传过程中创建进度条实时显示。
uploader.on('uploadProgress', function (file, percentage) {
var $li = $('#' + file.id),
$percent = $li.find('.progress .progress-bar'); // 避免重复创建
if (!$percent.length) {
$percent = $('<div class="progress progress-striped active">' +
'<div class="progress-bar" role="progressbar" style="width: 0%">' +
'</div>' +
'</div>').appendTo($li).find('.progress-bar');
} $li.find('.state').text('上传中'); $percent.css('width', percentage * 100 + '%');
$(".upload-file-name").val("");
$(".btn-start-uploader").hide();
}); uploader.on('uploadSuccess', function (file, response) {
if (response.IsSuccess) {
$('#' + file.id).find('.state').html('<span class="label label-success">' + response.Message + '</span>');
if ($.isFunction(param.after)) {
param.after(response, modal);
}
} else {
if (response.Message.indexOf("http://") >= 0) {
$('#' + file.id).find('.state').html("上传的数据中存在错误数据，请点击<a class='red' href='{0}' target='_blank'>下载错误数据</a>！".format(response.Message));
} else {
$('#' + file.id).find('.state').html('<span class="label label-danger" title="' + response.Message + '">' + response.Message + '</span>');
}
} }); uploader.on('uploadError', function (file, response) {
console.log(response);
$('#' + file.id).find('.state').text('上传出错');
}); uploader.on('uploadComplete', function (file) {
$('#' + file.id).find('.progress').fadeOut(200);
}); modal.modal('show');
}
}
}
npoi生成下拉框两种方式比较
在使用npoi操作excel生成下拉框过程中遇到了问题，花了大半天时间才解决，下面介绍一下如何使用npoi生成下拉框，并且对比两种生成下拉框方式的优劣势。
方式一：
//下拉框应用区域，起始行截止行 起始列截止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
//下拉选项数组
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(textlist);
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);
该方式使用简单几行代码即可搞定，缺点是所有选项字符大于255时会报异常，异常信息如下
String literals in formulas can't be bigger than 255 Chars ASCII"
异常验证方式
string[] textlist = new string[];
for(int i = ; i < ; i++)
{
textlist[i] = "好好学习天天向上";
}
方式二： 先创建一个Sheet专门用于存储下拉项的值，并将各下拉项的值写入其中
/// <summary>
/// 设置某些列的值只能输入预制的数据,显示下拉框
/// </summary>
/// <param name="sheet">要设置的sheet</param>
/// <param name="textlist">下拉框显示的内容</param>
/// <param name="firstRow">开始行</param>
/// <param name="endRow">结束行</param>
/// <param name="firstCol">开始列</param>
/// <param name="endCol">结束列</param>
/// <returns>设置好的sheet</returns>
public static ISheet SetHSSFValidation(ISheet sheet,
string[] textlist, int firstRow, int endRow, int firstCol,
int endCol)
{
IWorkbook workbook = sheet.Workbook;
if (endRow > sheet.LastRowNum)
{
endRow = sheet.LastRowNum;
}
ISheet hidden = null; string hiddenSheetName = "hidden" + sheet.SheetName;
int hIndex = workbook.GetSheetIndex(hiddenSheetName);
if (hIndex < )
{
hidden = workbook.CreateSheet(hiddenSheetName);
workbook.SetSheetHidden(sheet.Workbook.NumberOfSheets - , SheetState.HIDDEN);
}
else
{
hidden = workbook.GetSheetAt(hIndex);
} IRow row = null;
ICell cell = null;
for (int i = , length = textlist.Length; i < length; i++)
{
row = hidden.GetRow(i);
if (row == null)
{
row = hidden.CreateRow(i);
}
cell = row.GetCell(firstCol);
if (cell == null)
{
cell = row.CreateCell(firstCol);
}
cell.SetCellValue(textlist[i]);
} // 加载下拉列表内容
string nameCellKey = hiddenSheetName + firstCol;
IName namedCell = workbook.GetName(nameCellKey);
if (namedCell == null)
{
namedCell = workbook.CreateName();
namedCell.NameName = nameCellKey;
namedCell.RefersToFormula = string.Format("{0}!${1}$1:${1}${2}", hiddenSheetName, NumberToChar(firstCol + ), textlist.Length);
}
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(nameCellKey); // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// 数据有效性对象
HSSFDataValidation validation = new HSSFDataValidation(regions, constraint);
//// 取消弹出错误框
//validation.ShowErrorBox = false;
sheet.AddValidationData(validation);
return sheet;
}
- 创建隐藏的sheet页
- 将下拉选项值写入到对应列中
- 区域引用sheet页数据
该方式相当于Excel的以下操作
该方式不存在上限限制，方便在Excel里面查看下拉选项，更加通用。
总结
至此实现npoi实现通用导入功能已经完成，后续具体导入业务实现也很简单了，有需要的朋友可以直接拿去使用。
本篇所使用示例代码下载地址:
SVN地址：http://code.taobao.org/svn/TaskManagerPub/Branch 使用svn checkout指令进行下载。
GitHub地址：https://github.com/CrazyJson/TaskManager
体验工具下载地址：任务管理框架 V2.0