Hi,
I need to call addArea more than 5000 times and it take a lot of time.
finally the area is similar to the following
=$C$3,$E$3,$G$3,$I$3,$K$3,$M$3,$O$3,$Q$3,$S$3,$U$3,$W$3,$Y$3,$AA$3,$AC$3,$AE$3,$AG$3,$AI$3,$AK$3,$AM$3,$AO$3,…
could you give me some advice to make it more efficient?
We are afraid there is no better solution for adding lots of discontinuous areas for conditional formatting. When you adding one area into the collection, we need to check the order, overlap, merge, …etc. for the updated collection and do operation correspondingly. For the large area collection, the check and operations will take certain amount of time. Allowing user update the area collection without checking the constraint will bring the risk of unexpected result or even exception.
when the last area into the collection, to check the constraint, other case just add the area into the collection and not check. is this possible?
We are afraid such kind of operation helps little for the performance. The last operation will check and sort the whole collection, if many items need to be moved to proper position, the performance may be poorer than the normal way. And another requirement for this solution is user need one another api to let us know all areas have been added and we need to do the check and updating. We are afraid it is not convenient for you to use such kind of apis.
The possible solution may be we provide another api to let user add areas into the collection without check and other operations. If so, it is your responsibility to guarantee the areas have been merged and sorted correctly(currently we maintain those items in the area collection in ascending order by their upper-left corner). Unfortunately, as you can see from my comment in (), this solution depends on our internal implementation heavily. Once we changed the internal logic for other performance consideration, your code that uses the api(if we can provide) may need to be updated accordingly.