使用Apache POI API读写Excel

原文地址:http://www.journaldev.com/2562/java-readwrite-excel-file-using-apache-poi-api.

有时候我们需要从Excel文件中读取数据,或者我们为了商务或者财政的目的生成Excel格式的报表.Java没有对操作Excel文件提供内在的支持,所以我们需要寻找开源的APIs.当我开始寻找操作Excel的APIs时候,大部分人建议使用JExcel或者Apache POI.

在深入研究后,我发现由于以下主要原因Apache POI是正确的选择.还有些关于高级特性的原因,但是我们不深入太多细节.
1)Apache基金的支持.
2)JExcel不支持xlsx格式而POI既支持xls格式又支持xlsx格式.
3)Apache POI是基于流的处理,因此更适合大文件和要求更少的内存.
Apache POI对处理Excel文件提供了强大的支持,并且能处理xls和xlsx格式的电子表格.


关于Apache POI一些重要的地方:
1)Apache POI包含适合Excel97-2007(.xls文件)的HSSF实现.
2)Apache POI XSSF实现用来处理Excel2007文件(.xlsx).
3)Apache POI HSSF和XSSF提供了读/写/修改Excel表格的机制.
4)Apache POI提供了XSSF的一个扩展SXSSF用来处理非常大的Excel工作单元.SXSSF API需要更少的内存,因此当处理非常大的电子表格同时堆内存又有限时,很合适使用.
5)有两种模式可供选择--事件模式和用户模式.事件模式要求更少的内存,因为用tokens来读取Excel并处理.用户模式更加面向对象并且容易使用,因此在我们的示例中使用用户   模式.
6)Apache POI为额外的Excel特性提供了强大支持,例如处理公式,创建单元格样式--颜色,边框,字体,头部,脚部,数据验证,图像,超链接等. 
 
Apache POI的Maven依赖

[java] view plaincopy

1. <span style="font-size:14px;"><dependency>  
2.     <groupId>org.apache.poi</groupId>  
3.     <artifactId>poi</artifactId>  
4.     <version>3.10-FINAL</version>  
5. </dependency>  
6. <dependency>  
7.     <groupId>org.apache.poi</groupId>  
8.     <artifactId>poi-ooxml</artifactId>  
9.     <version>3.10-FINAL</version>  
10. </dependency></span>  

Apache POI的当前版本是3.10-FINAL.如果你使用单独的java应用,添加jars根据下面的图片.

读取Excel文件

假设我们有一个叫Sample.xlsx的Excel文件,里面有两个sheet并且下面图片中的数据.我们想要读取这个Excel文件并且创建Countries list.sheet1有些额外的数据,当我们解析时会忽略它.

我们的国家(Country)java bean如下:

Country.java

[java] view plaincopy

1. package com.journaldev.excel.read;  
2.    
3. public class Country {  
4.    
5.     private String name;  
6.     private String shortCode;  
7.        
8.     public Country(String n, String c){  
9.         this.name=n;  
10.         this.shortCode=c;  
11.     }  
12.        
13.     public String getName() {  
14.         return name;  
15.     }  
16.     public void setName(String name) {  
17.         this.name = name;  
18.     }  
19.     public String getShortCode() {  
20.         return shortCode;  
21.     }  
22.     public void setShortCode(String shortCode) {  
23.         this.shortCode = shortCode;  
24.     }  
25.        
26.     @Override  
27.     public String toString(){  
28.         return name + "::" + shortCode;  
29.     }  
30.        
31. }  

读取Excel文件并创建Countries list代码如下:

ReadExcelFileToList.java

[java] view plaincopy

1. package com.journaldev.excel.read;  
2.    
3. import java.io.FileInputStream;  
4. import java.io.IOException;  
5. import java.util.ArrayList;  
6. import java.util.Iterator;  
7. import java.util.List;  
8.    
9. import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
10. import org.apache.poi.ss.usermodel.Cell;  
11. import org.apache.poi.ss.usermodel.Row;  
12. import org.apache.poi.ss.usermodel.Sheet;  
13. import org.apache.poi.ss.usermodel.Workbook;  
14. import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
15.    
16. public class ReadExcelFileToList {  
17.    
18.     public static List<Country> readExcelData(String fileName) {  
19.         List<Country> countriesList = new ArrayList<Country>();  
20.            
21.         try {  
22.             //Create the input stream from the xlsx/xls file  
23.             FileInputStream fis = new FileInputStream(fileName);  
24.                
25.             //Create Workbook instance for xlsx/xls file input stream  
26.             Workbook workbook = null;  
27.             if(fileName.toLowerCase().endsWith("xlsx")){  
28.                 workbook = new XSSFWorkbook(fis);  
29.             }else if(fileName.toLowerCase().endsWith("xls")){  
30.                 workbook = new HSSFWorkbook(fis);  
31.             }  
32.                
33.             //Get the number of sheets in the xlsx file  
34.             int numberOfSheets = workbook.getNumberOfSheets();  
35.                
36.             //loop through each of the sheets  
37.             for(int i=0; i < numberOfSheets; i++){  
38.                    
39.                 //Get the nth sheet from the workbook  
40.                 Sheet sheet = workbook.getSheetAt(i);  
41.                    
42.                 //every sheet has rows, iterate over them  
43.                 Iterator<Row> rowIterator = sheet.iterator();  
44.                 while (rowIterator.hasNext())   
45.                 {  
46.                     String name = "";  
47.                     String shortCode = "";  
48.                        
49.                     //Get the row object  
50.                     Row row = rowIterator.next();  
51.                        
52.                     //Every row has columns, get the column iterator and iterate over them  
53.                     Iterator<Cell> cellIterator = row.cellIterator();  
54.                         
55.                     while (cellIterator.hasNext())   
56.                     {  
57.                         //Get the Cell object  
58.                         Cell cell = cellIterator.next();  
59.                            
60.                         //check the cell type and process accordingly  
61.                         switch(cell.getCellType()){  
62.                         case Cell.CELL_TYPE_STRING:  
63.                             if(shortCode.equalsIgnoreCase("")){  
64.                                 shortCode = cell.getStringCellValue().trim();  
65.                             }else if(name.equalsIgnoreCase("")){  
66.                                 //2nd column  
67.                                 name = cell.getStringCellValue().trim();  
68.                             }else{  
69.                                 //random data, leave it  
70.                                 System.out.println("Random data::"+cell.getStringCellValue());  
71.                             }  
72.                             break;  
73.                         case Cell.CELL_TYPE_NUMERIC:  
74.                             System.out.println("Random data::"+cell.getNumericCellValue());  
75.                         }  
76.                     } //end of cell iterator  
77.                     Country c = new Country(name, shortCode);  
78.                     countriesList.add(c);  
79.                 } //end of rows iterator  
80.                    
81.                    
82.             } //end of sheets for loop  
83.                
84.             //close file input stream  
85.             fis.close();  
86.                
87.         } catch (IOException e) {  
88.             e.printStackTrace();  
89.         }  
90.            
91.         return countriesList;  
92.     }  
93.    
94.     public static void main(String args[]){  
95.         List<Country> list = readExcelData("Sample.xlsx");  
96.         System.out.println("Country List\n"+list);  
97.     }  
98.    
99. }  

这个程序很容易明白,主要步骤如下:
1)根据文件类型(.xls与.xlsx)创建Workbook实例,xlsx用XSSFWorkbook,xls用HSSFWorkbook.我们可以基于文件名字使用工  厂模式创建一个包装类来创建Workbook实例.
2)使用Workbook getNumberOfSheets()来获取sheet的数量,然后循环解析每一个sheet.使用getSheetAt(int i)方法获取             Sheet实例.
3)获取Row和Cell迭代器来获取每一个Cell对象.Apache POI在这里使用了迭代器模式.
4)使用switch-case根据Cell的类型来处理它.

现在我们运行上面的程序,在控制台产生如下的输出:

[java] view plaincopy

1. Random data::1.0  
2. Random data::2.0  
3. Random data::3.0  
4. Random data::4.0  
5. Country List  
6. [India::IND, Afghanistan::AFG, United States of America::USA, Anguilla::AIA,   
7. Denmark ::DNK, Dominican Republic ::DOM, Algeria ::DZA, Ecuador ::ECU]  

写入Excel文件

除了我们首先创建Workbook,然后设置sheets,rows,cells的值并且使用FileOutputStream把Workbook写入文件外,与读取Excel的操作相似.我们把从上面的方法读取countries list写入到另一个文件作一个例子:

WriteListToExcelFile.java

[java] view plaincopy

1. package com.journaldev.excel.read;  
2.    
3. import java.io.FileOutputStream;  
4. import java.util.Iterator;  
5. import java.util.List;  
6.    
7. import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
8. import org.apache.poi.ss.usermodel.Cell;  
9. import org.apache.poi.ss.usermodel.Row;  
10. import org.apache.poi.ss.usermodel.Sheet;  
11. import org.apache.poi.ss.usermodel.Workbook;  
12. import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
13.    
14. public class WriteListToExcelFile {  
15.    
16.     public static void writeCountryListToFile(String fileName, List<Country> countryList) throws Exception{  
17.         Workbook workbook = null;  
18.            
19.         if(fileName.endsWith("xlsx")){  
20.             workbook = new XSSFWorkbook();  
21.         }else if(fileName.endsWith("xls")){  
22.             workbook = new HSSFWorkbook();  
23.         }else{  
24.             throw new Exception("invalid file name, should be xls or xlsx");  
25.         }  
26.            
27.         Sheet sheet = workbook.createSheet("Countries");  
28.            
29.         Iterator<Country> iterator = countryList.iterator();  
30.            
31.         int rowIndex = 0;  
32.         while(iterator.hasNext()){  
33.             Country country = iterator.next();  
34.             Row row = sheet.createRow(rowIndex++);  
35.             Cell cell0 = row.createCell(0);  
36.             cell0.setCellValue(country.getName());  
37.             Cell cell1 = row.createCell(1);  
38.             cell1.setCellValue(country.getShortCode());  
39.         }  
40.            
41.         //lets write the excel data to file now  
42.         FileOutputStream fos = new FileOutputStream(fileName);  
43.         workbook.write(fos);  
44.         fos.close();  
45.         System.out.println(fileName + " written successfully");  
46.     }  
47.        
48.     public static void main(String args[]) throws Exception{  
49.         List<Country> list = ReadExcelFileToList.readExcelData("Sample.xlsx");  
50.         WriteListToExcelFile.writeCountryListToFile("Countries.xls", list);  
51.     }  
52. }  

当我们执行上面的程序后,这个Excel文件被创建了,如下图:

读取Excel公式

有时候我们需要出来带有公司的复杂Excel文件.让我们来看一个关于读取一个有值的cell中的公式的例子:

ReadExcelFormula.java

[java] view plaincopy

1. package com.journaldev.excel.read;  
2.    
3. import java.io.FileInputStream;  
4. import java.io.IOException;  
5. import java.util.Iterator;  
6.    
7. import org.apache.poi.ss.usermodel.Cell;  
8. import org.apache.poi.ss.usermodel.Row;  
9. import org.apache.poi.ss.usermodel.Sheet;  
10. import org.apache.poi.ss.usermodel.Workbook;  
11. import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
12.    
13. public class ReadExcelFormula {  
14.    
15.     public static void readExcelFormula(String fileName) throws IOException{  
16.            
17.         FileInputStream fis = new FileInputStream(fileName);  
18.            
19.         //assuming xlsx file  
20.         Workbook workbook = new XSSFWorkbook(fis);  
21.         Sheet sheet = workbook.getSheetAt(0);  
22.         Iterator<Row> rowIterator = sheet.iterator();  
23.         while (rowIterator.hasNext())   
24.         {  
25.             Row row = rowIterator.next();  
26.             Iterator<Cell> cellIterator = row.cellIterator();  
27.                
28.             while (cellIterator.hasNext())   
29.             {  
30.                 Cell cell = cellIterator.next();  
31.                 switch(cell.getCellType()){  
32.                 case Cell.CELL_TYPE_NUMERIC:  
33.                     System.out.println(cell.getNumericCellValue());  
34.                     break;  
35.                 case Cell.CELL_TYPE_FORMULA:  
36.                     System.out.println("Cell Formula="+cell.getCellFormula());  
37.                     System.out.println("Cell Formula Result Type="+cell.getCachedFormulaResultType());  
38.                     if(cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC){  
39.                         System.out.println("Formula Value="+cell.getNumericCellValue());  
40.                     }  
41.                 }  
42.             }  
43.         }  
44.     }  
45.        
46.     public static void main(String args[]) throws IOException {  
47.         readExcelFormula("FormulaMultiply.xlsx");  
48.     }  
49. }  

当我们执行上面的程序后,我们得到下面的输出:

[java] view plaincopy

1. 1.0  
2. 2.0  
3. 3.0  
4. 4.0  
5. Cell Formula=A1*A2*A3*A4  
6. Cell Formula Result Type=0  
7. Formula Value=24.0  

写入Excel公式

有时候,我们需要做一些计算然后把值写入cell.我们可以使用Excel公司来做这些计算,这样会更加精确,因为这些值会随着计算的值改变而改变.让我们来看一个关于写入一个带有公式的Excel的例子:

WriteExcelWithFormula.java

[java] view plaincopy

1. package com.journaldev.excel.read;  
2.    
3. import java.io.FileOutputStream;  
4. import java.io.IOException;  
5.    
6. import org.apache.poi.ss.usermodel.Row;  
7. import org.apache.poi.ss.usermodel.Sheet;  
8. import org.apache.poi.ss.usermodel.Workbook;  
9. import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
10.    
11. public class WriteExcelWithFormula {  
12.    
13.     public static void writeExcelWithFormula(String fileName) throws IOException{  
14.         Workbook workbook = new XSSFWorkbook();  
15.         Sheet sheet = workbook.createSheet("Numbers");  
16.         Row row = sheet.createRow(0);  
17.         row.createCell(0).setCellValue(10);  
18.         row.createCell(1).setCellValue(20);  
19.         row.createCell(2).setCellValue(30);  
20.         //set formula cell  
21.         row.createCell(3).setCellFormula("A1*B1*C1");  
22.            
23.         //lets write to file  
24.         FileOutputStream fos = new FileOutputStream(fileName);  
25.         workbook.write(fos);  
26.         fos.close();  
27.         System.out.println(fileName + " written successfully");  
28.     }  
29.        
30.     public static void main(String[] args) throws IOException {  
31.         writeExcelWithFormula("Formulas.xlsx");  
32.     }  
33. }  

当我们执行上面的程序后,Excel被创建了,如下图:

这就是所有Apache POI处理Excel文件的内容.查看POI源代码可以学习它的更多特性.