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

最新推荐文章于 2024-07-22 16:44:01 发布

Jicky-17

最新推荐文章于 2024-07-22 16:44:01 发布

阅读量1w

点赞数 3

分类专栏： Java 文章标签： java excel poi

本文链接：https://blog.csdn.net/u014527058/article/details/50818391

版权

Java 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

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());
	}
}

Jicky-17

关注

3
点赞
踩
20

收藏

觉得还不错? 一键收藏
0
评论
Apache POI读写Excel文档入门（支持XLS和XLSX格式）

Apache POI是可用于Java程序，用于对Microsoft Office文档进行读写的API。本文通过一个简单的小例子，简要介绍通过Apache POI对Excel文档进行读写的方法。
复制链接

扫一扫