在java web系统应用中我们经常会用到大批量数据的导出,动辄就上几十万几百万的数据让我们的程序感觉压力很大,甚至都出现无法导出的情况,如内存溢出等。
在这里给大家提供一种思路:分多个文件导出,在生成文件的过程中注意回收内存,
System.out.println("---------------------");
File directory=null;
String zipPath=null;
String taskId = datas[0];
String diffFlag = datas[1];
try {
directory = new File(String.valueOf(System.currentTimeMillis()));
directory.mkdir();
String directoryPath = directory.getAbsolutePath();
StringBuffer buffer = new StringBuffer("select t.id,t.phone,t.diff_flag from ");
buffer.append(" dcs_task_user_diff_" + taskId + " t where t.diff_flag='" + diffFlag + "' and rownum <=1000000");
Connection connection = GetDBConnection.getConnection();
Statement createStatement = null;
createStatement = connection.createStatement();
ResultSet resultSet = createStatement.executeQuery(buffer.toString());
int p=0;
int i=0;
int j=0;
HSSFWorkbook workbook=null;
File file=null;
HSSFSheet createSheet=null;
FileOutputStream fileOutputStream=null;
int countPerFile = 50000;
int countPerSheet =5000;
while (resultSet.next()) {
if(i%countPerFile==0){
int countfile=(i/countPerFile+1);
String fileName=directoryPath+File.separator+countfile+".xls";
System.out.println("创建第"+fileName);
workbook=new HSSFWorkbook();
file=new File(fileName);
j=0;
}
if(j%countPerSheet==0){
int sheetCount=(j/countPerSheet+1);
System.out.println(" 创建第"+sheetCount+"个sheet");
createSheet=workbook.createSheet(sheetCount+"sheet");
System.gc();
p=0;
HSSFRow createRow = createSheet.createRow(p);
createRow.createCell(0).setCellValue(new HSSFRichTextString("编号"));
createRow.createCell(1).setCellValue(new HSSFRichTextString("手机号"));
p++;
}
HSSFRow createRow = createSheet.createRow(p);
createRow.createCell(0).setCellValue(new HSSFRichTextString(resultSet.getString("id")));
createRow.createCell(1).setCellValue(new HSSFRichTextString(resultSet.getString("phone")));
i=i+1;
j=j+1;
p++;
if(j%countPerSheet==0||j==1){
fileOutputStream = new FileOutputStream(file);
workbook.write(fileOutputStream);
fileOutputStream.flush();
}
if(j%countPerFile==0){
workbook.write(fileOutputStream);
fileOutputStream.flush();
fileOutputStream.close();
}
}
if(fileOutputStream!=null){
fileOutputStream = new FileOutputStream(file);
workbook.write(fileOutputStream);
fileOutputStream.flush();
}
GetDBConnection.destroyConnection(connection, createStatement);
System.out.println(file.getAbsolutePath());
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
zipPath= JasperExportHelper.CreateZipFile(directory.getAbsolutePath(), directory.getAbsolutePath()+".zip");
System.out.println(zipPath);
return zipPath;