You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2009/10/23 16:07:06 UTC
DO NOT REPLY [Bug 48043] New: Excel complains about a POI generated
function
https://issues.apache.org/bugzilla/show_bug.cgi?id=48043
Summary: Excel complains about a POI generated function
Product: POI
Version: 3.5-FINAL
Platform: PC
OS/Version: Windows XP
Status: NEW
Severity: normal
Priority: P2
Component: HSSF
AssignedTo: dev@poi.apache.org
ReportedBy: alamothe@gmail.com
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.
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 48043] Excel complains about a POI generated
function
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48043
alamothe@gmail.com changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |alamothe@gmail.com
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 48043] Excel complains about a POI generated
function
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48043
ivano.diana@ext.bhuman.it changed:
What |Removed |Added
----------------------------------------------------------------------------
Priority|P2 |P1
CC| |ivano.diana@ext.bhuman.it
Version|3.5-FINAL |3.7
--- Comment #2 from ivano.diana@ext.bhuman.it 2011-04-05 12:17:32 EDT ---
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.
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 48043] Excel complains about a POI generated
function
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48043
--- Comment #4 from ivano.diana@ext.bhuman.it 2011-04-11 03:06:23 EDT ---
(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.
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 48043] Excel complains about a POI generated
function
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48043
Nick Burch <ni...@alfresco.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #3 from Nick Burch <ni...@alfresco.com> 2011-04-08 11:12:34 EDT ---
(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?
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 48043] Excel complains about a POI generated
function
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48043
--- Comment #1 from suat gonul <su...@gmail.com> 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.
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org