In .Net I create a workbook with a lot of sheets, table and formulas. When I open it in Excel it takes more than 3 minutes to open. When I press “save”, close Excel and open the same workbook again it takes about 5 seconds.
I have compared the OpenXML and found that the biggest difference is that the “CalcChain.xml” is now filled with many more lines. Can this be the difference in loading speed? And if so, can I prepare this CalcChain in Aspose Cells so the user get a fast experience on the first time opening?
The issue you’re experiencing with the slow opening of the workbook in Excel is likely related to the calculation chain that is created when the workbook is first opened. When you save the workbook in Excel, it generates a more optimized CalcChain.xml, which significantly improves the loading speed for subsequent openings.
To address this in Aspose.Cells, you can pre-calculate the formulas in your workbook before saving it. This will create the calculation chain and optimize the workbook for faster opening times. You can achieve this by calling the CalculateFormula method on your Workbook object. Here’s a code example to illustrate this:
// Load your workbook
Workbook workbook = new Workbook("path/to/your/workbook.xlsx");
// Calculate all formulas in the workbook
workbook.CalculateFormula();
// Save the workbook
workbook.Save("path/to/your/optimized_workbook.xlsx");
Additionally, if you want to prevent the creation of a calculation chain for the first calculation, you can set the EnableCalculationChain property to false. This can be useful if you want to control the performance of formula calculations more finely:
// Disable calculation chain
workbook.Settings.FormulaSettings.EnableCalculationChain = false;
// Calculate formulas
workbook.CalculateFormula();
// Save the workbook
workbook.Save("path/to/your/optimized_workbook.xlsx");
By following these steps, you should be able to improve the initial loading time of your workbook in Excel significantly.
If you have further questions or need additional assistance, feel free to ask!
Thank you. But that didn’t work unfortunately.
The CalculateFormula() only takes about 1 seconds, which looks far the fast to calculate the whole workbook. I also tried workbook.RefreshDynamicArrayFormulas(true) but that didn’t make a difference either.
@Verycrispy
Would you please provide use the template file and code to generate the workbook so we can trace the issue for you? To test whether it is related with formula calculation, you may try below code:
workbook.Settings.FormulaSettings.CalculateOnOpen = false;
before saving the workbook to see whether the generated file can be opened by ms excel without performance issue.
Hello @johnson.shi,
I’ve tried with CalculateOnOpen = false, but no difference.
It is a big and complex code base, so I don’t think it is useful to provide the full code. But code for writing the formula’s is
worksheet.Cells[2, 2].GetTable().ListColumns[currentCellAddress.Column].Formula = $"={formula}";
The formulas can be something like
=IF([@firstcolumn]=namedCell1;namedCell2*[@[ColumnX]];namedCell3*[@[ColumnX]])
I have also removed all complex formulas by just =namedCell1 and than the described issue arises. So first time it takes long, after saving it’s fast.
When I leave the complex formulas it is equally slow the first time and only a bit faster after saving.
I’ve attached both files with Generated_SimpleFormula.xlsx being much faster after you save it in Excel.
ExcelSlow.zip (9.6 MB)
@johnson.shi
I’m not sure if it’s related but I also experience something strange with the formulas.
For example on the worksheet “Main Model” on Column E. The first row of the table evalues to the correct value. All the other rows evaluate to “#NAME?”.
When I click on the cell, the formula shows the in the formulabar. Just press enter and the correct value is calculated without changing any thing! Also pressing “Calculate Sheet” or “Calculate” will not update the cell. Only when you press enter on the formulabar.
I don’t understand this.
Thanks for the sample (generated) files.
I noticed the issue you mentioned with the files when opening them in MS Excel manually. I’m afraid, to evaluate your issue precisely, we need your input files (if any) and sample (runnable) code or console app. We need to check how you are setting data, formulas, calculations and inserting other contents to generate the final workbook(s). This will help us trace the issue and consequently figure it out soon.
If your code is complex and huge, please create a simplified version of your project (which contains fewer lines of code) to reproduce the issue on our end. We will check it soon.
For the “#NAME?” issue, it seems in those formulas such as ‘Main Model’!E56 you are using some kind of office addin custom function such as _xldudf_MMP_PULSE. In my excel I have not installed this addin so I always get “#NAME?” even if I press enter in the formula bar. Maybe there is some problem when we saving such kind of formulas to the generated excel file. And we think it is also possible that the performance issue was caused by this problem too.
So, we need to know which kind of addin should we install to make the formula work so we can install it and investigate the issue further.
@johnson.shi , Indeed there are custom functions in a Office Addin. This addin is what we currently are developing, so it’s not in the store yet. So unfortunately I am not able to provide you with that. I will try to see if I can replace that with some formula where you don’t need the add-in. In the ZIP file there where 2 files. “Generated_SimpleFormula” doesn’t have the custom functions and indeed not the #NAME issue. But is has the performance issue, so I do think the performance issue is not releated to the custom function.
Thank you for you help and quick responses!
@Verycrispy
Thank you for your feedback. We will further investigate your issue. Once there is an update, we will notify you promptly.
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-56522
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
This is to inform you that your issue (Ticket ID: “CELLSNET-56522”) has been resolved. The fix/enhancement will be included in the upcoming release (Aspose.Cells v24.11) scheduled for the next week of November 2024. You will be notified when the new version is published.
1 Like
The issues you have found earlier (filed as CELLSNET-56522) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi
Thank you very much! I’ll try it in the near future
@Verycrispy
You are welcome. Please take your time to try the release (Aspose.Cells v24.11). If you have any questions, please feel free to contact us at any time.