Hi Aspose team,
in attached project you can find simple code that is looping through formulas and making replacements. Unfortunately, after few replacements (212) it is throwing “Unhandled Exception: Aspose.Cells.CellsException: Cannot change part of the array/table formula”.
Can you please investigate?
FormulasReplacement.zip (129.7 KB)
Thx,
Oliver
@dr.doc,
We have logged the issue as “CELLSNET-47779” in our database for investigations. Once we will have some news for you, we will update you in this topic.
This is not an issue with the APIs. We think the message of the exception has explained the reason clearly. For array formulas applied on multiple cells in a range, you cannot change part of it, such as, modifying one cell’s formula. It is the same behavior of MS Excel. For such kind of formulas, you should remove the array formula for all the cells in the range together, and then set different formulas for them one by one or just set another array formula for the same range.
Hi Amjad,
I understand protection that you built protected but I am trying to change all formulas meaning that when my complete processing is done they all ok should be. If I missed something then this is my responsibility :).
Any chance to allow changes and shift responsibility on us using Aspose components?
Thx,
Oliver
I am not sure if we could change the behavior and go against MS Excel. Anyways, let us evaluate it further and we will get back to you.
I was testing further and if you use Ctrl+H you can make changes in this type of formulas so Aspose is not following Excel “rules” ;-).
Can you please check with the team that this is changed from “Won’t fix” as changes are allowed in Excel?
Settings that I used:
Within: Sheet
Search: By Rows
Look in: Formulas
Thx,
Oliver
@dr.doc,
Not like the CTRL+H operation in MS Excel which is a single operation, your code is changing cells one by one. Cell.Formula is an operation that you may call it anywhere and at anytime for a single cell. How can we know that you want to modify all cells together and how can it be ensured that those steps will update all cells in the range correctly?
For your requirement, we may consider to provide one single API to make modifying all formulas as one unique operation in later fix or versions. If you need that feature please let us know. We will create a separate ticket for it.
Currently the workaround should be that you detect array formulas and reset them yourself in the program.
If you can add one single API to make modification of all formulas as one unique operation that will be very appreciated as we have requirement to do this kind of actions and currently only way is to loop through all formulas (which is ok) except that this is not possible to array/table formulas like reported here.
Please create new ticket and thanks in advance,
Oliver
Thanks for your confirmation.
We have created a new ticket with an id “CELLSNET-47783” for it. The ticket is logged as following:
CELLSNET-47783 - Modify all formulas via single API as a unique operation
Once we have an update on it, we will let you know.
Thanks for new ticket 