POI: setCellType(Cell.CELL_TYPE_FORMULA)因为Cell.CELL_TYPE_ERROR而失败。

My Java application reads an xls file and presents it on a JTable. So far so good.

我的Java应用程序读取一个xls文件并将其呈现在JTable中。目前为止一切都很顺利。

When I try to save my worksheet, I iterate over row,col in my JTable and:

当我试图保存我的工作表时，我在JTable中遍历行，并:

String str = (String) Table.getValueAt(row, col);

HSSFRow thisrow = sheet.getRow(row);
HSSFCell thiscell = thisrow.getCell(col);
if(thiscell==null) thiscell = thisrow.createCell(col);

switch(inferType(str)) {
    case "formula":
        thiscell.setCellType(Cell.CELL_TYPE_FORMULA);
        thiscell.setCellFormula(str.substring(1));
        break;
    case "numeric":
        thiscell.setCellType(Cell.CELL_TYPE_NUMERIC);
        thiscell.setCellValue(Double.parseDouble(str));
        break;
    case "text":
        thiscell.setCellType(Cell.CELL_TYPE_STRING);
        thiscell.setCellValue(str);
        break;
    }

But when I run over a cell which was originally a formula, say A1/B1, that is #DIV/0! at the moment, setCellType fails.

但是当我在一个原来是公式的单元上运行时，A1/B1，就是#DIV/0!现在，setCellType失败了。

With much investigation I found out that when setCellType is called, it tries to convert the old content to the new type. BUT, this didn't seem a problem to me, since every table formula cell was already a formula in the xls. Hence, I am never actually changing types.

通过大量的调查，我发现当调用setCellType时，它尝试将旧内容转换为新类型。但是，这对我来说并不是问题，因为每个表格式的单元格已经是xls中的一个公式。因此，我从来没有真正改变过类型。

Even so, when I call setCellType(Cell.CELL_TYPE_FORMULA) on a cell that is already a formula, but it is evaluated to #DIV/0!, I get an conversion exception.

即使这样，当我在单元格上调用setCellType(cell . cell_type_formula)时，它已经是一个公式了，但是它被计算为#DIV/0!，我得到一个转换异常。

Exception in thread "AWT-EventQueue-0" java.lang.IllegalStateException: Cannot get a numeric value from a error formula cell
    at org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:648)
    at org.apache.poi.hssf.usermodel.HSSFCell.checkFormulaCachedValueType(HSSFCell.java:653)
    at org.apache.poi.hssf.usermodel.HSSFCell.getNumericCellValue(HSSFCell.java:678)
    at org.apache.poi.hssf.usermodel.HSSFCell.setCellType(HSSFCell.java:317)
    at org.apache.poi.hssf.usermodel.HSSFCell.setCellType(HSSFCell.java:283)

Actually my only workaround is, before setCellType:

实际上我唯一的工作是，在setCellType之前:

if(thiscell.getCachedFormulaResultType()==Cell.CELL_TYPE_ERROR)
    thiscell = thisrow.createCell(col);

This IS working, but I lose the original layout of the cell, e.g. it's colors.

这是有效的，但是我失去了单元格的原始布局，比如颜色。

How can I properly setCellType if the Cell is a formula with evaluation error?

如果单元格是一个带有评估错误的公式，我该如何正确设置它?

1 个解决方案

#1

I found this in the mailing list of poi-apache:

我在poi-apache的邮件列表中发现了这一点:

There are two possible scenarios when setting value for a formula cell;

在为公式计算单元设置值时，有两种可能的场景;

Update the pre-calculated value of the formula. If a cell contains formula then cell.setCellValue just updates the pre-calculated (cached) formula value, the formula itself remains and the cell type is not changed

更新公式的预计算值。如果一个单元格包含公式，那么单元格。setCellValue只是更新预先计算的(缓存的)公式值，公式本身保持不变，而单元格类型不变。

Remove the formula and change the cell type to String or Number:

删除公式并将单元格类型改为字符串或数字:

cell.setCellFormula(null); //Remove the formula

cell.setCellFormula(空);/ /删除公式

then cell.setCellValue("I changed! My type is CELL_TYPE_STRING now""); or cell.setCellValue(200); //NA() is gone, the real value is 200

然后细胞。setCellValue(“我改变了!我的类型是CELL_TYPE_STRING now“”;或cell.setCellValue(200);//NA()消失了，实际值是200。

I think we can improve cell.setCellValue for the case (1). If the new value conflicts with formula type then IllegalArgumentException should be thrown.

我认为我们可以改善细胞。如果新值与公式类型冲突，则应抛出IllegalArgumentException。

Regards, Yegor

问候,俄罗斯人

Still, it does feel like a workaround to me. But everything is now working.

尽管如此，它还是让我觉得自己是一个变通的人。但现在一切都在运转。

cell.setCellFormula(null) before any setCellType should prevent conversion failure, because the first will discard the cached content.

在任何setCellType之前都应该防止转换失败，因为第一个将丢弃缓存的内容。

#1