当前位置：首页 > article >正文

springboot使用Easy Excel导出列表数据为Excel

article 2025/1/13 11:29:12

springboot使用Easy Excel导出列表数据为Excel

Easy Excel官网：https://easyexcel.opensource.alibaba.com/docs/current/quickstart/write
主要记录一下引入时候的pom，直接引入会依赖冲突

解决方法：

<!-- 引入Easy Excel的依赖 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel-core</artifactId>
            <version>3.2.1</version>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml-schemas</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>

实体：

package com.geofly.dataservicecenter.api.vo.ybtq;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;
import org.springframework.format.annotation.DateTimeFormat;

import java.util.Date;

/**
 * @Description: 【工作量统计】-作业员工作量-列表返回vo
 *
 * @Auther: yanghaoxing
 * @Date: 2025/1/8
 */
@Getter
@Setter
@ApiModel("【工作量统计】-作业员工作量-列表返回vo")
@EqualsAndHashCode
public class YbStatisticsOperatorVo {

    @ExcelIgnore
    @ApiModelProperty("子任务主键")
    @TableId("pk_id")
    private String pkId;

    @ExcelIgnore
    @ApiModelProperty("任务ID")
    @TableField("fk_task_id")
    private String fkTaskId;

    @ExcelProperty("作业员Id")
    @ApiModelProperty("作业员Id")
    @ColumnWidth(35)
    @TableField("operator_id")
    private String operatorId;

    @ExcelIgnore
    @ApiModelProperty("行政区代码")
    @TableField("region_code")
    private String regionCode;

    @ExcelProperty("行政区名称")
    @ApiModelProperty("行政区名称")
    @TableField("region_name")
    private String regionName;

    @ExcelProperty("年度")
    @ApiModelProperty("后时相-年度")
    @TableField("after_year")
    private Integer afterYear;

    @ExcelProperty("季度")
    @ApiModelProperty("后时相-季度")
    @TableField("after_quarter")
    private Integer afterQuarter;

    @ExcelProperty("项目类型ID")
    @ApiModelProperty("项目类型ID（外键）")
    @ColumnWidth(35)
    @TableField("task_type")
    private String taskType;

    @ExcelProperty("项目负责人")
    @ApiModelProperty("项目负责人")
    @ColumnWidth(35)
    @TableField("leader")
    private String leader;

    @ExcelIgnore
    @ApiModelProperty("创建时间（开始时间）")
    @TableField("create_time")
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm")
    private Date createTime;

    @ExcelIgnore
    @ApiModelProperty("更新时间（结束时间）")
    @TableField("update_time")
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm")
    private Date updateTime;

    @ExcelProperty("状态")
    @ApiModelProperty("状态：0表示未完成，1表示已完成")
    @TableField("status")
    private int status;

    @ExcelProperty("工作区面积")
    @ApiModelProperty("工作区面积")
    private String workerArea;

    @ExcelProperty("指引图斑数量")
    @ApiModelProperty("指引图斑数量")
    @TableField("zy_count")
    private int zyCount;

    @ExcelProperty("图斑数量")
    @ApiModelProperty("提取图斑数量")
    @TableField("ysbhtb_count")
    private Integer ysbhtbCount;

    @ExcelProperty("一检员")
    @ApiModelProperty("一检质检员")
    @TableField("first_quality_inspector")
    private String firstQualityInspector;

    @ExcelProperty("合格率")
    @ApiModelProperty("一检合格率合,0~100")
    private int firstInspectionRate;

    @ExcelProperty("二检员")
    @ApiModelProperty("二检质检员")
    @TableField("second_quality_inspector")
    private String secondQualityInspector;

    @ExcelProperty("合格率")
    @ApiModelProperty("二检合格率合,0~100")
    private int secondInspectionRate;

    @ExcelProperty("成果质量")
    @ApiModelProperty("成果质量，-1：不通过，0未质检，1：合格，2：良好，3：优秀")
    @TableField("inspection_result")
    private int inspectionResult;

}

java：

@ApiOperation(value = "【工作量统计】-作业员工作量-导出", notes = "export", produces = "application/octet-stream")
    @PostMapping("/statistics-operator-download")
    @OpLog("【样本集管理】-样本集导出")
    public void statisticsOperatorDownload(@RequestBody YbStatisticsOperatorParams params, HttpServletResponse response) throws Exception {

       // 设置分页参数，确保获取所有数据
        BasePage page = new BasePage();
        page.setPageNo(1);
        page.setPageSize(999999);
        params.setPage(page);

       // 根据参数获取所有作业员工作量数据
        IPage<YbStatisticsOperatorVo> allList = ybStatisticsOperator(params);
        
       // 设置响应头，指定文件为 Excel 文件
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        String fileName = "作业员工作量统计.xlsx";
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));

     // 使用 EasyExcel 导出数据
        try {
           EasyExcel.write(response.getOutputStream(), YbStatisticsOperatorVo.class)
                    .sheet("作业员工作量")
                    .doWrite(allList.getRecords());
        } catch (Exception e) {
            e.printStackTrace();
            response.setContentType("application/json;charset=utf-8");
            response.getOutputStream().write(JSONUtil.toJsonStr(Result.error("导出失败！")).getBytes(StandardCharsets.UTF_8));
        }

}