Hello,
You are right! All the formulas and functions are within the Excel
spreadsheet. As I edit some input parameters for these formulas/functions
via Java, I need to re-calculate the formula/function cells.
Example:
Cell B173: =B6+(B135*$B$8*$B$7)+(B137*$B$9)+(B139*$B$10)-B138-B20 //Formula
works fine!
Cell B174: =IF((B172/($B$4*$B$3))>25, 25, (B172/($B$4*$B$3))) //Function
does not work
...for my understanding (Cell B174):
System.out.println(tmpCell.getCellFormula());
=> returns: IF((B172/($B$4*$B$3))>25, 25, (B172/($B$4*$B$3)))
System.out.println(tmpCell.getCellType());
=> returns 2
System.out.println(tmpCell.getNumericCellValue());
=> returns the right value of the cell (but this is the value before
the function is recalculated, isn't it?)
System.out.println(cellValue.getNumberValue());
=> returns 0.00
Thank you for all your effords in advance, Rainer
-----Ursprüngliche Nachricht-----
Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Gesendet: Montag, 20. November 2006 12:14
An: poi-user@jakarta.apache.org
Betreff: RE: IF-function
Hello,
As per my understanding you have if condition in the Excel - formula itself
instead of java code. If I am correct please send the formula listing of
one of the cells.
Thanks & Regards,
________________________________
Sunil Kumar Dhage
-----Original Message-----
From: rlaudien [mailto:[EMAIL PROTECTED]
Sent: Monday, November 20, 2006 2:32 PM
To: 'POI Users List'
Subject: AW: IF-function
Hello,
Thank you for the quick answer. I want to re-calculate temp2.xls which
contains formulas and if-functions within the cells B133 - AB224. Formulas
work fine, if-functions not!
Here is the sample code:
fileOut = new FileOutputStream("data/temp2.xls");
bedeutungGruppe = wb.getSheet(BILclim);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(bedeutungGruppe,
wb);
HSSFRow tmpRow;
HSSFRow row;
HSSFCell tmpCell;
HSSFCell cell;
for (int i = 132; i <= 223; i++) {
tmpRow = bedeutungGruppe.getRow(i);
row = bedeutungGruppe.getRow(i);
for (short j = 1; j <= 27; j++) {
if (tmpRow != null && bedeutungGruppe.getRow(i).getCell(j) != null) {
tmpCell = bedeutungGruppe.getRow(i).getCell(j);
if (tmpCell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
cell = row.createCell(j);
evaluator.setCurrentRow(tmpRow);
HSSFFormulaEvaluator.CellValue cellValue = evaluator
.evaluate(tmpCell);
cell.setCellValue(cellValue.getNumberValue());
wb.write(fileOut);
fileOut.close();
fileOut = new FileOutputStream("data/temp2.xls");
bedeutungGruppe = wb.getSheet(BILclim);
}
}
}
}
wb.write(fileOut);
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
Dr. Rainer Laudien
Geography, GIS & RS
University of Cologne
50923 Cologne
++49-221-470-6620 Fax: -1638
www.geographie.uni-koeln.de/gis
www.impetus.uni-koeln.de
-----Ursprüngliche Nachricht-----
Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Gesendet: Montag, 20. November 2006 09:50
An: poi-user@jakarta.apache.org
Betreff: RE: IF-function
Can I have sample code where and how you are using If statements.
Thanks & Regards,
________________________________
Sunil Kumar Dhage
-----Original Message-----
From: rlaudien [mailto:[EMAIL PROTECTED]
Sent: Monday, November 20, 2006 1:33 PM
To: poi-user@jakarta.apache.org
Subject: IF-function
Hello,
We are trying to execute if-functions within MS-Excel via HSSF and it
does
not work. By searching the mailing list, we found Sumit's email (see
below),
showing that he was able to execute normal IF functions. Does anybody
know
how to do it? We work with:
- poi-scratchpad-3.0-alpha2-20061017.jar
- poi-3.0-alpha2-20061017.jar
- poi-contrib-3.0-alpha2-20061017.jar
Best regards, Rainer
.
evaluator.setCurrentRow(tmpRow);
HSSFFormulaEvaluator.CellValue cellValue = evaluator.evaluate(tmpCell);
//contains if-function
cell.setCellValue(cellValue.getNumberValue()); //returns 0.00 instead of
the
if-function result
.
Message view
<http://mail-archives.apache.org/mod_mbox/jakarta-poi-user/200604.mbox/%
3c28
[EMAIL PROTECTED]> <
<http://mail-archives.apache.org/mod_mbox/jakarta-poi-user/200604.mbox/d
ate>
Date
<http://mail-archives.apache.org/mod_mbox/jakarta-poi-user/200604.mbox/%
3c00
[EMAIL PROTECTED]> > .
<http://mail-archives.apache.org/mod_mbox/jakarta-poi-user/200604.mbox/%
3c28
[EMAIL PROTECTED]> <
<http://mail-archives.apache.org/mod_mbox/jakarta-poi-user/200604.mbox/t
hrea
d> Thread
<http://mail-archives.apache.org/mod_mbox/jakarta-poi-user/200604.mbox/%
3c00
[EMAIL PROTECTED]> >
<http://mail-archives.apache.org/mod_mbox/jakarta-poi-user/200604.mbox/%
3cOF
[EMAIL PROTECTED]
arch
ives#archives> Top
<http://mail-archives.apache.org/mod_mbox/jakarta-poi-user/200604.mbox/%
3c28
[EMAIL PROTECTED]> <
<http://mail-archives.apache.org/mod_mbox/jakarta-poi-user/200604.mbox/d
ate>
Date
<http://mail-archives.apache.org/mod_mbox/jakarta-poi-user/200604.mbox/%
3c00
[EMAIL PROTECTED]> > .
<http://mail-archives.apache.org/mod_mbox/jakarta-poi-user/200604.mbox/%
3c28
[EMAIL PROTECTED]> <
<http://mail-archives.apache.org/mod_mbox/jakarta-poi-user/200604.mbox/t
hrea
d> Thread
<http://mail-archives.apache.org/mod_mbox/jakarta-poi-user/200604.mbox/%
3c00
[EMAIL PROTECTED]> >
From
[EMAIL PROTECTED]
Subject
Issues with Formula in HSSF API
Date
Mon, 10 Apr 2006 20:25:36 GMT
I am facing issues writing IF statements using HSSF API (MS-Excel) .
Normal IF function works fine. But when I use Nested IF statements or
have
AND, OR or ROUND functions within an IF statement, it gives #VALUE.
Ms-Excel simply returns #VALUE code for those inner functions or inner
IF
statement.
I have tried ROUND function outside IF statement, and it works perfectly
fine. But not inside an IF statement.
has anyone solved this issue ? pls let me know if there is a way out !
Its
urgent for me to resolve this..
Regards,
Sumit
+=========================================================+
This message may contain confidential and/or privileged
information. If you are not the addressee or authorized to
receive this for the addressee, you must not use, copy,
disclose or take any action based on this message or any
information herein. If you have received this message in
error, please advise the sender immediately by reply e-mail
and delete this message. Thank you for your cooperation.
+=========================================================+
Mime
* Unnamed multipart/alternative (inline,
None, 0 bytes)
o
<http://mail-archives.apache.org/mod_mbox/jakarta-poi-user/200604.mbox/r
aw/%
[EMAIL PROTECTED]
%3e/
1> Unnamed text/plain (inline, None, 1067 bytes)
View
<http://mail-archives.apache.org/mod_mbox/jakarta-poi-user/200604.mbox/r
aw/%
[EMAIL PROTECTED]
%3e>
raw message
The information contained in this electronic message and any attachments to
this message are intended for the exclusive use of the addressee(s) and may
contain proprietary, confidential or privileged information. If you are not
the intended recipient, you should not disseminate, distribute or copy this
e-mail. Please notify the sender immediately and destroy all copies of this
message and any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should
check this email and any attachments for the presence of viruses. The
company accepts no liability for any damage caused by any virus transmitted
by this email.
www.wipro.com
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
The information contained in this electronic message and any attachments to
this message are intended for the exclusive use of the addressee(s) and may
contain proprietary, confidential or privileged information. If you are not
the intended recipient, you should not disseminate, distribute or copy this
e-mail. Please notify the sender immediately and destroy all copies of this
message and any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should
check this email and any attachments for the presence of viruses. The
company accepts no liability for any damage caused by any virus transmitted
by this email.
www.wipro.com
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
