Springboot中使用easyExcel动态生成excel

	最近项目上的需求，让我来开发报表的导出功能，查阅了许多的资料，在功能完成之后，写下这篇文章，希望可以帮助到大家。废话不多说，直接上代码（用的idea2018.3，jdk11）

1、 引入依赖

//采用了目前最新的依赖，因为旧版本许多方法都已经过期了
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.6</version>
</dependency>

2、 相关实体类

//报表存储
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ReportPo implements Serializable {
	/**
     * 报表主键，自增
     */
    private Long reportId;
    /**
     * 报表名称
     */
    private String reportName;
    /**
     * 文件存储路径
     */
    private String filePath;
    /**
     * http下载请求路径
     */
    private String url;
    /**
     * 状态:-1导出失败,0导出中,1导出完成
     */
    private Integer status;
    /**
     * 创建时间
     */
    private LocalDateTime createTime;
    /**
     * 修改时间
     */
    private LocalDateTime updateTime;
}
//查询条件
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ExportExcelRequest implements Serializable {
    //动态选择的列
    private  SelectRequest selectRequest;
    //开始时间
    private LocalDate startTime;
    //结束时间
    private LocalDate endTime;
}
//动态选择的列
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class SelectRequest implements Serializable {
    //产品名称
    private boolean prd;
    //期初库存
    private boolean beginningStocks;
    //期初库存金额
    private boolean beginningAmt;
    //销售数量
    private boolean salesQty;
    }
//产品库存信息
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ProductStock implements Serializable {
    //产品名称
    private String prd;
    //期初库存
    private Integer beginningStocks;
    //期初库存金额
    private BigDecimal beginningAmt;
    //销售数量
    private Integer salesQty;
    }

3、yml文件配置

#文件上传根路径
file-upload:
  disk-root-path: /opt/st/
  static-root-url: http://localhost:8072/st

注：导出的报表将会在idea安装盘下自动生成的文件夹下
4、 Controller层

	@PostMapping("/export_excel")
    public Response exportExcel(@RequestBody ExportExcelRequest request) {
        return reportService.exportExcel(request);
    }

说明：此处的Response类是将数据以json的格式返回到前端。
5、 Service层

@Service
@Slf4j
public class ReportService{
	@Value("${file-upload.disk-root-path}report/%s")
    private String filePath;
    @Value("${file-upload.static-root-url}/report/%s")
    private String rootUrl;
    @Autowired
    private ReportMapper reportMapper;

    /**
     * 产品报表查询
     *
     * @param request
     * @return
     */
    public Response exportExcel(ExportExcelRequest request) {
        //对条件进行判空处理，此处就不写了
        //动态选择的列
        SelectRequest productSelect = request().getSelectRequest();
        if (productRequest == null) {
            return Response.errorMsg("动态选择的列不能为空!");
        }
        //导出信息建立
        ReportPo report = ReportPo.builder()
                .reportName("产品报表")
                .filePath(String.format(filePath, reportId + ".xlsx"))
                .url(String.format(rootUrl, reportId + ".xlsx"))
                .status(0)
                .createTime(LocalDateTime.now())
                .updateTime(LocalDateTime.now())
                .build();
        reportMapper.insert(report);
        //判断目录有没有创建
        File fileDir = new File(filePath);
        if (!fileDir.exists()) {
        //建议此处用mkdirs，目录是多层或单层都适用
            fileDir.mkdirs();
        }
        try {
            // 表单
            WriteSheet writeSheet = new WriteSheet();
            writeSheet.setSheetName(report.getReportName());
            writeSheet.setSheetNo(1);
            // 动态添加所有表头行集合
            List<List<String>> headList = new ArrayList<>();
            //表头
            List<String> selectList = getSelectList(productSelect);
            for (String s : selectList) {
                List<String> headTitle = new ArrayList<>();
                headTitle.add(s);
                headList.add(headTitle);
            }
            //获取ExcelWriter
            ExcelWriter excelWriter = EasyExcelFactory
                    .write(new FileOutputStream(report.getFilePath()))
                    .autoCloseStream(true)
                    .head(headList).build();
            	//查询，导出到excel中
                List<ProductStock> list = reportMapper.queryProductStock(request,productSelect);
                //如果查询结果为空，则结束，释放资源
                if (list.isEmpty()) {
                    //释放资源
                    excelWriter.finish();
                    break;
                }
                //将list转换成object
                List<List<Object>> listOut = getProductVoList(list, productSelect);
                excelWriter.write(listOut, writeSheet);
            //修改状态为1
            ReportPo updateReport = ReportPo.builder()
                    .status(1)
                    .build();
            reportMapper.update(updateReport, Wrappers.<ReportPo>lambdaUpdate().eq(ReportPo::getReportId, report.getReportId()));
        } catch (Exception e) {
            //更新状态
            ReportPo updateReport = ReportPo.builder()
                    .status(-1)
                    .build();
            reportMapper.update(updateReport, Wrappers.<ReportPo>lambdaUpdate().eq(ReportPo::getReportId, report.getReportId()));
            log.error("导出报表异常", e);
        }
        return Response.successMsg("导出成功，请到下载中心进行下载!");
    }
     /**
     * 获取输出的表头
     *
     * @param productSelect
     * @return
     */
    private List<String> getSelectList(SelectRequest productSelect) {
        List<String> list = new ArrayList<>();
        if (productSelect.isPrd()) {
            list.add("产品");
        }
        if (productSelect.isBeginningStocks()) {
            list.add("期初库存");
        }
        if (productSelect.isBeginningAmt()) {
            list.add("期初库存金额");
        }
        if (productSelect.isSalesQty()) {
            list.add("销售数量");
        }
        }
        /**
     * 转换查询结果
     *
     * @param list
     * @param productSelect
     * @return
     */
    private List<List<Object>> getProductVoList(List<ProductStock> list, SelectRequest productSelect) {
        List<List<Object>> listOut = new ArrayList<>();
        //循环
        for (ProductStock reportVo : list) {
            // 第 n 行的数据,需要与getSelectList()中的顺序保持一致
            List<Object> row = new ArrayList<>();
            if (productSelect.isPrd() && StringUtils.isNotBlank(reportVo.getPrd())) {
                row.add(reportVo.getPrd());
            } else if (productSelect.isPrd() && StringUtils.isBlank(reportVo.getPrd())) {
                row.add(null);
            }
            if (productSelect.isBeginningStocks() && reportVo.getBeginningStocks() != null) {
                row.add(reportVo.getBeginningStocks());
            } else if (productSelect.isBeginningStocks() && reportVo.getBeginningStocks() == null) {
                row.add(null);
            }
            if (productSelect.isBeginningAmt() && reportVo.getBeginningAmt() != null) {
                row.add(reportVo.getBeginningAmt());
            } else if (productSelect.isBeginningAmt() && reportVo.getBeginningAmt() == null) {
                row.add(null);
            }
            if (productSelect.isSalesQty() && reportVo.getSalesQty() != null) {
                row.add(reportVo.getSalesQty());
            } else if (productSelect.isSalesQty() && reportVo.getSalesQty() == null) {
                row.add(null);
            }
  }

6、 Dao层

List<ProductStock> list = queryProductStock(@Param("request") ExportExcelRequest request,
                                            @Param("productRequest") SelectRequest  productRequest);

7、 Mapper.xml文件

select
<!--去除多余的逗号-->
<trim prefix="" suffixOverrides=",">
<if test="productRequest.prd">
    prd as prd,
</if>
<if test="productRequest.beginningStocks">
   beginning_stocks beginningStocks,
</if>
<if test="productRequest.beginningAmt">
   beginning_amt as beginningAmt,
</if>
<if test="productRequest.salesQty">
    sales_qty as salesQty,
</if>
</trim>
from t_product 
<where>
 <if test="request.startTime != null">
  biz_time &gt;= #{request.startTime}
 </if>
 <if test="request.endTime != null">
  a.biz_time &lt; #{request.endTime}
 </if>
</where>

整体思路大概就是这样，下次有时间，我再把导出报表合并单元格的写一下