使用poi实现读写excel

pom.xml添加依赖

<!-- office文档组件 --> 
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.9</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.9</version>
</dependency>
<dependency>
	<groupId>org.apache.xmlbeans</groupId>
	<artifactId>xmlbeans</artifactId>
	<version>2.4.0</version>
</dependency>

 

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.junit.Test;

public class TestExcel {

	// 读取，全部sheet表及数据
	@Test
	public void showExcel() throws Exception {
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File("E:/temp/t1.xls")));
		HSSFSheet sheet = null;
		for (int i = 0; i < workbook.getNumberOfSheets(); i++) {// 获取每个Sheet表
			sheet = workbook.getSheetAt(i);
			for (int j = 0; j < sheet.getLastRowNum() + 1; j++) {// getLastRowNum，获取最后一行的行标
				HSSFRow row = sheet.getRow(j);
				if (row != null) {
					for (int k = 0; k < row.getLastCellNum(); k++) {// getLastCellNum，是获取最后一个不为空的列是第几个
						if (row.getCell(k) != null) { // getCell 获取单元格数据
							System.out.print(row.getCell(k) + "\t");
						} else {
							System.out.print("\t");
						}
					}
				}
				System.out.println(""); // 读完一行后换行
			}
			System.out.println("读取sheet表：" + workbook.getSheetName(i) + " 完成");
		}
	}

	// 读取，指定sheet表及数据
	@Test
	public void showExcel2() throws Exception {
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File("E:/temp/t1.xls")));
		HSSFSheet sheet = null;
		int i = workbook.getSheetIndex("xt"); // sheet表名
		sheet = workbook.getSheetAt(i);
		for (int j = 0; j < sheet.getLastRowNum() + 1; j++) {// getLastRowNum
																// 获取最后一行的行标
			HSSFRow row = sheet.getRow(j);
			if (row != null) {
				for (int k = 0; k < row.getLastCellNum(); k++) {// getLastCellNum
																// 是获取最后一个不为空的列是第几个
					if (row.getCell(k) != null) { // getCell 获取单元格数据
						System.out.print(row.getCell(k) + "\t");
					} else {
						System.out.print("\t");
					}
				}
			}
			System.out.println("");
		}
	}

	// 写入，往指定sheet表的单元格
	@Test
	public void insertExcel3() throws Exception {
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File("E:/temp/t1.xls"))); // 读取的文件
		HSSFSheet sheet = null;
		int i = workbook.getSheetIndex("xt"); // sheet表名
		sheet = workbook.getSheetAt(i);

		HSSFRow row = sheet.getRow(0); // 获取指定的行对象，无数据则为空，需要创建
		if (row == null) {
			row = sheet.createRow(0); // 该行无数据，创建行对象
		}

		Cell cell = row.createCell(1); // 创建指定单元格对象。如本身有数据会替换掉
		cell.setCellValue("tt"); // 设置内容

		FileOutputStream fo = new FileOutputStream("E:/temp/t1.xls"); // 输出到文件
		workbook.write(fo);

	}

}

 

问题：

1，excel某些单元格使用公式计算，如引用其他单元格的值，Java读取不了需要经过计算后的值？

　　分析：原因是获取有公式的单元格值前需要刷新公式，执行计算

　　解决：需要引用公式或保存excel(workbook.write(fo))前，添加：workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();