使用Apache POI API读写Excel

时间:2022-09-08 20:25:00

目录(?)[-]

  1. 读取Excel文件
  2. 写入Excel文件
  3. 读取Excel公式
  4. 写入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 plaincopyprint?使用Apache POI API读写Excel使用Apache POI API读写Excel
  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根据下面的图片.
使用Apache POI API读写Excel

读取Excel文件

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

使用Apache POI API读写Excel

使用Apache POI API读写Excel

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

Country.java

[java] view plaincopyprint?使用Apache POI API读写Excel使用Apache POI API读写Excel
  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 plaincopyprint?使用Apache POI API读写Excel使用Apache POI API读写Excel
  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 plaincopyprint?使用Apache POI API读写Excel使用Apache POI API读写Excel
  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 plaincopyprint?使用Apache POI API读写Excel使用Apache POI API读写Excel
  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文件被创建了,如下图:

使用Apache POI API读写Excel


读取Excel公式

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

ReadExcelFormula.java

[java] view plaincopyprint?使用Apache POI API读写Excel使用Apache POI API读写Excel
  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 plaincopyprint?使用Apache POI API读写Excel使用Apache POI API读写Excel
  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 plaincopyprint?使用Apache POI API读写Excel使用Apache POI API读写Excel
  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 API读写Excel

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