You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Jason Tomforde <jt...@live.com> on 2015/11/06 00:32:15 UTC
Formula not working as expected
Hi...
A1 string cell = 1
A2 numeric cell = 2
A3 numeric cell = 3
A4 formula cell = IF(A1=1,A2,A3)
The result I am getting is 0.
Any thoughts?
Jason.
RE: Formula not working as expected
Posted by Jason Tomforde <jt...@live.com>.
I downloaded the latest version and that did not fix the issue.
Here's a code snippet...
XSSFWorkbook wbook = new XSSFWorkbook();XSSFSheet sheet = wbook.createSheet("Formula Test");XSSFRow row = sheet.createRow(0);XSSFCell cell1 = row.createCell(0);XSSFCell cell2 = row.createCell(1);XSSFCell cell3 = row.createCell(2);XSSFCell cell4 = row.createCell(3);
cell1.setCellType(Cell.CELL_TYPE_STRING); cell2.setCellType(Cell.CELL_TYPE_NUMERIC); cell3.setCellType(Cell.CELL_TYPE_NUMERIC);cell4.setCellType(Cell.CELL_TYPE_FORMULA);cell1.setCellValue("1");cell2.setCellValue(2.0);cell3.setCellValue(3.0);cell4.setCellFormula("IF(A1=1, A2, A3)");
FormulaEvaluator eval = wbook.getCreationHelper().createFormulaEvaluator();
switch(eval.evaluateFormulaCell(cell4)) { case Cell.CELL_TYPE_NUMERIC: System.out.println(cell4.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.println(cell4.getStringCellValue()); break; case Cell.CELL_TYPE_ERROR: System.out.println(cell4.getErrorCellString()); break; }
Jason.
> Date: Thu, 5 Nov 2015 15:46:02 -0800
> Subject: RE: Formula not working as expected
> From: javenoneal@gmail.com
> To: user@poi.apache.org
>
> Keep this in mind:
> From http://poi.apache.org/spreadsheet/eval.html:
> "*Using** FormulaEvaluator.evaluate**(Cell cell)*
>
> This evaluates a given cell, and returns the new value, without affecting
> the cell"
> On 5 Nov 2015 3:41 p.m., "Jason Tomforde" <jt...@live.com> wrote:
>
> > If you are referring to this ...
> > CellValue cellValue = evaluator.evaluate(cell);
> >
> > Then yes.
> >
> > Most likely not the latest version.... I will give that a shot.
> >
> > Thank you,
> > Jason.
> >
> > > Date: Thu, 5 Nov 2015 23:36:20 +0000
> > > From: apache@gagravarr.org
> > > To: user@poi.apache.org
> > > Subject: Re: Formula not working as expected
> > >
> > > On Thu, 5 Nov 2015, Jason Tomforde wrote:
> > > > A1 string cell = 1
> > > > A2 numeric cell = 2
> > > > A3 numeric cell = 3
> > > > A4 formula cell = IF(A1=1,A2,A3)
> > > >
> > > > The result I am getting is 0.
> > > >
> > > > Any thoughts?
> > >
> > > Are you evaluating the formula when you're doing with settings cells?
> > > http://poi.apache.org/spreadsheet/eval.html
> > >
> > > Are you using a new enough version of Apache POI?
> > > http://poi.apache.org/download.html
> > >
> > > Nick
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> > > For additional commands, e-mail: user-help@poi.apache.org
> > >
> >
RE: Formula not working as expected
Posted by Javen O'Neal <ja...@gmail.com>.
Keep this in mind:
>From http://poi.apache.org/spreadsheet/eval.html:
"*Using** FormulaEvaluator.evaluate**(Cell cell)*
This evaluates a given cell, and returns the new value, without affecting
the cell"
On 5 Nov 2015 3:41 p.m., "Jason Tomforde" <jt...@live.com> wrote:
> If you are referring to this ...
> CellValue cellValue = evaluator.evaluate(cell);
>
> Then yes.
>
> Most likely not the latest version.... I will give that a shot.
>
> Thank you,
> Jason.
>
> > Date: Thu, 5 Nov 2015 23:36:20 +0000
> > From: apache@gagravarr.org
> > To: user@poi.apache.org
> > Subject: Re: Formula not working as expected
> >
> > On Thu, 5 Nov 2015, Jason Tomforde wrote:
> > > A1 string cell = 1
> > > A2 numeric cell = 2
> > > A3 numeric cell = 3
> > > A4 formula cell = IF(A1=1,A2,A3)
> > >
> > > The result I am getting is 0.
> > >
> > > Any thoughts?
> >
> > Are you evaluating the formula when you're doing with settings cells?
> > http://poi.apache.org/spreadsheet/eval.html
> >
> > Are you using a new enough version of Apache POI?
> > http://poi.apache.org/download.html
> >
> > Nick
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> > For additional commands, e-mail: user-help@poi.apache.org
> >
>
RE: Formula not working as expected
Posted by Jason Tomforde <jt...@live.com>.
If you are referring to this ...
CellValue cellValue = evaluator.evaluate(cell);
Then yes.
Most likely not the latest version.... I will give that a shot.
Thank you,
Jason.
> Date: Thu, 5 Nov 2015 23:36:20 +0000
> From: apache@gagravarr.org
> To: user@poi.apache.org
> Subject: Re: Formula not working as expected
>
> On Thu, 5 Nov 2015, Jason Tomforde wrote:
> > A1 string cell = 1
> > A2 numeric cell = 2
> > A3 numeric cell = 3
> > A4 formula cell = IF(A1=1,A2,A3)
> >
> > The result I am getting is 0.
> >
> > Any thoughts?
>
> Are you evaluating the formula when you're doing with settings cells?
> http://poi.apache.org/spreadsheet/eval.html
>
> Are you using a new enough version of Apache POI?
> http://poi.apache.org/download.html
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
Re: Formula not working as expected
Posted by Nick Burch <ap...@gagravarr.org>.
On Thu, 5 Nov 2015, Jason Tomforde wrote:
> A1 string cell = 1
> A2 numeric cell = 2
> A3 numeric cell = 3
> A4 formula cell = IF(A1=1,A2,A3)
>
> The result I am getting is 0.
>
> Any thoughts?
Are you evaluating the formula when you're doing with settings cells?
http://poi.apache.org/spreadsheet/eval.html
Are you using a new enough version of Apache POI?
http://poi.apache.org/download.html
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Formula not working as expected
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Morning Jason,
Double check the cell type please as I have a thought that could very well
be wrong but.... You said that you set it to text and then enter the numeric
value 1. It is likely that Excel automatically changes the data type of that
cell to numeric when you input the value. It might be better to double check
the formula by entering the number 1 and then setting the cells type so that
Excel 'knows' you are entering a number that should be treated as a string.
It is also likely that doing this will cause Excel to flag an error on the
cell as it looks on numbers stored as strings to be a possible error.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Formula-not-working-as-expected-tp5720885p5720941.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Formula not working as expected
Posted by Jason Tomforde <jt...@live.com>.
Hi,
In Excel, I set A1 to be a text cell. I then set the value to 1.
If I try IF(A1="1", B1, C1) in Excel, it always gives C1, even if A1 is the value 1. IF(A1=1, B1, C1) does the right thing. I was hoping that I could always copy the formula from Excel and use it as is as the input for the POI formula.
But If I use the one that works in Excel, it does not do the right thing in POI.
Jason.
> From: javenoneal@gmail.com
> Date: Thu, 5 Nov 2015 23:44:48 -0800
> Subject: Re: Formula not working as expected
> To: user@poi.apache.org
>
> I did a little more looking into this behavior you observed.
>
> In LibreOffice, "A1=1" evaluates to false when A1 is the text "1". You need
> to use "A1=\"1\"" if A1 is text. You can implicitly coerce A1 to a number
> when evaluating the formula by doing "A1+0=1" or "A1*1=0".
>
> See bug 58591 [1] for example code and more information.
> [1] https://bz.apache.org/bugzilla/show_bug.cgi?id=58591
>
> On Thu, Nov 5, 2015 at 8:20 PM, Javen O'Neal <ja...@gmail.com> wrote:
>
> > (Using POI 3.13 final)
> >
> > Here's your problem:
> >
> > XSSFCell cell1 = row.createCell(0); //A1
> > XSSFCell cell2 = row.createCell(1); //B1
> > XSSFCell cell3 = row.createCell(2); //C1
> > XSSFCell cell4 = row.createCell(3); //D1
> >
> > cell4.setCellFormula("IF(A1=1, A2, A3");
> >
> > A2 and A3 are 0!
> >
> > Also, just a note, you don't need to set the cell type before setting the
> > value. The cell type gets updated when you set the cell value.
> >
> > Changing your formula to "IF(A1=1, B1, C1)", I get 3.0--which is better,
> > but still not correct. I still get 3.0 even if I change cell1 to 1.0
> > (numeric) and/or change the formula to IF(A1=1.0, B1, C1)
> >
> > Here's my code (Jython)
> > from org.apache.poi.xssf.usermodel import XSSFWorkbook
> > wb = XSSFWorkbook()
> > row = wb.createSheet().createRow(0)
> > A1, B1, C1, D1 = [row.createCell(c) for c in range(4)]
> > A1.setCellValue("1")
> > B1.setCellValue(2.0)
> > C1.setCellValue(3.0)
> > D1.setCellFormula("IF(A1=1,B1,C1)")
> > evaluator = wb.getCreationHelper().createFormulaEvaluator()
> > evaluator.evaluateFormulaCell(D1); D1.getNumericCellValue() # returns 3.0
> > evaluator.evaluate(D1) returns org.apache.poi.ss.usermodel.CellValue [3.0]
> > e.evaluateAllFormulaCells(wb); D1.getNumericCellValue() # returns 2.0
> >
> > I haven't used formula evaluation before, so I don't know if this is my
> > inexperience or a bug in POI.
> >
> >
RE: Formula not working as expected
Posted by Jason Tomforde <jt...@live.com>.
Thank You for digging into this! The evaluateAllFormulaCells() call is the way to go for me. It is working asexpected now.
Jason.
> From: javenoneal@gmail.com
> Date: Thu, 5 Nov 2015 23:44:48 -0800
> Subject: Re: Formula not working as expected
> To: user@poi.apache.org
>
> I did a little more looking into this behavior you observed.
>
> In LibreOffice, "A1=1" evaluates to false when A1 is the text "1". You need
> to use "A1=\"1\"" if A1 is text. You can implicitly coerce A1 to a number
> when evaluating the formula by doing "A1+0=1" or "A1*1=0".
>
> See bug 58591 [1] for example code and more information.
> [1] https://bz.apache.org/bugzilla/show_bug.cgi?id=58591
>
> On Thu, Nov 5, 2015 at 8:20 PM, Javen O'Neal <ja...@gmail.com> wrote:
>
> > (Using POI 3.13 final)
> >
> > Here's your problem:
> >
> > XSSFCell cell1 = row.createCell(0); //A1
> > XSSFCell cell2 = row.createCell(1); //B1
> > XSSFCell cell3 = row.createCell(2); //C1
> > XSSFCell cell4 = row.createCell(3); //D1
> >
> > cell4.setCellFormula("IF(A1=1, A2, A3");
> >
> > A2 and A3 are 0!
> >
> > Also, just a note, you don't need to set the cell type before setting the
> > value. The cell type gets updated when you set the cell value.
> >
> > Changing your formula to "IF(A1=1, B1, C1)", I get 3.0--which is better,
> > but still not correct. I still get 3.0 even if I change cell1 to 1.0
> > (numeric) and/or change the formula to IF(A1=1.0, B1, C1)
> >
> > Here's my code (Jython)
> > from org.apache.poi.xssf.usermodel import XSSFWorkbook
> > wb = XSSFWorkbook()
> > row = wb.createSheet().createRow(0)
> > A1, B1, C1, D1 = [row.createCell(c) for c in range(4)]
> > A1.setCellValue("1")
> > B1.setCellValue(2.0)
> > C1.setCellValue(3.0)
> > D1.setCellFormula("IF(A1=1,B1,C1)")
> > evaluator = wb.getCreationHelper().createFormulaEvaluator()
> > evaluator.evaluateFormulaCell(D1); D1.getNumericCellValue() # returns 3.0
> > evaluator.evaluate(D1) returns org.apache.poi.ss.usermodel.CellValue [3.0]
> > e.evaluateAllFormulaCells(wb); D1.getNumericCellValue() # returns 2.0
> >
> > I haven't used formula evaluation before, so I don't know if this is my
> > inexperience or a bug in POI.
> >
> >
Re: Formula not working as expected
Posted by Javen O'Neal <ja...@gmail.com>.
I did a little more looking into this behavior you observed.
In LibreOffice, "A1=1" evaluates to false when A1 is the text "1". You need
to use "A1=\"1\"" if A1 is text. You can implicitly coerce A1 to a number
when evaluating the formula by doing "A1+0=1" or "A1*1=0".
See bug 58591 [1] for example code and more information.
[1] https://bz.apache.org/bugzilla/show_bug.cgi?id=58591
On Thu, Nov 5, 2015 at 8:20 PM, Javen O'Neal <ja...@gmail.com> wrote:
> (Using POI 3.13 final)
>
> Here's your problem:
>
> XSSFCell cell1 = row.createCell(0); //A1
> XSSFCell cell2 = row.createCell(1); //B1
> XSSFCell cell3 = row.createCell(2); //C1
> XSSFCell cell4 = row.createCell(3); //D1
>
> cell4.setCellFormula("IF(A1=1, A2, A3");
>
> A2 and A3 are 0!
>
> Also, just a note, you don't need to set the cell type before setting the
> value. The cell type gets updated when you set the cell value.
>
> Changing your formula to "IF(A1=1, B1, C1)", I get 3.0--which is better,
> but still not correct. I still get 3.0 even if I change cell1 to 1.0
> (numeric) and/or change the formula to IF(A1=1.0, B1, C1)
>
> Here's my code (Jython)
> from org.apache.poi.xssf.usermodel import XSSFWorkbook
> wb = XSSFWorkbook()
> row = wb.createSheet().createRow(0)
> A1, B1, C1, D1 = [row.createCell(c) for c in range(4)]
> A1.setCellValue("1")
> B1.setCellValue(2.0)
> C1.setCellValue(3.0)
> D1.setCellFormula("IF(A1=1,B1,C1)")
> evaluator = wb.getCreationHelper().createFormulaEvaluator()
> evaluator.evaluateFormulaCell(D1); D1.getNumericCellValue() # returns 3.0
> evaluator.evaluate(D1) returns org.apache.poi.ss.usermodel.CellValue [3.0]
> e.evaluateAllFormulaCells(wb); D1.getNumericCellValue() # returns 2.0
>
> I haven't used formula evaluation before, so I don't know if this is my
> inexperience or a bug in POI.
>
>
RE: Formula not working as expected
Posted by Javen O'Neal <ja...@gmail.com>.
(Using POI 3.13 final)
Here's your problem:
XSSFCell cell1 = row.createCell(0); //A1
XSSFCell cell2 = row.createCell(1); //B1
XSSFCell cell3 = row.createCell(2); //C1
XSSFCell cell4 = row.createCell(3); //D1
cell4.setCellFormula("IF(A1=1, A2, A3");
A2 and A3 are 0!
Also, just a note, you don't need to set the cell type before setting the
value. The cell type gets updated when you set the cell value.
Changing your formula to "IF(A1=1, B1, C1)", I get 3.0--which is better,
but still not correct. I still get 3.0 even if I change cell1 to 1.0
(numeric) and/or change the formula to IF(A1=1.0, B1, C1)
Here's my code (Jython)
from org.apache.poi.xssf.usermodel import XSSFWorkbook
wb = XSSFWorkbook()
row = wb.createSheet().createRow(0)
A1, B1, C1, D1 = [row.createCell(c) for c in range(4)]
A1.setCellValue("1")
B1.setCellValue(2.0)
C1.setCellValue(3.0)
D1.setCellFormula("IF(A1=1,B1,C1)")
evaluator = wb.getCreationHelper().createFormulaEvaluator()
evaluator.evaluateFormulaCell(D1); D1.getNumericCellValue() # returns 3.0
evaluator.evaluate(D1) returns org.apache.poi.ss.usermodel.CellValue [3.0]
e.evaluateAllFormulaCells(wb); D1.getNumericCellValue() # returns 2.0
I haven't used formula evaluation before, so I don't know if this is my
inexperience or a bug in POI.
RE: Formula not working as expected
Posted by Jason Tomforde <jt...@live.com>.
I did try that, thanks, though.
> Subject: Re: Formula not working as expected
> From: sdurette@gmail.com
> Date: Thu, 5 Nov 2015 19:19:20 -0500
> To: user@poi.apache.org
>
> Just a thought but shouldn't a4 be:
> IF(A1="1", A2, A3)
>
> After all it does say that a1 is a string/text.
>
> Sent from my iPhone
>
> > On Nov 5, 2015, at 6:32 PM, Jason Tomforde <jt...@live.com> wrote:
> >
> > Hi...
> >
> > A1 string cell = 1
> > A2 numeric cell = 2
> > A3 numeric cell = 3
> > A4 formula cell = IF(A1=1,A2,A3)
> >
> > The result I am getting is 0.
> >
> > Any thoughts?
> >
> > Jason.
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
Re: Formula not working as expected
Posted by Steven Durette <sd...@gmail.com>.
Just a thought but shouldn't a4 be:
IF(A1="1", A2, A3)
After all it does say that a1 is a string/text.
Sent from my iPhone
> On Nov 5, 2015, at 6:32 PM, Jason Tomforde <jt...@live.com> wrote:
>
> Hi...
>
> A1 string cell = 1
> A2 numeric cell = 2
> A3 numeric cell = 3
> A4 formula cell = IF(A1=1,A2,A3)
>
> The result I am getting is 0.
>
> Any thoughts?
>
> Jason.
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org