以前公司开发的这个就是单纯的导入功能，无法看到出错信息，要不然就是弹出个消息框，关闭后就没了，体验不好，又没法会查，所以我就自己写了个方法，封装个导出错误excle。

以前公司的写法，是先上传excle文件到后台，存成服务器本地文件返回文件路径，再在页面触发导入事件，将路径传入后台解析导入。好处就是前端用了统一的文件上传功能，坏处就是如果系统需要大量的数据导入，后台文件存储压力会很大，这里前端也做了改进，直接处理文件，不在服务器上存储了。

/**
     * 导入
     *
     * @param file
     * @param workCode
     * @param OMWorkOrderTempleteID
     * @param status
     * @param flowStatus
     * @param flowResult
     * @param req
     * @param response
     * @param model
     * @return
     */
    @RequestMapping("/importDataByIdcard")
    @ResponseBody
    public String importDataByIdcard(@RequestParam(name = "file", required = false) MultipartFile file,
                                    @RequestParam(name = "workCode") String workCode,
                                    @RequestParam(name = "OMWorkOrderTempleteID") String OMWorkOrderTempleteID, @RequestParam(name = "status") String status,
                                    @RequestParam(name = "flowStatus") String flowStatus, @RequestParam(name = "flowResult") String flowResult,
                                    HttpServletRequest req, HttpServletResponse response, Model model) {
        long stime = System.currentTimeMillis();

        String userName = req.getHeader("session_userName");
        String userID = req.getHeader("session_userid");
        JSONObject json = new JSONObject();
        //头部信息
        String[] hearts = new String[]{"regionCode", "unitName","ukeyType", "userName","userIdcard","UserTelephone","applyTime",  "yszxmk_dw_jb", "yszxmk_dw_sh", "yszxmk_dw_jb_dwgz", "yszxmk_dw_sh_fryz", "yszxmk_bm_sh", "ysbzmk_dw_jb", "ysbzmk_bm_sh", "cwjzglpt_dw_jb", "cwjzglpt_dw_sh", "cgglxt_dw_jb", "cgglxt_bm_jb", "cgglxt_bm_sh", "bmjs_dw_jb", "bmjs_dw_sh", "bmjs_bm_sh", "cbxt_dw_jb", "cbxt_dw_sh", "cbxt_bm_sh", "dzpjglxt_dw_jb", "dzpjglxt_dw_sh","zwglxt_dw_jb","zwglxt_dw_sh"};
        String[] heartVerify = new String[]{"","notNull", "",  "", "notNull","",  "",    "","", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""};
        /*Gson gson = new Gson();
        MyObject myObject = gson.fromJson(jsonObject.toString(), MyObject.class);*/

        try {
            SysUser user = userService.getUserInfo1(userID, "", "");//当前用户信息
            //获取的所有用户
            Map userMapS = new HashMap();
            userMapS.put("ugid", user.getUgid());
            List<SysUser> userList = userService.selectAllUser()
                    .stream().filter(SysUser-> SysUser.getIdCode()!=null && !SysUser.getIdCode().isEmpty()).collect(Collectors.toList());
            //所有用户信息
            Map<String, SysUser> userMap = userList.stream().collect(Collectors.toMap(SysUser -> SysUser.getIdCode().toUpperCase()+SysUser.getOMUnitCode(), SysUser->SysUser, (key1, key2) -> key2));
            Map<String, SysUser> userMap1 = userList.stream().collect(Collectors.toMap(SysUser -> SysUser.getIdCode().toUpperCase(), SysUser->SysUser, (key1, key2) -> key2));

            //单位信息
            Map<String, String> unitMap = userList
                    .stream().filter(SysUser-> SysUser.getOMUnitCode()!=null && !SysUser.getOMUnitCode().isEmpty()).collect(Collectors.toList())
                    .stream().collect(Collectors.toMap(SysUser -> SysUser.getMainunitname(), SysUser->SysUser.getOMUnitCode(), (key1, key2) -> key2));

            //ukey信息
            TCityformInfoLast last = new TCityformInfoLast();
            last.setWorkCode(workCode);
            last.setStatus(1L);
            last.setFlowStatus("2");
            last.setFlowResult("1");
            List<TCityformInfoLast> infoList = tCityformInfoLastService.selectTCityformInfoLastList(last);

            //Map<String, TCityformInfoLast> infoMap = infoList.stream().collect(Collectors.toMap(k-> k.getUserIdcard().toUpperCase(), Function.identity(), (key1, key2) -> key2));
            Map<String, TCityformInfoLast> infodwMap = infoList.stream().collect(Collectors.toMap(k-> k.getUserIdcard().toUpperCase()+k.getOMUnitCode(), Function.identity(), (key1, key2) -> key2));

            //excel文件路径
            if (file != null && !file.isEmpty()) {
                Workbook wb = WorkbookFactory.create(file.getInputStream());
                //开始解析
                Sheet sheet = wb.getSheetAt(0);     //读取sheet 0
                String sheetName = sheet.getSheetName();
                SheetError sheetError = new SheetError(0, sheetName, hearts.length);
                for (int rm = 4; rm <= sheet.getLastRowNum(); rm++) {//表头占3行
                    Row row = sheet.getRow(rm);

                    String error = "";//错误信息

                    String selectItem = "";//选中的权限
                    JSONObject rowJson = new JSONObject();
                    for (int cm = 0; cm < hearts.length; cm++) {//表头占3行
                        Cell cell = row.getCell(cm);
//                        System.out.println("rm======"+rm+";cm="+cm);
                        String cellValue = ExcelUtil.getCellValue(cell).trim();
                        String errorCell = ExcelUtil.checkCell(cellValue, heartVerify[cm]);
                        if (StringUtils.isNotEmpty(errorCell)) {//有错误
                            rowJson.put(hearts[cm], cellValue);
                            error += "【第" + (cm + 1) + "列】" + errorCell + ";";
                        } else {//没有错误
                            if (cm > 6) {
                                if (StringUtils.isNotEmpty(cellValue)) {
                                    selectItem += hearts[cm] + ",";
                                }
                            } else {
                                rowJson.put(hearts[cm], cellValue);
                            }
                        }
                    }
                    selectItem = StringUtils.delLastStr(selectItem);
                    //验证权限
                    /*if (StringUtils.isEmpty(selectItem)) {
                        error += "【岗位权限】未勾选岗位权限;";
                    }*/
                    //验证用户是否存在
                    String idcard = rowJson.getString("userIdcard").trim().toUpperCase();
                    String unitName = rowJson.getString("unitName").trim();

                    String oMUnitCode = unitMap.get(unitName);
                    if (StringUtils.isEmpty(oMUnitCode)) {
                        error += "【验证单位】单位不存在;";
                    }
                    SysUser sysUser = userMap.get(idcard+oMUnitCode);
                    if(StringUtils.isNotNull(oMUnitCode)){
                        sysUser = userMap.get(idcard+oMUnitCode);
                    }else{
                        sysUser = userMap1.get(idcard);
                    }
                    if (StringUtils.isNull(sysUser)) {
                        error += "【验证用户】用户身份证信息不存在;";
                    }else{
                        //验证重复
                        TCityformInfoLast infolast = infodwMap.get(idcard+sysUser.getOMUnitCode());

                        if (StringUtils.isNotNull(infolast)) {
                            String Source = infolast.getSource();
                            if("1".equals(Source)){
                                error += "【验证重复】用户ukey信息已存在，请通过工单变更ukey信息;";
                            }else{
                                rowJson.put("workOrderID",infolast.getWorkOrderID());
                            }
                        }
                    }

                    //判断是否有错误，有错误不提交
                    if (StringUtils.isNotEmpty(error)) {
                        sheetError.put(rm, error);
                    } else {
                        Gson gson = new Gson();
                        TCityformInfoLast obj = gson.fromJson(rowJson.toString(), TCityformInfoLast.class);
                        obj.setStatus(1L);
                        obj.setWorkCode(workCode);
                        obj.setFlowResult("1");//同意
                        obj.setFlowStatus("2");//办结
                        obj.setSource("2");//导入
                        if (StringUtils.isEmpty(OMWorkOrderTempleteID)) {
                            OMWorkOrderTempleteID = "20230215154445025101";
                        }
                        obj.setOMWorkOrderTempleteID(OMWorkOrderTempleteID);
                        obj.setSelectItem(selectItem);
                        obj.setOMUnitCode(sysUser.getOMUnitCode());
                        obj.setUnitName(sysUser.getMainunitname());
                        obj.setRegionCode(sysUser.getRegionCode());

                        obj.setUkeyType("10");//新增
                        obj.setUserIdcard(sysUser.getIdCode());
                        obj.setUserName(sysUser.getUsername());
                        obj.setUserUkeyCode(sysUser.getuKeyCode());
                        obj.setCreateUnitID(sysUser.getMainunitid());

                        obj.setName(user.getUsername());
                        obj.setIdcard(user.getIdCode());
                        obj.setUkeyCode(user.getuKeyCode());
                        obj.setApplyTime(DateUtils.getDate());
                        obj.setCreateUserID(user.getUserid());
                        obj.setCreateTime(new Date());
//                        obj.setCreateUnitID(user.getMainunitid());
                        if(StringUtils.isNotEmpty(obj.getWorkOrderID())){//修改
                            System.out.println("++++++修改++++++++++++++行："+rm);
                            tCityformInfoLastService.updateTCityformInfoLast(obj);
                        }else{//添加
                            System.out.println("++++++添加++++++++++++++行："+rm);
                            obj.setWorkOrderID(DBOperator.getBh());
                            tCityformInfoLastService.insertTCityformInfoLast(obj);
                            infodwMap.put(obj.getUserIdcard().toUpperCase()+obj.getOMUnitCode(),obj);
                        }
                    }
                }
                Gson gson = new Gson();
                if (sheetError.getCode()) {
                    //填充错误信息
                    ExcelUtil.makeImportErrorDataTip(wb, sheetError);
                    //导出excle
                    //ExcelUtil.exportExcle(wb,"ueky导入错误信息",response,req);
                    String base64Excel = ExcelUtil.base64Excel(wb);
                    json.put("wb", base64Excel);
                }
            }
            System.out.println("ueky导入库用时：" + (System.currentTimeMillis() - stime));
            json.put("flag", 0);
            json.put("desc", "导入成功");
        } catch (Exception e) {
            json.put("flag", 1);
            e.printStackTrace();
        }
        return json.toString();
    }

封装的填充错误信息，及导出错误excle

public static void makeImportErrorDataTip(Workbook workbook,SheetError sheetError) {
        if(sheetError.getCode()){
            makeImportErrorDataTip(workbook,sheetError.getCellNum(),sheetError.getSheet(),sheetError.getErrorMap());
        }
    }

    /**
     *
     * @param workbook
     * @param cellNum 填充错误信息的cell地址
     * @param sheetNum sheet下标
     * @param errorMap 错误信息
     */
    public static void makeImportErrorDataTip(Workbook workbook,int cellNum,int sheetNum, Map<Integer, String> errorMap) {
        // 读取sheet页
        Sheet sheet = workbook.getSheetAt(sheetNum);
        Iterator<Integer> iterator = errorMap.keySet().iterator();
        while (iterator.hasNext()) {
            Integer keys = iterator.next();
            String value = errorMap.get(keys);
            Row row = sheet.getRow(keys);
            row.createCell(cellNum);
            Cell cell = row.getCell(cellNum);
            cell.setCellValue(value);
        }
    }

前端也有了变化，以前是直接是form.submit()去提交，这次换成了ajax，有了变化

这里也记录下

html页面

<!-- 文件导入按钮 -->
<div id="uploadbtnByIdcard" onclick="uploadFileByIdcard()" class="sun_btn" title="使用导出数据为模版导入数据"
             style="background-color: #0080da;width: 120px;border:none;margin-bottom: 5px;display: none">
            按身份证号导入
</div>

<!-- 点击按钮触发这个，选择附件 -->
<input style="display:none;" name="file" type="file" id="fileContByIdcard" value="" />

页面js代码

/**
 * 点击按钮，触发fileContByIdcard上的点击事件
 */
function uploadFileByIdcard(){
   $('#fileContByIdcard').click();
}

/**
 * fileContByIdcard绑定修改事件，有变化就触发
 */
$('#fileContByIdcard').on('change', function(){
    flieChage(this,2);
});


/**
 *  选择文件
 */
function flieChage(obj,type){
        if(obj.files[0].size > 0){
            var filePath = $(obj).val();
            var strs = filePath.split('.');
            var suffix = strs[strs.length - 1];
            if(suffix == 'xlsx' || suffix == 'xls'){
                importData(obj.files[0],type);
            }else{
                jQuery.suntoast({
                    content: "上传失败，请上传Excel文件！",
                    position:"center",
                    time:1500
                });
                return;
            }
        }else{
            jQuery.suntoast({
                content: "上传失败，文件为空！",
                position:"center",
                time:1500
            });
            return;
        }
    }

/**
     * 导入
     * @param file
     */
    function importData(file,type){
        //参数封装
        var form = new FormData(); // FormData 对象
        form.append("file", file);//文件
        form.append("workCode", $("#workCode").val());
        form.append("OMWorkOrderTempleteID", $("#OMWorkOrderTempleteID").val());
        form.append("status", "1");
        form.append("flowStatus", "2");
        form.append("flowResult", "1");
        //弹出提示框
        loadShow(10,"导入中，请耐心等待...");
        var url =  "后端地址"
        $.ajax({
            url: url,
            type: "POST",
            processData: false, //必须
            contentType: false, //必须
            data: form,
            success: function(data) {
                console.log(data)
                loadClase();
                let fileRetObj = stringToJson(data);
                if(fileRetObj.flag == 0){
                    jQuery.suntoast({
                        content: "导入成功！",
                        position:"center",
                        time:1500
                    });
                    reloadGrid();
                    //错误信息excle数据流，解析成excle文件
                    downExcle(fileRetObj.wb,"ukey库错误信息.xls");
                }else{
                    jQuery.suntoast({
                        content: "导入失败！",
                        position:"center",
                        time:1500
                    });
                }

            }
        });
    }

js 封装的方法

/**
 * 数据流下载
 * @param content
 */
function downExcle(content, fileName) {
    let type = "application/vnd.ms-excel";
    downFile(content, fileName,type);
}

function downFile(content, fileName,type) {
    let bstr = window.atob(content);
    let n = bstr.length;
    let u8arr = new Uint8Array(n);
    while (n--) {
        u8arr[n] = bstr.charCodeAt(n);
    }

    const blob = new Blob([u8arr], {type: type})
    if ('download' in document.createElement('a')) {
        // 非IE下载
        const elink = document.createElement('a')
        elink.download = fileName
        elink.style.display = 'none'
        elink.href = URL.createObjectURL(blob)
        document.body.appendChild(elink)
        elink.click()
        URL.revokeObjectURL(elink.href) // 释放URL 对象
        document.body.removeChild(elink)
    } else {
        // IE10+下载
        navigator.msSaveBlob(blob, fileName)
    }
}

到这里就完成了

后端excle工具类方法：https://download.csdn.net/download/qq_40147106/89885929

js解析base64，下载为文件：https://download.csdn.net/download/qq_40147106/89885941