easy-poi实现动态列(标题)、多sheet导出excel文档等操作-附完整测试用例

牧羊人Ovo

已于 2023-10-19 23:37:17 修改

阅读量4.5k

点赞数 7

分类专栏：功能点汇总文章标签： java

于 2023-02-08 11:11:01 首次发布

本文链接：https://blog.csdn.net/m0_51706962/article/details/128931967

版权

功能点汇总专栏收录该内容

6 篇文章 2 订阅

订阅专栏

项目介绍

提供高度自定义样式Demo参考
支持动态列且多sheet导出
重新定义样式ExcelExportStyler 实现冻结行、填充表格颜色、加粗、居中、大小等操作
封装一个导出工具类，便于调用

一、效果图

在这里插入图片描述

二、测试地址

适用于以下情况

表头不固定、不能用注解的形式实现，例如输出答题记录、因为试卷不同、所以题目数量也不同、导致表头数目是不固定的
需要输出多个sheet的操作
同时需要满足上述两个需求的、需要输出到不同的sheet中、同时表头数目都是不固定的
【gitee地址跳转】
【gitub地址跳转】

三、贴出部分代码

1、引入pom依赖

  	<properties>
        <easypoi.version>4.1.2</easypoi.version>
        <poi.version>4.1.1</poi.version>
    </properties>
    <dependencies>
		<!--easypoi-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>${easypoi.version}</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>${easypoi.version}</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>${easypoi.version}</version>
        </dependency>
        <!--poi-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>${poi.version}</version>
        </dependency>
    </dependencies>

2、多sheet测试用例

package com.muyangren;

import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.export.ExcelExportService;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author: muyangren
 * @Date: 2023/1/31
 * @Description: easypoi实现多sheet，动态列导出（非注解形式）
 * @Version: 1.0
 */
public class ExportToExcelTest {

    public static void main(String[] args) {
        // 1、创建一个Workbook（XSSFWorkbook）
        Workbook workbook = new XSSFWorkbook();
        // 2、导出sheet1
        exportSheetOne(workbook);
        // 3、导出sheet2
        exportSheetTwo(workbook);
        // 4、通过浏览器或者下载到临时路径
        exportWorkbook(workbook);
    }

    private static void exportSheetOne(Workbook workbook) {
        // 1、创建动态表头
        List<ExcelExportEntity> entityList = new ArrayList<>();
        // 1.1、动态表头
        String sortKey = "colNum";
        for (int i = 0; i < 5; i++) {
            entityList.add(new ExcelExportEntity("列头" + i, sortKey + i, 10));
        }
        // 2、填充数据集合
        List<Map<String, Object>> dataList = new ArrayList<>();
        for (int j = 0; j < 100; j++) {
            HashMap<String, Object> contentMap = new HashMap<>();
            for (int x = 0; x < 5; x++) {
                contentMap.put(sortKey + x, "数据" + x);
            }
            dataList.add(contentMap);
        }
        // 3、定义标题和sheetName
        ExportParams exportParams = new ExportParams("【大标题】", "sheet1");
        // 默认未 ExcelType.HSSF导出失败
        exportParams.setType(ExcelType.XSSF);
        ExcelExportService service = new ExcelExportService();
        service.createSheetForMap(workbook, exportParams, entityList, dataList);
    }

    private static void exportSheetTwo(Workbook workbook) {
        // 1、创建动态表头
        List<ExcelExportEntity> entityList = new ArrayList<>();
        // 1.1、动态表头
        String sortKey = "colNum";
        for (int i = 0; i < 3; i++) {
            entityList.add(new ExcelExportEntity("列头" + i, sortKey + i, 10));
        }
        // 2、填充数据集合
        List<Map<String, Object>> dataList = new ArrayList<>();
        for (int j = 0; j < 3; j++) {
            HashMap<String, Object> contentMap = new HashMap<>();
            for (int x = 0; x < 3; x++) {
                contentMap.put(sortKey + x, "sheet2数据" + x);
            }
            dataList.add(contentMap);
        }
        // 3、定义标题和sheetName
        ExportParams exportParams = new ExportParams(null, "sheet2");
        // 默认未 ExcelType.HSSF导出失败
        exportParams.setType(ExcelType.XSSF);
        ExcelExportService service = new ExcelExportService();
        service.createSheetForMap(workbook, exportParams, entityList, dataList);
    }

    private static void exportWorkbook(Workbook workbook) {
        //1、通过浏览器下载(一般实际项目中用到)
        //ServletOutputStream outputStream = response.getOutputStream();
        //response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        //response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("动态列多sheet.xls", "UTF-8"));
        //workbook.write(outputStream);
        //outputStream.flush();
        //outputStream.close();

        // 2、直接下载到本地(一般用于测试)
        //注*：建议最好就是【.xlsx】后缀
        try (FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\WUDI\\Desktop\\导出测试用例.xlsx");) {
            workbook.write(fileOutputStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

3、单sheet测试用例

package com.muyangren;

import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import com.muyangren.excel.utils.ExportUtil;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * @author: muyangren
 * @Date: 2023/2/17
 * @Description: 单sheet导出 配合 自定义样式
 * @Version: 1.0
 */
public class SingleExportToExcelTest {
    public static void main(String[] args) {
        //1、填充标题
        String title ="标题";
        //2、填充标题
        List<ExcelExportEntity> resourceEntityList = new ArrayList<>();
        //2.1、动态表头
        String sortKey = "colNum";
        for (int i = 0; i < 5; i++) {
            resourceEntityList.add(new ExcelExportEntity("列头" + i, sortKey + i, 20));
        }
        //3、填充数据集合
        //3.1) 数据数组
        List<HashMap<String, Object>> dataList = new ArrayList<>();
        for (int j = 0; j < 100; j++) {
            //3.2)数据集合
            HashMap<String, Object> contentMap = new HashMap<>();
            //3.3)添加集合数据  {key:value}  key=动态列的key值
            for (int x = 0; x < 5; x++) {
                contentMap.put(sortKey + x, "数据" + x);
            }
            //3.4)集合添加到数组中
            dataList.add(contentMap);
        }
        //4、定义大标题
        ExportParams ex = new ExportParams(title, "sheet1");
        //导出工具
        ExportUtil.dynamicExport(title,resourceEntityList,ex,dataList,null,false);
    }
}