使用Apache POI API读写Excel

zhaoming · 发表于 2015-11-14 09:32:30

目录(?)[-]

读取Excel文件
写入Excel文件
读取Excel公式
写入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] viewplaincopyprint?http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/CODE_ico.png http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/ico_fork.svg

<span style="font-size:14px;"><dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10-FINAL</version>
</dependency></span>

Apache POI的当前版本是3.10-FINAL.如果你使用单独的java应用,添加jars根据下面的图片.
http://cdn1.journaldev.com/wp-content/uploads/2014/03/Apache-POI-Dependencies.png

读取Excel文件
假设我们有一个叫Sample.xlsx的Excel文件,里面有两个sheet并且下面图片中的数据.我们想要读取这个Excel文件并且创建Countries list.sheet1有些额外的数据,当我们解析时会忽略它.
　　
http://1-ps.googleusercontent.com/x/www.journaldev.com/cdn2.journaldev.com/wp-content/uploads/2014/03/450x366xJava-Read-Excel-File-1-450x366.png.pagespeed.ic.GtvRSYybva.png

http://1-ps.googleusercontent.com/x/www.journaldev.com/cdn1.journaldev.com/wp-content/uploads/2014/03/450x366xJava-Read-Excel-File-2-450x366.png.pagespeed.ic.GVs2b-4eT8.png

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

　　Country.java

　　
[java] viewplaincopyprint?http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/CODE_ico.png http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/ico_fork.svg

package com.journaldev.excel.read;
public class Country {
private String name;
private String shortCode;
public Country(String n, String c){
this.name=n;
this.shortCode=c;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getShortCode() {
return shortCode;
}
public void setShortCode(String shortCode) {
this.shortCode = shortCode;
}
@Override
public String toString(){
return name + "::" + shortCode;
}
}

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

　　
[java] viewplaincopyprint?http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/CODE_ico.png http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/ico_fork.svg

package com.journaldev.excel.read;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcelFileToList {
public static List<Country> readExcelData(String fileName) {
List<Country> countriesList = new ArrayList<Country>();
try {
//Create the input stream from the xlsx/xls file
FileInputStream fis = new FileInputStream(fileName);
//Create Workbook instance for xlsx/xls file input stream
Workbook workbook = null;
if(fileName.toLowerCase().endsWith("xlsx")){
workbook = new XSSFWorkbook(fis);
}else if(fileName.toLowerCase().endsWith("xls")){
workbook = new HSSFWorkbook(fis);
}
//Get the number of sheets in the xlsx file
int numberOfSheets = workbook.getNumberOfSheets();
//loop through each of the sheets
for(int i=0; i < numberOfSheets; i++){
//Get the nth sheet from the workbook
Sheet sheet = workbook.getSheetAt(i);
//every sheet has rows, iterate over them
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
String name = "";
String shortCode = "";
//Get the row object
Row row = rowIterator.next();
//Every row has columns, get the column iterator and iterate over them
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
//Get the Cell object
Cell cell = cellIterator.next();
//check the cell type and process accordingly
switch(cell.getCellType()){
case Cell.CELL_TYPE_STRING:
if(shortCode.equalsIgnoreCase("")){
shortCode = cell.getStringCellValue().trim();
}else if(name.equalsIgnoreCase("")){
//2nd column
name = cell.getStringCellValue().trim();
}else{
//random data, leave it
System.out.println("Random data::"+cell.getStringCellValue());
}
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.println("Random data::"+cell.getNumericCellValue());
}
} //end of cell iterator
Country c = new Country(name, shortCode);
countriesList.add(c);
} //end of rows iterator
} //end of sheets for loop
//close file input stream
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
return countriesList;
}
public static void main(String args[]){
List<Country> list = readExcelData("Sample.xlsx");
System.out.println("Country List\n"+list);
}
}

这个程序很容易明白,主要步骤如下:
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] viewplaincopyprint?http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/CODE_ico.png http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/ico_fork.svg

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

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

　　
[java] viewplaincopyprint?http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/CODE_ico.png http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/ico_fork.svg

package com.journaldev.excel.read;
import java.io.FileOutputStream;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class WriteListToExcelFile {
public static void writeCountryListToFile(String fileName, List<Country> countryList) throws Exception{
Workbook workbook = null;
if(fileName.endsWith("xlsx")){
workbook = new XSSFWorkbook();
}else if(fileName.endsWith("xls")){
workbook = new HSSFWorkbook();
}else{
throw new Exception("invalid file name, should be xls or xlsx");
}
Sheet sheet = workbook.createSheet("Countries");
Iterator<Country> iterator = countryList.iterator();
int rowIndex = 0;
while(iterator.hasNext()){
Country country = iterator.next();
Row row = sheet.createRow(rowIndex++);
Cell cell0 = row.createCell(0);
cell0.setCellValue(country.getName());
Cell cell1 = row.createCell(1);
cell1.setCellValue(country.getShortCode());
}
//lets write the excel data to file now
FileOutputStream fos = new FileOutputStream(fileName);
workbook.write(fos);
fos.close();
System.out.println(fileName + " written successfully");
}
public static void main(String args[]) throws Exception{
List<Country> list = ReadExcelFileToList.readExcelData("Sample.xlsx");
WriteListToExcelFile.writeCountryListToFile("Countries.xls", list);
}
}

当我们执行上面的程序后,这个Excel文件被创建了,如下图:
　　
　　http://cdn.journaldev.com/wp-content/uploads/2014/03/Java-Write-Excel-File-450x397.png

　　

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

　　
[java] viewplaincopyprint?http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/CODE_ico.png http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/ico_fork.svg

package com.journaldev.excel.read;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcelFormula {
public static void readExcelFormula(String fileName) throws IOException{
FileInputStream fis = new FileInputStream(fileName);
//assuming xlsx file
Workbook workbook = new XSSFWorkbook(fis);
Sheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
Cell cell = cellIterator.next();
switch(cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC:
System.out.println(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
System.out.println("Cell Formula="+cell.getCellFormula());
System.out.println("Cell Formula Result Type="+cell.getCachedFormulaResultType());
if(cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC){
System.out.println("Formula Value="+cell.getNumericCellValue());
}
}
}
}
}
public static void main(String args[]) throws IOException {
readExcelFormula("FormulaMultiply.xlsx");
}
}

当我们执行上面的程序后,我们得到下面的输出:
　　
　　
[java] viewplaincopyprint?http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/CODE_ico.png http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/ico_fork.svg

1.0
2.0
3.0
4.0
Cell Formula=A1*A2*A3*A4
Cell Formula Result Type=0
Formula Value=24.0

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

　　
[java] viewplaincopyprint?http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/CODE_ico.png http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://code.iyunv.com/assets/ico_fork.svg

package com.journaldev.excel.read;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class WriteExcelWithFormula {
public static void writeExcelWithFormula(String fileName) throws IOException{
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Numbers");
Row row = sheet.createRow(0);
row.createCell(0).setCellValue(10);
row.createCell(1).setCellValue(20);
row.createCell(2).setCellValue(30);
//set formula cell
row.createCell(3).setCellFormula("A1*B1*C1");
//lets write to file
FileOutputStream fos = new FileOutputStream(fileName);
workbook.write(fos);
fos.close();
System.out.println(fileName + " written successfully");
}
public static void main(String[] args) throws IOException {
writeExcelWithFormula("Formulas.xlsx");
}
}

当我们执行上面的程序后,Excel被创建了,如下图:
　　
　　http://cdn.journaldev.com/wp-content/uploads/2014/03/java-write-excel-formula-450x343.png

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

账号		自动登录	找回密码
密码			立即注册

c++ size_t 和 int 的区别

HERE 使用 AWS EF 和 JFrog Artifactory 打

C++ 指针大全：从基础到进阶，一篇快速上手

wirelessnetview好用的无线分析工具

Red Hat RHCE 8 (EX294) Cert Guide

亿图图示专家(EDraw Max) V7.9 中文破解版

zabbix3.4.1安装部署+微信推送信息+大屏显

[经验分享] 使用Apache POI API读写Excel

扫码加入运维网微信交流群