excel大数据量的写入

大数据量的写入

HSSF(03版)

• 优点：过程中数据全部写入缓存，不操作磁盘，最后一次写入缓存，速度快

• 缺点：最多只能处理65536行，否则会抛出异常

  Invalid row number (65536) outside allowable range (0..65535)
  java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)

实战

抛出异常测试

public class ExcelBigData {
  private static String PATH = "/Users/panyurou/IdeaProjects/exceldemo";

  @Test
  public void excelWrite03() throws IOException {
    Workbook workbook = new HSSFWorkbook();
    final Sheet sheet = workbook.createSheet("测试大数据量");
    final long begin = System.currentTimeMillis();
    for (int i = 0; i < 65537; i++) {
      final Row row = sheet.createRow(i);
      for (int j = 0; j < 10; j++) {
        final Cell cell = row.createCell(j);
        cell.setCellValue("${i,j}");
      }
    }
    System.out.println("over!");
    FileOutputStream outputStream = new FileOutputStream(PATH + "/excel大数据写入01.xls");
    workbook.write(outputStream);
    outputStream.close();
    System.out.println("excel 写入完毕！");
    final long end = System.currentTimeMillis();
    System.out.println("写入时间："+(double) ((end - begin) / 1000));
  }
}

将上面的65537改成65536，输出写入时长，可看出花费了1秒

SXSSFWorkbook

• 优点：可以写非常大的数据量，写速度更快，占用更少的内存
• 注意：
  • 过程中会产生临时文件，需要清理临时文件
  • 默认100条数据会被保留在内存中，如果超过这数量，则数据会被写入临时文件
    • 如果想自定义内存中的数量，可以使用new SXSSFWorkbook(数量);

实战

public void excelWriteSXSSF() throws IOException {
    SXSSFWorkbook workbook = new SXSSFWorkbook();
    final Sheet sheet = workbook.createSheet("测试大数据量");
    final long begin = System.currentTimeMillis();
    for (int i = 0; i < 65537; i++) {
      final Row row = sheet.createRow(i);
      for (int j = 0; j < 120; j++) {
        final Cell cell = row.createCell(j);
        cell.setCellValue("${i,j}");
      }
    }
    System.out.println("over!");
    FileOutputStream outputStream = new FileOutputStream(PATH + "/excel大数据写入09s.xlsx");
    workbook.write(outputStream);
    outputStream.close();
    // 清除临时文件！！！
    workbook.dispose();
    System.out.println("excel 写入完毕！");
    final long end = System.currentTimeMillis();
    System.out.println("写入时间："+(double) ((end - begin) / 1000));
  }

可以看出即使现在有120列，也只需要10秒，速度很快。