当前位置: 首页 > 知识库问答 >
问题:

用SXSSF POI编写大型Excel表格

邹丰羽
2023-03-14

我正在使用POI()的流式工作表编写一个大型Excel表(超过一百万条记录),但我得到的是Java.lang.OutOfMemoryError:Java堆空间。下面是我的代码:

 public void exportExcel() {
            try {
                SXSSFWorkbook workbook = new SXSSFWorkbook();
                Sheet sheet = workbook.createSheet(); 

                List<Oject> list = dao.getList();
                Cell cell = null;
                if (!list.isEmpty()) {

                Row row = sheet.createRow(0);
                cell = row.createCell(0);
                cell.setCellValue("s1");

                cell = row.createCell(1);
                cell.setCellValue("s2");

                cell = row.createCell(2);
                cell.setCellValue("s3");

                cell = row.createCell(3);
                cell.setCellValue("s4");

                cell = row.createCell(4);
                cell.setCellValue("s5");

                cell = row.createCell(5);
                cell.setCellValue("s6");

                cell = row.createCell(6);
                cell.setCellValue("s7");

                cell = row.createCell(7);
                cell.setCellValue("s8");

                cell = row.createCell(8);
                cell.setCellValue("s9");

                cell = row.createCell(9);
                cell.setCellValue("s10");

                cell = row.createCell(10);
                cell.setCellValue("s11");

                cell = row.createCell(11);
                cell.setCellValue("s12");

                cell = row.createCell(12);
                cell.setCellValue("s13");

                cell = row.createCell(13);
                cell.setCellValue("s14");

                cell = row.createCell(14);
                cell.setCellValue("s15");

                cell = row.createCell(15);
                cell.setCellValue("s16");

                cell = row.createCell(16);
                cell.setCellValue("s17");

                cell = row.createCell(17);
                cell.setCellValue("s18");

                cell = row.createCell(18);
                cell.setCellValue("s19");

                cell = row.createCell(19);
                cell.setCellValue("s20");

                cell = row.createCell(20);
                cell.setCellValue("s21");

                cell = row.createCell(21);
                cell.setCellValue("s22");

                cell = row.createCell(22);
                cell.setCellValue("s23");

                cell = row.createCell(23);
                cell.setCellValue("s24");

                cell = row.createCell(24);
                cell.setCellValue("s25");

                cell = row.createCell(25);
                cell.setCellValue("s26");

                cell = row.createCell(26);
                cell.setCellValue("s27");

                cell = row.createCell(27);
                cell.setCellValue("s28");

                cell = row.createCell(28);
                cell.setCellValue("s29");

                cell = row.createCell(29);
                cell.setCellValue("s30");

                cell = row.createCell(30);
                cell.setCellValue("s31");

                cell = row.createCell(31);
                cell.setCellValue("s32");

                cell = row.createCell(32);
                cell.setCellValue("s33");

                cell = row.createCell(33);
                cell.setCellValue("s34");

                cell = row.createCell(34);
                cell.setCellValue("s35");

                cell = row.createCell(35);
                cell.setCellValue("s36");

                cell = row.createCell(36);
                cell.setCellValue("s37");

                for (int i = 0; i < list.size(); i++) {

                    row = sheet.createRow(i + 1);

                    cell = row.createCell(0);
                    if (list.get(i).getOne == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getOne);
                    }

                    cell = row.createCell(1);
                    if (list.get(i).getTwo() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getTwo());
                    }

                    cell = row.createCell(2);
                    if (list.get(i).getThree() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getThree());
                    }

                    cell = row.createCell(3);
                    if (list.get(i).getFour() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getFour().toString());
                    }

                    cell = row.createCell(4);
                    if (list.get(i).getFive() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getFive());
                    }

                    cell = row.createCell(5);
                    if (list.get(i).getSix() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getSix());
                    }

                    cell = row.createCell(6);
                    if (list.get(i).getSeven() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getSeven());
                    }

                    cell = row.createCell(7);
                    if (list.get(i).getEight() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getEight());
                    }

                    cell = row.createCell(8);
                    if (list.get(i).getNine() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getNine());
                    }

                    cell = row.createCell(9);
                    if (list.get(i).getTen() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getTen().toString());
                    }

                    cell = row.createCell(10);
                    if (list.get(i).getD() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getD());
                    }

                    cell = row.createCell(11);
                    if (list.get(i).getF() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getF().toString());
                    }

                    cell = row.createCell(12);
                    if (list.get(i).getV() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getV().toString());
                    }

                    cell = row.createCell(13);
                    if (list.get(i).getVa() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getVa().toString());
                    }

                    cell = row.createCell(14);
                    if (list.get(i).getFo() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getFo().toString());
                    }

                    cell = row.createCell(15);
                    if (list.get(i).getVar() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getVar().toString());
                    }

                    cell = row.createCell(16);
                    if (list.get(i).getVarm() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getVarm().toString());
                    }

                    cell = row.createCell(17);
                    if (list.get(i).getFor() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getFor().toString());
                    }

                    cell = row.createCell(18);
                    if (list.get(i).getVarp() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getVarp().toString());
                    }

                    cell = row.createCell(19);
                    if (list.get(i).getVau() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getVau().toString());
                    }

                    cell = row.createCell(20);
                    if (list.get(i).getFoi() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getFoi().toString());
                    }

                    cell = row.createCell(21);
                    if (list.get(i).getVaro() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getVaro());
                    }

                    cell = row.createCell(22);
                    if (list.get(i).getVo() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getvo().toString());
                    }

                    cell = row.createCell(23);
                    if (list.get(i).getF5() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getF5().toString());
                    }

                    cell = row.createCell(24);
                    if (list.get(i).getV5() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getV5().toString());
                    }

                    cell = row.createCell(25);
                    if (list.get(i).getVm5() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getVm5().toString());
                    }

                    cell = row.createCell(26);
                    if (list.get(i).getDo() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getDo().toString());
                    }

                    cell = row.createCell(27);
                    if (list.get(i).getDmow() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getDmow().toString());
                    }

                    cell = row.createCell(28);
                    if (list.get(i).getCut() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getCut());
                    }

                    cell = row.createCell(29);
                    if (list.get(i).getDre() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getDre().toString());
                    }

                    cell = row.createCell(30);
                    if (list.get(i).getCli() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getCli());
                    }

                    cell = row.createCell(31);
                    if (list.get(i).getDn() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getDn().toString());
                    }

                    cell = row.createCell(32);
                    if (list.get(i).getCod() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getCod());
                    }

                    cell = row.createCell(33);
                    if (list.get(i).getCk() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getCk());
                    }

                    cell = row.createCell(34);
                    if (list.get(i).getRem() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getRem().toString());
                    }

                    cell = row.createCell(35);
                    if (list.get(i).getPs() == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getPs().toString());
                    }

                    cell = row.createCell(36);
                    if (list.get(i).getTyp) == null) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(list.get(i).getTyp().toString());
                    }

                }

            }

            FacesContext facesContext = FacesContext.getCurrentInstance();
            ExternalContext externalContext = facesContext.getExternalContext()
           externalContext.setResponseContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            externalContext.setResponseHeader("Content-Disposition", "attachment; filename=\"Lim_" + new Date().toString() + ".xlsx\"");

            workbook.write(externalContext.getResponseOutputStream());
            facesContext.responseComplete();
            workbook.dispose();
        } catch (Exception e) {
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", null));
            e.printStackTrace();
        }
    }

共有1个答案

包翔
2023-03-14

使用new SXSSFWorkbook();创建流工作簿时,隐式指定无限行访问。这通常不是你想要的写作。我建议在构造函数中添加一个整数参数,例如。

SXSSFWorkbook workbook = new SXSSFWorkbook(100);

这应该会大大减少内存消耗。您甚至可以使用1作为数字,只要您只按顺序写入行。

 类似资料:
  • 问题内容: 有人可以指出我正确的方向,以便在Java中写入Excel文件吗?我不了解我在网上找到的链接。您能给我发送链接还是我可以遵循的任何方式? 谢谢J 问题答案: 并不是平庸,但是Apache POI可以做到。您可以在此处找到一些代码示例: http //poi.apache.org/spreadsheet/examples.html

  • 由于网络的原因,如何有效的写大数据在异步框架是一个特殊的问题。因为写操作是非阻塞的,即便是在数据不能写出时,只是通知 ChannelFuture 完成了。当这种情况发生时,你必须停止写操作或面临内存耗尽的风险。所以写时,会产生大量的数据,我们需要做好准备来处理的这种情况下的缓慢的连接远端导致延迟释放内存的问题你。作为一个例子让我们考虑写一个文件的内容到网络。 在我们的讨论传输(见4.2节)时,我们

  • 我已经创建了从excel中获取元素的脚本。 这是我的剧本 如果我的测试用例通过,我想在下一列写通过,如果失败,然后“失败”。 如何做到这一点,需要做些什么!!!

  • 我可以刷新编写器并再次使用相同的编写器向文件中追加更多的xml,如下所示:

  • 下面的代码用于获取excel文件(.xlsx)的工作表名称 上面的代码存在的问题是,为一个9MB大小的文件创建需要占用大量内存(~700MB)&时间很长(5-6s)。即使将设置为也不会释放占用的内存(我知道可能被调用,也可能不被调用&JVM不会仅仅因为我将变量设置为null而释放内存) 我确实浏览了Workbook,XSSFWorkbook&根据我的理解,没有任何方法可以帮助我获得内存印记较低的工

  • 问题内容: 我有以下代码用于获取excel文件的工作表名称(.xlsx) 我上面的代码存在的问题是,为9MB大小的文件创建文件需要花费大量内存(〜700MB)和较长时间(5-6s)。即使将设置为,也不会释放占用的内存(我知道可能会叫,也可能不会叫&JVM不会因为我将变量设置为null而释放内存) 我确实浏览了Workbook,XSSFWorkbook的文档,据我了解,没有任何方法可以帮助我获得内存