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

版权声明:本文为博主原创文章,未经博主允许不得转载。 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的文档读取类为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());
	}
}


阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页