Java+Vue实现Excel批量数据解析上传以及为批量数据添加图片

云梦轻掩楼

于 2024-01-10 14:55:34 发布

阅读量720

点赞数 8

文章标签： java 状态模式开发语言

本文链接：https://blog.csdn.net/Su7148/article/details/135498081

版权

一、背景描述

最近接手一个老项目还没源码，整体反编译后要增加一个批量新增数据功能，要求保留批次清单红章图片，对数据校验后的报错信息支持查看。目前支持单条数据上传，最关键的是现有逻辑图片是存储到数据库的！这肯定是要调整的，话不多说，直接开整。

二、设计思路

1、前端下载Excel模板，录入批量数据后，上传同一批次清单红章照片。

2、后端保存照片到服务器，回传url信息（存redis返回key也可），前端点击上传导入批量数据excel并解析，通讯后端数据List以及图片Url。

3、后端执行批量分析操作，数据表新增图片地址字段，新导入数据录入图片地址，报错信息存入redis，返回指定Key给后端；

4、前端点击导出错误信息按钮通过用户id获取指定错误信息并导出excel。

三、详细设计

1、前段

1、按钮

    <a-button style="margin-left: 8px" href="/download/abandonExcel.xlsx" download="作废模板表.xlsx">下载批量作废模板</a-button>
                  <a-button style="margin-left: 8px" @click="showBatchModal()" v-if="this.deptId.length === 9">
                    <a-icon type="plus"/>批量导入作废信息
                  </a-button>
                  <a-button style="margin-left: 8px" @click="errorDownload">导出上次批量作废信息</a-button>

效果

2、上传页面

<template>
  <a-modal v-model="visible" title="批量上传作废信息" @ok="handleOk" @cancel="handleClose" :confirm-loading="loading">
    <a-spin :spinning="spinning">
    <a-form :form="form" layout="vertical">
      <a-row>
        <a-form-item label="废弃信息图片上传(jpg、png格式，大小不能超过8M)" style="margin-left: 10px">
          <a-upload
            list-type="picture"
            :file-list="fileList"
            :beforeUpload="(file) => {return beforeUpload(file)}"
            @change="fileChange"
            class="upload-list-inline"
            accept="image/*"
          ><a-button><a-icon type="upload" />上传图片</a-button></a-upload>
        </a-form-item>
      </a-row>
      <a-row :guitter="16">
        <a-form-item label="废弃信息列表" style="margin-left: 10px">
        <a-upload
          name="file"
          accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel"
          :multiple="false"
          :show-upload-list="false"
          :before-upload="(file) => beforeUploadExcel(file)"
        >
          <a-button >
            <a-icon type="plus"/>
            导入信息
          </a-button>
        </a-upload>
        </a-form-item>
      </a-row>
    </a-form>
      </a-spin >
  </a-modal>
</template>

效果

3、上传图片js（加了一些校验提示）

    beforeUpload (file) {
      const isJpgOrPng = file.type === 'image/jpeg' || file.type === 'image/png'
      if (!isJpgOrPng) {
        this.$message.error('只能上传jpg或png格式')
        return !isJpgOrPng
      }
      const isLt2M = file.size / 1024 / 1024 < 8
      if (!isLt2M) {
        this.$message.error('图片必须小于 8MB!')
        return !isLt2M
      }
      return false
    },
    fileChange (info) {
      let fileList = [...info.fileList]
      // 1. Limit the number of uploaded files
      //    Only to show two recent uploaded files, and old ones will be replaced by the new
      fileList = fileList.slice(-1)
      // 2. read from response and show file link
      fileList = fileList.map(file => {
        if (file.response) {
          // Component will show file.url as link
          file.url = file.response.url
        }
        return file
      })
      this.fileList = fileList
    },

4、上传Excel逻辑

注意 import XLSX from 'xlsx'

sheetArray[item].证书证明编号此处文字为导出模板字段名称必须一致

注意图片为表单提交，后端接受时使用的类型为MultipartFile


    beforeUploadExcel (file) {
      this.spinning = !this.spinning
      if (this.fileList.length === 0) {
        this.$message.error('请先上传图片')
        this.spinning = !this.spinning
        return false
      }
      const values = {
        'annex': this.fileList[0].originFileObj
      }
      this.axiosFormPost(values, '/abandoned/upLoadPic').then(res => {
        const key = res.key
        this.$message.success('上传图片成功')
        this.listTable = []
        const fileReader = new FileReader()
        this.UploadStatus = true
        fileReader.onload = (ev) => {
          try {
            const data = ev.target.result
            const workbook = XLSX.read(data, {
              type: 'binary'
            })
            for (const sheet in workbook.Sheets) {
              // 循环读取每个文件
              const sheetArray = XLSX.utils.sheet_to_json(workbook.Sheets[sheet])
              // 若当前sheet没有数据，则continue
              if (sheetArray.length === 0) {
                continue
              }
              for (const item in sheetArray) {
                const rowTable = {}
                // 这里的rowTable的属性名注意要与上面表格的prop一致
                // sheetArray的属性名与上传的表格的列名一致
                rowTable.number = sheetArray[item].信息编号 ? sheetArray[item].信息编号 + '' : ''
                rowTable.type = sheetArray[item].类型 === '信息类型A' ? 1 : sheetArray[item].类型 === '信息类型B'
                this.listTable.push(rowTable)
              }
              const url = '/abandoned/applyBatch'
              const param = {
                picUrlKey: key,
                zsList: this.listTable
              }
              this.axiosPost(param, url).then((res) => {
                this.$message.success('导入成功')
                this.spinning = !this.spinning
              })
            }
          } catch (e) {
            this.UploadStatus = false
            this.$message.warning('文件类型不正确！')
          }
        }
        fileReader.readAsBinaryString(file)
        return false
      })
    },

5、导出错误信息JS

（这里我没有使用后端处理过后返回的errKey，而是直接使用用户Id去查询）

   errorDownload () {
      window.open(process.BASE_URL + 'abandoned/getApplyBatchRes?userId=' + this.$store.state.user.info.id)
    },

2、后端

1、图片上传

路径根据自己服务器设计即可，响应返回图片地址

controller

    @PostMapping({"/upLoadPic"})
    @ApiOperation("上传图片")
    public Result upLoadPic( @RequestParam(value = "annex") MultipartFile annex) {
        try {
            SysUser user = SecurityUtils.getLoginUser().getUser();
            if (StringUtils.isBlank(user.getUsername())) {
                return Result.error("身份信息已过期");
            }
            return this.service.savePic(annex,user);
        }catch (Exception e){
            return Result.error(e.getMessage());
        }
    }

service

  @Override
  public Result savePic(MultipartFile annex, SysUser user) throws IOException {
    FileOutputStream fos = null;
    try {
      String dirPath = "./AbandonPicture/" + user.getDeptId() + "/";
      File dir = new File(dirPath);
      if (!dir.exists()){
        dir.mkdirs();
      }
    String name = UUID.randomUUID().toString().replace("-", "") + ".jpg";
    String path = dirPath + name;
    File file = new File(path);
    fos = new FileOutputStream(file);
    fos.write(annex.getBytes());
    fos.flush();
    Map map = new HashMap();
    map.put("key",path);
    return Result.ok(map);
    } catch (IOException e) {
      e.printStackTrace();
      return Result.error("上传失败");
    }
    finally {
      if(fos != null){
        fos.close();
      }
    }
  }

2、批量处理

dto

@Data
public class AbandonedBatchDTO extends BaseEntity {
 private List<AbandonedListDTO> zsList;
 private String picUrlKey;

}

@Data
public class AbandonedListDTO extends BaseEntity {
  private Long number;
  private Integer type;
}

controller

  @PostMapping({"/applyBatch"})
    @ApiOperation("废弃证书申请批量")
    @Transactional(rollbackFor = {Exception.class})
    public Result<Boolean> abandonBatch(@RequestBody AbandonedBatchDTO dto) {
        try{
        String userId = SecurityUtils.getLoginUser().getUser().getUsername();
        SysUser sysUser = this.userService.getOneByUserName(userId);
        if (sysUser == null)
            return Result.error("用户未登录");
        String userDeptId = sysUser.getDeptId();
        if (CollectionUtils.isEmpty(dto.getZsList())){
            return Result.error("废弃信息不能为空");
        }
        String redisKey = dto.getPicUrlKey();
        String errKey = "Apply_Batch_ErrList_"+userId;
        if (ObjectUtils.isEmpty(redisKey)){
            return Result.error("未找到图片信息");
        }
        asyncSaveCertificate.saveAbandonBatch(redisKey,userDeptId,dto.getZsList(),sysUser.getUserId(),errKey);
        redisUtil.del(redisKey);
        Map res = new HashMap();
        res.put("errKey",errKey);
        return Result.ok(res);
        }catch (Exception e){
            log.error(e.getMessage());
            return Result.error("系统异常");
        }
    }

service

对于校验逻辑只展示部分几条，根据实际业务添加修改即可

 //2024-01-09  暂时不做异步
   // @Async
    public void saveAbandonBatch(String picUrl, String userDeptId, List<AbandonedListDTO> list, String userId, String errKey) {
        List<ErrorMsgDO>  errList = new ArrayList<>();
        ErrorMsgDO errorMsgDO;
        Long number = 0L;
        Integer type;
        for (AbandonedListDTO dto : list) {
            errorMsgDO = new ErrorMsgDO();
            number = dto.getNumber();
            type = dto.getType();
            //错误信息
            errorMsgDO.setNumber(number);
            AbandonedDO abandonedExist = this.abandonedService.getOneByNumber(number, type);
            if (abandonedExist != null) {
                errorMsgDO.setMsg("该信息已被废弃");
                errList.add(errorMsgDO);
                continue;
            }
            switch (type){
                case 0:
                    ProveDO proveDO = this.proveService.findProByNumber(number);
                    if (proveDO == null) {
                        errorMsgDO.setMsg("该信息编号不存在");
                        errList.add(errorMsgDO);
                        continue;
                    }  
              
                    this.abandonedService.saveAbandoned2(proveDO, picUrl);
                    break;
                case 1:
                    CertificateDO certificateDO = this.certificateService.findCertByNumber(number);
                    if (certificateDO == null) {
                        errorMsgDO.setMsg("该信息编号不存在");
                        errList.add(errorMsgDO);
                        continue;
                    }
                    this.abandonedService.saveAbandoned2(certificateDO, picUrl);
                    break;
                default:
                    errorMsgDO.setMsg("未选择信息类型!");
                    errList.add(errorMsgDO);
                    continue;
            }
        }
        redisUtil.del(errKey);
        redisUtil.add(errKey,errList);
    }

3、错误信息下载

在上一步中返回了一个errKey，下载时在redis中取值即可，因为要求只保留一条记录，所以偷个懒直接用user信息拼接key了，可根据自己业务场景调整，（项目使用的是easypoi）

  @Override
    public void getApplyBatchRes(String userId, HttpServletResponse response) {
      List<ErrorMsgDO> list = redisUtil.getCacheList("Apply_Batch_ErrList_" + userId);
      if (ObjectUtils.isEmpty(list)){
        log.info("未查询到错误信息");
        return;
      }
       List<ExcelExportEntity> columnList = new ArrayList<>();
        ExcelExportEntity colEntity1 = new ExcelExportEntity("编号", "number");
        ExcelExportEntity colEntity2 = new ExcelExportEntity("错误信息", "msg");
        colEntity1.setWidth(30.0D);
        colEntity2.setWidth(30.0D);
        columnList.add(colEntity1);
        columnList.add(colEntity2);
        List<Map<String, Object>> dataList = new ArrayList<>();
        int sum = 0;
        for (ErrorMsgDO errorMsgDO : list) {
          sum++;
          Map<String, Object> map = new HashMap<>();
          map.put("number", errorMsgDO.getNumber());
          map.put("msg", errorMsgDO.getMsg());
          dataList.add(map);
        }
        ExportParams exportParams = new ExportParams( "错误详情", "sheet1");
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, columnList, dataList);
        try {
          ServletOutputStream servletOutputStream = response.getOutputStream();
          response.setHeader("content-Type", "application/vnd.ms-excel");
          response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(userId + "错误详情", "UTF-8") + ".xlsx");
          response.setCharacterEncoding("UTF-8");
          workbook.write(servletOutputStream);
          servletOutputStream.flush();
          servletOutputStream.close();
        } catch (IOException e) {
          log.error("导出错误信息异常：{},e.getMessage()");
        }
    }

导出效果

4、图片显示

是后端传输二进制给前端，因为存量数据图片在数据库中，增量数据在服务器，所以要简单调整下逻辑（单条新增以及修改根据自己需求调整）

@Override
  public Result getAbandonPic(String number) throws IOException {
    LambdaQueryWrapper<AbandonedDO> wrapper = Wrappers.lambdaQuery();
    if (number != null)
      wrapper.eq(AbandonedDO::getNumber, number);
    wrapper.select(AbandonedDO::getAnnex, AbandonedDO::getPicUrl);
    AbandonedDO abandonedDO = this.mapper.selectOne(wrapper);
    //2024-01-09 如果有图片地址则读取图片地址
    if (StringUtils.isNotBlank(abandonedDO.getPicUrl())) {
      FileInputStream fis = null;
      FileChannel channel = null;
      try {
        File file = new File(abandonedDO.getPicUrl());
        //服务器文件被删除就直接返回 空数据前端会提示没图片信息
        if (!file.exists()) {
          return Result.ok(abandonedDO);
        }
        fis = new FileInputStream(file);
        channel = fis.getChannel();
        ByteBuffer buffer = ByteBuffer.allocate((int) channel.size());
        channel.read(buffer);
        byte[] array = buffer.array();
        abandonedDO.setAnnex(array);
        fis.close();
        channel.close();
      } catch (IOException e) {
        e.printStackTrace();
      } finally {
        if (fis != null) {
          fis.close();
        }
        if (channel != null) {
          channel.close();
        }
      }
    }
    return Result.ok(abandonedDO);
  }

效果