EXCEL大資料量匯出的解決方案

jvjs發表於2018-07-25
Excel大資料

將web頁面上顯示的報表匯出到excel檔案裡是一種很常見的需求。然而，當資料量較大的情況下，excel本身的支援最多65535行資料的問題便凸顯出來。下面就給出大資料量匯出到excel的解決方 案。

首先，對於資料超過了65535行的問題，很自然的就會想到將整個資料分塊，利用excel的多sheet頁的功能，將超出65535行後的資料寫入到下一個sheet頁中，即通過多sheet頁的方式，突破了最高65535行資料的限定。

具體做法就是，單獨做一個連結，使用JSP匯出，在JSP上通過程式判斷報表行數，超過65535行後分SHEET寫入。這樣這個問題就得以解決了。

更進一步地說，在這種大資料量的報表生成和匯出中，要佔用大量的記憶體，尤其是在使用TOMCAT的情況下，JVM最高只能支援到2G記憶體，則會發生 記憶體溢位的情況。此時的記憶體開銷主要是兩部分，一部分是該報表生成時的開銷，另一部分是該報表生成後寫入一個EXCEL時的開銷。由於JVM的GC機制是 不能強制回收的，因此，對於此種情形，我們給出一個變通的解決方案。

首先，將該報表設定起始行和結束行引數，在API生成報表的過程中，分步計算報表（主要效能花費在查詢生成報表中），比如一張20萬行資料的報表，在生成過程中，可通過起始行和結束 行分4-5次進行。這樣，就降低了報表生成時的記憶體佔用，在後面報表生成的過程中，如果發現記憶體不夠，即可自動啟動JVM的GC機制，回收前面報表的緩 存。

匯出EXCEL的過程，放在每段生成報表之後立即進行，改多個SHEET頁為多個EXCEL，即在分步生成報表的同時分步生成EXCEL，則通過 POI包生成EXCEL的記憶體消耗也得以降低。通過多次生成，同樣可以在後面EXCEL生成所需要的記憶體不足時，有效回收前面生成EXCEL時佔用的內 存。

再使用檔案操作，對每個客戶端的匯出請求在伺服器端根據SESSIONID和登陸時間生成唯一的臨時目錄，用來放置所生成的多個EXCEL，然後調 用系統控制檯，打包多個EXCEL為RAR或者JAR方式，最終反饋給使用者一個RAR包或者JAR包，響應客戶請求後，再次呼叫控制檯刪除該臨時目錄。

使用這種方法，首先是通過分段運算和生成，有效降低了報表從生成結果到生成EXCEL的記憶體開銷。其次是通過使用壓縮包，響應給使用者的生成檔案體積 大大縮小，降低了多使用者併發訪問時伺服器下載檔案的負擔，有效減少多個使用者匯出下載時伺服器端的流量，從而達到進一步減輕伺服器負載的效果。

  • 建立系統全域性執行緒池
final int numOfCpuCores = Runtime.getRuntime().availableProcessors();
final double blockingCoefficient = 0.9;// 阻尼係數
final int maximumPoolSize = (int)(numOfCpuCores / (1 - blockingCoefficient));
ExecutorService threadPool = new ThreadPoolExecutor(numOfCpuCores,
					maximumPoolSize, 
					0L, 
					TimeUnit.MILLISECONDS, 
					new LinkedBlockingQueue <Runnable>(),
					Executors.privilegedThreadFactory(), 
					new ThreadPoolExecutor.DiscardOldestPolicy());
複製程式碼
  • 採用多執行緒分段查詢生成報表，同步生成Excel，最後壓縮成Zip檔案
// 1.這裡每個Excel放6萬條資料（分6個sheet頁，每個1萬條），當資料量超過6萬條時，資料採用分段查詢
//  傳遞（起始行，結束行）引數，分段查詢，即分步生成報表的同時分步生成EXCEL
int SINGLE_EXCEPORT_EXCEL_MAX_NUM = 60000;
int count = bo.getTotalRecord();
final String fileNameWithTimestamp = fileName + "_" + DateUtil.getNowDateminStr();
if (count > SINGLE_EXCEPORT_EXCEL_MAX_NUM ) {
	int excelCount = count / SINGLE_EXCEPORT_EXCEL_MAX_NUM +
			(count % SINGLE_EXCEPORT_EXCEL_MAX_NUM != 0 ? 1 : 0);
	final CountDownLatch latch = new CountDownLatch(excelCount);
	final Long userId = user.getUserId();
	for(int i = 1; i <= excelCount; i++){
		bo.setPageNo(i);
		bo.setPageSize(SINGLE_EXCEPORT_EXCEL_MAX_NUM);
		final ParkRecordQryBO itemBo = new ParkRecordQryBO(bo);
		final int index = i;
		// 取一執行緒執行本次查詢
		threadPool.execute(new Runnable(){
			@Override
			public void run() {
				Page page = service.getParkRecord(itemBo);
				List<ParkRecordQryBO> records = page.getResults();
				try {
					// 2.生成單個excel
					ExportExcelUtil.createOneExcel(fileNameWithTimestamp, index ,
							expRowsList, records, userId);
				} catch (Exception e) {
					e.printStackTrace();
				}
				latch.countDown();
			}
		});
	}
	// 3.壓縮excel檔案並匯出
	latch.await();
	ExportExcelUtil.createZipExport(request, response, fileNameWithTimestamp, userId);
複製程式碼
  • 生成一個Excel存放到本地路徑
/**
 * @Description: 生成一個Excel存放到本地路徑
 * @param fileNameWithTimestamp
 * @param index
 * @param excelHeader
 * @param dataList
 * @param <T>
 * @param userId
 */
public static <T> void createOneExcel(final String fileNameWithTimestamp, 
                                int index,
                            	final String[] excelHeader, 
                            	final List<T> dataList, 
                            	Long userId ) {
	final String localRelativePath = "" + userId + "/"+ fileNameWithTimestamp ;
	Workbook wb = null;
	FileOutputStream fos = null;
	try {
		// 建立一個Workbook，對應一個Excel檔案
		wb = writeExcel(dataList, excelHeader);
		// 生成本地Excel初始檔案
		Map<String, Object> fileInfo = new HashMap<String, Object>();
		FileUtil.createFile(localRelativePath, fileNameWithTimestamp +
				"_" + index + "_.xls", fileInfo);
		fos = new FileOutputStream(fileInfo.get("realPath").toString() );
		wb.write(fos);
	} catch (FileNotFoundException e) {
		e.printStackTrace();
	} catch (IOException e) {
		e.printStackTrace();
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		try {
			if (wb != null)
			    wb.close();
			if (fos != null) 
			    fos.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}
複製程式碼
  • 壓縮打包所有Excel檔案並匯出
/**
 * @param request
 * @param response
 * @param fileNameWithTimestamp
 * @param userId
 */
public static void createZipExport(HttpServletRequest request, 
                                HttpServletResponse response,
                                final String fileNameWithTimestamp, 
                                Long userId) throws Exception{
	final String localRelativePath = "" + userId + "/"+ fileNameWithTimestamp;
	// 建立資料夾,先將生成的excel儲存到伺服器本地目錄
	// excel檔案路徑:'/app/file/[userId]/[fileNameWithTimestamp]/[fileNameWithTimestamp_i].xlS'
	String excelFold = FileUtil.getFileRootPath() + localRelativePath;
	// zip檔案所在路徑："/app/file/userId/fileNameWithTimestamp.zip"
	String zipFold = FileUtil.getFileRootPath() + userId;

	// 生成zip檔案
	final String zipFileName = fileNameWithTimestamp +".zip";
	FileUtil.createZipFile(excelFold, zipFold, zipFileName);
	// 建立匯出輸入流
	InputStream is = null;
	try{
		is = new FileInputStream(new File(zipFold + File.separator + zipFileName));
	} catch(IOException e){
		e.printStackTrace();
	}
	BufferedInputStream bis = new BufferedInputStream(is);
	// ServletOutputStream out = response.getOutputStream();
	BufferedOutputStream bos = new BufferedOutputStream(response.getOutputStream());
	
	// 解決設定名稱時的亂碼問題
	String zipName = handleFileName(request, zipFileName);
	// 設定response引數，可以開啟下載頁面
	response.reset();
	response.setContentType("application/vnd.ms-excel;charset=utf-8");
	response.setHeader("Content-Disposition", "attachment;filename=" + zipName);

	byte[] buff = new byte[2048];
	int bytesRead;
	// Simple read/write loop.
	while ((bytesRead = bis.read(buff, 0, buff.length)) != -1 ) {
		bos.write(buff, 0, bytesRead);
	}
	bis.close();
	bos.close();
	// 刪除用來臨時儲存Excel的資料夾及zip檔案
	FileUtil.deleteDir(new File(zipFold));
}
複製程式碼

相關文章