java 读取excel文件中经过公式算出的列值

来源：互联网发布：快捷启动移动数据地址编辑：程序博客网时间：2024/06/03 20:12

我在读取excel表格的时候，发现明明好好的数值列，在某一行就是报错，debug了一下，发现那一列是经过公式算出来的 比如值是：=A1+B1+C1。 如果想要经过计算后的此列的值，就需要用到HSSFFormulaEvaluator.我参考的是在一个bbs上看到的：http://bbs.csdn.net/topics/340055433

import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;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.CellValue;import org.junit.After;import org.junit.AfterClass;import org.junit.Before;import org.junit.BeforeClass;import org.junit.Test;import com.zkhy.web.integration.settle.entity.SettlePay;import com.zkhy.web.presentation.settle.vo.PayAddressVO;public class TestAnalyseExcel {@BeforeClasspublic static void setUpBeforeClass() throws Exception {}@AfterClasspublic static void tearDownAfterClass() throws Exception {}@Beforepublic void setUp() throws Exception {}@Afterpublic void tearDown() throws Exception {} HSSFWorkbook hssfWorkbook;HSSFFormulaEvaluator evaluator;       @Testpublic void testGetListFromExcel() throws IOException {        InputStream is = new FileInputStream("D:\\file\\settle\\pay\\1435644172649socialSecurity_city.xls");        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);       evaluator = new HSSFFormulaEvaluator();        // 获取第一个工作表Sheet        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);                if (hssfSheet == null) {        throw new IOException("传入的excel的第一张表为空！");        }        List<SettlePay> list = new ArrayList<SettlePay>();                 HSSFRow hssfRow = hssfSheet.getRow(1);                    for (int rowNum = 2; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {                hssfRow = hssfSheet.getRow(rowNum);        SettlePay record = convertRowToSettlePay(hssfRow);        list.add(record);         }        }private SettlePay convertRowToSettlePay(HSSFRow hssfRow) { SettlePay record = new SettlePay(); for(int i=1;i<hssfRow.getLastCellNum();i++){ String b = getCellValue(hssfRow.getCell(i)); } return record;}    private StringgetCellValue（HSSFCell hssfCell) {    if (null == hssfCell) {            return null;        }    if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {           // 返回布尔类型的值           return String.valueOf(hssfCell.getBooleanCellValue());       } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {           // 返回数值类型的值           return String.valueOf(hssfCell.getNumericCellValue());       } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_FORMULA) {           //处理经excel公式算出的值 // 返回经公式计算后的

CellValue tempCellValue = evaluator.evaluate(hssfCell);double iCellValue = tempCellValue.getNumberValue(); return String.valueOf(iCellValue); }else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_ERROR) { return null;} else {// 返回字符串类型的值 return String.valueOf(hssfCell.getStringCellValue()); }}}

0 0