XLOOKUP returns #VALUE

Aspose.Cells Product Family
1

I am using
Aspose.Cells 25.11.0.
.NET 9.0

using System.Collections;
using Aspose.Cells;

var input = @"C:\input-for-aspose.xlsx";
var output = @"C:\output.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);
    
    engine.SetCellValues(wbc);
    
    wbc.CalculateFormula(opts);

    wbc.Save(output);
}
catch (Exception e)
{
    Console.WriteLine(e);
    throw;
}

public class CustomCalculationEngine : AbstractCalculationEngine
{
    private ArrayList _list = new ArrayList();
    
    public override void Calculate(CalculationData data)
    {
        _list.Add(data.Worksheet.Index);
        _list.Add(data.CellRow);
        _list.Add(data.CellColumn);
        _list.Add(data.Cell.Value);
        try
        {
            SkipCalculation();
        }
        catch(Exception e)
        {
            
        }
    }


    public void SetCellValues(Workbook wb)
    {
        for (int i = 0; i < _list.Count; i+=4)
        {
                
            Cell cell = wb.Worksheets[(int)_list[i]].Cells[(int)_list[i + 1], (int)_list[i + 2]];
            var value = _list[i + 3];
            cell.Formula = null;
            cell.R1C1Formula = null;
            cell.Value = value; //here just re-assign its value to the cell itself, the assignment will remove the formula
        }
    }
}

We convert formulas into values after CalculateFormula. =XLOOKUP returns #VALUE and =IFNA formula breaks.

But if I start editing formula and press Enter everything works correctly.

input-for-aspose.zip (9.9 KB)
output.zip (10.1 KB)

2

@aleksandr.l

What the code does

  • Workbook.CalculateFormula(opts) is called with a CalculationOptions object that contains a custom engine (CustomCalculationEngine).
  • For every cell that has to be calculated Aspose.Cells invokes CustomCalculationEngine.Calculate.
  • Inside CustomCalculationEngine.Calculate the code stores the worksheet index, row, column and data.Cell.Value in an ArrayList, then executes SkipCalculation().
  • SkipCalculation() tells Aspose.Cells to skip the built‑in calculation for that cell.
  • After the first CalculateFormula call the method engine.SetCellValues(wbc) iterates through the stored list and, for each recorded cell, executes
cell.Formula = null;
cell.R1C1Formula = null;
cell.Value = value;   // value is the one that was stored earlier
  • The supplied workbook contains cells with the formulas XLOOKUP (and IFNA). After the above steps those cells contain #VALUE!, as can be seen in the output.zip file.
  • When the same workbook is opened in Excel and the formula is edited and confirmed with Enter, the cell shows the correct value.

What can be observed from the provided material

  • The custom engine records the original cell value before the native calculation is performed (because SkipCalculation() is called).
  • The recorded value is written back to the cell after the formula is cleared.
  • For the XLOOKUP/IFNA cells the value that is written back results in the #VALUE! error, while editing the formula in Excel forces a recalculation that restores the correct result.
3

@aleksandr.l,

Thanks for the template XLSX file and details.

After initial testing, I was able to reproduce the issue as you mentioned by using your template Excel file and sample code snippet. I found XLOOKUP formula returns “#VALUE!” after calculations.

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-59478

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.