java下载execl优化_java-大型Excel工作表生成优化

我正在尝试使用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());

}

解决方法:

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

Excel在读取这些文件方面非常快.

标签：apache-poi,java,excel

来源： https://codeday.me/bug/20191122/2058421.html