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