项目经验-excel的下载、上传与解析实践

1.项目介绍

当时做项目时，需要用户下载excel，填好信息后上传，后端解析并录入相关信息。当时确实试了好几种办法，所以这里记录一下成功的这一种。背景是springboot+vue。
向前端发送excel文件，主要用的是response.getOutputStream()将文件流进行写入。
解析excel时主要是用org.apache.poi.xssf.usermodel.XSSFWorkbook和org.apache.poi.xssf.usermodel.wb进行处理。这两个类分别负责将文件流转为wb格式，以及利用wb格式对表进行读取。

2.实现代码

2.1 用户下载excel，后端向前端发送

前端代码

// 下载文件
downloadExcel () {
  this.$http({
    url: this.$http.adornUrl('/employee/list/downloadExcel'),
    method: 'get',
    data: {},
    responseType: 'blob',
    crossDomain: true
  }).then((res) => {
    const blob = new Blob([res.data], {type: 'application/vnd.ms-excel'})
    let filename = 'example.xlsx'
    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)
    document.body.removeChild(elink)
  }).catch(() => {})
},

后端代码

@SysLog("向前端发送excel")
@GetMapping(value = "/downloadExcel")
public void downloadExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
    String fileName = "example.xlsx";
    response.setHeader("Content-disposition", "attachment;fileName=" + fileName);
    response.setContentType("application/vnd.ms-excel;charset=UTF-8");

    File cfgFile = ResourceUtils.getFile(ResourceUtils.CLASSPATH_URL_PREFIX + "static/temp/example.xlsx");
    FileInputStream input = new FileInputStream(cfgFile);
    OutputStream out = response.getOutputStream();
    byte[] b = new byte[2048];
    int len;
    while ((len = input.read(b)) != -1) {
        out.write(b, 0, len);
    }
    response.setHeader("Content-Length", String.valueOf(input.getChannel().size()));
    input.close();
}

---

2.2 用户上传excel，后端接收并解析

前端代码

<template>
  <el-dialog
    title="上传文件"
    :close-on-click-modal="false"
    @close="closeHandle"
    :visible.sync="visible">
    <el-upload
      drag
      :action="url"
      :before-upload="beforeUploadHandle"
      :on-success="successHandle"
      multiple
      :file-list="fileList"
      style="text-align: center;">
      <i class="el-icon-upload"></i>
      <div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>
      <div class="el-upload__tip" slot="tip">请填写模板后上传！</div>
    </el-upload>
  </el-dialog>
</template>

<script>
  export default {
    data () {
      return {
        visible: false,
        url: '',
        num: 0,
        successNum: 0,
        fileList: []
      }
    },
    methods: {
      init (id) {
        this.url = this.$http.adornUrl(`/employee/list/saveInExcel?token=${this.$cookie.get('token')}`)
        this.visible = true
      },
      // 上传之前
      beforeUploadHandle (file) {
        console.log(file.type)
        if (file.type !== 'application/ms-excel' && file.type !== 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') {
          this.$message.error('只支持xlsx格式的文件！')
          return false
        }
        this.num++
      },
      // 上传成功
      successHandle (response, file, fileList) {
        this.fileList = fileList
        this.successNum++
        if (response && response.code === 0) {
          if (this.num === this.successNum) {
            this.$confirm('操作成功, 是否上传其他文件?', '提示', {
              confirmButtonText: '确定',
              cancelButtonText: '取消',
              type: 'warning'
            }).catch(() => {
              this.visible = false
            })
          }
        } else {
          this.$message.error(response.msg)
        }
      },
      // 弹窗关闭时
      closeHandle () {
        this.fileList = []
        this.$emit('refreshDataList')
      }
    }
  }
</script>

后端代码

//controller摘录
@SysLog("Excel形式保存employee")
@PostMapping("/saveInExcel")
public R saveInExcel(@RequestParam(value = "file") MultipartFile file) throws IOException {
    InputStream inputStream = file.getInputStream();
    Workbook wb = new XSSFWorkbook(inputStream);
    long companyId = 9;
    employeeService.saveEmployeeInExcel(wb, companyId);
    return R.ok();
}
//service类摘录
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public void saveEmployeeInExcel(Workbook wb, long companyId) {
    Sheet sheet = wb.getSheetAt(0);
    int firstRowIndex = sheet.getFirstRowNum()+1;
    int lastRowIndex = sheet.getLastRowNum();

    for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {
        Row row = sheet.getRow(rIndex);
        if (row != null && !"".equals(row.getCell(0).toString())) {
            EmployeeEntity employeeEntity = new EmployeeEntity();
            employeeEntity.setUsername(row.getCell(0).toString());
            employeeEntity.setCompanyId(companyId);
            employeeEntity.setDepartment(row.getCell(1).toString());
            employeeEntity.setMobile(Long.valueOf(row.getCell(2).toString()).toString());
            employeeEntity.setPassword(row.getCell(3).toString());
            employeeEntity.setCreateTime(new Date());
            employeeEntity.setStatus(0);
            this.baseMapper.saveEmployee(employeeEntity);
        }
    }
}