Bug 48043

Summary: Excel complains about a POI generated function
Product: POI Reporter: alamothe
Component: HSSFAssignee: POI Developers List <dev>
Status: NEEDINFO ---    
Severity: normal CC: alamothe, ivano.diana, onealj
Priority: P1    
Version: 3.7-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   

Description alamothe 2009-10-23 07:07:04 UTC
When I insert the following function in a cell:

=IF(AND(ISBLANK(A10),ISBLANK(B10)),"",CONCATENATE(A10,"-",B10))

using:

cell.setCellFormula(
		"IF(AND(ISBLANK(A10)," +
		"ISBLANK(B10)),\"\"," + 
		"CONCATENATE(A10,\"-\",B10))");

Excel complains about it (#VALUE!) when the workbook is first opened. However, it is enough just to press F2, Enter on a cell for the function to start evaluating properly. Interestingly, this problem does not appear in OpenOffice.
Comment 1 suat gonul 2009-11-05 08:09:59 UTC
I have the same problem and the code causing this problem looks like the following one:

String formula = "IF(F2<>\"\",CONCATENATE(F2,\" \",G2),G2)";
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula(formula);

In detail, when both F2 and G2 cells are filled with some text, the problem occurs but when F2 contains empty string, the problem does not occur.

I add a text into F2 cell with a code like in the following:

String str = "foo";
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(str);

PS: all cell variables have the type of HSSFCell.
PS2: I open the file I created; enter some text into F2 manually; press enter; and the formula cell again has #VALUE!. After clicking the formula cell, it shows the correct value.
Comment 2 ivano.diana 2011-04-05 12:17:32 UTC
I encountered some problems with HSSF component when using "CONCATENATE" function inside an excel formula. In particular, it seems this function doesn't work when inside other function or instructions-for example inside an IF. I post some code examples to better explain the problem:

cellFormula.setFormula("IF(true, CONCATENATE(B2,B3), \"\")");

cellFormula.setFormula("CONCATENATE(IF(C2=\"X\",CONCATENATE(B2,\" \"),\"\"),IF(C3=\"X\",CONCATENATE(B3,\" \"),\"\"),IF(C4=\"X\",CONCATENATE(B4,\" \"),\"\"))");

The issue is the classic "#VALUE!" that appears when trying to evaluate the formula. 

I was wondering if as happened with "Bug 50384" you are providing to fix it in Dev versions.
Comment 3 Nick Burch 2011-04-08 11:12:34 UTC
(In reply to comment #2)
> cellFormula.setFormula("IF(true, CONCATENATE(B2,B3), \"\")");

To confirm, if you set this formula and then run the formula evaluator, it'll show as #VALUE! in excel? And if so, what does POI evaluate the cell too, is that the correct string or not?
Comment 4 ivano.diana 2011-04-11 03:06:23 UTC
(In reply to comment #3)
> (In reply to comment #2)
> > cellFormula.setFormula("IF(true, CONCATENATE(B2,B3), \"\")");
> 
> To confirm, if you set this formula and then run the formula evaluator, it'll
> show as #VALUE! in excel? And if so, what does POI evaluate the cell too, is
> that the correct string or not?

Of course, even if I run the formula evaluator what i obtain in excel is "#VALUE!". However if i try to read the cell with POI what i retrieve is the expected value.
if we're going wrong, any suggestion is welcome.
Comment 5 Javen O'Neal 2015-10-07 09:55:35 UTC
Make sure you're using some evaluation that will cache the formula result in the cell, such as FormulaEvaluator.evaluateInCell(Cell). FormulaEvaluator.evaluate(Cell) returns the formula result but leaves the Cell unmodified. Perhaps this is why you were still getting #VALUE! errors when you opened the workbook in Excel.

Could you post the code you used to evaluate the formula after setting the formula but before saving the workbook?

Alternatively, you might be able to try Workbook. 	setForceFormulaRecalculation(true) before saving the workbook, which tells Excel to recalculate all the formulas next time it opens the workbook.