Apache POI读写Excel文档入门（支持XLS和XLSX格式）

Java读写Office文档的库非常多，且都日趋成熟，比较常见的API有Apache POI、docx4j和UNO等。本文将以Apache POI为例，向大家介绍通过Java读写Excel文档的方法。

一、Apache POI简介

Apache POI 是用Java编写的免费开源的跨平台的 Java API，具有创建和维护各种符合Office OpenXML（OOXML）和OLE 2复合文档格式的功能，可对Word、Excel、PowerPoint进行读写操作。
目前该API的最新版本为3.14，于2016年3月2日发布。在本文中所使用的POI则为3.11版本。
值得一提的是，POI的全称是Poor Obfuscation Implementation，意为“简陋又模糊的实现”，这和slf4j（Simple Log Facade for Java，Java简单日志门面）的取名有异曲同工之妙。这两个东西实际上是非常强大的，但是它们的作者却说自己的东西很poor、很simple，不得不佩服外国人的谦虚。

二、JAR包依赖

Excel文档分为XLS（针对Excel 97-2003）格式和XLSX（针对Excel 2007及以后版本）格式，不同格式所需的JAR包依赖是不一样的。

下面的依赖仅支持XLS格式：

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.11-beta1</version>
</dependency>

以下依赖既支持XLS格式，也支持XLSX格式：

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.11-beta1</version>
</dependency>

三、示例实体类

用户类。由用户名、密码、昵称组成。

package com.fhp.testpoi.entity;

public class User {
protected String username;
protected String password;
protected String nickname;

public User() {
super();
}

public User(String username, String password, String nickname) {
this.username = username;
this.password = password;
this.nickname = nickname;
}

public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getNickname() {
return nickname;
}
public void setNickname(String nickname) {
this.nickname = nickname;
}

@Override
public String toString() {
StringBuffer sb = new StringBuffer();

sb.append("username=");
sb.append(username);
sb.append(";password=");
sb.append(password);
sb.append(";nickname=");
sb.append(nickname);

return sb.toString();
}
}

四、读取Excel文档

总体思路如下：XLSX的文档读取类为XSSFWorkBook，其实现了WorkBook接口，可通过File类进行构造。然后通过Excel文档按照需求读取对应的工作表，再从工作表中根据所需行号等信息读取对应的行，最后根据列号定位到表中的单元格。该API可以从单元格中读取字符串、整形数、浮点数、日期和公式等数据。

读取XLSX格式文档：

package com.fhp.testpoi;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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;

import com.fhp.testpoi.entity.User;

public class UserXlsxReader {

public List<User> read(File file) throws InvalidFormatException, IOException {
Workbook workbook = new XSSFWorkbook(file);
    Sheet sheet = workbook.getSheetAt(0);

    List<User> result = new ArrayList<User>();

    int rowStart = sheet.getFirstRowNum() + 1;
    int rowEnd = sheet.getLastRowNum();

    for(int i = rowStart; i <= rowEnd; i++) {
    Row row = sheet.getRow(i);    
    User user = this.getUserFromRow(row);    
    if(user != null) result.add(user);
    }
    workbook.close();
    return result;
}

protected User getUserFromRow(Row row) {
if(row == null) return null;
    int current = row.getFirstCellNum() + 1;
    Cell cell = row.getCell(current);
    if(null != cell) {
    User user = new User();
    user.setUsername(cell.getStringCellValue());
        current++;

        cell = row.getCell(current);
        user.setPassword(cell.getStringCellValue());
        current++;

        cell = row.getCell(current);
        user.setNickname(cell.getStringCellValue());

        return user;
    }
    return null;
}
}

读取XLS格式文档，比读XLSX要稍微麻烦一些。

package com.fhp.testpoi;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
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;

import com.fhp.testpoi.entity.User;

public class UserXlsxReader {

public List<User> read(File file) throws InvalidFormatException, IOException {

POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(file));
Workbook workbook = new HSSFWorkbook(poifsFileSystem);

Sheet sheet = workbook.getSheetAt(0);

    List<User> result = new ArrayList<User>();

    int rowStart = sheet.getFirstRowNum() + 1;
    int rowEnd = sheet.getLastRowNum();

    for(int i = rowStart; i <= rowEnd; i++) {
    Row row = sheet.getRow(i);    
    User user = this.getUserFromRow(row);    
    if(user != null) result.add(user);
    }
    workbook.close();
    return result;
}

protected User getUserFromRow(Row row) {
if(row == null) return null;
    int current = row.getFirstCellNum() + 1;
    Cell cell = row.getCell(current);
    if(null != cell) {
    User user = new User();
    user.setUsername(cell.getStringCellValue());
        current++;

        cell = row.getCell(current);
        user.setPassword(cell.getStringCellValue());
        current++;

        cell = row.getCell(current);
        user.setNickname(cell.getStringCellValue());

        return user;
    }
    return null;
}
}

五、写入Excel文档

总体思路如下：和读取文档的思路相似，按照文件→工作表→行→列的方式进行定位。除了将Excel文档写入文件之外，还可以将Excel文档写入到流中，便于传输。
下面贴上写XLSX的代码。若需要写XLS格式的文档，把第33行的XSSFWorkBook改为HSSFWorkBook即可，这里就不贴了。

package com.fhp.testpoi;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Collection;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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;

import com.fhp.testpoi.entity.User;

public class UserXlsxWriter {
protected File file;
protected OutputStream os;
protected Workbook book = null;
public UserXlsxWriter() {
super();
}

public UserXlsxWriter(File file) throws IOException, InvalidFormatException {
super();
this.file = file;
if(!file.exists()) {
file.createNewFile();
}
os = new FileOutputStream(file);
book = new XSSFWorkbook();
Sheet sheet = book.createSheet("user");

String[] title = {"用户名", "密码", "昵称"};
Row titleRow = sheet.createRow(0);
for(int i = 0; i < title.length; i++) {
Cell cell = titleRow.createCell(i + 1);
cell.setCellValue(title[i]);
}
}

public void Write(User user) throws IOException {
Sheet sheet = book.getSheet("user");
int lastRowNum = sheet.getLastRowNum();
Row currentRow = sheet.createRow(lastRowNum + 1);
currentRow.createCell(0).setCellFormula("ROW() - 1");
currentRow.createCell(1).setCellValue(user.getUsername());
currentRow.createCell(2).setCellValue(user.getPassword());
currentRow.createCell(3).setCellValue(user.getNickname());
}

public void Write(Collection<User> users) throws IOException {
for(User u : users) {
this.Write(u);
}
}

public void Write(User... users) throws IOException {
for(User u : users) {
this.Write(u);
}
}

public void Extract() throws IOException {
book.write(os);
book.close();
}
}

六、测试类

读取Excel文档：

package com.fhp.testpoi;

import static org.junit.Assert.*;

import java.io.File;
import java.io.IOException;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.junit.Test;

import com.fhp.testpoi.entity.User;

public class TestUserXlsxReader {

@Test
public void testRead() throws InvalidFormatException, IOException {
File file = new File("H:/testxlsx.xlsx");
UserXlsxReader reader = new UserXlsxReader();
List<User> users = reader.read(file);
assertEquals(3, users.size());
}
}

写入Excel文档：

package com.fhp.testpoi;

import static org.junit.Assert.*;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.junit.Test;

import com.fhp.testpoi.entity.User;

public class TestUserXlsxWriter {

@Test
public void testWrite() throws InvalidFormatException, IOException {
File file = new File("H:/testxlsx.xlsx");
if(file.exists()) {
file.delete();
}
UserXlsxWriter writer = new UserXlsxWriter(file);

User user1 = new User("admin", "admin", "Administrator");
User user2 = new User("user1", "user1", "Sally");
User user3 = new User("user2", "zhangsan", "张三");

writer.Write(user1);
writer.Write(user2);
writer.Write(user3);
writer.Extract();
assertTrue(file.exists());
}

@Test
public void testBatchWrite() throws InvalidFormatException, IOException {
File file = new File("H:/testxlsxbatch.xlsx");
if(file.exists()) {
file.delete();
}
UserXlsxWriter writer = new UserXlsxWriter(file);

User user1 = new User("admin", "admin", "Administrator");
User user2 = new User("user1", "user1", "Sally");
User user3 = new User("user2", "zhangsan", "张三");

List<User> users = new ArrayList<User>();

users.add(user1);
users.add(user2);
users.add(user3);

writer.Write(users);
writer.Extract();
assertTrue(file.exists());
}
}