easypoi实现表格导出(实现数据结构一对多，合并单元格)

最近在做导出Excel，针对数据是一对多形式的对比后还是Easypoi比较好用。然后给大家分享哈

1.添加maven依赖

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>3.2.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>3.2.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>3.2.0</version>
</dependency>

2、导出对应的实体类（一对多中的“一”）

package com.zjt.shop.modules.personnel.entity.vo;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import com.zjt.shop.common.excel.ExcelField;
import com.zjt.shop.modules.order.entity.vo.CodeVo;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.List;
/**
 * 作者：zhuLin
 * 日期：2020-07-30 17:36
 * 备注：产品信息 - 实体类
 */
@Data
@ApiModel(value = "产品信息统计导出-实体类")
public class ProductSuitTjVo {
    //需要合并得列添加：needMerge = true 即可
    @Excel(name = "产品名称",needMerge = true,width = 20)
    @ApiModelProperty(value = "产品名称")
    private String msgName;
    @Excel(name = "产品状态",needMerge = true,width = 20)
    @ApiModelProperty(value = "产品状态")
    private String prodState;
    @Excel(name = "产品经理",needMerge = true,width = 20)
    @ApiModelProperty(value = "产品经理")
    private String productManager;
    //子集
    @ExcelCollection(name = "")
    List<SuitTjVo> suitList;
}

3、子集对象实体类（一对多中的“多”）

package com.zjt.shop.modules.personnel.entity.vo;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import com.zjt.shop.common.excel.ExcelField;
import com.zjt.shop.modules.order.entity.vo.CodeVo;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.List;
/**
 * 作者：zhuLin
 * 日期：2020-07-30 17:36
 * 备注：产品信息 - 实体类
 */
@Data
@ApiModel(value = "产品信息统计导出-实体类")
public class SuitTjVo {
    @Excel(name = "小达人佣金",width = 10)
    @ApiModelProperty(value = "小达人佣金")
    private double talentAmtMin;
    @Excel(name = "中达人佣金",width = 10)
    @ApiModelProperty(value = "中达人佣金")
    private double talentAmtCin;
    @Excel(name = "销售价",width = 30)
    @ApiModelProperty(value = "销售价")
    private String salePrice;
    @Excel(name = "结算价",width = 30)
    @ApiModelProperty(value = "结算价")
    private String settlePrice;
}

4、导出Excel的实现方法

/**
     * 导出Excel数据工具类
     * @param response
     * @param workbook
     * @param fileName
     * @throws Exception
     */
    public static void setExportExcelFormat(HttpServletResponse response, Workbook workbook, String fileName) throws Exception {
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1"));
        response.setHeader("Pargam", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        ServletOutputStream outStream = null;
        try {
            outStream = response.getOutputStream();
            workbook.write(outStream);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("导出Excel异常：" + e.getMessage());
        } finally {
            outStream.flush();
            outStream.close();
        }
    }

4、实例

@ApiOperation("产品信息统计-导出")
@PostMapping("/productTjExport")
public void productTjExport (HttpServletResponse response,@RequestBody ProductStatisticsVo productVo) throws Exception {
        String title = "产品信息统计";
        log.info(title + "导出参数：{}",productVo);
        List<ProductSuitTjVo> list = empInfoService.productTjExport(productVo);     //mybatis 一对多数据格式
        ExportParams params = new ExportParams();
        params.setTitle(title);         //设置表头
        params.setSheetName(title);     //设置sheet名
        Workbook workbook = ExcelExportUtil.exportExcel(params, ProductSuitTjVo.class, list);
        ExportExcel.setExportExcelFormat(response, workbook, title);
}

导出结果如图