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

官方文档：

EasyPoi教程_V1.0​easypoi.mydoc.io

最近在做导出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);
}

导出结果如图