I am encountering hundreds of internal exceptions thrown when calling Workbook.CalculateFormula for a particular Excel workbook. The exceptions are of the variety “Exception thrown: ‘’ in Aspose.Cells.dll” and, while ultimately not preventing formula calculation from completing, are causing a severe amount of lag. I would appreciate if someone could analyze the workbook and explain exactly which formulas are causing the issue and what could be done to mitigate the exceptions.
Please zip and attach your template Excel file. We will evaluate your issue using your sample file and try to figure it out soon.
@bytefyre
By testing on the latest version v24.10 using sample file and the following sample code, we were able to run normally without any exceptions.
Workbook wb = new Workbook(filePath + "Step 01 - Develop Bond Universe_US_20240724.xlsm");
wb.CalculateFormula();
We recommend you to kindly try using our latest version: Aspose.Cells for .NET 24.10.
If you still find the issue, kindly do share your complete sample (runnable) code to reproduce the issue on our end, we will check it soon.
By our further test, sometimes the calculation may cause StackoverflowException. We think it may be the issue you encountered at your end. In the template file, there are some formulas have deep dependencies which causes too much recursive cell calculation and that is the reason of stack issue. For your case, please try a smaller stack size(CalculationOptions.CalcStackSize) for the calculation:
Workbook wb = ...;
wb.CalculateFormula(new CalculationOptions() { CalcStackSize = 100 });
...
I tried upgrading my Aspose.Cells NuGet package to 24.10 and added the CalcStackSize = 100, and I am still getting the exceptions. These are the calculation options being used:
private static readonly CalculationOptions AsposeCalculationOptions;
AsposeCalculationOptions = new()
{
Recursive = true,
IgnoreError = false,
CalcStackSize = 100
};
We apologize that the issue persists despite following the recommendations. To thoroughly evaluate the issue, we require a sample console application (complete source code without compilation errors) along with any necessary resource files. As requested previously, please provide a standalone Visual Studio .NET application with the resource files, zip the project, and send it to us. We will review the issue promptly.
And please provide us more details about your environment and the exception, such as the information of the stack trace of the exception. If it is StackoverflowException, also you may try smaller value such 50 or 30 for the CalcStackSize.
The project is too big to attach even as a ZIP. Here is the code for my console application, a top-level .NET 8.0 project (it’s also worth noting I have Just My Code turned off, which exposes the internal exceptions being thrown and caught when debugging):
using Aspose.Cells;
using System.Diagnostics;
License license = new();
license.SetLicense(“Aspose.Cells.lic”);
Console.WriteLine("Please enter the workbook path for Aspose formula calculation: ");
var fileName = Console.ReadLine();
Console.WriteLine(“Calculating formulas…”);
var stopwatch = Stopwatch.StartNew();
Workbook wbk = new(fileName);
wbk.CalculateFormula();
stopwatch.Stop();
Console.WriteLine($“Finished calculating formulas in {stopwatch.Elapsed.TotalSeconds} seconds.”);
I’d also like to add that I’ve tried using EPPlus with this workbook in a similarly simple test app and it was able to calculate all formulas in 13 seconds compared to 86 seconds with Aspose, and it had no internal exceptions being thrown, so it’s clearly not an issue with the workbook. My company is currently paying for a commercial Aspose license and is in the process of getting onto your paid support, so this does cause us to question our choice of vendor.
We apologize for the ongoing issue. We have tested the formula calculations of Aspose.Cells using your template Excel file, and it worked flawlessly and efficiently. We did not find any issue or exception on our end. Unless we can reproduce the issue, we cannot help you fix it. Aspose.Cells has a built-in and robust formula calculations engine that can calculate a large list of formulas quickly. Many of our users use it and calculate formulas for their big and complex spreadsheets, and they are satisfied with the performance.
Please provide details about your environment and the complete exception trace. Screenshots of the exceptions would also be helpful. We appreciate the work you have done with us so far in tracing the issue. If your project is large, we request you to create a trimmed-down project version (with resource file(s)) and share it, so we can reproduce the issue and evaluate it precisely to figure it out quickly.
Unfortunately, I don’t have a more detailed stack trace to share because everything below my code has no line numbers and no actual function names available. We would need to obtain a PDB file to load symbols for Aspose.Cells.dll in order to do that, so if you can provide that, I should be able to obtain a full stack trace.
@bytefyre
You can perform calculation monitoring by implementing the AbstractComputerMonitor class. By monitoring, time-consuming cells can be identified. Please refer to the following example code.
Workbook wb = new Workbook(filePath + "Step 01 - Develop Bond Universe_US_20240724.xlsm");
CalculationOptions opts = new CalculationOptions();
opts.CalculationMonitor = new SelfCalculationMonitor(wb);
DateTime start = DateTime.Now;
wb.CalculateFormula(opts);
Console.WriteLine("total time: " + DateTime.Now.Subtract(start).ToString());
class SelfCalculationMonitor : AbstractCalculationMonitor
{
private Workbook workbook;
public SelfCalculationMonitor(Workbook book)
{
this.workbook = book;
}
private DateTime startTime;
public override void BeforeCalculate(int sheetIndex, int rowIndex, int colIndex)
{
startTime = DateTime.Now;
}
public override void AfterCalculate(int sheetIndex, int rowIndex, int colIndex)
{
Worksheet currSheet = workbook.Worksheets[sheetIndex];
Cell currCell = currSheet.Cells[rowIndex, colIndex];
int seconds = DateTime.Now.Subtract(startTime).Seconds;
if (seconds > 1)
{
Console.WriteLine(currSheet.Name + "!" + currCell.Name + " total seconds: " + seconds);
}
}
}
The output result:
total time: 00:00:01.1552565
If you can reproduce the issue, please share the output information with us. We will check it soon.