There is a file that passes CalculateFormula in version 25.9 but in version 25.10CalculateFormula throws an exception:
System.NullReferenceException: Object reference not set to an instance of an object.
at .( )
at Aspose.Cells.Workbook.(Boolean , CalculationOptions , e )
at Aspose.Cells.Workbook.CalculateFormula(CalculationOptions options)
at Program.<Main>$(String[] args) in ...
Code:
var input = @"C:\input_for_aspose.xlsx";
var output = @"C:\output_for_aspose.xlsx";
Workbook? wbc;
using (var ms = new FileStream(input, FileMode.Open))
wbc = new Workbook(ms, new LoadOptions(LoadFormat.Xlsx));
try
{
var engine = new CustomCalculationEngine()
{
};
var opts = new CalculationOptions
{
CustomEngine = engine
};
wbc.CalculateFormula(opts);
wbc.Save(output);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
public class CustomCalculationEngine : AbstractCalculationEngine
{
public override void Calculate(CalculationData data)
{
try
{
var val = data.Cell.Value;
data.Cell.Formula = data.Cell.R1C1Formula = null;
data.Cell.Value = val;
data.CalculatedValue = data.Cell.Value;
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
}
Summary Workbook.CalculateFormula works in Aspose.Cells 25.9 but throws a NullReferenceException in 25.10 when a custom calculation engine is used. The exception is caused by the engine trying to access data.Cell for cells that are null (typically error cells such as #REF!). In 25.10 the library performs stricter nullâchecking, so the missing guard now surfaces as an exception.
Why it happens
The workbook contains cells whose evaluation results in errors (#REF!, #NAME?, etc.).
During calculation Aspose.Cells creates a CalculationData instance for each cell that needs processing. For error cells the Cell property can be null.
Your CustomCalculationEngine.Calculate method accesses data.Cell (e.g., data.Cell.Value, data.Cell.Formula) without first verifying that the property is nonânull. When data.Cell is null, a NullReferenceException is thrown.
In version 25.9 the library silently ignored the null reference; version 25.10 now propagates it, exposing the issue in custom engines.
What to do next
Add nullâchecks (and optional error checks) in the custom engine
using Aspose.Cells;
public class CustomCalculationEngine : AbstractCalculationEngine
{
public override void Calculate(CalculationData data)
{
// Skip processing if the cell reference is missing
if (data?.Cell == null)
return;
// Optionally ignore cells that already contain an error value
if (data.Cell.IsError)
return;
try
{
var originalValue = data.Cell.Value;
// Remove the formula so the cell becomes a plain value
data.Cell.Formula = null;
data.Cell.R1C1Formula = null;
// Restore the original value
data.Cell.Value = originalValue;
data.CalculatedValue = originalValue;
}
catch (Exception ex)
{
// Log the exception as needed; reâthrow only if you want the caller to see it
Console.WriteLine(ex);
throw;
}
}
}
Pass the custom engine (and, if desired, the errorâignore flag) via CalculationOptions
var options = new CalculationOptions
{
CustomEngine = new CustomCalculationEngine(),
// The following flag tells the engine to skip cells that evaluate to an error.
// Set it only if you do not need to process those cells.
IgnoreError = true
};
workbook.CalculateFormula(options);
Verify the change
Reârun the calculation with the updated engine.
The workbook should now complete without raising a NullReferenceException, even when error cells are present.
If the exception still occurs
Confirm that you are using the latest available Aspose.Cells build (a newer patch may contain a regression fix).
If the problem persists, create an internal support ticket, attach the workbook and the full stack trace, and note that the crash happens only with a custom AbstractCalculationEngine on version 25.10. This will allow the product team to investigate a possible regression.
Following these steps should eliminate the NullReferenceException in version 25.10 and restore normal calculation behavior.
I could not fully verify this explanation against the retrieved sources. Please double-check the behaviour in your environment and confirm the exact SDK/version.
I reproduced the issue as you mentioned by using your sample Excel file and code snippet. I found an exception: âSystem.NullReferenceException: Object reference not set to an instance of an objectâ on Workbook.CalculateFormula() method.
We require thorough evaluation of the issue. 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-59304
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.
If you need to ignore the calculation of custom function, that is, keep the original values as they are for cells that use custom functions, please use AbstractCalculationEngine.SkipCalculation() in your implementation. Code example:
public class IgnoreFormulasWithCustomFunction : AbstractCalculationEngine
{
public override void Calculate(CalculationData data)
{
SkipCalculation();
}
}
Unfortunately, we are always changing formulas and this mechanism always worked until version 25.10. Could you please advise if there is another way to get around this problem?
In Calculate(CalculationData data), we essentially remove the formulas and insert only the values.
data.CalculatedValue also changes in our other engines and it works as expected
We are afraid we cannot reverse the behavior back to prior versions for this part.
To make the formula calculation produce correct results with expected performance, we keep adding optimizations/improvements/bug fixs for new versions, that requires changes such as using data caches in our calculation engine. We cannot add extra check and reset those caches for such special requirement and case, which will cause poor performance or incorrect results for other cases and break our fixes for other users.
And even in old versions, there are still some special cases including specific data set and formulas may cause such kind of exception for such kind of implementation of custom calculation engine. Thatâs why we add such note in the api doc to prevent such kind of usage long time ago.
To remove those formulas which use custom functions, we are afraid you have to split it into another procedure. As we recommend in the api reference, when calculating formula please gather those formulas(Cells) into one list firstly in your custom implementation. After the formula calculation, you may update those cells to remove the formulas.
private class MyEngine : AbstractCalculationEngine
{
private ArrayList _list = new ArrayList();
public int[] CustomFunctionCells
{
get
{
int[] arr = new int[_list.Count];
for (int i = 0; i < arr.Length; i++)
{
arr[i] = (int)_list[i];
}
return arr;
}
}
public override void Calculate(CalculationData data)
{
//by default only custom functions will cause the invocation of this method, so here we just record the cell into the list
_list.Add(data.Worksheet.Index);
_list.Add(data.CellRow);
_list.Add(data.CellColumn);
SkipCalculation();
}
}
...
Workbook wb = new Workbook("test.xlsx");
MyEngine engine = new MyEngine();
wb.CalculateFormula(new CalculationOptions() { CustomEngine = engine });
int[] arr = engine.CustomFunctionCells;
for (int i = 0; i < arr.Length; i += 3)
{
Cell cell = wb.Worksheets[arr[i]].Cells[arr[i + 1], arr[i + 2]];
cell.Value = cell.Value; //here just re-assign its value to the cell itself, the assignment will remove the formula
}
Iâve replacing our previous code with your snippet, the SkipCalculation() then throws an unhandled exception that seems to be from native code (Exception of type âeâ was thrown.). Also workbook formulas all seemed to have remained. Do you have an idea for another implementation or a way to prevent the exception?
Could you please share your sample (runnable) code or a sample app (please zip the project (you may exclude Aspose.Cells.Dll to mininimize the size) prior attaching here) with template Excel files (if any) to reproduce the exception (Exception of type âeâ was thrown) on our end. We will check your issue soon.
Thanks for the template Excel file and sample app.
After initial testing, I am able to reproduce the issue as you mentioned by using your sample app with your template Excel file. I found an exception: ââException of type âeâ was thrown.ââ on SkipCalculation() method.
We require thorough evaluation of the issue. 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-59415
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.
We tested your sample project and found the exception can be found only when you run it in debug mode. When you run the application directly(without debug and it should be the general case for production), the calculation of the workbook can be completed successfully.
If it is same with the situation you are talking about, it is not a bug but a designed behavior. Currently we use the internal exception to denote and make the formula chain break and then the original value of the formula can be preserved.
For another issue: âAlso workbook formulas all seemed to have remained.â, are you talking about the result in fact that almost all formulas in the template file have been removed from the generated file âAfter.xlsxâ? If it is the case you are talking about, the reason is this line of code in your custom engine:
public override bool ProcessBuiltInFunctions => true;
When you specify this property as true, then all functions(not only those custom functions, but also those built-in functions such as SUM, SORT, âŚ) will be processed by your engine, so all formulas will be gathered into your â_listâ. And then all formulas will be removed when you calling SetCellValues(Workbook) of your engine. If you only need to remove those custom functions, please set this property of your custom engine as false.
And for the internal exception, we also will investigate whether we can remove it from the implementation of custom engine for userâs convenience and will give feedback here when there is any update.
I could not fully verify this explanation against the retrieved sources. Please double-check the behaviour in your environment and confirm the exact SDK/version.