excel导入标准化

excel导入较导出还是复杂一些，一般分为三个步骤.市面上低代码平台可以将常用的操作固化，并且形成收益，这也是挺好的。我将我的一些总结分享到网上也是我自己乐意的。毕竟尊重技术的还是搞技术的自身，一般企业老板并不太关心技术代码到底有什么价值，认为脱离了业务代码，这些代码就像封存多年“宝物”上的灰尘。
1下载导入模板
先定义一个对话框，下载导入的excel模板

<template>
    <BaseDialog ref="dialog" title="导入其他入库单" :visible='visible' @close="cancelDialogs" @confirm='importConfirm' 
    :confirm-loading='isdeling'  cancelText='取消' confirmText='导入'>
      <p class="firstStep">第一步：请点击下面的链接下载Excel模板，并填写销售出库单信息</p>  
      <p class="upload" @click="upload()">下载模板</p>
      <p>第二步：导入完成的Excel文件</p>
      <el-upload class="upload-demo" ref="upload" accept=".xls,.xlsx" action="#" 
      :on-change="handleChange" :before-upload='beforeUpload' 
      :on-preview="handlePreview" :on-remove="handleRemove" 
      :file-list="fileList" :auto-upload="false" :http-request="httpRequest">
        <span slot="trigger" class="upload">选取文件</span>
        <!-- :action="UploadUrl()" -->
      </el-upload>
    </BaseDialog>
  </template>
  
  <script>
    import BaseDialog from '@/components/base/BaseDialog.vue';
    import {computed } from 'vue'
    import { useAppStore } from '@/store'
    const appStore = useAppStore()
    const userInfo = computed(() => appStore.userInfo);
  
    export default {
      props: {
        // period: {
        //   type: String,
        // },
        visible: {
          type: Boolean,
        },
        cancelDialog: {
          type: Function,
          default: () => { },
        },
        initTable:{
          type: Function,
          default: () => { },
        }
      },
      components:{
        BaseDialog
      },
      data() {
        return {
          fileList: [],
          fileData: '',
          isdeling: false,
          tableData: [],
          status:  false,
          message : '请选择上传文件',
        };
      },
      methods: {
        httpRequest(param) {
          console.log(param.file);
        },
        handleChange(file, fileList){
          this.fileList = fileList.slice(-1);  //限定上传文件为一个
        },
        handleRemove(file, fileList) {
          this.fileData = '';
          this.status = false;
          this.message = '请选择上传文件';
        },
        beforeUpload(file, fileList) {
          let testFile = file.name.substring(file.name.lastIndexOf('.')+1)
          const extension = testFile === 'xls'
          const extension2 = testFile === 'xlsx'
          const isLt1M = file.size / 1024 / 1024 < 1;
  
          if(!extension && !extension2) {
            this.message = '上传文件只能是xls或xlsx格式！'
            this.status = false;
          }else if (!isLt1M) {
            this.message = '上传Excel文件大小不能超过 1MB!'
            this.status = false;
          }else{
            let fd = new FormData()
            fd.append('file', file)
            fd.append('userId',userInfo.value.id)
            fd.append('asId', userInfo.value.currentAsId)
            // fd.append('period', this.period)
            this.fileData = fd;
            this.status = true;
          }
        },
        handlePreview(file) {
          console.log(file);
        },
        cancelDialogs(){
          this.cancelDialog();
          this.fileData = '';
          this.status = false;
          this.message = '请选择上传文件';
        },
        async importConfirm() {
          this.tableData = [];
          this.$refs.upload.submit()
          if (this.fileData && this.fileData != ''&& this.status) {
            this.isdeling = true;
            // 导入这里有点差别,post请求获取的导入后的结果
            const re = await this.$api.invOrder.otherInstockOrder.imp(this.fileData)
            const res = re.data
            if (res.success) {
              this.cancelDialog();
              this.initTable();
              this.isdeling = false;
              this.fileList = [];
              this.fileData = '';
              this.status = false;
              this.message = '请选择上传文件';
              var reason = ''
              if ( res.data.failNum > 0 ) {
                var i = 0;
                res.data.failReason.forEach(each => {
                  i++;
                  if (i < 5) {
                    reason = reason.concat(`<p style="padding-top:0.5rem">第${each.index}行${each.reason},</p>`)
                  }
                  if (i == 5) {
                    reason = reason.concat(`<p style="padding-top:0.5rem">...</p>`)
                  }
                  this.tableData.push({
                    错误行: "第" + each.index + "行",
                    错误信息: each.reason
                  });
                })
                //有错误信息将错误信息通过excel导出
                if (this.tableData.length > 0) {
                  this.$tool.json2Excel(this.tableData)
                }
              }
              let msg = `<p>总条数：${res.data.totalNum} , 成功条数：${res.data.successNum} , 失败条数：${res.data.failNum}</p>${reason}`
              this.$message.success({
                duration: 3000,
                dangerouslyUseHTMLString: true,
                message: msg
              });  
            } else {
              this.isdeling = false;
              this.fileList = [];
              this.fileData = '';
              this.cancelDialog();
              this.$message.error(res.msg);
            }
          } else {
            this.isdeling = false;
            this.fileList = [];
            this.fileData = '';
            this.cancelDialog();
            this.$message.error(this.message);
            return;
          }
        },
  
        async upload() {
          let name = '其他入库单导入模板.xls'
          const res = await this.$api.invOrder.otherInstockOrder.getTpl({asId:userInfo.value.currentAsId})
          let data = res.data;
          let url = window.URL.createObjectURL(new Blob([data], { type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" }))
          let link = document.createElement('a')
          link.style.display = 'none'
          link.href = url;
          link.setAttribute('download', name)
          document.body.appendChild(link)
          link.click()
          document.body.removeChild(link)
        },
        
  
      }
    };
  </script>
  
  <style lang="less" scoped>
    .firstStep {
      margin: 24px 0 12px;
    }
    .upload {
      cursor: pointer;
      color: #4f71ff;
    }
  </style>
  

后台代码也是固定的写法

    @GetMapping("getTpl")
    @ApiOperation("其他入库单导入模板")
    public void getSalOutstockTpl(@ApiParam(value = "账套ID", required = true) @RequestParam Integer asId,
                                  HttpServletResponse response){
        String fileName = "其他入库单导入模板.xls";

        //设置响应头
        try (
                InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("xls/" + fileName);
                OutputStream os = response.getOutputStream();
        ) {

            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            byte[] buf = new byte[1024];
            int len = 0;
            while ((len = is.read(buf)) != -1) {
                os.write(buf, 0, len);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

2 导入解析
excel的模板样式如下图

controller层,基本是套路，因为具体实现逻辑是在service层

 @ApiOperation("导入")
    @PostMapping("imp")
    public ResponseResult<ExcelErrMsg> imp(Integer asId, Integer userId, MultipartFile file){
        ResponseResult<ExcelErrMsg> resp = new ResponseResult<>(true, "导入完成!");
        try {
            ExcelErrMsg excelErrMsg = otherInstockListExcelService.imp(asId, userId, file);
            resp.setData(excelErrMsg);
        } catch (ExcelCommonException | ExcelAnalysisException e) {
            log.error(e.getMessage(), e);
            resp = new ResponseResult<>(false, "请使用正确的导入模板导入");
        } catch (RuntimeException e) {
            log.error(e.getMessage(), e);
            resp = new ResponseResult<>(false, e.getMessage());
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            resp = new ResponseResult<>(false, "导入失败!");
        }
        return resp;
    }

service层，这里相当于是基于easyexcel定义的一个算法模板，基本解析套路也就这样。

 @Override
    public ExcelErrMsg imp(Integer asId, Integer createUser, MultipartFile file) throws IOException {
        List<OtherInstockExcelDto> lines =new ArrayList<>();
        List<ExcelErrDetail> excelErrDetails = new ArrayList<>();
        // 开始处理excel
        EasyExcel.read(file.getInputStream(), new AnalysisEventListener<Map<Integer, String>>() {
            /**
             * 从这里判断出模板类型,目前是根据列数
             */
            @Override
            public void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context) {
                //校验表头
                if (headMap.isEmpty()) {
                    throw new RuntimeException("导入模板表头不可为空");
                }
                super.invokeHead(headMap, context);
            }

            /**
             * 表头信息
             *   检查的信息有限
             *   1. 检查表头是否存在
             *   2. 检查列数是否正确
             * @param headMap
             * @param context
             */
            @Override
            public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {

            }

            /**
             * 解析行信息
             * @param integerStringMap
             * @param analysisContext
             */
            @Override
            public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                //数据行号
                Integer rowIndex = analysisContext.readRowHolder().getRowIndex() + 1;
                // 行级校验
                if (rowIndex>4){
                    // 数据是从第5行开始
                    checkRow(integerStringMap,rowIndex,lines,excelErrDetails);
                }
            }

            /**
             * excel解析完毕后的数据处理
             *   逻辑是先完善基础设置，然后再写入期初
             *   处理辅助核算、处理科目、处理期初这三个顺序不能变
             * @param analysisContext
             */
            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                // 校验数据的正确性
                checkRecords(asId,lines,excelErrDetails);
                // 过滤掉存在问题的科目数据
                List<OtherInstockExcelDto> noerrorList = lines.stream().filter(a -> !a.isHasError()).collect(Collectors.toList());
                // 处理没有问题的信息
                handle(asId,createUser,noerrorList);
            }

            /**
             * 处理异常
             * @param exception
             * @param context
             * @throws Exception
             */
            @Override
            public void onException(Exception exception, AnalysisContext context) throws Exception {
                if (exception instanceof RuntimeException) {
                    throw (RuntimeException) exception;
                }
            }
        }).sheet().doRead();
        // 处理excel中的错误信息
        List<ExcelErrDetail> sortErrs = excelErrDetails.stream().sorted(Comparator.comparing(ExcelErrDetail::getIndex)).collect(Collectors.toList());
        List<Integer> errs = sortErrs.stream().map(ExcelErrDetail::getIndex).distinct().collect(Collectors.toList());
        ExcelErrMsg excelErrMsg = new ExcelErrMsg();
        // 所有条数求和
        excelErrMsg.setTotalNum(lines.size());
        excelErrMsg.setFailNum(errs.size());
        excelErrMsg.setSuccessNum(excelErrMsg.getTotalNum() - excelErrMsg.getFailNum());
        excelErrMsg.setFailReason(sortErrs);
        return excelErrMsg;
    }

校验excel中基本行数据,这里就不展开，因为这里是并不与数据库有交互，因为一次性校验，查看那些数据有问题，所以遇到错误不需要停止。

private void checkRow(Map<Integer, String> row, Integer rowIndex, List<OtherInstockExcelDto> records
            , List<ExcelErrDetail> excelErrDetails,PsiAccountSet psiAccountSet){
        OtherInstockExcelDto line = new OtherInstockExcelDto();
        line.setIndex(rowIndex);
        // *单据日期
        if (CheckEmptyUtil.isEmpty(row.get(0))){
            excelErrDetails.add(new ExcelErrDetail(rowIndex, "单据日期不能为空"));
            line.setHasError(true);
        }
        line.setBillDate(DateUtil.parseDate(row.get(0)));
        // *数量
        if (CheckEmptyUtil.isEmpty(row.get(13))){
            excelErrDetails.add(new ExcelErrDetail(rowIndex, "数量不能为空"));
            line.setHasError(true);
        }
        line.setSl(ExcelUtil.getNumber(row.get(13),psiAccountSet.getSlxsw()));
         //
        records.add(line);
        }

接着有些数据导入之前是一些基础数据，因此需要判断数据库中是否存在，因此进入到第二层筛选
为什么会有下面的代码，因为财务辅助核算中供应商、客户、存货基础数据都是在一张表中，有的用户，客户资料非常之多，因此即使你使用二级缓存，将数据全部加载进去，也是非常消耗性能的，因此获取基础数据的逻辑就是用到了什么，就取什么。

  /**
     * 获取辅助核算的map，key：code_type，value：id
     * 包含：供应商、客户、存货
     * @param records
     * @return
     */
    Map<String,Integer> getCodeIdMap(Integer asId,List<OtherInstockExcelDto> records){
        if (!CheckEmptyUtil.isEmpty(records)){
            Set<String> codes = new HashSet<>();
            for (OtherInstockExcelDto record: records){
                if (!CheckEmptyUtil.isEmpty(record.getVendorCode())){
                    codes.add(record.getVendorCode());
                }
                if (!CheckEmptyUtil.isEmpty(record.getCustomerCode())){
                    codes.add(record.getCustomerCode());
                }
                if (!CheckEmptyUtil.isEmpty(record.getStockCode())){
                    codes.add(record.getStockCode());
                }
            }
            List<Integer> aaTypes = Arrays.asList(CommonSettingConstants.AssistingAccountingType.STOCK_ID,
                    CommonSettingConstants.AssistingAccountingType.CUSTOMER_ID,CommonSettingConstants.AssistingAccountingType.VENDOR_ID);
            return accAssistingAccountingService.getCodeIdMap(asId,aaTypes,new ArrayList<>(codes));
        }
        return new HashMap<>();
    }

校验基础资料，取出最小集出来，然后判断系统中是否存在。如果不存在，则需要在导入之前先维护。

 /**
     * 基础数据的正确性
     * @param records
     */
    public void checkRecords(Integer asId,List<OtherInstockExcelDto> records,List<ExcelErrDetail> excelErrDetails,PsiAccountSet psiAccountSet){
        // 辅助核算：商品信息、供应商、客户辅助核算
        Map<String,Integer> aaMap = getCodeIdMap(asId,records);
        // 仓库信息
        Map<String,String> whMap = psiInvWarehouseService.getNameIdMap(asId);
        // 数据字典：单位、入库类型
        Map<String,String> ccMap = ccsDataDictionaryService.selectNameIdMap(asId,Arrays.asList(CommonSettingConstants.CcsDataDictType.UNIT
                ,CommonSettingConstants.CcsDataDictType.INSTOCK));
        //
        for (OtherInstockExcelDto record: records){
            // 单据日期
            if (DateUtil.getFirstDate(Integer.parseInt(psiAccountSet.getYear()),Integer.parseInt(psiAccountSet.getMonth())).compareTo(record.getBillDate())>0){
                excelErrDetails.add(new ExcelErrDetail(record.getIndex(), String.format("单据日期:%s，不能在账套的启用日期之前，请先调整", record.getBillDate())));
                record.setHasError(true);
            }
            // 供应商
            if (!CheckEmptyUtil.isEmpty(record.getVendorCode())){
                if (!aaMap.containsKey(record.getVendorCode()+ BaseConstant.Separate.UNDERLINE+CommonSettingConstants.AssistingAccountingType.VENDOR_ID)){
                    excelErrDetails.add(new ExcelErrDetail(record.getIndex(), String.format("供应商编码:%s，在岁月进销存中不存在，请先添加", record.getVendorCode())));
                    record.setHasError(true);
                } else{
                    record.setVendorId(aaMap.get(record.getVendorCode()+ BaseConstant.Separate.UNDERLINE+CommonSettingConstants.AssistingAccountingType.STOCK_ID));
                }
            }
            }

最后的handle是将正常的数据写入到系统中，这里就不再追溯，根据各自业务系统的逻辑来。

3 导入异常处理
异常处理的包含两方面，一方面在页面上给出提示，另一方面将错误信息导出到excel中。这样有助于排查错误在什么地方。

这里就用到了，将json数据转成excel下载。操作在页面中实现

tool.json2Excel = (dataSource,title)=> {
	var wopts = {
		bookType: 'xls',
		bookSST: false,
		type: 'binary'
	};
	var workBook = {
		SheetNames: ['Sheet1'],
		Sheets: {},
		Props: {}
	};
	//1、XLSX.utils.json_to_sheet(data) 接收一个对象数组并返回一个基于对象关键字自动生成的“标题”的工作表，默认的列顺序由使用Object.keys的字段的第一次出现确定
	//2、将数据放入对象workBook的Sheets中等待输出
	workBook.Sheets['Sheet1'] = XLSX.utils.json_to_sheet(dataSource)
	//3、XLSX.write() 开始编写Excel表格
	//4、changeData() 将数据处理成需要输出的格式
	saveAs(new Blob([changeData(XLSX.write(workBook, wopts))], {type: 'application/octet-stream'}),title)
}