You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Aniket Banerjee <co...@gmail.com> on 2010/12/02 10:13:13 UTC
Stop formula manipulation in POI 3.7 with Excel
Hi,
Is it possible to stop POI 3.7 for manipulating formulas. Previously POI 2.5
used to ignore formulas so there were no #REF! while now as POI 3.7
understands formulas very well it throws #REF! error whenever a referred row
is shifted. So kindly help me out if this error can be stopped as in our
case we have to shift the rows and we want the calculation to be done by
excel itself and not by POI.
Thanks in advance!!
Aniket
Re: Stop formula manipulation in POI 3.7 with Excel
Posted by Aniket Banerjee <co...@gmail.com>.
Thanks!!!
On Wed, Dec 8, 2010 at 5:59 AM, Nick Burch <ni...@alfresco.com> wrote:
> On Tue, 7 Dec 2010, Aniket Banerjee wrote:
>
>> Thanks Nick for the reply. Is POI planning to implement functions like
>> CLEAN and ADDRESS etc in future release, as I have found there many such
>> functions that are "Not Implemented Yet".
>>
>
> POI is an open source project driven by volunteer contributions. Features
> get added just as soon as someone is willing to work on them... I'd suggest
> you look at Yegor's apachecon talk, then if you can make a start on adding
> the support. Do please submit a patch if you can get it to work!
>
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Stop formula manipulation in POI 3.7 with Excel
Posted by Nick Burch <ni...@alfresco.com>.
On Tue, 7 Dec 2010, Aniket Banerjee wrote:
> Thanks Nick for the reply. Is POI planning to implement functions like
> CLEAN and ADDRESS etc in future release, as I have found there many such
> functions that are "Not Implemented Yet".
POI is an open source project driven by volunteer contributions. Features
get added just as soon as someone is willing to work on them... I'd
suggest you look at Yegor's apachecon talk, then if you can make a start
on adding the support. Do please submit a patch if you can get it to work!
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Stop formula manipulation in POI 3.7 with Excel
Posted by Aniket Banerjee <co...@gmail.com>.
Thanks Nick for the reply. Is POI planning to implement functions like CLEAN
and ADDRESS etc in future release, as I have found there many such functions
that are "Not Implemented Yet".
On Tue, Dec 7, 2010 at 1:14 PM, Nick Burch <ni...@alfresco.com> wrote:
> On Tue, 7 Dec 2010, Aniket Banerjee wrote:
>
>> Can you tell me why some of the formulas like :
>> *=IF(ISBLANK('Export Data'!B2),"",IF(LEFT('Export
>> Data'!B2,3)="%%%","",VALUE('Export Data'!B2)))*
>> *=IF(ISERROR(FIND(":",A1)),TRIM(A1),TRIM(RIGHT(A1,LEN(A1)-FIND(":",A1))))
>>
>> *are required to evaluate when I am resetting them on the cell whereas
>> some
>> get evaluated automatically by excel.
>>
>
> As a general rule, if you change any of the cells with formulas in them, or
> referenced by formulas, you need to recalculate them in poi if you want
> excel to see the new value without needing to click in the cell.
>
>
> Also when I set the below formula in excel I get Value Error, so when I
>> try to evaluate it with POI I get "*Not Implemented Yet " *exception : *
>> =(SUBTOTAL(109,(INDIRECT(ADDRESS(ROW()+1,COLUMN())):INDIRECT(ADDRESS(ROW()+1000,COLUMN())))))
>>
>
> Which function isn't implemented yet? Assuming you're on the latest 3.7, if
> you need a function that isn't there, you'll probably need to code it up.
> That isn't actually that hard to do, see Yegor's talk from ApacheCon for
> more details:
>
> http://people.apache.org/~yegor/apachecon_us2010/Evaluation_Of_Excel_Formulas_In_POI.pptx<http://people.apache.org/%7Eyegor/apachecon_us2010/Evaluation_Of_Excel_Formulas_In_POI.pptx>
>
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Stop formula manipulation in POI 3.7 with Excel
Posted by Nick Burch <ni...@alfresco.com>.
On Tue, 7 Dec 2010, Aniket Banerjee wrote:
> Can you tell me why some of the formulas like :
> *=IF(ISBLANK('Export Data'!B2),"",IF(LEFT('Export
> Data'!B2,3)="%%%","",VALUE('Export Data'!B2)))*
> *=IF(ISERROR(FIND(":",A1)),TRIM(A1),TRIM(RIGHT(A1,LEN(A1)-FIND(":",A1))))
>
> *are required to evaluate when I am resetting them on the cell whereas some
> get evaluated automatically by excel.
As a general rule, if you change any of the cells with formulas in them,
or referenced by formulas, you need to recalculate them in poi if you want
excel to see the new value without needing to click in the cell.
> Also when I set the below formula in excel I get Value Error, so when I
> try to evaluate it with POI I get "*Not Implemented Yet " *exception : *
> =(SUBTOTAL(109,(INDIRECT(ADDRESS(ROW()+1,COLUMN())):INDIRECT(ADDRESS(ROW()+1000,COLUMN())))))
Which function isn't implemented yet? Assuming you're on the latest 3.7,
if you need a function that isn't there, you'll probably need to code it
up. That isn't actually that hard to do, see Yegor's talk from ApacheCon
for more details:
http://people.apache.org/~yegor/apachecon_us2010/Evaluation_Of_Excel_Formulas_In_POI.pptx
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Stop formula manipulation in POI 3.7 with Excel
Posted by Aniket Banerjee <co...@gmail.com>.
Hi Nick,
Sure If required I will create a bug.
Can you tell me why some of the formulas like :
*=IF(ISBLANK('Export Data'!B2),"",IF(LEFT('Export
Data'!B2,3)="%%%","",VALUE('Export Data'!B2)))*
*=IF(ISERROR(FIND(":",A1)),TRIM(A1),TRIM(RIGHT(A1,LEN(A1)-FIND(":",A1))))
*are required to evaluate when I am resetting them on the cell whereas some
get evaluated automatically by excel.
Also when I set the below formula in excel I get Value Error, so when I try
to evaluate it with POI I get "*Not Implemented Yet " *exception :
*
=(SUBTOTAL(109,(INDIRECT(ADDRESS(ROW()+1,COLUMN())):INDIRECT(ADDRESS(ROW()+1000,COLUMN())))))
*
So I am kind of stuck here. Can you let me know when I need to evaluate a
formula and when a formula is evaluated automatically by excel???
Also when all these not implemented functions will be implemented in POI.
Thanks in Advance!!!
On Tue, Dec 7, 2010 at 11:18 AM, Nick Burch <ni...@alfresco.com> wrote:
> On Tue, 7 Dec 2010, Aniket Banerjee wrote:
>
>> Hope I have made the problem clear???
>>
>
> Could you create a really simple case (none of your business logic or
> content, just the very simplest excel file + java unit test that shows the
> issue), then upload that to a new bugzilla entry?
>
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Stop formula manipulation in POI 3.7 with Excel
Posted by Nick Burch <ni...@alfresco.com>.
On Tue, 7 Dec 2010, Aniket Banerjee wrote:
> Hope I have made the problem clear???
Could you create a really simple case (none of your business logic or
content, just the very simplest excel file + java unit test that shows the
issue), then upload that to a new bugzilla entry?
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Stop formula manipulation in POI 3.7 with Excel
Posted by Aniket Banerjee <co...@gmail.com>.
Hope I have made the problem clear???
On Mon, Dec 6, 2010 at 5:37 PM, Aniket Banerjee <co...@gmail.com>wrote:
> Sending the java file in text format.
>
>
> On Mon, Dec 6, 2010 at 5:11 PM, Aniket Banerjee <co...@gmail.com>wrote:
>
>> Hey Srini,
>> Which Java file you are talking about?
>>
>>
>> On Mon, Dec 6, 2010 at 4:59 PM, srinivas rapolu <
>> rapolu.srinivas@gmail.com> wrote:
>>
>>> Java file is missing.
>>>
>>> On Mon, Dec 6, 2010 at 3:26 AM, Aniket Banerjee <
>>> contact.aniket@gmail.com>wrote:
>>>
>>> > I am attaching a Java example that explains our case. I can understand
>>> the
>>> > reason for #REF! error. But can you suggest some way I can stop the
>>> #REF!
>>> > error.. The code at the bottom of the file is used to reset the formula
>>> is
>>> > giving #VALUE! error. I am also attaching the input
>>> > excel(ValueErrorTest.xls) and also the result.xls.
>>> > Thanks in advance!!!
>>> >
>>> >
>>> > On Thu, Dec 2, 2010 at 5:30 PM, Nick Burch <nick.burch@alfresco.com
>>> >wrote:
>>> >
>>> >> On Thu, 2 Dec 2010, Aniket Banerjee wrote:
>>> >>
>>> >>> *=IF(ISBLANK('Export Data'!B1),"",IF(LEFT('Export
>>> >>>
>>> >>> Data'!B1,3)="%%%","",VALUE(TRIM(CLEAN('Export Data'!B1)))))*
>>> >>>
>>> >>> So it refers to a sheet *Export Data* and to the cell B1.
>>> >>> Now in Export Data cell B1 we have some application dependent code
>>> >>> like *%%%package_name:1.
>>> >>> *Now in this cell we take the package name from Data base and but the
>>> >>> actual
>>> >>> value. While putting the actual value we shift the above code
>>> >>> %%%package_name:1 down with the below function:
>>> >>> *
>>> >>> sheet.shiftRows(currentRow, sheet.getLastRowNum() + 1, 1, true,
>>> false);*
>>> >>>
>>> >>
>>> >> So to confirm, do you have:
>>> >>
>>> >> Sheet 1:
>>> >> Formula that references something on sheet 2
>>> >> Sheet 2:
>>> >> Row
>>> >> Row
>>> >> Row
>>> >>
>>> >> Then you shift rows on Sheet 2, and the formula on sheet 1 breaks?
>>> >>
>>> >> If so, could you try creating a really simple testcase, with the
>>> simplest
>>> >> formula and number of rows on sheet 2 that you can?
>>> >>
>>> >> Nick
>>> >>
>>> >>
>>> >> ---------------------------------------------------------------------
>>> >> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> >> For additional commands, e-mail: user-help@poi.apache.org
>>> >>
>>> >>
>>> >
>>> >
>>> > ---------------------------------------------------------------------
>>> > To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> > For additional commands, e-mail: user-help@poi.apache.org
>>> >
>>>
>>
>>
>
Re: Stop formula manipulation in POI 3.7 with Excel
Posted by Aniket Banerjee <co...@gmail.com>.
Sending the java file in text format.
On Mon, Dec 6, 2010 at 5:11 PM, Aniket Banerjee <co...@gmail.com>wrote:
> Hey Srini,
> Which Java file you are talking about?
>
>
> On Mon, Dec 6, 2010 at 4:59 PM, srinivas rapolu <rapolu.srinivas@gmail.com
> > wrote:
>
>> Java file is missing.
>>
>> On Mon, Dec 6, 2010 at 3:26 AM, Aniket Banerjee <contact.aniket@gmail.com
>> >wrote:
>>
>> > I am attaching a Java example that explains our case. I can understand
>> the
>> > reason for #REF! error. But can you suggest some way I can stop the
>> #REF!
>> > error.. The code at the bottom of the file is used to reset the formula
>> is
>> > giving #VALUE! error. I am also attaching the input
>> > excel(ValueErrorTest.xls) and also the result.xls.
>> > Thanks in advance!!!
>> >
>> >
>> > On Thu, Dec 2, 2010 at 5:30 PM, Nick Burch <nick.burch@alfresco.com
>> >wrote:
>> >
>> >> On Thu, 2 Dec 2010, Aniket Banerjee wrote:
>> >>
>> >>> *=IF(ISBLANK('Export Data'!B1),"",IF(LEFT('Export
>> >>>
>> >>> Data'!B1,3)="%%%","",VALUE(TRIM(CLEAN('Export Data'!B1)))))*
>> >>>
>> >>> So it refers to a sheet *Export Data* and to the cell B1.
>> >>> Now in Export Data cell B1 we have some application dependent code
>> >>> like *%%%package_name:1.
>> >>> *Now in this cell we take the package name from Data base and but the
>> >>> actual
>> >>> value. While putting the actual value we shift the above code
>> >>> %%%package_name:1 down with the below function:
>> >>> *
>> >>> sheet.shiftRows(currentRow, sheet.getLastRowNum() + 1, 1, true,
>> false);*
>> >>>
>> >>
>> >> So to confirm, do you have:
>> >>
>> >> Sheet 1:
>> >> Formula that references something on sheet 2
>> >> Sheet 2:
>> >> Row
>> >> Row
>> >> Row
>> >>
>> >> Then you shift rows on Sheet 2, and the formula on sheet 1 breaks?
>> >>
>> >> If so, could you try creating a really simple testcase, with the
>> simplest
>> >> formula and number of rows on sheet 2 that you can?
>> >>
>> >> Nick
>> >>
>> >>
>> >> ---------------------------------------------------------------------
>> >> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> >> For additional commands, e-mail: user-help@poi.apache.org
>> >>
>> >>
>> >
>> >
>> > ---------------------------------------------------------------------
>> > To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> > For additional commands, e-mail: user-help@poi.apache.org
>> >
>>
>
>
Re: Stop formula manipulation in POI 3.7 with Excel
Posted by srinivas rapolu <ra...@gmail.com>.
Java file is missing.
On Mon, Dec 6, 2010 at 3:26 AM, Aniket Banerjee <co...@gmail.com>wrote:
> I am attaching a Java example that explains our case. I can understand the
> reason for #REF! error. But can you suggest some way I can stop the #REF!
> error.. The code at the bottom of the file is used to reset the formula is
> giving #VALUE! error. I am also attaching the input
> excel(ValueErrorTest.xls) and also the result.xls.
> Thanks in advance!!!
>
>
> On Thu, Dec 2, 2010 at 5:30 PM, Nick Burch <ni...@alfresco.com>wrote:
>
>> On Thu, 2 Dec 2010, Aniket Banerjee wrote:
>>
>>> *=IF(ISBLANK('Export Data'!B1),"",IF(LEFT('Export
>>>
>>> Data'!B1,3)="%%%","",VALUE(TRIM(CLEAN('Export Data'!B1)))))*
>>>
>>> So it refers to a sheet *Export Data* and to the cell B1.
>>> Now in Export Data cell B1 we have some application dependent code
>>> like *%%%package_name:1.
>>> *Now in this cell we take the package name from Data base and but the
>>> actual
>>> value. While putting the actual value we shift the above code
>>> %%%package_name:1 down with the below function:
>>> *
>>> sheet.shiftRows(currentRow, sheet.getLastRowNum() + 1, 1, true, false);*
>>>
>>
>> So to confirm, do you have:
>>
>> Sheet 1:
>> Formula that references something on sheet 2
>> Sheet 2:
>> Row
>> Row
>> Row
>>
>> Then you shift rows on Sheet 2, and the formula on sheet 1 breaks?
>>
>> If so, could you try creating a really simple testcase, with the simplest
>> formula and number of rows on sheet 2 that you can?
>>
>> Nick
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
Re: Stop formula manipulation in POI 3.7 with Excel
Posted by Aniket Banerjee <co...@gmail.com>.
I am attaching a Java example that explains our case. I can understand the
reason for #REF! error. But can you suggest some way I can stop the #REF!
error.. The code at the bottom of the file is used to reset the formula is
giving #VALUE! error. I am also attaching the input
excel(ValueErrorTest.xls) and also the result.xls.
Thanks in advance!!!
On Thu, Dec 2, 2010 at 5:30 PM, Nick Burch <ni...@alfresco.com> wrote:
> On Thu, 2 Dec 2010, Aniket Banerjee wrote:
>
>> *=IF(ISBLANK('Export Data'!B1),"",IF(LEFT('Export
>>
>> Data'!B1,3)="%%%","",VALUE(TRIM(CLEAN('Export Data'!B1)))))*
>>
>> So it refers to a sheet *Export Data* and to the cell B1.
>> Now in Export Data cell B1 we have some application dependent code
>> like *%%%package_name:1.
>> *Now in this cell we take the package name from Data base and but the
>> actual
>> value. While putting the actual value we shift the above code
>> %%%package_name:1 down with the below function:
>> *
>> sheet.shiftRows(currentRow, sheet.getLastRowNum() + 1, 1, true, false);*
>>
>
> So to confirm, do you have:
>
> Sheet 1:
> Formula that references something on sheet 2
> Sheet 2:
> Row
> Row
> Row
>
> Then you shift rows on Sheet 2, and the formula on sheet 1 breaks?
>
> If so, could you try creating a really simple testcase, with the simplest
> formula and number of rows on sheet 2 that you can?
>
> Nick
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Stop formula manipulation in POI 3.7 with Excel
Posted by Nick Burch <ni...@alfresco.com>.
On Thu, 2 Dec 2010, Aniket Banerjee wrote:
> *=IF(ISBLANK('Export Data'!B1),"",IF(LEFT('Export
> Data'!B1,3)="%%%","",VALUE(TRIM(CLEAN('Export Data'!B1)))))*
>
> So it refers to a sheet *Export Data* and to the cell B1.
> Now in Export Data cell B1 we have some application dependent code
> like *%%%package_name:1.
> *Now in this cell we take the package name from Data base and but the actual
> value. While putting the actual value we shift the above code
> %%%package_name:1 down with the below function:
> *
> sheet.shiftRows(currentRow, sheet.getLastRowNum() + 1, 1, true, false);*
So to confirm, do you have:
Sheet 1:
Formula that references something on sheet 2
Sheet 2:
Row
Row
Row
Then you shift rows on Sheet 2, and the formula on sheet 1 breaks?
If so, could you try creating a really simple testcase, with the simplest
formula and number of rows on sheet 2 that you can?
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Stop formula manipulation in POI 3.7 with Excel
Posted by Aniket Banerjee <co...@gmail.com>.
One more thing that is in our case we cannot shift the formula as it is set
by the user and it is dependent on the Export Data sheet's cell B1 's value
that comes from Data base(*%%%package_name:1*) after we do all the
manipulation. Hope I am clear.
And really hope there is some solution to this case.
On Thu, Dec 2, 2010 at 4:31 PM, Aniket Banerjee <co...@gmail.com>wrote:
> Thanks Yegor for the reply.
>
> I have a formula like :
>
> *=IF(ISBLANK('Export Data'!B1),"",IF(LEFT('Export
> Data'!B1,3)="%%%","",VALUE(TRIM(CLEAN('Export Data'!B1)))))*
>
> So it refers to a sheet *Export Data* and to the cell B1.
> Now in Export Data cell B1 we have some application dependent code like *%%%package_name:1.
> *Now in this cell we take the package name from Data base and but the
> actual value. While putting the actual value we shift the above code
> %%%package_name:1 down with the below function:
> *
> sheet.shiftRows(currentRow, sheet.getLastRowNum() + 1, 1, true, false);*
>
> Now there might be more than one %%%package_name:1 in the data base so we
> populate all the values and at the end we delete the code %%%package_name:1
> from the excel file using the below code :
>
> *sheet.shiftRows(rowNumberToStartTheShifting, rowCount, -1,true,false);*
>
> In between all this we do not touch the formulas, but when we traverse the
> workbook at the end we find the formulas as :
>
> *=IF(ISBLANK(#REF!),"",IF(LEFT(**#REF!**,3)="%%%","",VALUE(TRIM(CLEAN(**
> #REF!**)))))
>
> *We couldn't solve this problem, so as a temporary solution we stored the
> formula cell and reset the values at the end by :
>
> *cell.setCellFormula(formulaValue);
> cell.setCellType(Cell.CELL_TYPE_FORMULA);*
>
> But now the cell is showing *#VALUE! *error. I personally think if we can
> avoid the #REF! error this issue can be solved.
>
> *Note* : Previously with POI 2.5 when we did the same thing above
> formulas were working fine.They used to remain in the same position without
> #REF! error and excel could do proper calculation. Now as we migrated to POI
> 3.7 this issue came up.
>
> Please let me know if I need to provide more information. Any kind of help
> will be appreciated.
> (Sorry but if I am not able to resolve the issue our client is insisting to
> revert to 2.5 and I have my full faith on POI 3.7 and I know it
> rocksss...Please Help :).. )
>
> Regards,
> Aniket
>
>
> On Thu, Dec 2, 2010 at 3:32 PM, Yegor Kozlov <ye...@dinom.ru> wrote:
>
>> POI attempts to shift dependent formulas when you shift a row. This
>> behavior is intentional and can't be turned off.
>>
>> Can you post a test case demonstrating what is wrong in your case? I'd
>> rather fix the formula shift logic instead of disabling it.
>>
>> Yegor
>>
>> Hi,
>>> Is it possible to stop POI 3.7 for manipulating formulas. Previously POI
>>> 2.5
>>> used to ignore formulas so there were no #REF! while now as POI 3.7
>>> understands formulas very well it throws #REF! error whenever a referred
>>> row
>>> is shifted. So kindly help me out if this error can be stopped as in our
>>> case we have to shift the rows and we want the calculation to be done by
>>> excel itself and not by POI.
>>>
>>> Thanks in advance!!
>>> Aniket
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
Re: Stop formula manipulation in POI 3.7 with Excel
Posted by Aniket Banerjee <co...@gmail.com>.
Thanks Yegor for the reply.
I have a formula like :
*=IF(ISBLANK('Export Data'!B1),"",IF(LEFT('Export
Data'!B1,3)="%%%","",VALUE(TRIM(CLEAN('Export Data'!B1)))))*
So it refers to a sheet *Export Data* and to the cell B1.
Now in Export Data cell B1 we have some application dependent code
like *%%%package_name:1.
*Now in this cell we take the package name from Data base and but the actual
value. While putting the actual value we shift the above code
%%%package_name:1 down with the below function:
*
sheet.shiftRows(currentRow, sheet.getLastRowNum() + 1, 1, true, false);*
Now there might be more than one %%%package_name:1 in the data base so we
populate all the values and at the end we delete the code %%%package_name:1
from the excel file using the below code :
*sheet.shiftRows(rowNumberToStartTheShifting, rowCount, -1,true,false);*
In between all this we do not touch the formulas, but when we traverse the
workbook at the end we find the formulas as :
*=IF(ISBLANK(#REF!),"",IF(LEFT(**#REF!**,3)="%%%","",VALUE(TRIM(CLEAN(**
#REF!**)))))
*We couldn't solve this problem, so as a temporary solution we stored the
formula cell and reset the values at the end by :
*cell.setCellFormula(formulaValue);
cell.setCellType(Cell.CELL_TYPE_FORMULA);*
But now the cell is showing *#VALUE! *error. I personally think if we can
avoid the #REF! error this issue can be solved.
*Note* : Previously with POI 2.5 when we did the same thing above formulas
were working fine.They used to remain in the same position without #REF!
error and excel could do proper calculation. Now as we migrated to POI 3.7
this issue came up.
Please let me know if I need to provide more information. Any kind of help
will be appreciated.
(Sorry but if I am not able to resolve the issue our client is insisting to
revert to 2.5 and I have my full faith on POI 3.7 and I know it
rocksss...Please Help :).. )
Regards,
Aniket
On Thu, Dec 2, 2010 at 3:32 PM, Yegor Kozlov <ye...@dinom.ru> wrote:
> POI attempts to shift dependent formulas when you shift a row. This
> behavior is intentional and can't be turned off.
>
> Can you post a test case demonstrating what is wrong in your case? I'd
> rather fix the formula shift logic instead of disabling it.
>
> Yegor
>
> Hi,
>> Is it possible to stop POI 3.7 for manipulating formulas. Previously POI
>> 2.5
>> used to ignore formulas so there were no #REF! while now as POI 3.7
>> understands formulas very well it throws #REF! error whenever a referred
>> row
>> is shifted. So kindly help me out if this error can be stopped as in our
>> case we have to shift the rows and we want the calculation to be done by
>> excel itself and not by POI.
>>
>> Thanks in advance!!
>> Aniket
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Stop formula manipulation in POI 3.7 with Excel
Posted by Yegor Kozlov <ye...@dinom.ru>.
POI attempts to shift dependent formulas when you shift a row. This
behavior is intentional and can't be turned off.
Can you post a test case demonstrating what is wrong in your case? I'd
rather fix the formula shift logic instead of disabling it.
Yegor
> Hi,
> Is it possible to stop POI 3.7 for manipulating formulas. Previously POI 2.5
> used to ignore formulas so there were no #REF! while now as POI 3.7
> understands formulas very well it throws #REF! error whenever a referred row
> is shifted. So kindly help me out if this error can be stopped as in our
> case we have to shift the rows and we want the calculation to be done by
> excel itself and not by POI.
>
> Thanks in advance!!
> Aniket
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org