程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 編程語言 >> .NET網頁編程 >> 關於.NET >> npoi批量導入實現及相關技巧,npoi導入技巧

npoi批量導入實現及相關技巧,npoi導入技巧

編輯:關於.NET

npoi批量導入實現及相關技巧,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 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>;
                $(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">);
                    }

                    $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

 

如果,您認為閱讀這篇博客讓您有些收獲,不妨點擊一下右下角的【推薦】按鈕。
如果,您希望更容易地發現我的新博客,不妨點擊一下綠。色通道的【關注我】。

如果,想給予我更多的鼓勵,求打

因為,我的寫作熱情也離不開您的肯定支持。

感謝您的閱讀,如果您對我的博客所講述的內容有興趣,請繼續關注我的後續博客,我是焰尾迭 。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved