原文地址https://blog.csdn.net/u014527058/article/details/50818391
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格式文档:
- 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());
- }
- }