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

今天药忘吃喽~ 2024-03-27 14:35 123阅读 0赞

#### 目录 ####

*  项目介绍
 *  一、效果图
 *  二、测试地址
 *   *  适用于以下情况
 *  三、贴出部分代码
 *   *  1、引入pom依赖
     *  2、多sheet测试用例
     *  3、单sheet测试用例
 *  四、项目整体路径
 *  五、 拓展：思路来源
 *   *  1、点击进入ExcelExportUtil
     *  2、实现方法
     *  3、 注解转为ExcelExportEntity
     *  4、所以我们直接跳过注解转为ExcelExportEntity，直接传入ExcelExportEntity即可

## 项目介绍 ##

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

## 一、效果图 ##

![在这里插入图片描述][84f5b656691543e9bd038e9048fc4160.png]  
![在这里插入图片描述][dda44425432c457eb76b3eb7e8eb1064.png]  
![在这里插入图片描述][7f8e2cc782834a9eba65dc14ad80c0f4.png]

## 二、测试地址 ##

### 适用于以下情况 ###

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

## 三、贴出部分代码 ##

### 1、引入pom依赖 ###

<properties>
            <easypoi.version>4.1.2</easypoi.version>
            <poi.version>4.1.1</poi.version>
        </properties>
        <dependencies>
    		
            <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>
            
            <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);
        }
    }

## 四、项目整体路径 ##

![在这里插入图片描述][7b3d26265240433a9782469afd1a068f.png]

## 五、 拓展：思路来源 ##

### 1、点击进入ExcelExportUtil ###

![在这里插入图片描述][539fb8913a0e447ca0ce8dbe83b21230.png]

### 2、实现方法 ###

![在这里插入图片描述][a438f21f357747d78a1df4435d4d8493.png]

### 3、 注解转为ExcelExportEntity ###

![在这里插入图片描述][04e0ecc065e74b95bcdc2acf3595aa1c.png]

### 4、所以我们直接跳过注解转为ExcelExportEntity，直接传入ExcelExportEntity即可 ###

[84f5b656691543e9bd038e9048fc4160.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/4692dc54530a4a5eae12be05689c9fa7.png
[dda44425432c457eb76b3eb7e8eb1064.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/459743f03460473db75dbffa0eb615ba.png
[7f8e2cc782834a9eba65dc14ad80c0f4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/2d3e4601acbf40d2a6f8bc6d7d3d9050.png
[gitee]: https://gitee.com/muyangrenOvo/export-to-excel
[gitub]: https://github.com/baike777/export-to-excel
[7b3d26265240433a9782469afd1a068f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/7c42fa3691bf48bbb5f14b04abbeb832.png
[539fb8913a0e447ca0ce8dbe83b21230.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/d7792930cbfd45508f37aacfb7df4a60.png
[a438f21f357747d78a1df4435d4d8493.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/eebbc5d49d184b969285468f2e6706e9.png
[04e0ecc065e74b95bcdc2acf3595aa1c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/25fb402eff0b4ea8b774cc4083c3a7f8.png