Re: Formula in O10 does not work. Does in pure excel

[ac...@apache.org]

Yeah, we need to make a final push to flush out our formula
parser/generator.  There isn't much work left to be done.  Any volunteers?
-- 
Andrew C. Oliver
http://www.superlinksoftware.com/poi.jsp
Custom enhancements and Commercial Implementation for Jakarta POI

http://jakarta.apache.org/poi
For Java and Excel, Got POI?

The views expressed in this email are those of the author and are almost
definitely not shared by the Apache Software Foundation, its board or its
general membership.  In fact they probably most definitively disagree with
everything espoused in the above email.

> From: "Leedom, Erik C" <er...@lehman.com>
> Reply-To: "POI Users List" <po...@jakarta.apache.org>
> Date: Thu, 10 Jun 2004 10:24:46 -0400
> To: "'poi-user@jakarta.apache.org'" <po...@jakarta.apache.org>
> Subject: Formula in O10 does not work. Does in pure excel
> 
> 
> The formula is generated using the following method: ex
> "=IF(C10="B",((-1)*F10*G10-I10),(IF(C10="BCOV",((-1)*F10*G10-I10),((1)*F10*G
> 10-I10))))"
> 
> private String genNetMoneyFormula(int i) {
>       String formula = "IF(C" + i + "=\"B\", ((-1)*F"+i+"*G"+i+"-I"+i+"),
> " +
>           "(IF(C" + i + "=\"BCOV\",
> ((-1)*F"+i+"*G"+i+"-I"+i+"),((1)*F"+i+"*G"+i+"-I"+i+"))))";
>       return formula;
>   }
> 
> If I step through the calculation using the excel formula evaluator the
> first if block evaluates correctly.  Then when the nested inner if block
> attempts to access the same source cell as the first if statement ( the C
> cell in the formula) it evaluates to at #value! error.
> 
> The poi code: 
> 
> public void setFormula(String formula, int rowNum, int colNum) {
>       HSSFCell cell = getNonNullCell(rowNum, colNum);
>       cell.setCellFormula(formula);
>  }
> 
> protected HSSFCell getNonNullCell(int rowNum, int colNum) {
>       HSSFRow row = sheet.getRow(rowNum);
>       if (row == null) {
>           row = sheet.createRow(rowNum);
>       }
>       HSSFCell cell = row.getCell((short)colNum);
>       if (cell == null) {
>           cell = row.createCell((short)colNum);
>       }
>       return cell;
>   }
> 
> 
> The result:
> <<RAPTEST.xls>> 
> 
> 
> Please let me know if I should submit this as a bug to the developers.
> Thanks
> 
> Erik
> 
> 
> ------------------------------------------------------------------------------
> This message is intended only for the personal and confidential use of the
> designated recipient(s) named above.  If you are not the intended recipient of
> this message you are hereby notified that any review, dissemination,
> distribution or copying of this message is strictly prohibited.  This
> communication is for information purposes only and should not be regarded as
> an offer to sell or as a solicitation of an offer to buy any financial
> product, an official confirmation of any transaction, or as an official
> statement of Lehman Brothers.  Email transmission cannot be guaranteed to be
> secure or error-free.  Therefore, we do not represent that this information is
> complete or accurate and it should not be relied upon as such.  All
> information is subject to change without notice.
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-user-help@jakarta.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-dev-help@jakarta.apache.org