Bug 38358 - Unable to parse formula for function from analysis toolpack
Summary: Unable to parse formula for function from analysis toolpack
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-dev
Hardware: All All
: P2 critical (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2006-01-23 21:29 UTC by PiTiLeZarD
Modified: 2010-06-03 07:26 UTC (History)
1 user (show)



Attachments
A piece of process that produce an error (4.84 KB, application/x-jar)
2006-06-07 07:05 UTC, PiTiLeZarD
Details

Note You need to log in before you can comment on or make changes to this bug.
Description PiTiLeZarD 2006-01-23 21:29:30 UTC
I have to do a report with some value issued from an AS400. Anyway, when I put
all my values and finally my formula, all other formula works perfectly but
YIELD(TODAY()+3;V5;W5;... and so on, dosn't work...

The formula is parsed without any errors, and as a result, we ca see in the Cell :

TODAY()+3;otherparams;)XYIELD

where X is a strange caracter (sometimes a chinese sign, or a rectangle ...)

I have to explain to my client that he must click on the Cell and click Enter
and after that, copy the formula right down ... I've putted my formula with a
'=' and in string type ...

I've search a lot but there is apparently any other possibility to put a formula
and to bypass the parsing done by HSSF ...

So if its possible when you know that your way is not completely finished,
provide another possibilty or a workaround ... If there is one, I'm really
interested !!!!
Comment 1 Jason Height 2006-01-24 03:36:53 UTC
Can you provide this example of the problem as a small java application and
attach it to this bug. Dont forget to include any excel files that this
application uses to exhbit the problem.

Thanks

Jason
Comment 2 PiTiLeZarD 2006-02-02 14:03:55 UTC
(In reply to comment #0)
> I have to do a report with some value issued from an AS400. Anyway, when I put
> all my values and finally my formula, all other formula works perfectly but
> YIELD(TODAY()+3;V5;W5;... and so on, dosn't work...
> 
> The formula is parsed without any errors, and as a result, we ca see in the Cell :
> 
> TODAY()+3;otherparams;)XYIELD
> 
> where X is a strange caracter (sometimes a chinese sign, or a rectangle ...)
> 
> I have to explain to my client that he must click on the Cell and click Enter
> and after that, copy the formula right down ... I've putted my formula with a
> '=' and in string type ...
> 
> I've search a lot but there is apparently any other possibility to put a formula
> and to bypass the parsing done by HSSF ...
> 
> So if its possible when you know that your way is not completely finished,
> provide another possibilty or a workaround ... If there is one, I'm really
> interested !!!!

Sorry for the so long gap between my answers.

Some functions usefull to understand my code :

	public HSSFCell getCell(HSSFSheet sheet,int row,int col,int type) {
		HSSFRow r = sheet.getRow(row);
		if (r == null) {
			r = sheet.createRow(row);
		}
		HSSFCell c = r.getCell((short)col);
		if (c == null) {
			c = r.createCell((short)col);
		}
		c.setCellType(type);
		return c;
	}
	public HSSFCell getCell(HSSFSheet sheet,int row, int col) {
		return getCell(sheet,row,col,HSSFCell.CELL_TYPE_STRING);
	}

That's what I have now :
getCell(sheet,gap,9).setCellValue("=YIELD(DATE("+year+";"+month+";"+day+") +
3;W"+(gap+1)+";E"+(gap+1)+"/100;G"+(gap+1)+";100;V"+(gap+1)+";1)*100");

with gap increasing and with some values everywhere ...

I'd like to have:
getCell(sheet,gap,9,HSSFCell.CELL_TYPE_FORMULA).setCellFormula("=YIELD(DATE("+year+";"+month+";"+day+")
+ 3;W"+(gap+1)+";E"+(gap+1)+"/100;G"+(gap+1)+";100;V"+(gap+1)+";1)*100");

Which is the same thing but without the need to type enter on each row of the
book ;)

I cannot specify more as it is confidential data but I think all is here !

Thank you :)

PS: If I have the time tonight I'll make a jar with an application running this
bug but you have to get some financial plugins in you'r Excel in order to read
it ... As YIELD is not a default function ...
Comment 3 PiTiLeZarD 2006-06-07 07:05:31 UTC
Created attachment 18416 [details]
A piece of process that produce an error

Howto:
* new folder
* put testPOI.jar and poi.jar (version 2.5.1-final-20040804)
* new empty excel file named test.xls
* java -classpath testPOI.jar:poi.jar com.tests.Main

file outputed is result.xls which should contain a formula with YIELD in the
first sheet column K.

Thank you in advance ;)
Comment 4 PiTiLeZarD 2006-06-07 07:06:46 UTC
I just put an attachement in order to show my problem !

regards
Comment 5 Josh Micich 2008-05-10 18:15:41 UTC
As you mentioned YIELD() is not a standard function (it comes from the analysis toolpack).  Such functions need to be encoded with FuncVarPtg(ix=255) and a NameXPtg pointing to an external sheet. As of version 3.1, the formula parser does do this. Hopefully that will change in a release soon after.
Comment 6 Nick Burch 2010-06-03 07:26:52 UTC
This bug references a very old version of POI. As no new comments have been added in a long time, I'm assuming that this bug has now been fixed

If the bug still exists with the latest version of POI, please re-open the bug and add a comment indicating this, ideally also with a failing unit test