Cell.setR1C1Formula() takes a long time

Hi,

I have been playing with Aspose cells for a while and having problems
with Cell.setR1C1Formula().

I did some profiling with jprofiler with a
test which sets about 15k formulas in a spreadsheet (see the attached screenshot
for details). It takes about 60 seconds to complete the formula operations and
about 60% of the whole runtime. I tried setting the calculation mode to manual
to see if it helps, but it doesn’t make much difference.

Is there a way
to bring down the time taken to set the
formulas?

Thanks,
Srivatsa.


This message was posted using Email2Forum by Amjad Sahi.

Hi Srivatsa,

For the performance of setting formula, it mainly depends on the complexity
of the formula, such as, the tokens need to be parsed in the formula. I have
tested the following code to set formulas for about 15k cells and the total time for
those cells is about 5 seconds:
for(int row = 0; row<1000; row++)
{
for(int col = 0; col<15; col++)
{
cell = cells.getCell(row, col);
cell.setR1C1Formula("=SUMPRODUCT(((R"+(row+5)+"C" + (col+2) + ":R" + (row+10) + "C" + (col+2) + ")=R" + (row+6) + "C" + (col+2) + ")*1,((R" + (row+5) + "C" + (col+3) + ":R" + (row+10) + "C" + (col+3) + ")=\"1A\")*1)");
}
}

So I am afraid, we need your sample code for setting those formulas so we can check whether there is bottleneck for some special formulas or we can make some improvements for that.

Thank you

Perhaps you should try with a more complicated spreadsheet.


I can’t share the data. Would it be possible to generate an un-obfuscated version of your library so that I may be able to give you more insights from my profiling?

Thanks

Hi,

Thanks for your suggestions.

We are working on to test some complicated formulas. We will try to improve the performance of setting formulas and respond you soon on the results we get.


Thank you.

Hi,

We are still tracing and checking whether we can make some improvements for

the performance issue. Before we can make improvement for it, would you please
check whether you can change your formulas to shared formula? If there are
formulas that can be changed to shared formula, please try this new fix(attached). In the
new fix we provided two new APIs:

1) Cell.setR1C1SharedFormula(String sharedFormula,int rowNumber,int columnNumber)
2) Cell.setR1C1ArrayFormula(String arrayFormula,int rowNumber,int columnNumber)

We believe the performance will get better if many formulas have been changed to fewer shared formulas.

Thank you.