When I use the sum formula to calculate the region value and change the region value dynamically, the following problem occurs

[toca-Y]

When I use some (C2) cell whose value is sum(A1:A5), A3 is blank in excel, so I'm going to define A3 as input and C2 as output, and after forming a function model.compile(inputs, outputs), I don't get the result I expected

This is the code

from utils.formulas import ExcelModel

model = ExcelModel()
file_name = 'demo.xlsx'
sheet_name = 'Sheet1'

dict_info = {
    "'[demo.xlsx]SHEET1'!A1": 1,
    "'[demo.xlsx]SHEET1'!A2": 2,
    "'[demo.xlsx]SHEET1'!A4": 2,
    "'[demo.xlsx]SHEET1'!A5": 2,
    "'[demo.xlsx]SHEET1'!A3": '#EMPTY',
    "'[demo.xlsx]SHEET1'!C2": "=SUM('[demo.xlsx]SHEET1'!A1:A5)"
}

model.from_dict(dict_info)

inputs = {
    "'[demo.xlsx]SHEET1'!A3": 3,
}
outputs = [
    "'[demo.xlsx]SHEET1'!C2"
]
func = model.compile(inputs, outputs)

print(func(inputs))  # <Ranges>('[demo.xlsx]SHEET1'!C2)=[[7.0]] 

[vinci1it2000]

The following code returns a function as the first and unique input cell A3.

func = model.compile(inputs, outputs)

Therefore, in func(inputs), you are passing a dictionary as input of the cell A3, and this is not a number, so the Excel function SUM skips all non-numeric values. And it returns 7

The proper way of compiling the function is the following:

inputs = [
    "'[demo.xlsx]SHEET1'!A3"   # in this way you ensure the function argument order.
]
outputs = [
    "'[demo.xlsx]SHEET1'!C2"
]
func = model.compile(inputs, outputs)

print(func(3))  # <Ranges>('[demo.xlsx]SHEET1'!C2)=[[10.0]] 

[toca-Y]

The following code returns a function as the first and unique input cell A3.

func = model.compile(inputs, outputs)

Therefore, in func(inputs), you are passing a dictionary as input of the cell A3, and this is not a number, so the Excel function SUM skips all non-numeric values. And it returns 7

The proper way of compiling the function is the following:

inputs = [
    "'[demo.xlsx]SHEET1'!A3"   # in this way you ensure the function argument order.
]
outputs = [
    "'[demo.xlsx]SHEET1'!C2"
]
func = model.compile(inputs, outputs)

print(func(3))  # <Ranges>('[demo.xlsx]SHEET1'!C2)=[[10.0]] 

Hello actually maybe my problem description is wrong, originally I used an excel, and then through the to_dict exported data, in the export when there is a cell empty, will directly export "'[demo.xlsx]SHEET1'! A3": '#EMPTY', however in practice dict_info does not declare the empty cell when there are multiple empty cells in the range, so attempts to assign to these cells are invalid.

dict_info = {
    "'[demo.xlsx]SHEET1'!A1": 1,
    "'[demo.xlsx]SHEET1'!A2": 2,
    "'[demo.xlsx]SHEET1'!A4": 2,
    "'[demo.xlsx]SHEET1'!A5": 2,
    # "'[demo.xlsx]SHEET1'!A3": '#EMPTY',
    # "'[demo.xlsx]SHEET1'!A6": '#EMPTY',
    "'[demo.xlsx]SHEET1'!C2": "=SUM('[demo.xlsx]SHEET1'!A1:A6)"
}

model.from_dict(dict_info)

inputs = [
    "'[demo.xlsx]SHEET1'!A3",
    "'[demo.xlsx]SHEET1'!A6",
]
outputs = [
    "'[demo.xlsx]SHEET1'!C2"
]
func = model.compile(inputs, outputs)

print(func(3, 3))   #  <Ranges>('[demo.xlsx]SHEET1'!C2)=[[7.0]]

These are EMPTY, and to_dict does not show"A3": "#EMPTY" and "A6": "#EMPTY" when loaded by load, so calculating the formula directly from the load file will result in an inconsistency This is model.to_dict():

{
    "'[demo.xlsx]SHEET1'!A1": 1,
    "'[demo.xlsx]SHEET1'!A2": 2,
    "'[demo.xlsx]SHEET1'!A4": 2,
    "'[demo.xlsx]SHEET1'!A5": 3,
    "'[demo.xlsx]SHEET1'!C2": "=SUM('[demo.xlsx]SHEET1'!A1:A6)"
}

This is demo.xlsx Bother you, thank you!