java poi 写入Excel后读取公式值问题-poi读取excel数据,poi读取大数据量excel

if(cell.getCachedFormulaResultType() == 0){res = String.valueOf(cell.getNumericCellValue());};
if(cell.getCachedFormulaResultType() == 1){res = cell.getStringCellValue();}
不用evaluator

你好，我之zd前一直在研究poi和jxl，应该可以帮助你

“但是读取出来的值是0.0”，你说的这句话，是不是说，你用poi写了excel后，再读取excel时发现那个值是0.0？

“直接打专开excel文件后公式才能正确计算”，这句话是不是说，直接打开excel文件后，双击那个你设置公式了但取值为0.0的单元格后，它又能属用公式计算出正确的结果？
如果单元格所存的内容为函数，则通过poi得到的cell type为Cell.CELL_TYPE_FORMULA;解析时7a686964616fe4b893e5b19e362的函数：
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
System.out.println(cellValue.getBooleanValue());
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.println(cellValue.getNumberValue());
break;
case Cell.CELL_TYPE_STRING:
System.out.println(cellValue.getStringValue());
break;
case Cell.CELL_TYPE_BLANK:
break;
case Cell.CELL_TYPE_ERROR:
break;

case Cell.CELL_TYPE_FORMULA:
CellValue cellValue = evaluator.evaluate(cell);
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
System.out.println(cellValue.getBooleanValue());
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.println(cellValue.getNumberValue());
break;
case Cell.CELL_TYPE_STRING:
System.out.println(cellValue.getStringValue());
break;
case Cell.CELL_TYPE_BLANK:
break;
case Cell.CELL_TYPE_ERROR:
break;

// CELL_TYPE_FORMULA will never happen
case Cell.CELL_TYPE_FORMULA:
break;
}

}
就是说要进行多一层的判断，poi会通过其内部实现的函数去解析excel的函数；所以这里可以解析excel函数，而如果是你自己用VB定义的函数，就需要另外处理了。

1 package hrds.zpf.poi;
2
3 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
4 import org.apache.poi.hssf.usermodel.HSSFSheet;
5 import org.apache.poi.hssf.usermodel.HSSFRow;
6 import org.apache.poi.hssf.usermodel.HSSFCell;
7
8 import java.io.*;
9
10 public class FormulaToString {
11
12 /**
13 * @636f70797a686964616f336param args
14 */
15 public void fileInput() throws IOException {
16
17 HSSFWorkbook hw = new HSSFWorkbook(new FileInputStream(
18 "d:/My Documents/Desktop/poi.xls"));
19 HSSFSheet hsheet = hw.getSheet("poi test");
20 HSSFRow hrow = hsheet.getRow(0);
21 HSSFCell hcell = hrow.getCell(0);
22 String cellValue = this.getCellValue(hcell);
23 System.out.println(cellValue);
24
25 }
26
27 public String getCellValue(HSSFCell cell) {
28 String value = null;
29 if (cell != null) {
30 switch (cell.getCellType()) {
31 case HSSFCell.CELL_TYPE_FORMULA:
32 // cell.getCellFormula();
33 try {
34 value = String.valueOf(cell.getNumericCellValue());
35 } catch (IllegalStateException e) {
36 value = String.valueOf(cell.getRichStringCellValue());
37 }
38 break;
39 case HSSFCell.CELL_TYPE_NUMERIC:
40 value = String.valueOf(cell.getNumericCellValue());
41 break;
42 case HSSFCell.CELL_TYPE_STRING:
43 value = String.valueOf(cell.getRichStringCellValue());
44 break;
45 }
46 }
47
48 return value;
49 }
50
51 public static void main(String[] args) {
52 try {
53 // TODO Auto-generated method stub
54 FormulaToString fts = new FormulaToString();
55 fts.fileInput();
56 } catch (IOException e) {
57 e.printStackTrace();
58 }
59 }
60
61 }