Add empty cells used by ranges into the ExcelModel

[axeso-devadmin]

I downloaded the latest code from github and wrote a very simple test. I have a formula in an excel spreadsheet BOOK1.XLS with
Result = X+Y+Bias stored in excel in 3 tabs Inputs, Data, Outputs as:
Outputs!B3 = Inputs!B3 + Inputs!B4 + Data!B5

I wrote a piece of code based on the README.rst file. I load the excel file, I supply my own inputs, calculate but when I call write() I get an error in line 264 of the file /formulas/excel/init.py:
Exception has occurred: AttributeError
'int' object has no attribute 'ranges'

The line in question is:
rng = r.ranges[0]
I debugged into the write() function of init.py and found that r has the last value of my inputs which is 8 and k for whatever is worth has "'[BOOK1.XLSX]Data'!B5"

It looks to me like r should contain a dictionary with the keys 'excel' and 'sheet' but based on the sample code in README.rst I don't understand how are these being passed. So either there's a bug in the write() function or the sample code in README doesn't work.

The test code is below:

import formulas
fpath = '/formula_test/Book1.xlsx'
dir_output = '/formula_test/output'
xl_model = formulas.ExcelModel().loads(fpath).finish()
xl_model.calculate(
inputs={
"'[BOOK1.XLSX]Inputs'!B3": 6, # Value X.
"'[BOOK1.XLSX]Inputs'!B4": 8, # Value Y.
"'[BOOK1.XLSX]Data'!B5": 8 # Value Y.
},
outputs=[
"'[BOOK1.XLSX]Outputs'!B3"
]
# To define the outputs that you want to calculate.
)
xl_model.write()

[mishu-]

cc @vinci1it2000 Any light on this issue? still there I think
I tried passing in data in many ways, but it seems that the calculate function adds the custom input in a weird way to the solution data so that the write function can't interpret it.

[vinci1it2000]

The problem is the input data names. The convention is that they have to be UPPERCASE.

I replicated your problem using a sample excel named book.xlsx.

The following code is the solution:

>>> import formulas
>>> xl = formulas.ExcelModel()
>>> xl.load('book.xlsx')
>>> list(xl.dsp.data_nodes)
["'[BOOK.XLSX]INPUTS'!A2",
 "'[BOOK.XLSX]INPUTS'!A3",
 "'[BOOK.XLSX]INPUTS'!B3",
 "'[BOOK.XLSX]INPUTS'!A4",
 "'[BOOK.XLSX]INPUTS'!B4",
 "'[BOOK.XLSX]DATA'!A3",
 "'[BOOK.XLSX]DATA'!B3",
 "'[BOOK.XLSX]OUTPUTS'!A3",
 "'[BOOK.XLSX]OUTPUTS'!B3"]
>>> sol = xl.calculate({"'[BOOK.XLSX]INPUTS'!B3": 6,  "'[BOOK.XLSX]INPUTS'!B4": 8, "'[BOOK.XLSX]DATA'!B3": 4})
>>> xl.write(dirpath='./outputs')
{'BOOK.XLSX': {Book: <openpyxl.workbook.workbook.Workbook at 0x7f9ce81a6670>}}

The resulting excel output file (i.e., ./outputs/BOOK.XLSX) has the cell "'[BOOK.XLSX]OUTPUTS'!B3" equal to 18, i.e. the sum of the provided inputs. You can also verify the correctness of your calculation, without saving the excel file, using the object sol as follow:

>>> sol["'[BOOK.XLSX]OUTPUTS'!B3"]
<Ranges>('[BOOK.XLSX]OUTPUTS'!B3)=[[18.0]]

I hope that this explanation can be useful for your development by using formulas.

[mishu-]

So, I think the uppercase thing is an issue, but it's not the real issue in the example. I have everything uppercase and it still doesn't work. However, I think it's due to the fact that I don't have all the cells pre-defined in my excel. Some of them are empty so they are not initialized. Your example assumes that whatever inputs you give are also in the initial XLS file (book.xls in your example). Somehow the cells should be created if you provide an input that's not already in the initial input file. Does this make sense?

[vinci1it2000]

I tested the previous code cleaning the prefilled cells and it is working. Probably you have some range with empty values in your formulas, so in this case, the excel file is compiled differently. I made a change in the library that can solve your problem. You can temporarily use the commit 5a4f132 (I will release soon a new official release).

I made a simple test case using a new sample excel partially pre-filled named new_book.xlsx.

The following code shows how to use the library:

>>> import formulas
>>> xl = formulas.ExcelModel()
>>> xl.load('new_book.xlsx')
>>> xl.finish()
>>> list(xl.dsp.data_nodes)
["'[NEW_BOOK.XLSX]INPUTS'!A2",
 "'[NEW_BOOK.XLSX]INPUTS'!A3",
 "'[NEW_BOOK.XLSX]INPUTS'!B3",
 "'[NEW_BOOK.XLSX]INPUTS'!A4",
 "'[NEW_BOOK.XLSX]DATA'!A3",
 "'[NEW_BOOK.XLSX]OUTPUTS'!A3",
 "'[NEW_BOOK.XLSX]OUTPUTS'!B3",
 "'[NEW_BOOK.XLSX]DATA'!B3",
 "'[NEW_BOOK.XLSX]INPUTS'!B3:B4",
 "'[NEW_BOOK.XLSX]INPUTS'!B4"]
>>> sol = xl.calculate({
...     "'[BOOK.XLSX]INPUTS'!B2": 3,  # Extra data to be saved.
...     "'[BOOK.XLSX]INPUTS'!B3": 3,  # Overwritten value.
...     "'[BOOK.XLSX]INPUTS'!B4": 3,  # New value.
...     "'[BOOK.XLSX]DATA'!B3": 1,    # New value.
... })
>>> xl.write(dirpath='./outputs')
{'BOOK.XLSX': {Book: <openpyxl.workbook.workbook.Workbook at 0x7f9ce81a6670>}}

[vinci1it2000]

Close with the new release v1.0.0.