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

easypoi教程文档：http://easypoi.mydoc.io/#text_197817

教程写的比零散，每次看都比较费劲。所以每次费劲之后就想整理一下。

1、pom依赖

        <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>
        <!--        我这里用了lombok，根据个人情况选择是否使用，此依赖与easypoi无关，只是许需要写get set方法了-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

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

package sol.erms.model.entity.process.use;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.List;

/**
 * <p>
 * 借阅记录表/借阅主表导出表
 * </p>
 *
 * @author groot
 * @since 2020-03-18
 */
@Data
public class ErmsUseMainExport implements Serializable {

    private static final long serialVersionUID = 1L;


    /**
     * 借阅编号
     */
    @Excel(name = "借阅单编号", width = 20,needMerge = true)
    private String useNo;

    /**
     * 借阅人
     */
    @Excel(name = "申请人", width = 20,needMerge = true)
    private String ermsLender;

    /**
     * 借阅目的
     */
    @Excel(name = "借阅目的",width = 20, needMerge = true)
    private String ermsLendingpurpose;

    //子条目集合（这里是实现一对多的关键。name=""是为了不出现表头，如果不为空表头会多一层合并的单元格）
    @ExcelCollection(name = "")
    private List<ERMSUseExport> archList;






}

3、子对象（一对多中的“多”）

package sol.erms.model.entity.process.use;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

import java.util.Date;

@Data
public class ERMSUseExport {

    @Excel(name = "档案名称",width = 30)
    private String archName;

    @Excel(name = "档案编号",width = 30)
    private String archNo;

    @Excel(name = "借阅方式",width = 20 ,replace = {"在线查看_0","现场查看_1","原件借出_2"})
    private String ermsApplymode;

    @Excel(name = "借出时间",width = 30)
    private Date startUseDate;

    @Excel(name = "借出受理人",width = 20)
    private String ermsLendhandler;

    @Excel(name = "借出审核人",width = 20)
    private String ermsBorrowReviewer;

    @Excel(name = "归还时间",width = 30)
    private Date ermsReturntime;

    @Excel(name = "归还人",width = 20)
    private Date ermsReturner;

    @Excel(name = "归还受理人",width = 20)
    private Date ermsReturnhandler;

    @Excel(name = "归还复核人",width = 20)
    private Date ermsReturnReviewer;

}

4、一对多导出的实现方法

    /**
     * excel导出时（一对多实现）
     * 
     *
     * @param response
     * @param workbook
     * @param fileName
     * @throws Exception
     */
    public Response exportUseRecord(HttpServletResponse response, List<Long> useIds) {
        try {
            if(ObjectUtils.isEmpty(useIds))return Response.fail("参数为空");
            Map<String,Object> map = new HashMap<>();
            map.put("ids",useIds);
            //获取导出数据
            List<ErmsUseMainExport> mainList = ermsUseMainService.getMainListUseCollectionByCondition(map);
            // 简单模板导出方法
            ExportParams params = new ExportParams();
            params.setSheetName("借阅单");//设置sheet名
            Workbook workbook = ExcelExportUtil.exportExcel(params, ErmsUseMainExport.class, mainList);
            //返回头设置下载，并设置文件名，返回
            setExportExcelFormat(response, workbook, "借阅单导出");
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return Response.fail("系统错误！");
        }
        return Response.success();
    }

    /**
     * excel导出时
     * 返回头设置下载，并设置文件名
     * 注意：以下设置编码格式是为了ie，当前项目是ie下使用的。360或者谷歌可能会文件名会乱码。根据自己需要调整编码。或者不用设置这么多，直接outStream输出得了。
     * @param response
     * @param workbook
     * @param fileName
     * @throws Exception
     */
    private void setExportExcelFormat(HttpServletResponse response, Workbook workbook, String fileName) throws Exception {
        response.reset();
        response.setContentType("application/x-msdownload");//下载
        fileName = fileName + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
        response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("gb2312"), "ISO-8859-1") + ".xls");
        ServletOutputStream outStream = null;
        try {
            outStream = response.getOutputStream();
            workbook.write(outStream);
        } finally {
            outStream.close();
        }
    }

5、效果图