You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Rene Becker <re...@siteos.de> on 2011/11/08 15:28:19 UTC
Problem evaluation date arithmetic within formula cell
Hi,
given the attached use case, I perform a simple substraction of one day
to a date referenced from a textual split on another sheet:
=RIGHT(VNA115462!A2;10)-1
This formula is evaluated to #VALUE!, no so in Excel. If I remove the
substraction, everything is working.
Is date arithmetic currently supported with POI (tested with 3.6 and
3.8-beta4)?
Formula is evaluated like this:
if(cell.getCellType() == Cell.CELL_TYPE_FORMULA)
{
final FormulaEvaluator evaluator =
wb.getCreationHelper().createFormulaEvaluator();
cell = (HSSFCell) evaluator.evaluateInCell(cell);
// --> cell is evaluated to #VALUE!
}
/* ... rest of code, string conversion to java.util.Date */
Thanks for clarification
Kind regards
Rene Becker
Re: Problem evaluation date arithmetic within formula cell
Posted by Nick Burch <ni...@alfresco.com>.
On Wed, 9 Nov 2011, Rene Becker wrote:
> The problem is that the date arithmetic is performed on textual content.
> Performing date arithmetic on numeric cells works, however in the given
> testcase the date arithmetic is performed on a date that is retrieved
> from a substring(i.e. right()) of a textual cell. Unfortunately
> DATEVALUE() is also not available for converting the text cell to a date
> cell.
I'm not sure I follow you. Any chance you could create a very simple
spreadsheet with a very simple problem formula in it, then write a unit
test that shows the evaluation issue? If so, please upload it as a new
issue in bugzilla, then people can hopefully easily see what's going wrong
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Problem evaluation date arithmetic within formula cell
Posted by Rene Becker <re...@siteos.de>.
Dear Nick,
thanks for your response.
The formula is not set within POI, it is an Excel-file that is being
imported, the formula was copied from the Excel fx-line.
The problem is that the date arithmetic is performed on textual content.
Performing date arithmetic on numeric cells works, however in the given
testcase the date arithmetic is performed on a date that is retrieved
from a substring(i.e. right()) of a textual cell. Unfortunately
DATEVALUE() is also not available for converting the text cell to a date
cell.
--
Mit freundlichen Grüßen
Rene Becker
SiteOS AG
Grillparzerstraße 10
81675 München
Tel. +49 (0)89 309 089 7-12
Fax +49 (0)89 309 089 7-99
mailto:rene.becker@siteos.de
Vorstand : Andreas Germeroth, Thomas Reischl
Vorsitzender des Aufsichtsrats: Hajo Fritz
Registergericht München | Registernummer: HR B 130513
Am 08.11.2011 23:41, schrieb Nick Burch:
> On Tue, 8 Nov 2011, Rene Becker wrote:
>> given the attached use case, I perform a simple substraction of one
>> day to a date referenced from a textual split on another sheet:
>> =RIGHT(VNA115462!A2;10)-1
>
> When setting the formula in POI, you mustn't include the leading = sign
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Problem evaluation date arithmetic within formula cell
Posted by Nick Burch <ni...@alfresco.com>.
On Tue, 8 Nov 2011, Rene Becker wrote:
> given the attached use case, I perform a simple substraction of one day to a
> date referenced from a textual split on another sheet:
> =RIGHT(VNA115462!A2;10)-1
When setting the formula in POI, you mustn't include the leading = sign
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org