记录导入导出功能样例poi——自用

//poi 5.0.0，（也可以使用ExcelHelper）
    @PostMapping("/exportGoodsInstForUpdate")
    @ApiOperation(value = PROJECT_PRE_NAME + "-物料实例更新导出",  httpMethod = "POST", response = Result.class, notes = "<br/>")
    public void exportGoodsInstForUpdate(@RequestBody @Validated GoodsInstListPageReqDto reqDto, HttpServletResponse response) {
        log.info("物料实例导出.req:{}", JSON.toJSONString(reqDto));
　　　　　//获取数据
        reqDto.setPageNo(1);
        reqDto.setPageLength(20000);
        PageResultDto<GoodsInstDetailPageResDto> goodsInstListByCondition = goodsInstService.queryGoodsInstListForUpdate(reqDto);
        List<GoodsInstUpdateExportResDto> goodsInstExportResDtos = CopyUtils.copyList(goodsInstListByCondition.getResultList(), GoodsInstUpdateExportResDto.class);
        for (GoodsInstUpdateExportResDto goodsInstExportResDto : goodsInstExportResDtos) {
            goodsInstExportResDto.setGoodsCons(GoodsConsE.NO.getGoodsCons().toString().equals(goodsInstExportResDto.getGoodsCons())?"否":"是");
            goodsInstExportResDto.setStatus(GeneralStatus.DISABLE.getStatus().toString().equals(goodsInstExportResDto.getStatus())?"禁用":"启用");
            goodsInstExportResDto.setUseFlag(GeneralStatus.DISABLE.getStatus().toString().equals(goodsInstExportResDto.getUseFlag())?"未用":"在用");
            goodsInstExportResDto.setGoodsInstType(GoodsInstTypeE.NO.getGoodsInstType().toString().equals(goodsInstExportResDto.getGoodsInstType())?"否":"是");
        }
        Workbook wk = null;
        try {
            //拉取模板
            wk = this.buildUpdateExcel(reqDto.getOrganId(), reqDto.getGoodsTypeIds(),16);
            //参考crtRowHeadForUpdate方法设置大小
            Integer rId=3;
            int sheetIndex=0;
　　　　　　　//填充数据
            insertGoodsInst(wk,goodsInstExportResDtos,rId,sheetIndex);
        } catch (Exception e1) {
            log.error("导出EXCEL异常", e1);
        }
　　　　　//文件流写出
        OutputStream os = null;
        try {
            String fileName =URLEncoder.encode( "物品信息_","utf-8")+ new SimpleDateFormat("yyyyMMddHHmm").format(new Date());// 需要下载的文件名字
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=\""
                    + fileName + ".xls\"");
            response.setHeader("Connection", "close");

            os = response.getOutputStream();
            wk.write(os);
            os.flush();

        } catch (Exception e) {
            response.setContentType("text/xml;charset=UTF-8");
            response.setHeader("Content-Disposition", null);
        } finally {
            if (null != os) {
                try {
                    os.close();
                    os = null;
                } catch (Exception e) {
                    log.error("error:", e);
                }
            }
        }
    }

    /**
     * 插入数据
     * @param workbook 插入对象
     * @param goodsInstExportResDtos 数据
     * @param rId 开始行
     */
    private void insertGoodsInst(Workbook workbook,List<GoodsInstUpdateExportResDto> goodsInstExportResDtos,Integer rId,int sheetIndex){
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        StringBuilder sb=new StringBuilder();
        for (GoodsInstUpdateExportResDto goodsInstExportResDto : goodsInstExportResDtos) {
            Row row = sheet.createRow(rId++);
            sb.delete(0,sb.length());
            sb.append(goodsInstExportResDto.getGoodsTypeNames()).append("####").append(goodsInstExportResDto.getGoodsTypeId());
            setCellStyleAndValue(row.createCell(0), String.valueOf(goodsInstExportResDto.getGoodsTypeInstRelId()), cellStyle);
            row.createCell(1).setCellValue(String.valueOf(sb));
            row.createCell(2).setCellValue(goodsInstExportResDto.getGoodsCode());
            row.createCell(3).setCellValue(goodsInstExportResDto.getGoodsInstName());
            row.createCell(4).setCellValue(goodsInstExportResDto.getGoodsUnitName());
            row.createCell(5).setCellValue(goodsInstExportResDto.getAssisUnitName());
            row.createCell(6).setCellValue(goodsInstExportResDto.getAssisUnitRateDesc());
            row.createCell(7).setCellValue(goodsInstExportResDto.getGoodsInstModel());
            row.createCell(8).setCellValue(goodsInstExportResDto.getGoodsInstBrand());
            row.createCell(9).setCellValue(goodsInstExportResDto.getAbcType());
            row.createCell(10).setCellValue(goodsInstExportResDto.getGoodsCons());
            row.createCell(11).setCellValue(goodsInstExportResDto.getGoodsInstType());
            row.createCell(12).setCellValue(goodsInstExportResDto.getSalePrice());
            row.createCell(13).setCellValue(goodsInstExportResDto.getEffectiveDayName());
            row.createCell(14).setCellValue(goodsInstExportResDto.getWarningDayName());
            row.createCell(15).setCellValue(goodsInstExportResDto.getStatus());
            row.createCell(16).setCellValue(goodsInstExportResDto.getGoodsInstDesc());
        }
    }
　　 //设置单元格样式和值
    private Cell setCellStyleAndValue(Cell cell,String val,CellStyle style){
        cell.setCellValue(val);
        cell.setCellStyle(style);
        return cell;
    }
　　 //构建模板
    private Workbook buildUpdateExcel(Integer organId, List<Integer> goodsTypeIdList,int colLength){
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet1 = workbook.createSheet("物料信息更新");
        setSameSheetWidth(sheet1, colLength);
        Integer rId = 0;
        crtRowHeadForUpdate(rId, workbook, sheet1);
        //校验
        Map<String,Object> validMap = setValidataForUpdate(workbook, organId, goodsTypeIdList);
        String supplierFormula = validMap.get("positionFormula_position").toString();
        if (!StringUtils.isEmpty(supplierFormula)) {
            CellRangeAddressList positionRegions = new CellRangeAddressList(1, 65530, 1, 1);
            DVConstraint positionConstraint = DVConstraint.createFormulaListConstraint(supplierFormula);
            sheet1.addValidationData(new HSSFDataValidation(positionRegions, positionConstraint));
        }
        String unitFormula = validMap.get("positionFormula_unit").toString();
        if (!StringUtils.isEmpty(unitFormula)) {
            CellRangeAddressList positionRegions = new CellRangeAddressList(1, 65530, 4, 4);
            DVConstraint positionConstraint = DVConstraint.createFormulaListConstraint(unitFormula);
            sheet1.addValidationData(new HSSFDataValidation(positionRegions, positionConstraint));

            // 辅计量单位
            CellRangeAddressList assisUnitPositionRegions = new CellRangeAddressList(1, 65530, 5, 5);
            DVConstraint assisUnitPositionConstraint = DVConstraint.createFormulaListConstraint(unitFormula);
            sheet1.addValidationData(new HSSFDataValidation(assisUnitPositionRegions, assisUnitPositionConstraint));
        }
        String abctypesFormula = validMap.get("positionFormula_abcTypes").toString();
        if (!StringUtils.isEmpty(abctypesFormula)) {
            CellRangeAddressList positionRegions = new CellRangeAddressList(1, 65530, 9, 9);
            DVConstraint positionConstraint = DVConstraint.createFormulaListConstraint(abctypesFormula);
            sheet1.addValidationData(new HSSFDataValidation(positionRegions, positionConstraint));
        }
        String consFormula = validMap.get("positionFormula_cons").toString();
        if (!StringUtils.isEmpty(consFormula)) {
            CellRangeAddressList positionRegions = new CellRangeAddressList(1, 65530, 10, 10);
            DVConstraint positionConstraint = DVConstraint.createFormulaListConstraint(consFormula);
            sheet1.addValidationData(new HSSFDataValidation(positionRegions, positionConstraint));
        }
        String goodsInstTypeFormula = validMap.get("positionFormula_goodsInstType").toString();
        if (!StringUtils.isEmpty(goodsInstTypeFormula)) {
            CellRangeAddressList positionRegions = new CellRangeAddressList(1, 65530, 11, 11);
            DVConstraint positionConstraint = DVConstraint.createFormulaListConstraint(goodsInstTypeFormula);
            sheet1.addValidationData(new HSSFDataValidation(positionRegions, positionConstraint));
        }
        String statusNamesFormula = validMap.get("positionFormula_statusNames").toString();
        if (!StringUtils.isEmpty(statusNamesFormula)) {
            CellRangeAddressList positionRegions = new CellRangeAddressList(1, 65530, 15, 15);
            DVConstraint positionConstraint = DVConstraint.createFormulaListConstraint(statusNamesFormula);
            sheet1.addValidationData(new HSSFDataValidation(positionRegions, positionConstraint));
        }
        return workbook;
    }

    public void crtRowHeadForUpdate(Integer rId, Workbook workbook, Sheet sheet) {
        Row row1 = sheet.createRow(rId);
        Cell c0 = row1.createCell(0);
        c0.setCellValue("物品信息模板");
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontName("黑体");
        font.setFontHeight((short) 300);
        cellStyle.setFont(font);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        c0.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 16));//第一行,最后一行,第一列,最后一列
        //第二行
        //字体
        Font font0 = workbook.createFont();
        font0.setFontName("黑体");
        font0.setFontHeight((short) 200);
        //设置单元格格式
        CellStyle cellStyle2 = workbook.createCellStyle();
        cellStyle2.setWrapText(true);
        cellStyle2.setAlignment(HorizontalAlignment.LEFT);
        cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle2.setFont(font0);
        //创建单元格
        Row row2 = sheet.createRow(rId+1);
        row2.setHeight((short) 850);
        Cell c1 = row2.createCell(0);
        c1.setCellStyle(cellStyle2);
        c1.setCellValue(new HSSFRichTextString("说明：\r\n 1、已使用的物品不支持修改分类、名称、单位、规格、品牌\r\n 2、换算率仅支撑输入正整数。如单位：瓶，辅计量单位；箱，1箱=16瓶，则换算率输入16"));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 16));//第一行,最后一行,第一列,最后一列
        //第三行
        CellStyle cellStyle3 = workbook.createCellStyle();
        cellStyle3.setAlignment(HorizontalAlignment.LEFT);
        Row rowHead = sheet.createRow(rId+2);
        Cell datacell0 = rowHead.createCell(0);
        datacell0.setCellStyle(cellStyle3);
        datacell0.setCellValue("id(勿改！！！)");
        rowHead.createCell(1).setCellValue("分类(必填)");
        rowHead.createCell(2).setCellValue("编码");
        rowHead.createCell(3).setCellValue("物品名称(必填)");
        rowHead.createCell(4).setCellValue("单位(必填)");
        rowHead.createCell(5).setCellValue("辅计量单位");
        rowHead.createCell(6).setCellValue("换算率(1辅计量单位换算基本单位数量,填纯数字)");
        rowHead.createCell(7).setCellValue("规格");
        rowHead.createCell(8).setCellValue("品牌");
        rowHead.createCell(9).setCellValue("ABC类");
        rowHead.createCell(10).setCellValue("易耗品");
        rowHead.createCell(11).setCellValue("固定资产");
        rowHead.createCell(12).setCellValue("销售单价");
        rowHead.createCell(13).setCellValue("保质期");
        rowHead.createCell(14).setCellValue("提前预警天数");
        rowHead.createCell(15).setCellValue("是否启用");
        rowHead.createCell(16).setCellValue("备注");
    }

    public Map<String,Object> setValidataForUpdate(Workbook workbook, Integer organId, List<Integer> goodsTypeIdList) {
        Map<String,Object> vilidMap = new HashMap<>();
        Sheet sheet2 = workbook.createSheet("sheet2");
        int rdx = 1;
        List<String> typeNames=new ArrayList<>();
        if (ObjectUtils.isNotEmpty(goodsTypeIdList)){
            for (Integer integer : goodsTypeIdList) {
                List<String> strings = goodsTypeService.queryChildGoodsTypeNameIds(organId, integer);
                if (ObjectUtils.isNotEmpty(strings)){
                    typeNames.addAll(strings);
                }
            }
        }
        //去重
        List<String> newTypeNames = typeNames.stream().filter(Objects::nonNull).distinct().collect(Collectors.toList());
        if (ObjectUtils.isNotEmpty(newTypeNames)&& newTypeNames.size() > 0) {
            for (int i = 0; i < newTypeNames.size(); i++) {
                Row row1 = sheet2.createRow(i + 1);
                Cell c1 = row1.createCell(0);
                c1.setCellValue(newTypeNames.get(i));
            }
            vilidMap.put("positionFormula_position", "Sheet2!$A$" + rdx + ":$A$" + (rdx + newTypeNames.size()));
        } else {
            vilidMap.put("positionFormula_position", "");
        }
        rdx = rdx + newTypeNames.size() + 1;
        List<GoodsUnitDto> units = goodsUnitService.queryGoodsUnitOrganRel(organId);
        if (units.size() > 0) {
            for (int i = 0; i < units.size(); i++) {
                Row row2 = sheet2.createRow(rdx + i);
                Cell c2 = row2.createCell(0);
                c2.setCellValue(units.get(i).getGoodsUnitName());
            }
            vilidMap.put("positionFormula_unit", "Sheet2!$A$" + rdx + ":$A$" + (rdx + units.size()));
        } else {
            vilidMap.put("positionFormula_unit", "");
        }
        rdx = rdx + units.size() + 1;
        String[] abcTypes = new String[] {"A","B","C"};
        for (int i = 0; i < abcTypes.length; i++) {
            Row row2 = sheet2.createRow(rdx + i);
            Cell c2 = row2.createCell(0);
            c2.setCellValue(abcTypes[i]);
            vilidMap.put("positionFormula_abcTypes", "Sheet2!$A$" + rdx + ":$A$" + (rdx + abcTypes.length));
        }
        rdx = rdx + abcTypes.length + 1;
        String[] consNames = new String[] {"是","否"};
        putFormula("positionFormula_cons", consNames, rdx, vilidMap, sheet2);

        rdx = rdx + consNames.length + 1;
        String[] goodsTypeInsts = new String[] {"是","否"};
        putFormula("positionFormula_goodsInstType", goodsTypeInsts, rdx, vilidMap, sheet2);

        rdx = rdx + goodsTypeInsts.length + 1;
        String[] statusNames=new String[]{"启用","禁用"};
        putFormula("positionFormula_statusNames",statusNames,rdx,vilidMap,sheet2);

        if (sheet2 != null) {
            workbook.setSheetHidden(1, true);
        }
        return vilidMap;
    }

    @ApiOperation(value = PROJECT_PRE_NAME + "更新导入", httpMethod = "POST", response = Result.class, notes = "导入更新物料")
    @PostMapping("/importGoodsInstUpdate")
    @ResponseBody
    public Result importGoodsInstUpdate(@RequestParam("unitsFile") MultipartFile file, @RequestParam("organId") Integer organId, @AdminUserParam User user) {
        WmsUserThreadLocal.set(user);
        //总行数
        int totalCount=0;
        int succCount = 0;
        //取得当前上传文件的文件名称
        String realFileName = file.getOriginalFilename();
        //如果名称不为"",说明该文件存在，否则说明该文件不存在
        if (ObjectUtils.isEmpty(realFileName)||"".equals(realFileName.trim())) {
            return Result.fail("上传文件为空");
        }
        // 文件格式校验
        String suffix = realFileName.substring(realFileName.lastIndexOf(".") + 1);
        if (!suffix.contains("xls")&&!suffix.contains("xlsx")) {
            return Result.fail("文件格式不正确[" + realFileName + "]");
        }
        List<GoodsInstUpdateExportResDto> errorTotalList = new ArrayList<>();
        Workbook workbook = null;
        try {
            StringBuilder sb = new StringBuilder();
            workbook = WorkbookFactory.create(file.getInputStream());
            Sheet centersSheet = workbook.getSheetAt(0);
            Integer noOfEntries = JxlsUtils.getNumberOfRows(centersSheet, 2);
            if (noOfEntries < 4) {
                return Result.fail("导入内容不能为空");
            }
            Map<String,Integer> units = transformUnits(organId);
            Map<Integer, GoodsTypeInfoResDto> goodsTypeMap = getGoodsTypeMap(organId);;
            List<ImportUpdateGoodsInstDto> importUpdateGoodsInstDtoList = new ArrayList<>();
            List<GoodsInstUpdateExportResDto> failList=new ArrayList<>();
            List<GoodsInstUpdateExportResDto> oldUpdateExportResDtoList=new ArrayList<>();
　　　　　　　//循环验证
            for (int rowIndex = 3; rowIndex < noOfEntries; rowIndex++) {
                Row row = centersSheet.getRow(rowIndex);
                if (row == null || row.getCell(0) == null) {
                    continue;
                }
                //计算非空行数
                totalCount++;
                sb.delete(0,sb.length());
                GoodsInstUpdateExportResDto goodsInstUpdateExportResDto = getGoodsInstUpdateExportResDto(row);
                Integer goodsTypeInstRelId = goodsInstUpdateExportResDto.getGoodsTypeInstRelId();
                if (goodsTypeInstRelId==null) {
                    sb.append("id为空,请勿改动！").append(",");
                }
                String goodsTypeStr = goodsInstUpdateExportResDto.getGoodsTypeNames();
                if (StringUtils.isEmpty(goodsTypeStr) || !goodsTypeStr.contains("####")) {
                    sb.append("分类为空或填写错误！").append(",");

                }
                //校验物品编码是否符合规范
                String goodsCode = goodsInstUpdateExportResDto.getGoodsCode();
                String checkReturn = CommonUtil.checkGoodsCodeValid(goodsCode);
                if (StringUtils.isNotEmpty(checkReturn)) {
                    sb.append(checkReturn).append(",");
                }
                String goodsInstName = goodsInstUpdateExportResDto.getGoodsInstName();
                if (StringUtils.isEmpty(goodsInstName)) {
                    sb.append("物品名称不能为空！ ").append(",");
                }
                String unitName = goodsInstUpdateExportResDto.getGoodsUnitName();
                if (StringUtils.isEmpty(unitName)) {
                    sb.append("单位不能为空！").append(",");
                }
                // 校验福计量单位
                String assisUnitName = goodsInstUpdateExportResDto.getAssisUnitName();
                String assisUnitRate = goodsInstUpdateExportResDto.getAssisUnitRateDesc();
                if (StringUtils.isNotEmpty(assisUnitName) && StringUtils.isEmpty(assisUnitRate)) {
                    // 已选辅计量单位，未填换算率
                    sb.append("未输入换算率！").append(",");
                }
                if (StringUtils.isEmpty(assisUnitName) && StringUtils.isNotEmpty(assisUnitRate)) {
                    // 未选辅计量单位，已填换算率
                    sb.append("未选择辅计量单位！").append(",");
                }
                if (StringUtils.isNotEmpty(assisUnitRate) && !NumberUtil.isPositive(assisUnitRate)) {
                    sb.append("换算率只能填正整数！").append(",");
                }
                if (StringUtils.isNotEmpty(assisUnitRate) && assisUnitRate.length() > 5) {
                    // 换算率不能超过五位数
                    sb.append("换算率不能超过五位数！").append(",");
                }
                if (unitName.equals(assisUnitName)) {
                    // 基本单位不能和辅助单位相等
                    sb.append("基本单位不能和辅计量单位相等！").append(",");
                }
                //校验销售单价
                String salePrice=goodsInstUpdateExportResDto.getSalePrice();
                if (StringUtils.isNotEmpty(salePrice) && !NumberUtil.isNumber(salePrice)) {
                    sb.append("销售单价只能填数字！").append(",");
                }
                //校验保质期
                String effectiveDay = goodsInstUpdateExportResDto.getEffectiveDayName();
                if (ObjectUtils.isNotEmpty(effectiveDay)) {
                    boolean checkFlag=true;
                    if (!NumberUtil.isEffectiveDayMatch(effectiveDay)) {
                        sb.append("保质期格式错误！").append(",");
                        //错误则不继续验证
                        checkFlag=false;
                    }
                    //截取数字
                    if (checkFlag){
                        effectiveDay = NumberUtil.getMatchNumbers(effectiveDay);
                        if (!NumberUtil.isPositive(effectiveDay)) {
                            sb.append("保质期只能填正整数！").append(",");
                            checkFlag=false;
                        }
                        if (checkFlag&&effectiveDay.length() > 3) {
                            sb.append("保质期限3位数内！").append(",");
                        }
                    }
                }
                String warningDay = goodsInstUpdateExportResDto.getWarningDayName();
                if (ObjectUtils.isNotEmpty(warningDay)) {
                    boolean checkFlag=true;
                    if (!NumberUtil.isWarningDayMatch(warningDay)) {
                        sb.append("提前预警天数格式错误！").append(",");
                        checkFlag=false;
                    }
                    if (checkFlag){
                        //截取数字
                        warningDay=NumberUtil.getMatchNumbers(warningDay);
                        if (warningDay.length() > 3) {
                            sb.append("提前预警天数限3位数内！").append(",");
                        }
                    }
                }
                //是否启用
                String  statusName= goodsInstUpdateExportResDto.getStatus();
                if (StringUtils.isEmpty(statusName)){
                    sb.append("是否启用未选择！").append(",");
                }
                if (!StringUtils.equals(statusName,GoodsInstStatusEnum.ENABLE.getName())&&!StringUtils.equals(statusName,GoodsInstStatusEnum.DISABLE.getName())){
                    sb.append("是否启用状态不正确！").append(",");
                }

                ImportUpdateGoodsInstDto importUpdateGoodsInstDto = getImportUpdateGoodsInstDto(goodsInstUpdateExportResDto);
                if (!goodsTypeMap.containsKey(importUpdateGoodsInstDto.getGoodsTypeId())) {
                    sb.append("分类不存在！").append(",");
                } else {
                    GoodsTypeInfoResDto goodsTypeInfoResDto = goodsTypeMap.get(importUpdateGoodsInstDto.getGoodsTypeId());
                    String goodsTypeNames = goodsTypeStr.substring(0, goodsTypeStr.indexOf("#"));
                    if (null != goodsTypeInfoResDto && !goodsTypeInfoResDto.getGoodsTypeNames().equals(goodsTypeNames)) {
                        sb.append("分类名称和分类ID不匹配！").append(",");
                    }
                }

                //记录不同分类对应的行号
                goodsInstUpdateExportResDto.setRow(rowIndex+1);
                importUpdateGoodsInstDto.setRow(rowIndex + 1);
                importUpdateGoodsInstDto.setOrganId(organId);
                importUpdateGoodsInstDto.setEffectiveDay(effectiveDay);
                importUpdateGoodsInstDto.setWarningDay(StringUtils.isEmpty(warningDay)?"0":warningDay);
                importUpdateGoodsInstDto.setAssisUnitId(units.get(assisUnitName));

                if (StringUtils.isNotEmpty(importUpdateGoodsInstDto.getUnitName())) {
                    importUpdateGoodsInstDto.setUnit(units.get(importUpdateGoodsInstDto.getUnitName()));
                    importUpdateGoodsInstDto.setGoodsUnit(importUpdateGoodsInstDto.getUnit());
                }
                if (importUpdateGoodsInstDto.getUnit() == null){
                    sb.append("单位不存在！").append(",");
                }
                //未通过初步校验的数据
                if (StringUtils.isNotEmpty(sb)){
                    goodsInstUpdateExportResDto.setErrMsg(sb.substring(0,sb.length()-1));
                    failList.add(goodsInstUpdateExportResDto);
                    continue;
                }
                //存储通过初步校验的数据
                importUpdateGoodsInstDtoList.add(importUpdateGoodsInstDto);
                //原始数据
                oldUpdateExportResDtoList.add(goodsInstUpdateExportResDto);
            }
            if(importUpdateGoodsInstDtoList.size()>5000){
                return Result.fail("导入数量超过5000条,请分批导入!");
            }
            errorTotalList.addAll(failList);
            List<ImportUpdateGoodsInstDto> result = goodsInstService.saveImportUpdateGoodsInsts(organId,importUpdateGoodsInstDtoList);
            //存储错误结果集
            if(ObjectUtils.isNotEmpty(result)&&ObjectUtils.isNotEmpty(oldUpdateExportResDtoList)){
                List<GoodsInstUpdateExportResDto> goodsInstUpdateExportResDtos = getGoodsInstUpdateExportResDtoByRow(result,oldUpdateExportResDtoList);
                errorTotalList.addAll(goodsInstUpdateExportResDtos);
            }
        } catch (Exception e) {
            log.error("导入物料出错.",e);
            return Result.fail();
        }
        //回写数据
        String errInfoFileName=null;
        if (ObjectUtils.isNotEmpty(errorTotalList)){
            //括号会导致文件路径无法被找到
            realFileName = FileNameUtil.replaceBracketAndBlank(realFileName);
            errInfoFileName = writeBackWorkBook(errorTotalList, organId, realFileName);
        }
        succCount=totalCount-errorTotalList.size();
        ImportGoodsUnitResDto res = new ImportGoodsUnitResDto(totalCount, succCount, errorTotalList.size(),errInfoFileName);
        return Result.success(res);
    }

　　//回写功能
    private String writeBackWorkBook(List<GoodsInstUpdateExportResDto> resDtoList, Integer organId, String fileName){
        OutputStream fileOut = null;
        String errInfoFileName = null;
        Workbook workbook= null;
        try {
            //拉取模板
            workbook = buildUpdateExcel(organId, null,17);
            //填充数据
            insertUpdateGoodsInst(workbook,resDtoList,3,0);
            fileName = StringUtils.replace(fileName, " ", "");
            String fileNameBuilder = new StringBuilder()
                    .append(fileName, 0, fileName.lastIndexOf("."))
                    .append(DateUtils.formatFromDate(DateUtils.DATA_FORMAT_17, new Date()))
                    .append("Rsp.xls").toString();
            errInfoFileName = ExcelHandleUtils.getCarTempUploadPath() + fileNameBuilder;
            fileOut = new FileOutputStream(errInfoFileName);
            workbook.write(fileOut);
            fileOut.flush();
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        } finally {
            if (null != fileOut) {
                try {
                    fileOut.close();
                } catch (Exception e) {
                    log.error("文件关闭异常:", e);
                }
            }
        }
        return errInfoFileName;
    }

    /**
     * 对比原始数据和转换后的数据行数，从而获取原始数据
     * @param reqDto 转换后的数据
     * @param oldResDto 原始数据
     * @return
     */
    private List<GoodsInstUpdateExportResDto> getGoodsInstUpdateExportResDtoByRow(List<ImportUpdateGoodsInstDto> reqDto, List<GoodsInstUpdateExportResDto> oldResDto) {
        List<GoodsInstUpdateExportResDto> resDtoList=new ArrayList<>();
        for (ImportUpdateGoodsInstDto importReqDto : reqDto) {
            for (GoodsInstUpdateExportResDto exportResDto : oldResDto) {
                if (importReqDto.getGoodsTypeInstRelId().equals(exportResDto.getGoodsTypeInstRelId())){
                    exportResDto.setErrMsg(importReqDto.getErrMsg());
                    resDtoList.add(exportResDto);
                }
            }
        }
        return resDtoList;
    }

    /**
     * 组装excel读取结果为ImportUpdateGoodsInstDto
     */
    private ImportUpdateGoodsInstDto getImportUpdateGoodsInstDto(GoodsInstUpdateExportResDto resDto){
        ImportUpdateGoodsInstDto importUpdateGoodsInstDto = new ImportUpdateGoodsInstDto();
        importUpdateGoodsInstDto.setGoodsTypeInstRelId(resDto.getGoodsTypeInstRelId());
        importUpdateGoodsInstDto.setGoodsTypeId(NumberUtils.toInt(resDto.getGoodsTypeNames().substring(resDto.getGoodsTypeNames().lastIndexOf("#") + 1)));
        importUpdateGoodsInstDto.setGoodsInstName(resDto.getGoodsInstName());
        importUpdateGoodsInstDto.setGoodsInstBrand(resDto.getGoodsInstBrand());
        importUpdateGoodsInstDto.setGoodsInstModel(resDto.getGoodsInstModel());
        importUpdateGoodsInstDto.setGoodsCode(resDto.getGoodsCode());
        importUpdateGoodsInstDto.setGoodsAbc(resDto.getAbcType());
        importUpdateGoodsInstDto.setGoodsTypeNames(resDto.getGoodsTypeNames());
        //校验易耗品
        int goodsCons;
        if (GoodsConsE.YES.getName().equals(resDto.getGoodsCons())) {
            goodsCons = GoodsConsE.YES.getGoodsCons();
        } else {
            goodsCons = GoodsConsE.NO.getGoodsCons();
        }
        importUpdateGoodsInstDto.setGoodsCons(goodsCons);
        importUpdateGoodsInstDto.setUnitName(resDto.getGoodsUnitName());
        importUpdateGoodsInstDto.setGoodsInstType(GoodsInstTypeE.YES.getName().equals(resDto.getGoodsInstType()) ?
                GoodsInstTypeE.YES.getGoodsInstType() : GoodsInstTypeE.NO.getGoodsInstType());
        importUpdateGoodsInstDto.setSalePrice(StringUtils.isEmpty(resDto.getSalePrice())?new BigDecimal(0): new BigDecimal(resDto.getSalePrice()));
        String goodsUnitName = resDto.getGoodsUnitName();
        String unit=null;
        if (goodsUnitName.contains("天")){
            unit="天";
        }else if(goodsUnitName.contains("月")){
            unit="月";
        }
        importUpdateGoodsInstDto.setStatusName(resDto.getStatus());
        Integer status=resDto.getStatus()==null? GoodsInstStatusEnum.ENABLE.getStatus():
                resDto.getStatus().equals(GoodsInstStatusEnum.ENABLE.getName())?GoodsInstStatusEnum.ENABLE.getStatus():
                        GoodsInstStatusEnum.DISABLE.getStatus();
        importUpdateGoodsInstDto.setStatus(status);
        importUpdateGoodsInstDto.setEffectiveDayUnit(EffectiveDayUnitEnum.getValue(unit));
        importUpdateGoodsInstDto.setGoodsDesc(resDto.getGoodsInstDesc());
        if (StringUtils.isNotEmpty(resDto.getAssisUnitRateDesc())){
            String assisUnitRateDesc = resDto.getAssisUnitRateDesc();
            String assisUnitRateDesc2=null;
            //可能出现换算率为（16(1台=16个的情况)，避免转换成Integer时错误）
            if (!NumberUtil.isPositive(assisUnitRateDesc)){
                if (assisUnitRateDesc.contains("(")){
                    assisUnitRateDesc2 = StringUtils.substringBefore(resDto.getAssisUnitRateDesc(), "(");
                }
                //再次判断
                if (!NumberUtil.isPositive(assisUnitRateDesc2)){
                    assisUnitRateDesc2=null;
                }
            }else {
                assisUnitRateDesc2=assisUnitRateDesc;
            }
            importUpdateGoodsInstDto.setAssisUnitRate(assisUnitRateDesc2==null?null:Integer.valueOf(assisUnitRateDesc2));
        }
        return importUpdateGoodsInstDto;
    }

    /**
     * 读取excel表格转化为GoodsInstUpdateExportResDto
     * @param row
     * @return
     */
    private GoodsInstUpdateExportResDto getGoodsInstUpdateExportResDto(Row row) {

        GoodsInstUpdateExportResDto resDto = new GoodsInstUpdateExportResDto();
        //id
        resDto.setGoodsTypeInstRelId(Integer.valueOf(JxlsUtils.readAsString(0, row)));
        //分类
        resDto.setGoodsTypeNames(JxlsUtils.readAsString(1, row));
        //编码
        resDto.setGoodsCode(JxlsUtils.readAsString(2, row));
        //名称
        resDto.setGoodsInstName(JxlsUtils.readAsString(3, row));
        //单位
        resDto.setGoodsUnitName(JxlsUtils.readAsString(4, row));
        //辅助计量单位
        resDto.setAssisUnitName(JxlsUtils.readAsString(5, row));
        //换算率
        resDto.setAssisUnitRateDesc(JxlsUtils.readAsString(6, row));
        //规格
        resDto.setGoodsInstModel(JxlsUtils.readAsString(7, row));
        //品牌
        resDto.setGoodsInstBrand(JxlsUtils.readAsString(8, row));
        //ABC类
        resDto.setAbcType(JxlsUtils.readAsString(9, row));
        //易耗品
        resDto.setGoodsCons(JxlsUtils.readAsString(10, row));
        //是否固定资产
        resDto.setGoodsInstType(JxlsUtils.readAsString(11, row));
        //销售单价
        resDto.setSalePrice(JxlsUtils.readAsString(12, row));
        //保质期
        resDto.setEffectiveDayName(JxlsUtils.readAsString(13, row));
        //提前预警天数
        resDto.setWarningDayName(JxlsUtils.readAsString(14, row));
        //是否启用
        resDto.setStatus(JxlsUtils.readAsString(15, row));
        //备注
        resDto.setGoodsInstDesc(JxlsUtils.readAsString(16, row));
        return resDto;
    }


    /**
     * 插入数据
     *
     * @param workbook               插入对象
     * @param goodsInstExportResDtos 数据
     * @param rId                    开始行
     */
    private void insertUpdateGoodsInst(Workbook workbook, List<GoodsInstUpdateExportResDto> goodsInstExportResDtos, Integer rId, int sheetIndex) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        removeMergedRegion(sheet,new CellRangeAddress(0,0,0,16));
        removeMergedRegion(sheet,new CellRangeAddress(1,1,0,16));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 17));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 17));
        insertGoodsInst(workbook, goodsInstExportResDtos, rId, sheetIndex);
        // 错误信息列
        int CHECKOUT_COL_IDX = 17;
        // 错误信息背景格式
        CellStyle failStyle = ExcelHandleUtils.getCellStyle(workbook.createCellStyle(), IndexedColors.RED);
        // 设置错误列列名
        ExcelHandleUtils.setCellErrorValue(failStyle, sheet.getRow(2), CHECKOUT_COL_IDX, "错误提示");
        for (GoodsInstUpdateExportResDto goodsInstExportResDto : goodsInstExportResDtos) {
            Row row = sheet.getRow(rId++);
            row.getCell(1).setCellValue(goodsInstExportResDto.getGoodsTypeNames());
            ExcelHandleUtils.setCellErrorValue(failStyle,row , CHECKOUT_COL_IDX, goodsInstExportResDto.getErrMsg());
        }
    }
　　//解除合并区域
    private  boolean removeMergedRegion(Sheet sheet, CellRangeAddress mergedRegionToRemove) {
            boolean removed = false;
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
                if (mergedRegionToRemove.equals(mergedRegion)) {
                    sheet.removeMergedRegion(i);
                    removed = true;
                    break;
                }
            }
            return removed;
        }

仅供参考。