I loaded an ExcelCompiler model from an openpyxlWorkbook using the excel attribute of ExcelCompiler and got different and wrong results depending on the order in which the cells were evaluated. This is different than #128 because there we were trying to evaluate after an error, but here there was no error. The unexpected behavior doesn't happen when a model is loaded directly from a path. Maybe there's something wrong about my usage of the excel parameter in ExcelCompiler?
What was expected to happen
Evaluations should be the same and correct whether loaded from an openpyxlWorkbook or a path.
Code Sample
Here's an example Excel file: pycel_order_of_eval_test.xlsx. The original file I was experimenting with was much larger and pycel was evaluating pretty much everything incorrectly, but unfortunately I can't share that file.
from pycel import ExcelCompiler
from openpyxl import load_workbook
wb = load_workbook('pycel_order_of_eval_test.xlsx')
model = ExcelCompiler(excel=wb)
# prints 0, 43255.2, 43255.2
print(model.evaluate('Sheet1!B5'))
print(model.evaluate('Sheet1!B4'))
print(model.evaluate('Sheet1!B3'))
wb = load_workbook('pycel_order_of_eval_test.xlsx')
model = ExcelCompiler(excel=wb)
# prints 43255.2, None, 86510.4
print(model.evaluate('Sheet1!B3'))
print(model.evaluate('Sheet1!B4'))
print(model.evaluate('Sheet1!B5'))
wb = load_workbook('pycel_order_of_eval_test.xlsx')
model = ExcelCompiler(excel=wb)
# same as above
print(model.evaluate('Sheet1!B4'))
print(model.evaluate('Sheet1!B3'))
print(model.evaluate('Sheet1!B5'))
wb = load_workbook('pycel_order_of_eval_test.xlsx')
model = ExcelCompiler(excel=wb)
# and finally the correct values: 43255.2, 86510.4, 43255.2
print(model.evaluate('Sheet1!B4'))
print(model.evaluate('Sheet1!B5'))
print(model.evaluate('Sheet1!B3'))
This doesn't seem to happen if I define a Workbook directly in openpyxl like this:
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws['A1'] = 1
ws['A2'] = 2
ws['A3'] = '=SUM(A1:A2)'
model = ExcelCompiler(excel=wb)
# prints 1, 2, 3
print(model.evaluate('Sheet!A1'))
print(model.evaluate('Sheet!A2'))
print(model.evaluate('Sheet!A3'))
What actually happened
I loaded an
ExcelCompiler
model from anopenpyxl
Workbook
using theexcel
attribute ofExcelCompiler
and got different and wrong results depending on the order in which the cells were evaluated. This is different than #128 because there we were trying to evaluate after an error, but here there was no error. The unexpected behavior doesn't happen when a model is loaded directly from a path. Maybe there's something wrong about my usage of theexcel
parameter inExcelCompiler
?What was expected to happen
Evaluations should be the same and correct whether loaded from an
openpyxl
Workbook
or a path.Code Sample
Here's an example Excel file: pycel_order_of_eval_test.xlsx. The original file I was experimenting with was much larger and pycel was evaluating pretty much everything incorrectly, but unfortunately I can't share that file.
This doesn't seem to happen if I define a Workbook directly in openpyxl like this: