Absolute references broken (i.e. references with $-signs like $A$1)

[ckp95]

The evaluator does not know what to do with formulae that have absolute references like $A$1.

Using this file which simply adds two cells together but varies the referencing style:

absolute_references_test.xlsx

from xlcalculator import ModelCompiler, Evaluator

filename = "absolute_references_test.xlsx"
compiler = ModelCompiler()
model = compiler.read_and_parse_archive(filename)
evaluator = Evaluator(model)

cells = ["C1", "C2", "C3", "C4"]
cells = ["Sheet1!"+i for i in cells]

for cell in cells:
    sheet_value = evaluator.get_cell_value(cell)
    calculated_value = evaluator.evaluate(cell)
    print(f"address: {cell}")
    print(f"sheet value: {sheet_value}")
    print(f"calculated value: {calculated_value}")
    print()

Result is

address: Sheet1!C1
sheet value: 9
calculated value: 9

address: Sheet1!C2
sheet value: 9
calculated value: 4.0

address: Sheet1!C3
sheet value: 9
calculated value: 4.0

address: Sheet1!C4
sheet value: 9
calculated value: 4.0

I ran into this problem while working on implementing the base conversion functions (DEC2BIN, OCT2HEX etc) and I couldn't figure out why it would fail when I ran it on a test file -- turns out my implementation was fine but I had used absolute references in some of the test case formulae.

[bradbase]

@ckp95 Are you able to contribute your code for base conversion functions?

[ckp95]

@ckp95 Are you able to contribute your code for base conversion functions?

Oh goodness, I completely forgot about this. Yes I'll fix up what I've got and do a pull request in the next few days.

[bradbase]

That would be very much appreciated. I've been really looking forward to supporting those functions. PyCel and Formulas support them all and it's the only function category we don't have any coverage at all.

In terms of absolute references...

Did you try something like this?
cells = ["$C$1", "$C$2", "$C$3", "$C$4"]

In any case intentionally handling the $ is a positive.

Are you comfortable if they get stripped from the address?

My figuring is the absolute cell reference is designed to help when filling and moving cells. That doesn't mean much when the formula has been translated to Python.

It could "break" the in-person usability of the workbook if we write out a workbook (save a model to an .xlsx) and the absolute references had been dropped. But that's not currently a supported use case. I had thought it might be a nice feature.

[ckp95]

I don't have much of an opinion on how reference-style should get handled when writing back out to .xlsx, since at the moment that's not really my use case. If pressed I would try to reduce the round-trip information loss, since that's the minimum-surprise thing to do.

As for the base functions, I'm a bit busy this week but I can try to get something on the weekend. I actually did quite a bit more than just those functions. I kept running into weird edge cases where the naive implementation disagreed with how Excel did it (and also differences between LibreOffice and Excel), so I threw up my hands and started using hypothesis and xlwings in the test suite so that the entire input domain was exhaustively checked. That test code might be useful in other parts of the project too; you wrote in the README that better test coverage is a goal.

[ckp95]

I have not forgotten about this.

[ckp95]

Okay the base conversion functions are implemented in this PR #49

[Xandaros]

Is there a workaround until this gets fixed? As it is, xlcalculator is basically unusable if absolute referencing is used anywhere...

In [12]: compiler.model.formulae["Sheet1!E1"].formula
Out[12]: '=B1'

In [13]: compiler.model.formulae["Sheet1!F1"].formula
Out[13]: '=$B$1'

In [14]: evaluator = xlcalculator.Evaluator(compiler.model)

In [15]: evaluator.evaluate("Sheet1!E1")
Out[15]: <Number 2>

In [16]: evaluator.evaluate("Sheet1!F1")
Out[16]: <BLANK>

Simply stripping every single $ would probably work, but I don't know how I would do that.

[augmento]

Any update on this feature? Any pointers @ckp95 ?

[monoasbush]

I tried stripping the $ from the formulas and it didn't work :/

from xlcalculator import ModelCompiler
from xlcalculator import Model
from xlcalculator import Evaluator

filename='OC1 Controls and Econ_singleColumn_v8_abdev.xlsx'
compiler = ModelCompiler()
new_model = compiler.read_and_parse_archive(filename)
evaluator = Evaluator(new_model)

val2 = evaluator.evaluate('FULL TABLE!H1227')
print("Value for H1227 is", val2)
bro = new_model.formulae["FULL TABLE!H1227"].formula
print(bro)

rep = str(new_model.formulae["FULL TABLE!H1227"].formula).replace('$','')
new_model.formulae["FULL TABLE!H1227"].formula = rep

print(new_model.formulae["FULL TABLE!H1227"].formula)

print(evaluator.get_cell_value('FULL TABLE!H1227'))

valfix = evaluator.evaluate('FULL TABLE!H1227')
print(valfix)

The formula in H1227 is =H1175/(H$1061)*1000

The output of the above is:

Value for H1227 is #DIV/0!
=H1175/(H$1061)*1000
=H1175/(H1061)*1000
#DIV/0!
#DIV/0!

[rstucchi]

I solved by replacing the $ before creating the model. Test.xlsx is a simple Excel file where B1=5 and B2=2*B1. Using the file Test_mod.xlsx (created by replacing the all the $ in the formulas with xlwings), the output is correct.

from xlcalculator import Evaluator
from xlcalculator import ModelCompiler
import xlwings as xw

filename = r'Test.xlsx'
filename_mod = r'Test_mod.xlsx'
sheet_name = 'Foglio1'

excel_app = xw.App(visible=False)
wb = excel_app.books.open(filename)
ws = wb.sheets[sheet_name]
rng = ws.used_range

for cell in rng:
    if cell.api.HasFormula:
        cell.formula = cell.formula.replace('$', '')

wb.save(filename_mod)
wb.close()
excel_app.quit()

compiler = ModelCompiler()

model = compiler.read_and_parse_archive(filename)
evaluator = Evaluator(model)
print('B2 from '+filename+' is: ', evaluator.evaluate(sheet_name+'!B2'))

compiler = ModelCompiler()
model_mod = compiler.read_and_parse_archive(filename_mod)
evaluator_mod = Evaluator(model_mod)
print('B2 from '+filename_mod+' is: ', evaluator_mod.evaluate(sheet_name+'!B2'))

The output is:

B2 from Test.xlsx is:  0.0
B2 from Test_mod.xlsx is:  10

Anyway, it would be nice if someone can fix this issue without using this workaround.

[nijam13]

is this issue fixed ?