easypoi导出 双sheet页

最近项目中需要有下载多sheet页的功能,对于一个连下载都没有做过的人,一脸懵逼,于是通过网上搜罗解决办法和麻烦老员工得已解决.

下面就来说明一下如何使用easy-poi来解决多sheet页的下载问题.

1. 引入easypoi相关依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.2-beta5</version>
        </dependency>

 2. 新建实体类,用来对应数据可相应的需要导出的字段

package com.cnpc.mcq.sys.entity;

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

import java.io.Serializable;

/**
 * @ClassName SuperviseListExcel
 * @Description : 下载对应实体类1
 * @Author : R
 */
@Data
@ExcelTarget("SysSuperviseListExcel")
public class SysSuperviseListExcel implements Serializable {

    @Excel(name = "单位",  width = 25, height = 15,needMerge = true)
    private String secondUnit;

    @Excel(name = "部门",  width = 25, needMerge = true)
    private String problemDepartment;

    @Excel(name = "位置",  width = 25, needMerge = true)
    private String problemLocation;

    @Excel(name = "方式",  width = 15, needMerge = true)
    private String superviseMode;

    @Excel(name = "描述",  width = 90, needMerge = true)
    private String problemDescription;

    @Excel(name = "人员",  width = 20, needMerge = true)
    private String supervisers;

    @Excel(name = "情况",  width = 15, needMerge = true)
    private String rectification;

    @Excel(name = "依据",  width = 20, needMerge = true)
    private String inspectBasis;

    @Excel(name = "备注",  width = 15, needMerge = true)
    private String remarks;

}

package com.cnpc.mcq.sys.entity;

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

import java.io.Serializable;

/**
 * @ClassName SuperviseListExcel
 * @Description : 下载对应实体类2
 * @Author : R
 */
@Data
@ExcelTarget("SysSuperviseDailyExcel")
public class SysSuperviseDailyExcel implements Serializable {

    @Excel(name = "问题情况", width = 45, height = 50, needMerge = true, orderNum = "2")
    private String superviseMatterStatus;

    @Excel(name = "问题原因", width = 90, needMerge = true, orderNum = "4")
    private String matterCauseSource;

    @Excel(name = "典型情况", width = 90, needMerge = true, orderNum = "6")
    private String typicalSituation;

}

 其中,注解的意思为(详细可查看官方文档) : 

@Excel

3. 开始进行导出方法的编写

   /**
     * 导出excel
     *
     * @param id
     * @param response
     * @throws IOException
     */
    @Transactional
    public void download(String id, HttpServletResponse response) throws IOException {
		//数据库查询
        Map<String, Object> details = this.getDetails(id);
        //sheet1 获取数据写入下载实体
         List<SysSuperviseListExcel> superviseList = new ArrayList<>();
            SysSuperviseDataExcel sysSuperviseDataExcel = new SysSuperviseDataExcel();
			//此处是从数据库查询出的数组集合(SysHiddenDangerInformation是数据库表对应的实体类)
            List<SysHiddenDangerInformation> list1 = (List<SysHiddenDangerInformation>) details.get("list");
            for (SysHiddenDangerInformation objectMap : list1) {
                SysSuperviseListExcel sysSuperviseListExcel = new SysSuperviseListExcel();
                sysSuperviseListExcel.setSecondUnit(objectMap.getSecondUnit());
                sysSuperviseListExcel.setProblemDepartment(objectMap.getProblemDepartment());
                sysSuperviseListExcel.setProblemLocation(objectMap.getProblemLocation());
                sysSuperviseListExcel.setSuperviseMode(objectMap.getSuperviseMode());
                sysSuperviseListExcel.setProblemDescription(objectMap.getProblemDescription());
                sysSuperviseListExcel.setSupervisers(objectMap.getSupervisers());
                sysSuperviseListExcel.setRectification(objectMap.getRectification());
                sysSuperviseListExcel.setInspectBasis(objectMap.getInspectBasis());
                sysSuperviseListExcel.setRemarks(objectMap.getRemarks());
                superviseList.add(sysSuperviseListExcel);
            }
        
        //sheet2 获取数据写入下载实体
        List<SysSuperviseDailyExcel> dailyList = new ArrayList<>();
            SysSuperviseDataExcel sysSuperviseDataExcel = new SysSuperviseDataExcel();
			//此处是从数据库查询出的数组集合
            List<SysHiddenDangerInformation> list2 = (List<SysHiddenDangerInformation>) details.get("data");
            for (SysInformation objectMap : list2) {
                SysSuperviseDailyExcel sysSuperviseDailyExcel = new SysSuperviseDailyExcel();
				sysSuperviseDailyExcel.setSuperviseMatterStatus(objectMap.getSuperviseMatterStatus());
				sysSuperviseDailyExcel.setMatterCauseSource(objectMap.getMatterCauseSource());
				sysSuperviseDailyExcel.setTypicalSituation(objectMap.getTypicalSituation());
                dailyList.add(sysSuperviseListExcel);
            }

        // 使用map 创建sheet1
        Map<String, Object> sheetMap1 = new HashMap<>();
        //把我们构造好的bean对象放到params里
        ExportParams params1 = new ExportParams();
        //设置标题头
        params1.setTitle(details.get("list").toString);
        params1.setTitleHeight((short) 15);
        params1.setSheetName("sheet1");
        params1.setFixedTitle(false);
        params1.setStyle(CustomExcelExportStylermpl.class);
        // title的参数为ExportParams类型
        sheetMap1.put("title", params1);
        // 模版导出对应得实体类型
        sheetMap1.put("entity", SysSuperviseListExcel.class);
        // sheet中要填充得数据
        sheetMap1.put("data", superviseList);

        // 使用map 创建sheet2
        Map<String, Object> sheetMap2 = new HashMap<>();
        //把我们构造好的bean对象放到params里
        ExportParams params2 = new ExportParams();
        params2.setTitle(details.get("data").toString);
        params2.setSheetName("sheet2");
        params2.setFixedTitle(false);
        // title的参数为ExportParams类型
        sheetMap2.put("title", params2);
        // 模版导出对应得实体类型
        sheetMap2.put("entity", SysSuperviseDailyExcel.class);
        // sheet中要填充得数据
        sheetMap2.put("data", dailyList);

        //创建总的集合存放每一页的数据集合
        List<Map<String, Object>> sheetsList = new ArrayList<>();
        sheetsList.add(sheetMap1);
		sheetsList.add(sheetMap2);
        Workbook workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);

        //设置sheet1 副标题样式
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        font.setFontHeightInPoints((short) 12);
        workbook.getSheetAt(1).getRow(0).getCell(0).setCellStyle(style);

        //设置sheet2 样式
        CellStyle style1 = workbook.createCellStyle();
        style1.setAlignment(HorizontalAlignment.CENTER);
        style1.setVerticalAlignment(VerticalAlignment.CENTER);
        Font font1 = workbook.createFont();
        font1.setBold(true);
        font1.setColor((short) 0xa);
        font1.setFontHeightInPoints((short) 18);
        style1.setFont(font1);
        workbook.getSheetAt(0).getRow(0).getCell(0).setCellStyle(style1);

        //格式
        response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("测试导出excel" + DateUtils.formatDate(new Date(), "yyyyMMdd") + ".xls", "UTF-8"));
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }

4. 导出样式

参考博客 : https://blog.csdn.net/yang_1011123/article/details/107265882

官方文档 : https://easypoi.mydoc.io/