4 Star 18 Fork 9

minwk/excel-batch-picture-support

Create your Gitee Account
Explore and code with more than 12 million developers,Free private repositories !:)
Sign up
Clone or Download
contribute
Sync branch
Cancel
Notice: Creating folder will generate an empty file .keep, because not support in Git
Loading...
README
MulanPSL-2.0

Excel支持大量图片导出

Build Status codecov

背景

用户在导出统计数据时需要导出大量图片.目前用的比较多的poi导出能支持批量导出大量数据(不包括自媒体).但是当需要导出大量图片时,即使设置了flushSize ,但是对于图片对象却没有效果,图片在内存中无法释放,写的图片越多,占用内存越大,导致频繁GC,甚至OOM

思路

excel文件由声明,表数据,单元格数据,媒体文件等等组件组成, 这些组件分别对应了不同的数据单元.只要把数据分别写入对应的组件,最后构建成一个需要的excel文件.

原理

生成excel时先将读取到的图片缓存在磁盘上,然后逐步将磁盘上的图片写入到excel中。不像poi导出图片时它是把所有的图片数据放在内存里,然后写入到excel中。

功能

采用流式方法写入文件,不会导致内存堆积而占用太多系统资源,有效避免频繁GC问题

  1. 支持自动合并单元格
  2. 使用流式处理,支持大量图片导出
  3. 支持注解导出,在实体上添加注解,自动生成标题

测试

经测试,可以生成几个G的文件。(保证生成的文件没问题,文件是否能打开由使用者计算机决定)

问题反馈

快速使用

Maven导入

在项目的pom.xml的dependencies中加入以下内容: 点击查看最新版本 ${excel-x.version}

<dependency>
    <groupId>top.minwk</groupId>
    <artifactId>excel-x</artifactId>
    <version>${excel-x.version}</version>
</dependency>

示例

具体功能使用示例可见版本更迭

  • 注解使用示例代码

    @GetMapping("/export/lastversion/{row}")
    public void exportLastVersion(HttpServletResponse response, @PathVariable int row) throws IOException {
         /*
         操作窗口
         当写入excel数据行数大于flushSize时{@link Sheet.SheetHandler#createRow(int)},
         会刷新数据到流,调用该方法
         {@link  com.ibiz.excel.picture.support.flush.DrawingXmlRelsHandler#copyPictureAppendDrawingRelsXML(Sheet, Picture)}
         将图片刷新在磁盘中
         不会占用内存空间
         flushSize = -1 时不刷新流
         */
         Workbook workBook = Workbook.getInstance(1);
         Sheet sheet = workBook.createSheet("测试");
         // 给标题行加上背景色,加颜色时,会对字体加粗
         sheet.addCellStyle(new CellStyle(0, "66cc66"));
         List<UserPicture> list = new ArrayList<>();
         UserPicture userPicture;
         for (int r = 0; r < row; r++) {
             userPicture = new UserPicture();
             userPicture.setAge(15);
             userPicture.setName("测试-" + r);
             // 导出本地单张图片
             userPicture.setPicture("E:\\test\\img\\1.jpg");
             // 导出url单张图片
             userPicture.setHeaderPicture("https://portrait.gitee.com/uploads/avatars/user/552/1657608_mwk719_1641537497.png");
             // 导出本地图片集合
             userPicture.setPictures(Arrays.asList("E:\\test\\img\\1.jpg","E:\\test\\img\\2.jpg"));
             // 导出url图片集合
             userPicture.setUrlPictures(Arrays.asList("https://portrait.gitee.com/uploads/avatars/user/552/1657608_mwk719_1641537497.png",
                     "https://img2.baidu.com/it/u=2602880481,728201544&fm=26&fmt=auto"));
             list.add(userPicture);
         }
         sheet.write(UserPicture.class).createRow(list);
         WebUtil.writeExcel(workBook, "最新使用示例代码导出".concat(String.valueOf(System.currentTimeMillis())).concat(".xlsx"), response);
     }
    /**
     * @auther 喻场
     * @date 2020/7/813:41
     */
    public class UserPicture {
    
        public UserPicture() {
        }
    
        @ExportModel( sort = 0, title = "姓名")
        private String name;
        @ExportModel(sort = 1, title = "年龄")
        private Integer age;
        @ExportModel(sort = 3, title = "部门")
        private String department;
        @ExportModel(sort = 2, isPicture = true, title = "图片1")
        private String picture;
        @ExportModel(sort = 4, isPicture = true, title = "图片2")
        private String headerPicture;
        @ExportModel(sort = 5, isPicture = true, title = "多图片")
        private List<String> pictures;
        @ExportModel(sort = 6, isPicture = true, title = "url多图片")
        private List<String> urlPictures;
    
        public UserPicture(String name, Integer age, String department, String picture) {
            this.name = name;
            this.age = age;
            this.department = department;
            this.picture = picture;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public Integer getAge() {
            return age;
        }
    
        public void setAge(Integer age) {
            this.age = age;
        }
    
        public String getDepartment() {
            return department;
        }
    
        public void setDepartment(String department) {
            this.department = department;
        }
    
        public String getPicture() {
            return picture;
        }
    
        public void setPicture(String picture) {
            this.picture = picture;
        }
    
        public String getHeaderPicture() {
            return headerPicture;
        }
    
        public void setHeaderPicture(String headerPicture) {
            this.headerPicture = headerPicture;
        }
    
        public List<String> getPictures() {
            return pictures;
        }
    
        public void setPictures(List<String> pictures) {
            this.pictures = pictures;
        }
    
        public List<String> getUrlPictures() {
            return urlPictures;
        }
    
        public void setUrlPictures(List<String> urlPictures) {
            this.urlPictures = urlPictures;
        }
    }
    
  • 动态配置表头excel导出示例代码

    实体对象需要实现BizExcelPojoInterface接口

    @GetMapping("/export/dynamic-config-header")
        public void exportDynamicConfigHeader(HttpServletResponse response) throws IOException {
            // 模拟需要导出的数据集合
            List<Student> students = new ArrayList<>();
            students.add(new Student("李四", 16, null, null, 0));
            students.add(new Student("张三", 17, null,
                    Arrays.asList("https://portrait.gitee.com/uploads/avatars/user/552/1657608_mwk719_1641537497.png",
                    "https://img2.baidu.com/it/u=2602880481,728201544&fm=26&fmt=auto"), 1));
            students.add(new Student("王五", 15, IMG_PATH_1, null, 2));
    
            // 配置导出excel的表头、顺序、对应导出的数据集合的字段、是否是图片、单元格宽度等
            List<BizExcelRel> excels = new ArrayList<>();
            excels.add(new BizExcelRel("姓名", "name", 2));
            excels.add(new BizExcelRel("年龄", "age", 3));
            excels.add(new BizExcelRel("表现", "performance", 4));
            excels.add(new BizExcelRel("头像", "headPicture", 5, true, 20));
            excels.add(new BizExcelRel("相册", "album", 6, true));
    
            // 创建excel
            Workbook workBook = Workbook.getInstance(100);
            Sheet sheet = workBook.createSheet("测试");
            // 创建样式
            CellStyle cellStyle = new CellStyle(0, "F0F0F0");
            // 创建数据字典
            Map<String, String> performanceMap = new HashMap<>(3);
            performanceMap.put("0", "一般");
            performanceMap.put("1", "良好");
            performanceMap.put("2", "优秀");
    
            // 构建sheet
            ExcelTableProcessor.sheet(sheet)
                    // 添加样式
                    .addCellStyle(cellStyle)
                    // 添加对应属性字段的数据字典
                    .registryEnumMap("performance", performanceMap)
                    // 构建excel
                    .buildExcel(excels, students);
            WebUtil.writeExcel(workBook, "ExportExampleDynamicConfigHeader".concat(String.valueOf(System.currentTimeMillis())).concat(".xlsx"), response);
        }
    public class Student implements BizExcelPojoInterface {
    
        public Student(String name, Integer age) {
            this.name = name;
            this.age = age;
        }
    
        public Student(String name, Integer age, String headPicture) {
            this.name = name;
            this.age = age;
            this.headPicture = headPicture;
        }
    
        public Student(String name, Integer age, String headPicture, List<String> album, Integer performance) {
            this.name = name;
            this.age = age;
            this.headPicture = headPicture;
            this.album = album;
            this.performance = performance;
        }
    
        private String name;
    
        private Integer age;
    
        private String headPicture;
    
        /**
         * 相册
         */
        private List<String> album;
    
        /**
         * 表现 0一般;1良好;2优秀
         */
        private Integer performance;
    
        public Integer getPerformance() {
            return performance;
        }
    
        public void setPerformance(Integer performance) {
            this.performance = performance;
        }
    
        public List<String> getAlbum() {
            return album;
        }
    
        public void setAlbum(List<String> album) {
            this.album = album;
        }
    
        public String getHeadPicture() {
            return headPicture;
        }
    
        public void setHeadPicture(String headPicture) {
            this.headPicture = headPicture;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public Integer getAge() {
            return age;
        }
    
        public void setAge(Integer age) {
            this.age = age;
        }
    }
  • excel含图片导出demo地址,具体使用以后缀最新日期为准,其他示例仅供测试

  • 微云-6767张图片共800mb资源.rar 可用于测试

  • 项目中导出下载excel使用示例

项目中测试使用

  1. 设置项目jvm堆栈大小都是20m

    -Xms20m -Xmx20m -Dfile.encoding=UTF-8 -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=D:\log\springlearn.hprof
  2. 复制上方 【最新使用示例代码】到项目中

  3. 找一堆图片随机添加到UserPicture中

  4. 导出一个5000条的记录,在最大堆栈占用为20m的情况下,导出excel大小为700m,未发生内存溢出情况

版本更迭

点击可跳转链接可以查看功能使用示例

2.4.3(2023.07.20)

  • 修复单元格合并错误

2.4.2(2023.07.17)

2.4.1(2023.06.06)

2.4.0(2023.01.12)

2.3.1(2022.12.19)

  • 修复渲染生成列元素A-Z坐标时,使用到Z时,下一列从AA开始
  • 修复行元素为空时合并单元格错误

2.3.0(2022.02.23)

2.2.2(2022.02.08)

  • 修复合并后的单元格没有边框线

2.2.1(2022.01.28)

2.2.0(2022.01.27)

2.1.0(2022.01.14)

2.0.0(2021.12.30)

1.0.4(2021.12.08)

  • 添加使用注解导出含图片或文本的使用示例
  • 修复图片遮挡所在单元格边框线
  • 修复f使用注解导出图片所在下边框不是加粗实线

1.0.3(2021.02.26)

  • 简化使用示例
  • 修复flushSize = -1 时不刷新流
  • 修复其他未知问题

1.0.2(2021.01.26)

  • 修复MD5时未关闭流

1.0.1(2021.01.23)

  • 添加合并单元列值
  • 添加设置单元格背景色
  • 添加可自定义单元格宽度
  • 添加设置字体,目前有默认字体
  • 修复office打开提示需修复的问题

组件介绍

EXCEL由几大组件构建而成

具体部分:

在这里插入图片描述

工作文件实例

  1. workbook代表一个excel工作文件
  2. Sheet对应文件中多个sheet页
  3. Row 为sheet页中的行数据,包含多个Cell单元格
  4. Cell 具体单元格数据
  5. MergeCell 合并单元格信息,指定了合并起始行和结束行,起始列和结束列
  6. Picture 为图片信息,一个Sheet有n个Picture

在这里插入图片描述

注解 在这里插入图片描述

需要导出的字段使用ExportModel注解,程序会自动识别标题,合并单元格,插入图片


/**
 * 导出模型
 * 使用该注解程序会根据相应属性对单元格做设置
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface ExportModel {
    /**排序*/
    int sort() default 0;
    /**是否是图片*/
    boolean isPicture() default false;
    /**表头*/
    String title() default "";
    /**已这列为准进行合并列*/
    boolean mergeMaster() default false;
    /**这一列是否要合并*/
    boolean merge() default false;
}

创建一个工作文件,需要经过一个工作周期,分别对应init(初始化) , write(写数据), close(关闭)

init 初始化各个组件,构建流并写入组件头部信息 write 写入内容 close 写入剩余内存中的数据到流, 写入组件尾部信息,关闭工作文件

init, write, close分别对应了三种事件,程序使用事件驱动机制,当监听器监听到组件在执行对应注册事件时分别进行相应操作

事件

在这里插入图片描述

监听器

在这里插入图片描述

部分代码

public interface WorkbookEvent<E extends ContentListener> {
    WorkbookEvent registry(E listener);
    void onEvent(Sheet sheet);
}
public class InitListener extends AbstractContentListener {
    @Override
    public void invoke(Sheet sheet) {
        repositories.forEach(r -> r.write(sheet));
    }
}

触发init事件,最后一行

void init() {
            sheetContext = SheetContext.getInstance(Sheet.this);
            ContentListener init = ListenerFactory.getInstance(InitListener.class);
            ContentListener flush = ListenerFactory.getInstance(FlushListener.class);
            ContentListener close = ListenerFactory.getInstance(CloseListener.class);
            sheetContext.getRepositoryHolder().forEach((alias, rep) -> {
                if (!closeAlias.contains(alias)) {
                    //app.xml workbook.xml最后写,考虑getSheetName
                    init.addRepository(rep);
                    flush.addRepository(rep);
                }
                close.addRepository(rep);
            });
            sheetContext.getEvents().add(EventFactory.getInstance(InitEvent.class).registry(init));
            sheetContext.getEvents().add(EventFactory.getInstance(FlushEvent.class).registry(flush));
            sheetContext.getEvents().add(EventFactory.getInstance(CloseEvent.class).registry(close));
            //创建组件文件
            sheetContext.getEvents().stream().filter(e -> e instanceof InitEvent).forEach(e -> e.onEvent(Sheet.this));
        }

Write方法很简单,只是把内存中的缓存写入流。

public void write(Sheet sheet) {
        try {
            if (null == write) {
                return;
            }
            writeToStream();
        } catch (Exception e) {
            throw new RuntimeException("写文件异常 :" + file.getAbsolutePath(), e);
        }
    }

    private void writeToStream() {
        try {
            if (!hasWriteXmlHead && writeXmlHead) {
                write.write(AutoXmlHeadEndContent.XML_HEAD);
                hasWriteXmlHead = true;
            }
            write.write(content.toString());
            clearContent();
        } catch (IOException e) {
            throw new RuntimeException("写文件异常 :" + file.getAbsolutePath(), e);
        }
    }

在写入流之前,需要把写入的数据先写入缓存中,这里使用代理。部分代码:

public class Sheet1Handler implements InvocationHandler {
    private IRepository target;
    public Sheet1Handler(IRepository proxy) {
        this.target = proxy;
    }
    @Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        Sheet sheet = (Sheet)args[0];
        if (method.getName().equals("write")) {
            List<Row> rows = sheet.getRows();
            if (!rows.isEmpty()) {
                //未刷新过说明没有写入过流,这里主要为了写表头
                //如果写过了,则从脚标1开始,原因是为了对比合并单元格在row1中保存上一次刷新的最后一条数据
                int subIndex = !sheet.hasFlush() ? 0 : 1;
                setMergeCell(sheet, rows);
                rows.subList(subIndex, rows.size()).stream().forEach(r -> 	          writeSheetXML(r));
            }
        } else if (method.getName().equals("close")) {
            setEndSheetData();
            setMergeContent(sheet);
        }
        return method.invoke(target, args);
    }
}
木兰宽松许可证, 第2版 木兰宽松许可证, 第2版 2020年1月 http://license.coscl.org.cn/MulanPSL2 您对“软件”的复制、使用、修改及分发受木兰宽松许可证,第2版(“本许可证”)的如下条款的约束: 0. 定义 “软件”是指由“贡献”构成的许可在“本许可证”下的程序和相关文档的集合。 “贡献”是指由任一“贡献者”许可在“本许可证”下的受版权法保护的作品。 “贡献者”是指将受版权法保护的作品许可在“本许可证”下的自然人或“法人实体”。 “法人实体”是指提交贡献的机构及其“关联实体”。 “关联实体”是指,对“本许可证”下的行为方而言,控制、受控制或与其共同受控制的机构,此处的控制是指有受控方或共同受控方至少50%直接或间接的投票权、资金或其他有价证券。 1. 授予版权许可 每个“贡献者”根据“本许可证”授予您永久性的、全球性的、免费的、非独占的、不可撤销的版权许可,您可以复制、使用、修改、分发其“贡献”,不论修改与否。 2. 授予专利许可 每个“贡献者”根据“本许可证”授予您永久性的、全球性的、免费的、非独占的、不可撤销的(根据本条规定撤销除外)专利许可,供您制造、委托制造、使用、许诺销售、销售、进口其“贡献”或以其他方式转移其“贡献”。前述专利许可仅限于“贡献者”现在或将来拥有或控制的其“贡献”本身或其“贡献”与许可“贡献”时的“软件”结合而将必然会侵犯的专利权利要求,不包括对“贡献”的修改或包含“贡献”的其他结合。如果您或您的“关联实体”直接或间接地,就“软件”或其中的“贡献”对任何人发起专利侵权诉讼(包括反诉或交叉诉讼)或其他专利维权行动,指控其侵犯专利权,则“本许可证”授予您对“软件”的专利许可自您提起诉讼或发起维权行动之日终止。 3. 无商标许可 “本许可证”不提供对“贡献者”的商品名称、商标、服务标志或产品名称的商标许可,但您为满足第4条规定的声明义务而必须使用除外。 4. 分发限制 您可以在任何媒介中将“软件”以源程序形式或可执行形式重新分发,不论修改与否,但您必须向接收者提供“本许可证”的副本,并保留“软件”中的版权、商标、专利及免责声明。 5. 免责声明与责任限制 “软件”及其中的“贡献”在提供时不带任何明示或默示的担保。在任何情况下,“贡献者”或版权所有者不对任何人因使用“软件”或其中的“贡献”而引发的任何直接或间接损失承担责任,不论因何种原因导致或者基于何种法律理论,即使其曾被建议有此种损失的可能性。 6. 语言 “本许可证”以中英文双语表述,中英文版本具有同等法律效力。如果中英文版本存在任何冲突不一致,以中文版为准。 条款结束 如何将木兰宽松许可证,第2版,应用到您的软件 如果您希望将木兰宽松许可证,第2版,应用到您的新软件,为了方便接收者查阅,建议您完成如下三步: 1, 请您补充如下声明中的空白,包括软件名、软件的首次发表年份以及您作为版权人的名字; 2, 请您在软件包的一级目录下创建以“LICENSE”为名的文件,将整个许可证文本放入该文件中; 3, 请将如下声明文本放入每个源文件的头部注释中。 Copyright (c) [Year] [name of copyright holder] [Software Name] is licensed under Mulan PSL v2. You can use this software according to the terms and conditions of the Mulan PSL v2. You may obtain a copy of Mulan PSL v2 at: http://license.coscl.org.cn/MulanPSL2 THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT, MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE. See the Mulan PSL v2 for more details. Mulan Permissive Software License,Version 2 Mulan Permissive Software License,Version 2 (Mulan PSL v2) January 2020 http://license.coscl.org.cn/MulanPSL2 Your reproduction, use, modification and distribution of the Software shall be subject to Mulan PSL v2 (this License) with the following terms and conditions: 0. Definition Software means the program and related documents which are licensed under this License and comprise all Contribution(s). Contribution means the copyrightable work licensed by a particular Contributor under this License. Contributor means the Individual or Legal Entity who licenses its copyrightable work under this License. Legal Entity means the entity making a Contribution and all its Affiliates. Affiliates means entities that control, are controlled by, or are under common control with the acting entity under this License, ‘control’ means direct or indirect ownership of at least fifty percent (50%) of the voting power, capital or other securities of controlled or commonly controlled entity. 1. Grant of Copyright License Subject to the terms and conditions of this License, each Contributor hereby grants to you a perpetual, worldwide, royalty-free, non-exclusive, irrevocable copyright license to reproduce, use, modify, or distribute its Contribution, with modification or not. 2. Grant of Patent License Subject to the terms and conditions of this License, each Contributor hereby grants to you a perpetual, worldwide, royalty-free, non-exclusive, irrevocable (except for revocation under this Section) patent license to make, have made, use, offer for sale, sell, import or otherwise transfer its Contribution, where such patent license is only limited to the patent claims owned or controlled by such Contributor now or in future which will be necessarily infringed by its Contribution alone, or by combination of the Contribution with the Software to which the Contribution was contributed. The patent license shall not apply to any modification of the Contribution, and any other combination which includes the Contribution. If you or your Affiliates directly or indirectly institute patent litigation (including a cross claim or counterclaim in a litigation) or other patent enforcement activities against any individual or entity by alleging that the Software or any Contribution in it infringes patents, then any patent license granted to you under this License for the Software shall terminate as of the date such litigation or activity is filed or taken. 3. No Trademark License No trademark license is granted to use the trade names, trademarks, service marks, or product names of Contributor, except as required to fulfill notice requirements in Section 4. 4. Distribution Restriction You may distribute the Software in any medium with or without modification, whether in source or executable forms, provided that you provide recipients with a copy of this License and retain copyright, patent, trademark and disclaimer statements in the Software. 5. Disclaimer of Warranty and Limitation of Liability THE SOFTWARE AND CONTRIBUTION IN IT ARE PROVIDED WITHOUT WARRANTIES OF ANY KIND, EITHER EXPRESS OR IMPLIED. IN NO EVENT SHALL ANY CONTRIBUTOR OR COPYRIGHT HOLDER BE LIABLE TO YOU FOR ANY DAMAGES, INCLUDING, BUT NOT LIMITED TO ANY DIRECT, OR INDIRECT, SPECIAL OR CONSEQUENTIAL DAMAGES ARISING FROM YOUR USE OR INABILITY TO USE THE SOFTWARE OR THE CONTRIBUTION IN IT, NO MATTER HOW IT’S CAUSED OR BASED ON WHICH LEGAL THEORY, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. 6. Language THIS LICENSE IS WRITTEN IN BOTH CHINESE AND ENGLISH, AND THE CHINESE VERSION AND ENGLISH VERSION SHALL HAVE THE SAME LEGAL EFFECT. IN THE CASE OF DIVERGENCE BETWEEN THE CHINESE AND ENGLISH VERSIONS, THE CHINESE VERSION SHALL PREVAIL. END OF THE TERMS AND CONDITIONS How to Apply the Mulan Permissive Software License,Version 2 (Mulan PSL v2) to Your Software To apply the Mulan PSL v2 to your work, for easy identification by recipients, you are suggested to complete following three steps: i Fill in the blanks in following statement, including insert your software name, the year of the first publication of your software, and your name identified as the copyright owner; ii Create a file named “LICENSE” which contains the whole context of this License in the first directory of your software package; iii Attach the statement to the appropriate annotated syntax at the beginning of each source file. Copyright (c) [Year] [name of copyright holder] [Software Name] is licensed under Mulan PSL v2. You can use this software according to the terms and conditions of the Mulan PSL v2. You may obtain a copy of Mulan PSL v2 at: http://license.coscl.org.cn/MulanPSL2 THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT, MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE. See the Mulan PSL v2 for more details.

About

解决导出excel含图片写的图片越多,占用内存越大,导致频繁GC,甚至OOM expand collapse
Java
MulanPSL-2.0
Cancel

Releases (2)

All

Contributors

All

Activities

Load More
can not load any more
马建仓 AI 助手
尝试更多
代码解读
代码找茬
代码优化
Java
1
https://gitee.com/mwk719/excel-batch-picture-support.git
git@gitee.com:mwk719/excel-batch-picture-support.git
mwk719
excel-batch-picture-support
excel-batch-picture-support
master

Search