npoi batch import implementation and related skills

npoi batch import implementation and related skills

  The bulk import function is an indispensable part of most back-end systems. The common scenario-the entry of basic data (department, user), is convenient and quick to use the batch import. Recently, the project needs to use batch import, so I decided to spend some time writing a more general Excel import function. After consideration, the final realization needs to reach

1. There is no need to consider npoi related operations when importing different businesses. You only need to pay attention to your own business logic. The two most important points of business logic here (data verification and data storage)

   2. Import exceptions (template mismatch, data filling error...), reminding the information to be accurate and precise, to help users correct the data

     Online experience address: http://tm.myscloud.cn:9000 , the final effect is achieved

     Imported successfully

   Import failed

Read the table of contents

back to the top

Design flow chart

The npoi version used in this article: 1.2.5, you can download the corresponding package with nuget. The system-related processes and class diagrams of important classes are as follows.

    Design Principles:

    1. Common operations and business-independent codes are implemented in the base class

    2. For personalized business, you can rewrite the base class method to achieve

    Development Process:

    1. Make Excel import template

  2. Add inherited ExcelImport business import class

    3. Add the enumeration and the template path address corresponding to the business enumeration in ExcelImportMapper

    4. The business import class rewrites Type, DictFields, SavaImportData, and decides whether to rewrite the GetExportTemplate method according to the template

back to the top

Code

 1. Return to import template

The default implementation, directly return to the response stream according to the template file path

       ///<summary>
       ///Return the corresponding export template data
       ///</summary>
       ///<param name="FilePath">the path of the template</param>
       ///<param name="s">response stream</param>
       ///<returns>Template 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);
            }
        }

Personalized realization, such as export template with drop-down options

       ///<summary>
       ///Return the corresponding export template data
       ///</summary>
       ///<param name="FilePath">the path of the template</param>
       ///<param name="s">response stream</param>
       ///<returns>Template MemoryStream</returns>
        public override void GetExportTemplate(string FilePath, Stream s)
        {
           //Write drop-down box value task status
            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. Import the template

Import process provided by abstract class

       ///<summary>
       ///Return the corresponding export template data
       ///</summary>
       ///<param name="ins">import file stream</param>
       ///<param name="fileName">file name</param>
       ///<param name="userInfo">user information</param>
       ///<returns>ImportResult</returns>
        public virtual ImportResult ImportTemplate(Stream ins, string fileName, UserInfo userInfo)
        {
            if (DictFields == null)
            {
                throw new ArgumentNullException("Excel field mapping and verification cache dictionary DictFields empty exception");
            }
           //1. Read data
            ISheet datasheet = null;
            DataTable dt = GetDataFromExcel(ins, out datasheet);

           //2. Check whether the column is correct
           //The same number of columns
            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("The template column is inconsistent with the regulations, the correct column is ({0})", string.Join(",", DictFields.Keys)));
            }


           //2. Change the column name to the English field name
            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. Delete invalid columns
            foreach (string remove in removeColumns)
            {
                dt.Columns.Remove(remove);
            }

           //4. Obtain additional parameters required for verification
            Dictionary<string, object> extraInfo = GetExtraInfo(columns, dt);

           //The mapping relationship between English field names and Chinese column names
            Dictionary<string, ImportVerify> DictColumnFields = DictFields.Values.ToDictionary(e => e.FieldName, e => e);

           //5. Start verification
            ImportResult result = Verify(dt, datasheet, extraInfo, userInfo, fileName, DictColumnFields);

            if (result.IsSuccess)
            {
               //Data type conversion after verification
                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;
                }
               //Copy data to the cloned datatable  
                try
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        dtNew.ImportRow(dr);
                    }
                }
                catch {}

               //6. Save data
                result.ExtraInfo = SaveImportData(dtNew, extraInfo, userInfo);
                result.Message = string.Format("Successfully imported {0} data", dtNew.Rows.Count);
            }
            return result;
        }

Abstract class verification process

       ///<summary>
       ///Check whether the data is normal
       ///</summary>
       ///<param name="dt">Data set</param>
       ///<param name="outputStream">output stream</param>
       ///<param name="sheet">data sheet</param>
       ///<param name="userInfo">user information</param>
       ///<param name="fileName">file name</param>
       ///<param name="DictColumnFields">The mapping relationship between English field names and Chinese column names</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;

           //Wrong data row style
            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;
                   //Set error message
                    sheetRow = sheet.GetRow(StartRowIndex + 1 + i);
                    errorCell = sheetRow.GetCell(columnCount);
                    if (errorCell == null)
                    {
                        errorCell = sheetRow.CreateCell(columnCount);
                    }
                    errorCell.CellStyle = cellErrorStyle;
                    errorCell.SetCellValue(errorMsg);
                }
            }

           //Output error message template
            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("error message");

               //Adaptive column width
                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;
        }

 Business class preservation method

       ///<summary>
       ///Save data in batch
       ///</summary>
       ///<param name="dt">data</param>
       ///<param name="extraInfo">Extra parameters</param>
       ///<param name="userInfo">user information</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;
       //The drop-down option text is converted to 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
            {
               //Save task data
                List<TaskUtil> list = dt.ToList<TaskUtil>();
                foreach (var item in list)
                {
                    TaskHelper.SaveTask(item);
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

3. Front-end code packaging

The upload plug-in is based on Baidu's webuploader plug-in, with a progress bar that works well

 Template download method

   /*
    * Function: Download the template file of imported data according to the type of business
    * Parameters: type: business type value refer to Ywdsoft.Utility.Excel.ExcelImportType enumeration
    * Return value: None
    * Created by: Flame Tail
    * Creation time: 2016-08-19
    */
    DownloadExcelTemplate: function (type) {
        if (type == "undefined") {
            return;
        }
        var param = {type: type };
        $.download("/Excel/DownLoadTemplate", param, "get");
    },

 Template upload

/*
    * Function: Download the template file of imported data according to the type of business
    * Parameters: options:
                {
                    type: business type, value refer to Ywdsoft.Utility.Excel.ExcelImportType enumeration
                    Ext: importable file types,
                    ReturnDetailData: Whether to return detailed data
                    after:function(){}//callback function
                }
    * Return value: None
    * Created by: Flame Tail
    * Creation time: 2016-08-22
    */
    ImportExcelTemplate: function (options) {
        if ($.isPlainObject(options)) {
            var defaults = {
                ReturnDetailData: 0
            };

            var param = $.extend({}, defaults, options);

            if (param.type != "undefined") {
               //Load style and js file
                $.loadFile("/Content/Css/plugins/webuploader/webuploader.css");
                $.loadFile("/Content/Scripts/plugins/webuploader/webuploader.min.js");
                if (!WebUploader.Uploader.support()) {
                    var error = "The upload control does not support your browser! Please try to upgrade the flash version or use the Chrome engine browser. <a target='_blank' href='http://www.chromeliulanqi.com'>download page</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 import</h4>' +
                                '</div>' +
                                '<div class="modal-body">' +
                                    '<div id="uploader" class="wu-example">' +
                                        '<p style="font-weight:bold;">Import instructions:</p><p class="pt5">The imported file is in EXCEL format. Please download the template and fill in the necessary information. Download the template<a href= "javascript:;" onclick="$.DownloadExcelTemplate(\'{0}\')">Please click here</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">Select file</div>' +
                                        '<button id="ctlBtn" class="btn btn-white btn-sm btn-start-uploader ml5" style="display:none;">Start uploading</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();
                });

               //When a file is added to the queue
                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();
                });

               //The creation progress bar is displayed in real time during the file upload process.
                uploader.on('uploadProgress', function (file, percentage) {
                    var $li = $('#' + file.id),
                        $percent = $li.find('.progress .progress-bar');

                   //Avoid repeated creation
                    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('Uploading');

                    $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("There are incorrect data in the uploaded data, please click <a class='red' href='{0}' target='_blank '>Download wrong data</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('Upload error');
                });

                uploader.on('uploadComplete', function (file) {
                    $('#' + file.id).find('.progress').fadeOut(200);
                });

                modal.modal('show');
            }
        }
    }

back to the top

Comparison of two ways to generate drop-down boxes in npoi

 I encountered a problem in the process of using npoi to operate excel to generate a drop-down box. It took more than a day to solve it. Here is how to use npoi to generate a drop-down box, and compare the advantages and disadvantages of the two methods of generating drop-down boxes.

method one:

   //Application area of ​​the drop-down box, starting row ending row starting column ending column
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
   //Drop-down options array
    DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(textlist);
    HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
    sheet.AddValidationData(dataValidate);

This method can be done with a few simple lines of code. The disadvantage is that an exception will be reported when all option characters are greater than 255. The exception information is as follows

String literals in formulas can't be bigger than 255 Chars ASCII"

Exception verification method

string[] textlist = new string[50];
for(int i = 0; i <50; i++)
        {
            textlist[i] = "Study hard and make progress every day";
        }

 Method 2: First create a Sheet dedicated to storing the value of the drop-down items, and write the value of each drop-down item into it

       ///<summary>
       ///Set the value of some columns can only enter pre-made data, display a drop-down box
       ///</summary>
       ///<param name="sheet">sheet to be set</param>
       ///<param name="textlist">the content displayed in the drop-down box</param>
       ///<param name="firstRow">start row</param>
       ///<param name="endRow">end row</param>
       ///<param name="firstCol">Start column</param>
       ///<param name="endCol">End column</param>
       ///<returns>set 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]);
            }

           //Load the contents of the drop-down list  
            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);

           //Set the cell on which the data validity is loaded, the four parameters are: start row, end row, start column, end column  
            CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
           //Data validity object  
            HSSFDataValidation validation = new HSSFDataValidation(regions, constraint);
           ////Cancel the pop-up error box
           //validation.ShowErrorBox = false;
            sheet.AddValidationData(validation);
            return sheet;
        }
  • Create a hidden sheet page
  • Write the drop-down option value into the corresponding column
  • Area reference sheet page data

    This method is equivalent to the following operations of Excel

There is no upper limit for this method, which is convenient for viewing the drop-down options in Excel and is more versatile.

back to the top

summary

  At this point, the realization of the universal import function of npoi has been completed, and the subsequent specific import business implementation is also very simple. Friends in need can directly use it.

      Download address of the sample code used in this article:

    GitHub address: https://github.com/CrazyJson/TaskManager

      Download link of experience tool: Task Management Framework V2.0

Reference: https://cloud.tencent.com/developer/article/1014517 npoi batch import implementation and related skills-Cloud + Community-Tencent Cloud