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

  批量导入功能对于大部分后台系统来说都是不可或缺的一部分,常见的场景-基础数据的录入(部门,用户),用批量导入方便快捷。最近项目需要用到批量导入,决定花点时间写套比较通用的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 = 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 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[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实现通用导入功能已经完成,后续具体导入业务实现也很简单了,有需要的朋友可以直接拿去使用。

      本篇所使用示例代码下载地址:

    GitHub地址:https://github.com/CrazyJson/TaskManager

      体验工具下载地址:任务管理框架 V2.0

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏岑玉海

sqoop 从sqlserver2008 导入数据到hadoop

  今天终于开始上手导入数据到hadoop了,哈哈,过程蛮崎岖的,和官方文档的还不太一样。   OK,let's go!试验对象是我第一个名为ST_Statis...

3285
来自专栏GuZhenYin

使用localResizeIMG3+WebAPI实现手机端图片上传

前言 惯例~惯例~昨天发表的使用OWIN作为WebAPI的宿主..嗯..有很多人问..是不是缺少了什么 - - 好吧,如果你要把OWIN寄宿在其他的地方...代...

1888
来自专栏潇涧技术专栏

Art of Android Development Reading Notes 13

《Android开发艺术探索》读书笔记 (13) 第13章 综合技术、第14章 JNI和NDK编程、第15章 Android性能优化

802
来自专栏mySoul

window对象(一) 计时器 定位导航 url解析 浏览历史 对话框 消息推送

一个jQuery作者的写的关于定时器解释,记的顺手订阅一下。John Resig,jQuery之父

754
来自专栏面朝大海春暖花开

vue页面开发遇到的坑,都是泪!src属性,freemarker取值

效果图如下 对应的连接 http://www.xinghengedu.com/getTenActivityQuestions.jspx 可以玩一玩。

1043
来自专栏java 成神之路

URL 源码分析

34713
来自专栏Scott_Mr 个人专栏

RxSwift 实战操作【注册登录】

2786
来自专栏Seebug漏洞平台

抓住“新代码”的影子 —— 基于GoAhead系列网络摄像头多个漏洞分析

PDF 版本下载:抓住“新代码”的影子 —— 基于GoAhead系列网络摄像头多个漏洞分析

77410
来自专栏禁心尽力

总结切面编程AOP的注解式开发和XML式开发

       有段日子没有总结东西了,因为最近确实有点忙,一直在忙于hadoop集群的搭建,磕磕碰碰现在勉强算是能呼吸了,因为这都是在自己的PC上,资源确实有点...

22510
来自专栏葡萄城控件技术团队

扩展GridView控件——为内容项添加拖放及分组功能

引言 相信大家对GridView都不陌生,是非常有用的控件,用于平铺有序的显示多个内容项。打开任何WinRT应用或者是微软合作商的网站,都会在APP中发现Gri...

2675

扫码关注云+社区