使用easyexcel将大量数据写入excel,使用的是文件缓存还是先暂存在内存

txu3uszq  于 5个月前  发布在  其他
关注(0)|答案(5)|浏览(70)

使用easyexcel将大量数据写入excel,使用的是文件缓存还是先暂存在内存?
分多次通过excelWriter.write(data, sheet0);写入excel,是可以解决内存占用过多的问题吗
找了很多文档,都没有明确的解释

wnrlj8wa

wnrlj8wa1#

easyExcel 默认使用的是SXSSFWorkbook ,SXSSFWorkbook 会在数据行超出范围后刷新到文件中去。注意不要使用内存模式及文件类型是xlsx,图片类型是不支持的。写入数据后及时释放避免内存泄漏

public class WorkBookUtil {

    private WorkBookUtil() {}

    public static void createWorkBook(WriteWorkbookHolder writeWorkbookHolder) throws IOException {
        switch (writeWorkbookHolder.getExcelType()) {
            case XLSX:
                if (writeWorkbookHolder.getTempTemplateInputStream() != null) {
                    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(writeWorkbookHolder.getTempTemplateInputStream());
                    writeWorkbookHolder.setCachedWorkbook(xssfWorkbook);
                    if (writeWorkbookHolder.getInMemory()) {
                        writeWorkbookHolder.setWorkbook(xssfWorkbook);
                    } else {
                        writeWorkbookHolder.setWorkbook(new SXSSFWorkbook(xssfWorkbook));
                    }
                    return;
                }
                Workbook workbook;
                if (writeWorkbookHolder.getInMemory()) {
                    workbook = new XSSFWorkbook();
                } else {
                    workbook = new SXSSFWorkbook();
                }
                writeWorkbookHolder.setCachedWorkbook(workbook);
                writeWorkbookHolder.setWorkbook(workbook);
                return;
public class SXSSFWorkbook implements Workbook {
    /**
     * Specifies how many rows can be accessed at most via {@link SXSSFSheet#getRow}.
     * When a new node is created via {@link SXSSFSheet#createRow} and the total number
     * of unflushed records would exceed the specified value, then the
     * row with the lowest index value is flushed and cannot be accessed
     * via {@link SXSSFSheet#getRow} anymore.
     */
    public static final int DEFAULT_WINDOW_SIZE = 100;
iklwldmw

iklwldmw2#

easyExcel 默认使用的是SXSSFWorkbook ,SXSSFWorkbook 会在数据行超出范围后刷新到文件中去。注意不要使用内存模式及文件类型是xlsx,图片类型是不支持的。写入数据后及时释放避免内存泄漏

public class WorkBookUtil {

    private WorkBookUtil() {}

    public static void createWorkBook(WriteWorkbookHolder writeWorkbookHolder) throws IOException {
        switch (writeWorkbookHolder.getExcelType()) {
            case XLSX:
                if (writeWorkbookHolder.getTempTemplateInputStream() != null) {
                    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(writeWorkbookHolder.getTempTemplateInputStream());
                    writeWorkbookHolder.setCachedWorkbook(xssfWorkbook);
                    if (writeWorkbookHolder.getInMemory()) {
                        writeWorkbookHolder.setWorkbook(xssfWorkbook);
                    } else {
                        writeWorkbookHolder.setWorkbook(new SXSSFWorkbook(xssfWorkbook));
                    }
                    return;
                }
                Workbook workbook;
                if (writeWorkbookHolder.getInMemory()) {
                    workbook = new XSSFWorkbook();
                } else {
                    workbook = new SXSSFWorkbook();
                }
                writeWorkbookHolder.setCachedWorkbook(workbook);
                writeWorkbookHolder.setWorkbook(workbook);
                return;
public class SXSSFWorkbook implements Workbook {
    /**
     * Specifies how many rows can be accessed at most via {@link SXSSFSheet#getRow}.
     * When a new node is created via {@link SXSSFSheet#createRow} and the total number
     * of unflushed records would exceed the specified value, then the
     * row with the lowest index value is flushed and cannot be accessed
     * via {@link SXSSFSheet#getRow} anymore.
     */
    public static final int DEFAULT_WINDOW_SIZE = 100;

图片类型不支持,意思是如果导出的excel包含图片的话?那么必须将图片全部加载到内存吗?那我导出1-2g的文件,是不是需要非常大的堆内存才行呢?大佬求解

hrysbysz

hrysbysz3#

你好,我们发现此Issue已经超过三个月没有活动了,为了更好的帮助您解决问题,我们将在两周后关闭此Issue,如果您仍然有问题,请在两周内内回复此Issue,谢谢!如果您已经解决或者不需要帮助,请忽略此消息。

g52tjvyc

g52tjvyc4#

你好,我们发现此Issue已经超过三个月没有活动了,为了更好的帮助您解决问题,我们将在两周后关闭此Issue,如果您仍然有问题,请在两周内内回复此Issue,谢谢!如果您已经解决或者不需要帮助,请忽略此消息。

你好,我在尝试多次写入时报错说sheet已存在,请您看一下.

WriteSheet writeSheet = EasyExcel.writerSheet( "测试表").build();
                Map<String, String> custMap = new HashMap<String, String>();
                for (int j = 0; j < 5; j++) {
                    List<TestExcelInfo> detailList = new ArrayList<>();
                    for (int i = 0; i < offset; i++) {
                        TestExcelInfo testExcelInfo = new TestExcelInfo();
                        testExcelInfo.setCustName("客户" + i);
                        testExcelInfo.setFundName("南方");
                        testExcelInfo.setTradeacco("000000000000000" + i);
                        testExcelInfo.setShare(100.00 + i / 100.00);
                        detailList.add(testExcelInfo);
                        totalBalance = totalBalance.add(BigDecimal.valueOf(testExcelInfo.getShare()).multiply(BigDecimal.valueOf(10.0d)));
                        totalShare = totalShare.add(BigDecimal.valueOf(testExcelInfo.getShare()));
                        failureNum++;
                    }
                    excelWriter.write(detailList, writeSheet);
                }```
日志为:
16:43:42.468 [main] DEBUG com.alibaba.excel.context.WriteContextImpl - Begin to Initialization 'WriteContextImpl'
16:43:42.549 [main] DEBUG com.alibaba.excel.metadata.property.ExcelHeadProperty - The initialization sheet/table 'ExcelHeadProperty' is complete , head kind is CLASS
16:43:42.619 [main] DEBUG com.alibaba.excel.context.WriteContextImpl - CurrentConfiguration is writeWorkbookHolder
16:43:42.699 [main] DEBUG com.alibaba.excel.context.WriteContextImpl - Initialization 'WriteContextImpl' complete
16:43:42.699 [main] DEBUG com.alibaba.excel.metadata.property.ExcelHeadProperty - The initialization sheet/table 'ExcelHeadProperty' is complete , head kind is CLASS
16:43:42.699 [main] DEBUG com.alibaba.excel.context.WriteContextImpl - CurrentConfiguration is writeSheetHolder
16:43:42.699 [main] DEBUG com.alibaba.excel.context.WriteContextImpl - Can not find sheet:null ,now create it
16:43:42.769 [main] INFO com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder - create new style:com.alibaba.excel.write.metadata.style.WriteCellStyle@1fdae5fe,null
16:43:42.779 [main] INFO com.alibaba.excel.util.StyleUtil - create new font:com.alibaba.excel.write.metadata.style.WriteFont@e747bd82,null
16:43:42.789 [main] DEBUG com.alibaba.excel.context.WriteContextImpl - Sheet:null,测试表 is already existed
16:43:42.789 [main] DEBUG com.alibaba.excel.context.WriteContextImpl - CurrentConfiguration is writeSheetHolder
16:43:42.789 [main] DEBUG com.alibaba.excel.context.WriteContextImpl - Sheet:null,测试表 is already existed
16:43:42.789 [main] DEBUG com.alibaba.excel.context.WriteContextImpl - CurrentConfiguration is writeSheetHolder
16:43:42.789 [main] DEBUG com.alibaba.excel.context.WriteContextImpl - Sheet:null,测试表 is already existed
16:43:42.789 [main] DEBUG com.alibaba.excel.context.WriteContextImpl - CurrentConfiguration is writeSheetHolder
16:43:42.789 [main] DEBUG com.alibaba.excel.context.WriteContextImpl - Sheet:null,测试表 is already existed
16:43:42.789 [main] DEBUG com.alibaba.excel.context.WriteContextImpl - CurrentConfiguration is writeSheetHolder
16:43:42.839 [main] DEBUG com.alibaba.excel.context.WriteContextImpl - Finished write.
ev7lccsx

ev7lccsx5#

你好,我们发现此Issue已经超过三个月没有活动了,为了更好的帮助您解决问题,我们将在两周后关闭此Issue,如果您仍然有问题,请在两周内内回复此Issue,谢谢!如果您已经解决或者不需要帮助,请忽略此消息。

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.1.2 </version>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>3.3.4</version>
    </dependency>

相关问题