批量导入功能对于大部分后台系统来说都是不可或缺的一部分，常见的场景-基础数据的录入(部门，用户)，用批量导入方便快捷。最近项目需要用到批量导入，决定花点时间写套比较通用的Excel导入功能。经过考虑，最终的实现需要达到

     1.不同业务导入无需考虑npoi相关操作，只需要关注自己的业务逻辑，这里的业务逻辑最重要的两点(数据校验和数据保存)

　  2.导入异常(模板不匹配,数据填写错误...)，提醒信息准确精细，达到帮助用户修正数据的目地 

     在线体验地址：http://tm.myscloud.cn:9000，最终实现效果

     导入成功

   导入失败

阅读目录

• 设计流程图

• 代码实现

• npoi生成下拉框两种方式比较
  

• 总结

回到顶部

设计流程图

　　　本文使用的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 = 0;            using (FileStream fs = File.OpenRead(FilePath))            {                do                {                    count = fs.Read(m_buffer, 0, BUFFER_SIZE);                    s.Write(m_buffer, 0, 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 + 1;            NPOIHelper.SetHSSFValidation(sheet, taskStatus, dataRowIndex, 3);            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 = 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="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">&times;</span></button>' +                                    '<h4 class="modal-title">Excel导入</h4>' +                                '</div>' +                                '<div class="modal-body">' +                                    '<div id="uploader" class="wu-example">' +                                        '<p >导入说明:</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"  />' +                                        '<div id="picker">选择文件</div>' +                                        '<button id="ctlBtn" class="btn btn-white btn-sm btn-start-uploader ml5" >开始上传</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" >' +                          '</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[50];for(int i = 0; i < 50; 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 < 0)            {                hidden = workbook.CreateSheet(hiddenSheetName);                workbook.SetSheetHidden(sheet.Workbook.NumberOfSheets - 1, SheetState.HIDDEN);            }            else            {                hidden = workbook.GetSheetAt(hIndex);            }            IRow row = null;            ICell cell = null;            for (int i = 0, 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 + 1), 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