java - 大型 Excel 工作表生成优化

Question

我正在尝试使用 POI 库生成一个包含近 13000 行和 3 列的 .xls 文件。但是生成完整的文件需要将近 8-10 分钟。谁能建议我如何减少执行时间？

public static void generateReconReport(Connection con,String neName,String reportTable) throws SQLException, IOException{
    Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

    String sql = "SELECT * FROM "+reportTable;
    ResultSet rsNERecon = stmt.executeQuery(sql);

    System.out.println("Resulset obtained, Generating Report");

    Date date = new Date();
    SimpleDateFormat sid = new SimpleDateFormat("MMddyyyy");
    String curDate = sid.format(date);

    String fileName = neName.toUpperCase()+"_" + curDate + ".xlsx";

    File ob_file = new File(fileName);
    if(!ob_file.exists())
        ob_file.createNewFile();

    HSSFWorkbook hsfWb = new HSSFWorkbook();

    HSSFSheet hsfSheet =  hsfWb.createSheet(neName.toUpperCase()+" Recon Report");

    HSSFRow hsfRow = hsfSheet.createRow(0);

    ResultSetMetaData metaRs = rsNERecon.getMetaData();
    int colCount = metaRs.getColumnCount();

    for (int j = 1; j <= colCount; j++) {
        String colName = metaRs.getColumnName(j);
        hsfRow.createCell(j).setCellValue(colName);
    }
    FileOutputStream fileOut =  new FileOutputStream(fileName);
    int rowNum = 1;
    while (rsNERecon.next()) {
        hsfRow = hsfSheet.createRow(rowNum);
        for (int j = 1; j <= colCount; j++) 
            hsfRow.createCell(j).setCellValue(rsNERecon.getString(j));
        rowNum++;
    }

    for (int j = 1; j <= colCount; j++) 
        hsfSheet.autoSizeColumn(j);

    rsNERecon.close();
    stmt.close();

    hsfWb.write(fileOut);
    fileOut.close();
    System.out.println("Report generated for "+neName.toUpperCase());
}

Answer 1

使用制表符或逗号作为分隔符生成字符分隔值文件 (csv) 会更快。使用 .csv 扩展名保存文件。

Excel 读取这些文件的速度非常快。