Excel导出（Java + Vue）

记录一下今天导出Excel的过程

后端

1、添加依赖

<dependency>
    <groupId>com.wuwenze</groupId>
    <artifactId>ExcelKit</artifactId>
    <version>2.0.72</version>
</dependency>
<dependency>
    <groupId>xml-apis</groupId>
    <artifactId>xml-apis</artifactId>
    <version>1.4.01</version>
</dependency>

2、创建VO类，并添加字段注解

@Data
@Excel(value = "咨询")
public class ConsultationExport {

    /** 行业 */
    @ExcelField(value = "行业", name="industry")
    private String industry;

    /** 服务 */
    @ExcelField(value = "服务", name="service")
    private String service;

    ......

3、使用ExcelKit类导出数据

@PostMapping("export")
@ApiOperation("导出")
public void export(@RequestBody ExportParam exportParam, HttpServletResponse response) throws IOException {
    List<ConsultationExport> consultationList = consultationService.selectByExportParam(exportParam);
    ExcelKit.$Export(ConsultationExport.class, response).downXlsx(consultationList, false);
}

我整理了一个导入导出的工具类如下

public class ExcelKitUtils {

    /**
     * 下载文档
     * @param clazz
     * @param response
     * @param target
     * @param isTemplate
     */
    public static void download(Class clazz, HttpServletResponse response, List target, boolean isTemplate){
        if (Objects.isNull(clazz) || CollectionUtils.isEmpty(target)){
            return;
        }
        ExcelKit.$Export(clazz,response).downXlsx(target, isTemplate);
    }

    /**
     * 上传文档
     */
    public static <T> List<T> upload(Class T, MultipartFile file) throws IOException {
        ArrayList<T> successList = Lists.newArrayList();
        ArrayList<Map<String, Object>> errorList = Lists.newArrayList();
        ExcelKit.$Import(T).readXlsx(file.getInputStream(), new ExcelReadHandler<T>() {
            @Override
            public void onSuccess(int sheetIndex, int rowIndex, T t) {
                successList.add(t);  // 单行读取成功，加入入库队列。
            }

            @Override
            public void onError(int sheetIndex, int rowIndex, List<ExcelErrorField> errorFields) {
                // 读取数据失败，记录了当前行所有失败的数据
                HashMap<String, Object> map = new HashMap<>();
                map.put("sheetIndex", sheetIndex);
                map.put("rowIndex", rowIndex);
                map.put("errorFields", errorFields);
                errorList.add(map);
            }
        });

        if ( !CollectionUtils.isEmpty(errorList)){
            throw new GlobalException(CodeMsg.UPLOAD_SAVEBATCH);
        }

        return successList;
    }
}

VUE 前端

此时虽然后端返回了流数据，但是我们用PostMan 接收了看来都是一串看不懂的乱码，需要前端处理一下。虽然vue里有封装好的请求接口的方法，但这里要单独用axios，我创建了一个util.js中的方法单独处理这个导出的逻辑，记得先导入axios import axios from 'axios'

export function exportSth(data) {
  axios({
    method: 'POST',
    url: `http://localhost:8080/web/consultation/export`,
    data: data.params,
    responseType: 'blob'
  }).then((res) => {
    const link = document.createElement('a')
    let blob = new Blob([res.data], {type: 'application/vnd.ms-excel'})
    link.style.display = 'none'
    link.href = URL.createObjectURL(blob)
    // link.download = res.headers['content-disposition'] //下载后文件名
    link.download = data.fileName //下载的文件名
    document.body.appendChild(link)
    link.click()
    document.body.removeChild(link)
  }).catch(error => {
    this.$message.error({ title: '错误', desc: '网络连接错误' })
    console.log(error)
  })
}

最后在具体的模块中调用该方法即可

performExport () {
    if (!this.preTimes || this.preTimes.length < 1) {
        this.$alert('请选择时间范围', '警告', {
            confirmButtonText: '确定',
            callback: action => { }
        })
        return
    }
    let param = {}
    param.startTime = new Date(this.preTimes[0]).getTime()
    param.endTime = new Date(this.preTimes[1]).getTime()
    param.hasConnectionInfo = parseInt(this.exportForm.hasConnectionInfo)
    let data = { fileName: '咨询统计', params: param }
    exportSth(data)
    this.exportDialogVisible = false
}