springboot 导出Excel

一、引入依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>4.0.3</version>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-compress</artifactId>
    <version>1.26.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.4.0</version>
</dependency>

二、VO

package com.foxlink.vo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

import java.time.LocalDate;

/**
 * @Description: TODO
 * @Author: 1872428
 * @Date: 2025/6/25 14:21
 * @Version: 1.0
 **/
@Data
public class HumanResourcesVO {
    /**
     * 日期
     */
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "Asia/Bangkok")
    @ExcelProperty(value = "日期")
    private LocalDate date;

    /**
     * 班别
     */
    @ExcelProperty("班別")
    private String currentClass;

    /**
     * 人员来源
     */
    @ExcelProperty("人員來源")
    private String personnelSource;

    /**
     * 人员类别
     */
    @ExcelProperty("人員類別")
    private String personnelCategory;

    /**
     * 应到人数
     */
    @ExcelProperty("應到")
    private Integer shouldArrive;

    /**
     * 实到人数
     */
    @ExcelProperty("實到")
    private Integer actualArrive;

    /**
     * 请假人数
     */
    @ExcelProperty("請假")
    private Integer leaveNumber;

    /**
     * 旷工人数
     */
    @ExcelProperty("礦工")
    private Integer absenteeismNumber;

    /**
     * 离职人数
     */
    @ExcelProperty("離職")
    private Integer resignationNumber;

    /**
     * 其它人数
     */
    @ExcelProperty("其他")
    private Integer otherNumber;

    /**
     * 出勤率
     */
    @ExcelProperty("出勤率")
    private Double attendanceRate;

    /**
     * 备注
     */
    @ExcelProperty("備注")
    private String remarks;

}

三、Controller

@Operation(summary = "导出Excel表格")
@GetMapping("/export/excel")
public Result exportHumanResourcesWithEasyExcel(
        HttpServletResponse response,
        @RequestParam(name = "开始日期", required = false) String startDate,
        @RequestParam(name = "结束日期", required = false) String endDate
) throws IOException {
    // 1. 准备数据（示例）
    List<HumanResourcesVO> list = resourcesService.listExportHumanResources(startDate, endDate);
    // 2. 设置响应头（关键：文件名编码+MIME类型）
    String fileName = "人力数据_" + System.currentTimeMillis() + ".xlsx";
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-Disposition",
            "attachment; filename*=UTF-8''" + URLEncoder.encode(fileName, StandardCharsets.UTF_8));
    response.setCharacterEncoding("UTF-8");
    // 3. 使用EasyExcel写入响应流
    EasyExcel.write(response.getOutputStream(), HumanResourcesVO.class)
            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 自动列宽
            .sheet("人力")
            .doWrite(list);
    return Result.ok();
}