java中使用poi导出Excel大批量数据到客户端

在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;