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
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
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之前都应该防止转换失败,因为第一个将丢弃缓存的内容。