You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Maria Gutierrez <ma...@digitalbridges.com> on 2004/07/15 13:25:50 UTC
Formula problem
Hi,
I'm trying to get this following formula working when generating an
Excel file using HSSF.
=IF(EXACT(B101,0),PRODUCT(C101,100),ROUND((C101-B101)/B101*100,0))
I use the following code:
HSSFCell c = r.createCell(cellnum);
c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
c.setCellFormula(formula); //The string above
When using it in normal Excel the formula works fine. In the HSSF
generated file I'm getting a #VALUE! error. However, if I click in the
formula in excel and confirm it without changing anything it also works.
Any way to sort out my problem?
Cheers.
Maria
________________________________________________________________________
E-mail is an informal method of communication and may be subject to data corruption, interception and unauthorised amendment for which Digital Bridges Ltd will accept no liability. Therefore, it will normally be inappropriate to rely on information contained on e-mail without obtaining written confirmation.
This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
________________________________________________________________________
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
Re: Formula problem
Posted by Alex Karshakevich <al...@thoughtspeed.biz>.
Hi Pavan,
Entire columns must be hidden, not individual cells. Just set the width
of the column to 0 (HSSFSheet.setColumnWidth(short column, short width))
and it will do the trick.
We also did the same HSSFCellStyle.setHidden(true) for each cell.
Another solution might be to try and store the temporary values in
another 'scratchpad' sheet.
Alex.
Pavan Guduru wrote:
>Thanks Alex,
>Your solution is working. But now I am not able to hide the cells where the intermidiate formula value is stored.
>
>HSSFCellStyle.setHidden(true) doesnot seem to be working to hide the value in the cell.
>Is there anyway to hide the value of the cell.
>
>Thanks
>pavan
>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
Re: Formula problem
Posted by Pavan Guduru <pa...@yahoo.com>.
Thanks Alex,
Your solution is working. But now I am not able to hide the cells where the intermidiate formula value is stored.
HSSFCellStyle.setHidden(true) doesnot seem to be working to hide the value in the cell.
Is there anyway to hide the value of the cell.
Thanks
pavan
Alex Karshakevich <al...@thoughtspeed.biz> wrote:
Hi,
We were having the same problem with a different formula, having several
nested IFs and math functions. We thought it may be because of nesting
or length of the formula, so our solution was to break the formula into
several shorter pieces and put them into hidden columns, and combine the
result in the destination cell. That worked out in our case. You may
wish to give that a shot.
Alex
>>>I'm trying to get this following formula working when generating an
>>>Excel file using HSSF.
>>>
>>>=IF(EXACT(B101,0),PRODUCT(C101,100),ROUND((C101-B101)/B101*100,0))
>>>
>>>I use the following code:
>>>
>>>HSSFCell c = r.createCell(cellnum);
>>>c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
>>>c.setCellFormula(formula); //The string above
>>>
>>>When using it in normal Excel the formula works fine. In the HSSF
>>>generated file I'm getting a #VALUE! error. However, if I click in
>>>the formula in excel and confirm it without changing anything it also
>>>works.
>>>
>>>
>>>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
---------------------------------
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
Re: Formula problem
Posted by Alex Karshakevich <al...@thoughtspeed.biz>.
Hi,
We were having the same problem with a different formula, having several
nested IFs and math functions. We thought it may be because of nesting
or length of the formula, so our solution was to break the formula into
several shorter pieces and put them into hidden columns, and combine the
result in the destination cell. That worked out in our case. You may
wish to give that a shot.
Alex
>>>I'm trying to get this following formula working when generating an
>>>Excel file using HSSF.
>>>
>>>=IF(EXACT(B101,0),PRODUCT(C101,100),ROUND((C101-B101)/B101*100,0))
>>>
>>>I use the following code:
>>>
>>>HSSFCell c = r.createCell(cellnum);
>>>c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
>>>c.setCellFormula(formula); //The string above
>>>
>>>When using it in normal Excel the formula works fine. In the HSSF
>>>generated file I'm getting a #VALUE! error. However, if I click in
>>>the formula in excel and confirm it without changing anything it also
>>>works.
>>>
>>>
>>>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
Re: Formula problem
Posted by Pavan Guduru <pa...@yahoo.com>.
Hello Maria,
I am having the same problem. Please let me know if you are able to get the excel sheet calculate the formula on load.
Thanks
Pavan
Maria Gutierrez <ma...@digitalbridges.com> wrote:
Sorry,
I forgot to say that I'm not using the = in the formula passed.
I'm passing in
"IF(EXACT(B101,0),PRODUCT(C101,100),ROUND((C101-B101)/B101*100,0)) "
Maria
Danny Mui wrote:
> try taking out the = in the formula when setting it
>
> Maria Gutierrez wrote:
>
>> Hi,
>>
>> I'm trying to get this following formula working when generating an
>> Excel file using HSSF.
>>
>> =IF(EXACT(B101,0),PRODUCT(C101,100),ROUND((C101-B101)/B101*100,0))
>>
>> I use the following code:
>>
>> HSSFCell c = r.createCell(cellnum);
>> c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
>> c.setCellFormula(formula); //The string above
>>
>> When using it in normal Excel the formula works fine. In the HSSF
>> generated file I'm getting a #VALUE! error. However, if I click in
>> the formula in excel and confirm it without changing anything it also
>> works.
>>
>> Any way to sort out my problem?
>>
>> Cheers.
>>
>> Maria
>>
>>
>> ________________________________________________________________________
>>
>> E-mail is an informal method of communication and may be subject to
>> data corruption, interception and unauthorised amendment for which
>> Digital Bridges Ltd will accept no liability. Therefore, it will
>> normally be inappropriate to rely on information contained on e-mail
>> without obtaining written confirmation.
>>
>> This e-mail may contain confidential and/or privileged information.
>> If you are not the intended recipient (or have received this e-mail
>> in error) please notify the sender immediately and destroy this
>> e-mail. Any unauthorized copying, disclosure or distribution of the
>> material in this e-mail is strictly forbidden.
>>
>> ________________________________________________________________________
>>
>>
>> ---------------------------------------------------------------------
>> 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
>
________________________________________________________________________
E-mail is an informal method of communication and may be subject to data corruption, interception and unauthorised amendment for which Digital Bridges Ltd will accept no liability. Therefore, it will normally be inappropriate to rely on information contained on e-mail without obtaining written confirmation.
This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
________________________________________________________________________
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
---------------------------------
Do you Yahoo!?
vote.yahoo.com - Register online to vote today!
Re: Formula problem
Posted by Maria Gutierrez <ma...@digitalbridges.com>.
Sorry,
I forgot to say that I'm not using the = in the formula passed.
I'm passing in
"IF(EXACT(B101,0),PRODUCT(C101,100),ROUND((C101-B101)/B101*100,0)) "
Maria
Danny Mui wrote:
> try taking out the = in the formula when setting it
>
> Maria Gutierrez wrote:
>
>> Hi,
>>
>> I'm trying to get this following formula working when generating an
>> Excel file using HSSF.
>>
>> =IF(EXACT(B101,0),PRODUCT(C101,100),ROUND((C101-B101)/B101*100,0))
>>
>> I use the following code:
>>
>> HSSFCell c = r.createCell(cellnum);
>> c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
>> c.setCellFormula(formula); //The string above
>>
>> When using it in normal Excel the formula works fine. In the HSSF
>> generated file I'm getting a #VALUE! error. However, if I click in
>> the formula in excel and confirm it without changing anything it also
>> works.
>>
>> Any way to sort out my problem?
>>
>> Cheers.
>>
>> Maria
>>
>>
>> ________________________________________________________________________
>>
>> E-mail is an informal method of communication and may be subject to
>> data corruption, interception and unauthorised amendment for which
>> Digital Bridges Ltd will accept no liability. Therefore, it will
>> normally be inappropriate to rely on information contained on e-mail
>> without obtaining written confirmation.
>>
>> This e-mail may contain confidential and/or privileged information.
>> If you are not the intended recipient (or have received this e-mail
>> in error) please notify the sender immediately and destroy this
>> e-mail. Any unauthorized copying, disclosure or distribution of the
>> material in this e-mail is strictly forbidden.
>>
>> ________________________________________________________________________
>>
>>
>> ---------------------------------------------------------------------
>> 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
>
________________________________________________________________________
E-mail is an informal method of communication and may be subject to data corruption, interception and unauthorised amendment for which Digital Bridges Ltd will accept no liability. Therefore, it will normally be inappropriate to rely on information contained on e-mail without obtaining written confirmation.
This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
________________________________________________________________________
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
Re: Formula problem
Posted by Danny Mui <da...@muibros.com>.
try taking out the = in the formula when setting it
Maria Gutierrez wrote:
> Hi,
>
> I'm trying to get this following formula working when generating an
> Excel file using HSSF.
>
> =IF(EXACT(B101,0),PRODUCT(C101,100),ROUND((C101-B101)/B101*100,0))
>
> I use the following code:
>
> HSSFCell c = r.createCell(cellnum);
> c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
> c.setCellFormula(formula); //The string above
>
> When using it in normal Excel the formula works fine. In the HSSF
> generated file I'm getting a #VALUE! error. However, if I click in the
> formula in excel and confirm it without changing anything it also works.
>
> Any way to sort out my problem?
>
> Cheers.
>
> Maria
>
>
> ________________________________________________________________________
>
> E-mail is an informal method of communication and may be subject to data
> corruption, interception and unauthorised amendment for which Digital
> Bridges Ltd will accept no liability. Therefore, it will normally be
> inappropriate to rely on information contained on e-mail without
> obtaining written confirmation.
>
> This e-mail may contain confidential and/or privileged information. If
> you are not the intended recipient (or have received this e-mail in
> error) please notify the sender immediately and destroy this e-mail. Any
> unauthorized copying, disclosure or distribution of the material in this
> e-mail is strictly forbidden.
>
> ________________________________________________________________________
>
>
> ---------------------------------------------------------------------
> 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