springboot整合easyExcel导出excel

1. 引入依赖

<!--excel读写-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
</dependency>

2.导出工具

public class ExportUtil
{
    public static final Logger LOGGER = LoggerFactory.getLogger(ExportUtil.class);

    private static final String WORD = ".docx";

    private static final String PDF = ".pdf";

    private static final String XLSX = ".xlsx";

    private static final String TYPE = "application/octet-stream";

    private static final String HEADER_K = "Content-disposition";

    private static final String HEADER_V = "attachment;filename=";


    /**
     * @Author LiXiangrong
     * @Description 导出Excel到web流
     * @Date 2024/04/02 9:37:07
     * @param clazz 导出目标类
     * @param response 浏览器响应
     * @param fileName 文件名末尾会自动拼上导出时间
     * @param sheetName sheetName
     * @param dataList 数据集合
     * @Return void
     **/
    public static void exportExcel(Class<?> clazz, HttpServletResponse response, String fileName,String sheetName, List<?> dataList)
    {
        try {
            String DateTime = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy年MM月dd日HH时mm分ss秒"));
            fileName = URLEncoder.encode(fileName+DateTime,"UTF-8").replaceAll("\\+", "%20");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader(HEADER_K, "attachment;filename*=utf-8''" + fileName + XLSX);
            EasyExcel.write(response.getOutputStream(),clazz)
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet(sheetName)
                    .doWrite(dataList);
        } catch (IOException e)
        {
            e.printStackTrace();
            LOGGER.error("导出{}Excel失败",fileName, e);
            throw new DataException("导出Excel失败");
        }
    }
}

3.在控制层查询导出数据并使用导出工具导出到web

@ApiOperation("XXX导出")
    @PostMapping("/xxxExport")
    public void exportExcel(HttpServletResponse response,@RequestBody JProjectSupervisePagination jProjectSupervisePagination)
    {
        jProjectSupervisePagination.setDataType("1"); //无需分页
        List<JProjectSuperviseEntity> list = jProjectSuperviseService.getList(jProjectSupervisePagination);
        //处理id字段转名称，若无需转或者为空可删除
        List<HPGExportVO> listVO = JsonUtil.getJsonToList(list, HPGExportVO.class);
        for (HPGExportVO exportVO : listVO)
        {
            JTopicLibraryEntity jTopicLibraryEntity = jProjectSuperviseService.getJTopicLibrary(exportVO.getTopicId());
            if (Objects.nonNull(jTopicLibraryEntity))
            {
                exportVO.setTopicName(jTopicLibraryEntity.getTopicName());
                exportVO.setUpdatedBy(generaterSwapUtil.userSelectValues(exportVO.getUpdatedBy()));
            }
        }
        ExportUtil.exportExcel(HPGExportVO.class, response, "任务后评估", "任务后评估", listVO);
    }

3.常用注解

// 导出字段注解，value为表头名，index为顺序，从0开始，如果不指定则按照字段顺序导出
@ExcelProperty(value = "任务编码",index = 0)
// 无需导出
@ExcelIgnore

4.前端导出方法

 methods: {
    exportExcel() {
      let data = {
        ...this.listQuery,
        ...this.query,
        keyword: this.keyword,
        dataType: 0,
        menuId: this.menuId,
        moduleId: '426394122396395781',
        sourceType: '3'
      }
      downloadExcel('/api/szyd/JProjectSupervise/hpgExport', data, { fileName: '任务后评估导出' })
    },
}

5.前端导出工具

// excel文件导出
export function downloadExcel(url, data = {}, options = {}) {
  const fileName = `${options.fileName || 'file'}-${getCurrentDateTime()}.xlsx`
  const hideNotify = options.hideNotify || false
  return new Promise((resolve, reject) => {
    !hideNotify && Notification({
      title: "导出中",
      message: "正在导出一份电子表格",
      iconClass: "el-icon-loading",
      duration: 2000
    })
    request({
      url,
      method: "post",
      data,
      responseType: "blob"
    }).then(file => {
      downloadFileByBlob(file, fileName)
      resolve(file)
    }).catch(err => {
      reject(err)
    })
  })
}

// Blob下载文件
export function downloadFileByBlob (content, filename) {
    let eleLink = document.createElement("a");
    eleLink.download = filename;
    eleLink.style.display = "none";
    let blob = new Blob([content]);
    eleLink.href = URL.createObjectURL(blob);
    document.body.appendChild(eleLink);
    eleLink.click();
    document.body.removeChild(eleLink);
}

6.导出到本地或指定位置

EasyExcel.write("D:\\Tencent Files\\处理分析结果.xlsx")
                .head(ExcelWriteData.class)
                .excelType(ExcelTypeEnum.XLSX).sheet("Sheet1").doWrite(readList);