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

比眉伴天荒 2023-07-17 04:48 80阅读 0赞

easypoi教程文档：[http://easypoi.mydoc.io/\#text\_197817][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>
            
            <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、效果图

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NhcHRhaW5KYXZh_size_16_color_FFFFFF_t_70][]

[http_easypoi.mydoc.io_text_197817]: http://easypoi.mydoc.io/#text_197817
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NhcHRhaW5KYXZh_size_16_color_FFFFFF_t_70]: /images/20230528/c88080f037e14e59aeafad28fefc7e81.png