You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Leedom, Erik C" <er...@lehman.com> on 2004/06/10 16:24:46 UTC
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.
Re: Formula in O10 does not work. Does in pure excel
Posted by 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-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
Re: Formula in O10 does not work. Does in pure excel
Posted by 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