Add empty cells used by ranges into the ExcelModel about formulas HOT 5 CLOSED

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.

from formulas.

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.

from formulas.

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?

from formulas.

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>}}

from formulas.

Close with the new release v1.0.0.

from formulas.