'System.StackOverflowException' on workbook.CalculateFormula (reproduction)

Aspose.Cells Product Family
1
Cells, DotNet, 4.5

In the attached reproduction, a 'System.StackOverflowException' will occur when performing a 'CalculateFormula'. The file has been reduced to its' simplest form that we know of.

Pease advise on how to work with or around this problem.
Thanks,

Bas.

(PS: the popup for adding tags is too small for its' contents,
and or there are too many tags, a search function would be helpful)
2

I noticed I forgot to mention (or include) which version we are using,

So I have attached the file here.
3

Hi,


Thanks for providing us sample project with template file.

Please try our latest version/fix: Aspose.Cells for .NET v8.6.2.4

I have tested using the following sample code with your template file using v8.6.2.4 and I do not get any exception:


Workbook workbook = new Workbook(@“E:\test2\CallStackOverFlow\ThisWillBlowTheStack.xlsx”);
var settings = workbook.Settings;
settings.CalcMode = Aspose.Cells.CalcModeType.Automatic;
settings.CreateCalcChain = true;
settings.EnableMacros = true;
settings.Iteration = true;
settings.MaxChange = 100000;
settings.RecalculateBeforeSave = true;
settings.ParsingFormulaOnOpen = true;

var designer = new WorkbookDesigner();
designer.Workbook = workbook;
var cellIn = workbook.Worksheets[0].Cells[“A4”];
cellIn.Value = 3; // ‘c’
designer.Process();
workbook.CalculateFormula(false);


Also, please see the document for your reference:

Thank you.

4
HI, thank you for the quick reply.

Technically, you are correct and the StackPverflow no longer occurs.
However, the application now never return from the calculate method (in any reasonable amount of time. say minutes.).

I have attached the updated project.

-------------------------------------------
I have looked at the suggested document, but that seems to be a workaround for providing empty streams to the document... in this case we are using a file and not a stream and we know the file to be good because we got past the constructor.
------------------------------------------------------------------
A question about the 'application interface' changes:

Now that the 'Open' method has been deleted,
What is the purpose of the "settings.ParsingFormulaOnOpen = false" property?

Is the workbook never calculated on open?
Is the workbook always calculated on open and could this be a performance issue on high-performance applications?
--------------------------------

Thanks in advance,
Bas.
5

Hi,


Thanks for providing us further details.

Well, Workbook.CalculateFormula does take considerable amount of time to calculate long list of diverse and recursive formulas in the workbook. I think you may try to use WorkbookSettings.CalcStackSize attribute to set it to some value (minimum value e.g 40) which would consequently minimize the amount of time as it will process the Workbook.CalculateFormula() in lessor time now.

Also, regarding your concerns about ParsingFormulaOnOpen, you may try to use LoadOptions API now,

I have updated your code segment now, see the sample code below for your reference:
e.g
Sample code:

LoadOptions options = new LoadOptions(LoadFormat.Xlsx);
options.ParsingFormulaOnOpen = true; //do parse formulas while opening the file in Workbook object model.
Workbook workbook = new Workbook(@“E:\test2\CallStackOverFlow\ThisWillBlowTheStack.xlsx”, options);
var settings = workbook.Settings;
settings.CalcMode = Aspose.Cells.CalcModeType.Automatic;
settings.CreateCalcChain = true;
settings.EnableMacros = true;
settings.Iteration = true;
settings.MaxChange = 100000;
settings.RecalculateBeforeSave = true;
settings.CalcStackSize = 40;

var designer = new WorkbookDesigner();
designer.Workbook = workbook;
var cellIn = workbook.Worksheets[0].Cells[“A4”];
cellIn.Value = 3; // ‘c’
designer.Process();
workbook.CalculateFormula(false);

Hope, this helps a bit.

Thank you.


6

Thank you, that does indeed seem to shorten the time processing,

though, the target SLA says the calculation must be completed within 1 second.

I will advice my users to come up with a better approach.

Thank you for your time and help.
Bas.
7

Oaky, so I got a bit of a bewildered reply from the users…

"How come that Excel can do it in 1 second, and Aspose needs to 1 Minute?"

To which I replied, good question, I will ask.
So with this comment you will find attached the original file that, when opened in excel works within 1 second.

Please let me know if there’s anything I can do to speed up performance?

About the file,

It is passwrd protected using this t-h-r-e-a-d-i-d as the password.
on the first worksheet (YUM) you will find cell ‘C1’ which is coloured yellow.
This cell contains several testcases, picking a testcase will recalculate the worksheet.

There is a big () difference between the performance of Native Excel and Aspose.

Can you explain this difference, because I have to.
Thanx Bas.


---------------------------------------------------------------------------------------------------
) I originally wrote this in a more humorous manner, but I did not wish to offend.
However, if you are still interested, please read on:

When doing this natively in Excel this is ‘done’ before you can blink.
When doing this with Aspose this is ‘done’ after I’ve grabbed a cup of coffee, smoked a smoke, solved a Sudoku puzzle, wrote 14 haikus and brought world peace. :wink:
8

Hi,


Thanks for sharing the zipped archive.

Unfortunately, I could not extract your zipped archive (.rar file), I tried all the combinations, e.g “this t-h-r-e-a-d-i-d”, “using this t-h-r-e-a-d-i-d” or even “t-h-r-e-a-d-i-d” but to no avail.

Could you re-attach zipped archive without any password, so I could extract the file and evaluate your issue on our end. Alternatively please provide a valid password in quotes, so I could try the password while extracting it without any combinations etc.

Thank you.
9

I meant the actual id of this thread, it starts with a 6

10

Hi,


Thanks for providing us further details.

I am able to extract the Excel (XLSX) file after providing the valid password.
After an initial test after evaluating your scenario/ case, I observed the issue as you mentioned. I found that Workbook.CalculateFormula() takes more time to calculate the formulas in the Workbook. It takes a few minutes to process all the formulas in the Excel file. I used your newly attached file with the following sample code.
e.g
Sample code:

LoadOptions options = new LoadOptions(LoadFormat.Xlsx);
options.ParsingFormulaOnOpen = true;

Workbook workbook = new Workbook(@“E:\test2\DownloadAttachment (2).xlsx”, options);

var settings = workbook.Settings;
settings.CalcMode = Aspose.Cells.CalcModeType.Automatic;
settings.CreateCalcChain = true;
settings.EnableMacros = true;
settings.Iteration = true;
settings.MaxChange = 100000;
settings.RecalculateBeforeSave = true;
settings.CalcStackSize = 40;


var designer = new WorkbookDesigner();
designer.Workbook = workbook;
var cellIn = workbook.Worksheets[0].Cells[“A4”];
cellIn.Value = 3; // ‘c’
designer.Process();
workbook.CalculateFormula(false); //This is taking more time.
MessageBox.Show(“Done!”);

I have logged a ticket with an id “CELLSNET-44120” for your issue. Our product team will look into your issue soon if we can speed up the formula calculations a bit or there might be other workaround to process the formulas speedily. Otherwise, we might try to enhance the formula calculation engine (if required).

Once we have any update on it, we will let you know here.

Thank you.


11

Hi Amjad,

This issue is blocking the proof of concept at our biggest prospect ever. We must decide whether to propose Aspose or hardcode the model.

Can you please inform us about the ETA of the solution for this issue?

regards,

Dirk-Jan

12

Hi,


Thanks for sharing your concerns.

Well, your issue “CELLSNET-44120” is in progress and hopefully it will be fixed soon(e.g within 2 weeks time). We have also logged your concerns against your issue into our database. Normally, if the issue is not too complex, we might fix issues well within this time span.

Keep in touch.

13

Thank you for your reply Amjad. I will be monitoring this thread closely and looking forward to your reply with the solution.

keep in touch

DJ

14

Hi Amjad,

Do you already have any indication about the complexity of this problem?

thnx DJ

15

Hi,


Well, your issue is complex and our product team is still working over your issue. If you could change your project’s framework to 2.0 or change the target platform to other than “Any CPU”, it might work fine. Our product team will try to reduce the depth of calculation stack for your given template file. But we are not sure whether we can solve it entirely or not. If reducing the depth of calculation stack solves the issue, we may provide a fix in the next week. If not, I am afraid, we need to look some other solution for it and we cannot give any eta for it at the moment.

Thanks for your understanding!
16

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v8.6.3.2 and let us know your feedback.

For this issue, we made improvement for the calculation engine for calculation stack. Now user should be able to give larger stack size when calculating formulas, such as change:

settings.CalcStackSize = 40;
to:
settings.CalcStackSize = 100;

may give better performance. Even with 40, the new fix should give much better performance than old versions now.

17

We will report our findings shortly.

18
I have downloaded the new version.
The performance impact of changing the stack size is quite impressive.

Size 1, takes forever.
Size 50, a second or two.
Size 100, instantly.

Our problem is solved, Thank you.

19

Hi,


Good to know that your issue is sorted out by the new fix, we have closed your ticket now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.
20

The issues you have found earlier (filed as CELLSNET-44120) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.