JXLとPOIエクスポートデータ

9385 ワード

最近データをExcelにエクスポートして、JXLとPOIの性能を比較して、ついでに両者の使い方を記録します
 
まずテスト条件を定義します
 
 
public class TestCondition {
	
	/**
	 *  
	 */
	public static final int RECORD_COUNT = 21000;
	
	/**
	 *  
	 */
	public static final String TEMPLATE_FILE = "E:/MyKernelPlatformWorkspace/Template/query_order.xls";
	
	/**
	 * JXL 
	 */
	public static final String JXL_TARGET_FILE_NAME = "E:/MyKernelPlatformWorkspace/Template/target/jxl_order.xls";
	
	/**
	 * POI 
	 */
	public static final String POI_TARGET_FILE_NAME = "E:/MyKernelPlatformWorkspace/Template/target/poi_order.xls";
	
	/**
	 * JXL 
	 */
	public static final String JXL_TEMP_DIR = "E:/MyKernelPlatformWorkspace/Template/temp";
	
}

 
それからこのテスト条件の下でJXLとPOIのテストクラスを編纂して、まずJXLの
 
 
public class JXLExcel {
	
	/**
	 *  
	 */
	private static final int start_row = 3;
	
	private WorkbookSettings settings;
	
	private File target;
	
	public JXLExcel() {
		this.settings = new WorkbookSettings();
		
		// JXL excel 
		settings.setUseTemporaryFileDuringWrite(true);
		settings.setTemporaryFileDuringWriteDirectory(new File(TestCondition.JXL_TEMP_DIR));
		
		this.target = new File(TestCondition.JXL_TARGET_FILE_NAME);
	}
	
	public void execute() throws Exception {

		//  
		Workbook template = Workbook.getWorkbook(new File(TestCondition.TEMPLATE_FILE));
		
		WritableWorkbook worbook = Workbook.createWorkbook(target, template, settings);
		
		//  sheet
		WritableSheet sheet = worbook.getSheet(0);
		
		Random random = new Random();
		
		//  
		for(int i = 0;i < TestCondition.RECORD_COUNT;i++) {
			int row = i + start_row;
			sheet.insertRow(row);
			
			Label col1 = new Label(0, row, String.valueOf(i + 1));
			Label col2 = new Label(1, row, String.valueOf(random.nextLong()));
			Label col3 = new Label(2, row, String.valueOf(random.nextLong()));
			Label col4 = new Label(3, row, "merchant" + (i +1));
			jxl.write.Number col5 = new Number(4, row, random.nextDouble());
			jxl.write.Number col6 = new Number(5, row, random.nextDouble());
			jxl.write.Number col7 = new Number(6, row, random.nextDouble());
			jxl.write.Number col8 = new Number(7, row, random.nextDouble());
			Label col9 = new Label(8, row, String.valueOf(random.nextLong()));
			Label col10 = new Label(9, row, "PAY");
			Label col11 = new Label(10, row, "POS");
			Label col12 = new Label(11, row, "2010-09-03 12:45:13");
			Label col13 = new Label(12, row, "2010-09-09 12:45:13");
			Label col14 = new Label(13, row, "interface" + (i + 1));
			Label col15 = new Label(14, row, "18701001830");
			Label col16 = new Label(15, row, "ccbc");
			Label col17 = new Label(16, row, String.valueOf(random.nextLong()));
			Label col18 = new Label(17, row, String.valueOf(random.nextLong()));
			jxl.write.Number col19 = new Number(18, row, random.nextDouble());
			jxl.write.Number col20 = new Number(19, row, random.nextDouble());
			Label col21 = new Label(20, row, "payer" + (i + 1));
			Label col22 = new Label(21, row, String.valueOf(random.nextLong()));
			Label col23 = new Label(22, row, "192.168.1.1");
			Label col24 = new Label(23, row, "192.168.1.1");
			
			sheet.addCell(col1);
			sheet.addCell(col2);
			sheet.addCell(col3);
			sheet.addCell(col4);
			sheet.addCell(col5);
			sheet.addCell(col6);
			sheet.addCell(col7);
			sheet.addCell(col8);
			sheet.addCell(col9);
			sheet.addCell(col10);
			sheet.addCell(col11);
			sheet.addCell(col12);
			sheet.addCell(col13);
			sheet.addCell(col14);
			sheet.addCell(col15);
			sheet.addCell(col16);
			sheet.addCell(col17);
			sheet.addCell(col18);
			sheet.addCell(col19);
			sheet.addCell(col20);
			sheet.addCell(col21);
			sheet.addCell(col22);
			sheet.addCell(col23);
			sheet.addCell(col24);
		}
		
		worbook.write();
		worbook.close();
	}

}

 
 
Main関数の実行
 
 
public class JXLMain {

	/**    
	 *  :    
	 * @param args    
	 * @throws Exception 
	 */
	public static void main(String[] args) throws Exception {
		 
		long jxlStart = System.currentTimeMillis();
		JXLExcel jxl = new JXLExcel();
		jxl.execute();
		long jxlStop = System.currentTimeMillis();
		System.out.println("jxl takes : " + (jxlStop - jxlStart)/1000 + " seconds.");
		
	}
 
 
そしてPOIの
 
public class POIExcel {
	/**
	 *  
	 */
	private static final int start_row = 3;

	public void execute() throws Exception {

		//  
		InputStream is = new FileInputStream(TestCondition.TEMPLATE_FILE);
		POIFSFileSystem poifsFileSystem = new POIFSFileSystem(is);
		
		HSSFWorkbook workbook = new HSSFWorkbook(poifsFileSystem);
		
		//  sheet
		HSSFSheet sheet = workbook.getSheetAt(0);

		Random random = new Random();

		//  
		sheet.shiftRows(3, 4, TestCondition.RECORD_COUNT);
		
		OutputStream os = new FileOutputStream(
				TestCondition.POI_TARGET_FILE_NAME);

		//  
		for (int i = 0; i < TestCondition.RECORD_COUNT; i++) {
			int rowNum = i + start_row;
			HSSFRow row = sheet.createRow(rowNum);

			HSSFCell cell1 = row.createCell(0);
			cell1.setCellValue(i + 1);
			
			HSSFCell cell2 = row.createCell(1);
			cell2.setCellValue(String.valueOf(random.nextLong()));
			
			HSSFCell cell3 = row.createCell(2);
			cell3.setCellValue(String.valueOf(random.nextLong()));
			
			HSSFCell cell4 = row.createCell(3);
			cell4.setCellValue("merchant" + (i +1));
			
			HSSFCell cell5 = row.createCell(4);
			cell5.setCellValue(random.nextDouble());
			
			HSSFCell cell6 = row.createCell(5);
			cell6.setCellValue(random.nextDouble());
			
			HSSFCell cell7 = row.createCell(6);
			cell7.setCellValue(random.nextDouble());
			
			HSSFCell cell8 = row.createCell(7);
			cell8.setCellValue(random.nextDouble());
			
			HSSFCell cell9 = row.createCell(8);
			cell9.setCellValue(String.valueOf(random.nextLong()));
			
			HSSFCell cell10 = row.createCell(9);
			cell10.setCellValue("PAY");
			
			HSSFCell cell11 = row.createCell(10);
			cell11.setCellValue("POS");
			
			HSSFCell cell12 = row.createCell(11);
			cell12.setCellValue(new Date());
			
			HSSFCell cell13 = row.createCell(12);
			cell13.setCellValue(new Date());
			
			HSSFCell cell14 = row.createCell(13);
			cell14.setCellValue("interface" + (i + 1));
			
			HSSFCell cell15 = row.createCell(14);
			cell15.setCellValue("18701001830");
			
			HSSFCell cell16 = row.createCell(15);
			cell16.setCellValue("ccbc");
			
			HSSFCell cell17 = row.createCell(16);
			cell17.setCellValue(String.valueOf(random.nextLong()));
			
			HSSFCell cell18 = row.createCell(17);
			cell18.setCellValue(String.valueOf(random.nextLong()));
			
			HSSFCell cell19 = row.createCell(18);
			cell19.setCellValue(random.nextDouble());
			
			HSSFCell cell20 = row.createCell(19);
			cell20.setCellValue(random.nextDouble());
			
			HSSFCell cell21 = row.createCell(20);
			cell21.setCellValue("payer" + (i + 1));
			
			HSSFCell cell22 = row.createCell(21);
			cell22.setCellValue(String.valueOf(random.nextLong()));
			
			HSSFCell cell23 = row.createCell(22);
			cell23.setCellValue("192.168.1.1");
			
			HSSFCell cell24 = row.createCell(23);
			cell24.setCellValue("192.168.1.1");
			
		}
		workbook.write(os);
		os.close();
	}
 
Main関数の実行
 
 
public class POIMain {

	/**    
	 *  :    
	 * @param args    
	 * @throws Exception 
	 */
	public static void main(String[] args) throws Exception {
		
		long jxlStart = System.currentTimeMillis();
		POIExcel poi = new POIExcel();
		poi.execute();
		long jxlStop = System.currentTimeMillis();
		System.out.println("poi takes : " + (jxlStop - jxlStart)/1000 + " seconds.");
	}

}

 
テストの結果、デフォルトのJVMメモリの下で、24列のデータ、POIは15000行以上でout of memory異常が発生し、jxlは21000以上で異常が発生し、jxlの使用時に急いで一時ファイルの使用を開始することで、エクスポート性能を効果的に向上させることが分かった.
また、POIはexcel 2003および2007をサポートし、jxlはexcel 2003のみをサポートします.