java操作Excel效率之poi与fastexcel比较

最近看到有关fastexcel的消息

自己做了个小测试
POI代码如下

Java代码

1.      public void testPoiExcel(String strPath) throws Exception {  
2.   
3. HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(strPath));  
4.   
5. HSSFSheet sheet = wb.getSheetAt(0);  
6.   
7. HSSFRow row;  
8. HSSFCell cell;  
9.   
10. for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i++) {  
11.     row = sheet.getRow(i);  
12.     for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {  
13.         cell = row.getCell((short) j);  
14.     }  
15. }  

       public void testPoiExcel(String strPath) throws Exception {HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(strPath));HSSFSheet sheet = wb.getSheetAt(0);HSSFRow row;HSSFCell cell;for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i++) {row = sheet.getRow(i);for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {cell = row.getCell((short) j);}}}

fastExcel代码如下

Java代码

1. public void testFastExcel(String strPath) throws Exception {  
2.       
3.     Workbook workBook = FastExcel.createReadableWorkbook(new File(strPath));  
4.     workBook.open();  
5.   
6.     Sheet s = workBook.getSheet(0);  
7.       
8.     String[] row;  
9.     String cell;  
10.       
11.     for (int i = s.getFirstRow(); i <= s.getLastRow(); i++) {  
12.         row = s.getRow(i);  
13.         for (int j = s.getFirstColumn(); j <= s.getLastColumn(); j++) {  
14.             cell = s.getCell(i, j);  
15.         }  
16.     }  
17.       
18.     workBook.close();  
19. }  

public void testFastExcel(String strPath) throws Exception {Workbook workBook = FastExcel.createReadableWorkbook(new File(strPath));workBook.open();Sheet s = workBook.getSheet(0);String[] row;String cell;for (int i = s.getFirstRow(); i <= s.getLastRow(); i++) {row = s.getRow(i);for (int j = s.getFirstColumn(); j <= s.getLastColumn(); j++) {cell = s.getCell(i, j);}}workBook.close();}

Excel一行数据如下【AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA,AAAAAAAA
】

测试结果如下
POI         2272条     5782毫秒
FastExcel   2272条     828毫秒
在我的机器上设置的jvm内存 poi只能跑2272条数据，再多就内存溢出了。
FastExcel做到48000条还没溢出时间是14093毫秒

为什么这么大区别 我自己感觉是 POI用的是大对象HSSFRow，HSSFCell而Fastexcel是String[]和String
毕竟在集合中数组是占用空间最小的

但是这样的话 fastexcel不能操作Excel的样式了 ，如果是做简单的Excel导入导出首选FastExcel

• Source.rar (1.1 MB)
• 下载次数: 230

<script type="text/javascript"><!-- google_ad_client = &quot;pub-9971846879634397&quot;;google_ad_slot = &quot;6749798418&quot;;google_ad_width = 728;google_ad_height = 90;// --></script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"></script><script src="http://pagead2.googlesyndication.com/pagead/expansion_embed.js"></script><script src="http://googleads.g.doubleclick.net/pagead/test_domain.js"></script><script type="text/javascript"><!-- window.google_render_ad();// --></script>

声明：JavaEye文章版权属于作者，受法律保护。没有作者书面许可不得转载。

推荐链接

• 
• 6月份JAVA技术沙龙活动通知
• 在繁琐中挣扎还是简化自主管理？

                                 

• houwen
• 等级: 初级会员
• 
• 文章: 2
• 积分: 30
• 来自: 上海
• 

Java代码

1. public static void main(String [] args){  
2.         writeExcel("testPoi.xls");  
3.         System.out.println(readExcel(new File("testPoi.xls")));  
4.     }  
5.   
6.     private static void writeExcel(String fileName) {  
7.         HSSFWorkbook wb = new HSSFWorkbook();  
8.         for(int sheetIndx = 0; sheetIndx < 5 ;sheetIndx++){  
9.             HSSFSheet sheet = wb.createSheet("sheet" + sheetIndx);     
10.             HSSFRow row;     
11.             HSSFCell cell;     
12.             for (int rowIndex = 0; rowIndex < 10; rowIndex++) {     
13.                 row = sheet.createRow(rowIndex);     
14.                 for (int cellIndex = 0; cellIndex < 5; cellIndex++) {     
15.                     cell = row.createCell(cellIndex);  
16.                     cell.setCellValue("hello");  
17.                 }   
18.             }    
19.         }  
20.         FileOutputStream fos = null;     
21.         try{  
22.             initFile(fileName);  
23.             fos = new FileOutputStream(fileName);   
24.             wb.write(fos);  
25.         }catch(Exception e){  
26.             e.printStackTrace();  
27.   
28.         } finally {     
29.             try {     
30.                 fos.flush();  
31.                 fos.close();    
32.             } catch (Exception e) {     
33.                 e.printStackTrace();     
34.             }     
35.         }     
36.     }  
37.   
38.     private static String readExcel(File file) {  
39.         StringBuffer strBuffer = new StringBuffer();  
40.         HSSFWorkbook wb;  
41.         FileInputStream fs = null;   
42.         try {  
43.             fs = new FileInputStream(file);   
44.             wb = new HSSFWorkbook(fs);     
45.             int sheetNum = wb.getNumberOfSheets();  
46.             for(int sheetIndx = 0; sheetIndx < sheetNum ;sheetIndx++){  
47.                 HSSFSheet sheet = wb.getSheetAt(sheetIndx);     
48.                 HSSFRow row;     
49.                 HSSFCell cell;     
50.                 strBuffer.append(wb.getSheetName(sheetIndx)+ "/r/n");   
51.                 for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i++) {     
52.                     row = sheet.getRow(i);     
53.                     for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {     
54.                         cell = row.getCell(j);  
55.                         strBuffer.append(cell.toString() + "/t");  
56.                     }   
57.                     strBuffer.append("/r/n");   
58.                 }    
59.             }  
60.         }catch (Exception e){  
61.   
62.         } finally {  
63.             try {  
64.                 fs.close();  
65.             } catch (IOException e) {  
66.                 e.printStackTrace();  
67.             }  
68.         }  
69.         return strBuffer.toString();  
70.     }  
71.     private static void initFile(String filePath) {  
72.         File file = new File(filePath);  
73.         if(!file.exists()){  
74.             try {  
75.                 file.createNewFile();  
76.             } catch (IOException e) {  
77.                 // TODO Auto-generated catch block  
78.                 e.printStackTrace();  
79.             }  
80.         }     
81.     }  

public static void main(String [] args){writeExcel("testPoi.xls");System.out.println(readExcel(new File("testPoi.xls")));}private static void writeExcel(String fileName) {HSSFWorkbook wb = new HSSFWorkbook();for(int sheetIndx = 0; sheetIndx < 5 ;sheetIndx++){HSSFSheet sheet = wb.createSheet("sheet" + sheetIndx);   HSSFRow row;   HSSFCell cell;   for (int rowIndex = 0; rowIndex < 10; rowIndex++) {   row = sheet.createRow(rowIndex);   for (int cellIndex = 0; cellIndex < 5; cellIndex++) {   cell = row.createCell(cellIndex);cell.setCellValue("hello");} }  }FileOutputStream fos = null;   try{initFile(fileName);fos = new FileOutputStream(fileName); wb.write(fos);}catch(Exception e){e.printStackTrace();} finally {   try {   fos.flush();fos.close();  } catch (Exception e) {   e.printStackTrace();   }   }   }private static String readExcel(File file) {StringBuffer strBuffer = new StringBuffer();HSSFWorkbook wb;FileInputStream fs = null; try {fs = new FileInputStream(file); wb = new HSSFWorkbook(fs);   int sheetNum = wb.getNumberOfSheets();for(int sheetIndx = 0; sheetIndx < sheetNum ;sheetIndx++){HSSFSheet sheet = wb.getSheetAt(sheetIndx);   HSSFRow row;   HSSFCell cell;   strBuffer.append(wb.getSheetName(sheetIndx)+ "/r/n"); for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i++) {   row = sheet.getRow(i);   for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {   cell = row.getCell(j);strBuffer.append(cell.toString() + "/t");} strBuffer.append("/r/n"); }  }}catch (Exception e){} finally {try {fs.close();} catch (IOException e) {e.printStackTrace();}}return strBuffer.toString();}private static void initFile(String filePath) {File file = new File(filePath);if(!file.exists()){try {file.createNewFile();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}

         

poi当cell对象过多的时候 会造成内存溢出的
而且样式什么的设置 如果过多的话也会内存溢出