如果你从开始菜单中启动excel2007,默认是:
1048576
如果你保存或者打开类型为excel工作簿(.xlsx):
1048576
如果你保存或者打开类型为97-2003工作簿(.xls):
65536
public class MaxRowsTest { public static void main(String[] args) { SXSSFWorkbook wb = new SXSSFWorkbook(); Sheet sheet = wb.createSheet(); for (int i = 0; i < 1048577; i++) { Row row = sheet.createRow(i); Cell cell = row.createCell(0); cell.setCellValue("我是第:" + i + " 条数据"); } File file = new File("C:\\Users\\Administrator\\Desktop" + File.separator + "MaxRows.xlsx"); OutputStream os; try { os = new FileOutputStream(file); wb.write(os); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
当写入的数据超过最大限制时:
Exception in thread "main" java.lang.IllegalArgumentException: Invalid row number (1048576) outside allowable range (0..1048575) at org.apache.poi.xssf.streaming.SXSSFSheet.createRow(SXSSFSheet.java:92) at com.energyfuture.poitest.MaxRowsTest.main(MaxRowsTest.java:34)
写大量数据时,发生 Java heap space 内存溢出时的解决办法:
是用最新版的poi ,这个问题已经解决了,利用缓存的cell,不创建更多的对象实例,导致heap 回收不了。SXSSFWorkbook关键是这个类的使用
- FileOutputStream out = new FileOutputStream(new File("D://result.xlsx"));
- Workbook writeWB = new SXSSFWorkbook(500);
- Sheet writeSheet = writeWB.createSheet();
- for (int rr = 0; rr < 400000; rr++) {
- Row writeRow = writeSheet.createRow(rr);
- for (int cc = 0; cc < 10; cc++) {
- writeRow.createCell(cc).setCellValue("测试" + rr + "," + cc);
- }
- }
- writeWB.write(out);