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 !!!!

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

(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 ...

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 ;)

I just put an attachement in order to show my problem ! regards

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.

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