npoi批量导入实现及相关技巧

　　批量导入功能对于大部分后台系统来说都是不可或缺的一部分，常见的场景-基础数据的录入(部门，用户)，用批量导入方便快捷。最近项目需要用到批量导入，决定花点时间写套比较通用的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 id="{0}">'.format(id) +            '<div >' +              '<div >' +                '<div >' +                  '<button type="button" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>' +                  '<h4 >Excel导入</h4>' +                '</div>' +                '<div >' +                  '<div id="uploader" >' +                    '<p >导入说明:</p><p >导入文件为EXCEL格式，请先下载模板进行必要信息填写，模板下载<a href="javascript:;" onclick="$.DownloadExcelTemplate(\'{0}\')">请点击这里</a>！</p>'.format(param.type) +                    '<div id="thelist" ></div>' +                    '<div >' +                    '<input type="text" readonly  />' +                    '<div id="picker">选择文件</div>' +                    '<button id="ctlBtn" >开始上传</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 + '" >' +            '<div ></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 >' +             '<div 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 >' + 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 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;    }