1

Closed

Formula engine fails on ranges that point to formulas

description

If you put a formula in a cell, then reference that cell via a named range in another formula, the latter formula fails in XLWorksheet.Range().

Steps to reproduce:
  1. Create a named range for cell A1.
  2. Put a formula in cell A1, e.g. a sum of some other cells.
  3. Put a formula in cell A2 that references the range name.
  4. Attempt to get the value of cell A2 (or call Evaluate on its formula)

file attachments

Closed Apr 25 at 8:39 PM by igitur
Please try the latest version of ClosedXML (v0.87.1 at this stage). If you still experience the issue, log it on the new GitHub page.

comments

GrahamSutherland wrote Nov 1, 2012 at 9:31 AM

I think this is caused by the order of the expressions in the last line. It calls Ranges.First() before checking that Ranges.Count == 1, which would cause an InvalidOperationException in any case where no ranges exist.

GrahamSutherland wrote Nov 1, 2012 at 10:07 AM

Ok, I've found the reason. I forgot to mention the all-important step: the range needs to point to a cell in a different sheet. The reference lookup incorrectly points to the same sheet as the first formula, so it calls Range() on the wrong sheet, resulting in the n.Ranges.First().Worksheet == this condition to always fail. The mistake is in XLCalcEngine.cs, line 34, inside GetExternalObject. Notice that _ws.Range() is called, which doesn't take into account ranges on other sheets.

GrahamSutherland wrote Nov 1, 2012 at 10:27 AM

Attached is a proposed patch which solves the simple case of a reference to a range name containing a fixed value. However, this does not fix the case where the target range has a formula assigned to it.

MDeLeon wrote Dec 5, 2012 at 4:59 PM

Sorry for the delay. I took a break from the project for a while.

I can't reproduce the error. For example, the following works fine:

var wb = new XLWorkbook();var ws = wb.AddWorksheet("Sheet1"); ws.Cell("B1").SetValue(1).CellBelow().SetValue(2); // Values to use in the formula

ws.Cell("A1") .AddToNamed("SUMA1") // 1. Create a named range for cell A1. .SetFormulaA1("Sum(B1:B2)"); // 2. Put a formula in cell A1, e.g. a sum of some other cells.

ws.Cell("A2").SetFormulaA1("SUMA1 + 1"); // 3. Put a formula in cell A2 that references the range name.

ws.Cell("A3").Value = ws.Cell("A2").Value; // 4. Attempt to get the value of cell A2 (or call Evaluate on its formula)

wb.SaveAs(@"Sandbox.xlsx");