Add support for circular references and defined names

[vinci1it2000]

Add support for circular references and defined names

[vinci1it2000]

The fund.xlsx file has a setting that is not supported by formulas. This allows the operations between numbers and strings like =1+"string". The standard behavior returns #VALUE!, while in this case returns empty. I cannot reproduce this behavior in a standard excel file; can you explain how you can configure the file to reproduce this behavior?

For the moment I modified the excel file in order to reproduce the same results using the standard behavior.

In conclusion, I add the missing functions and I corrected the bug that leads the error (04f178d). Since, the fund.xlsx file has circular references (e.g., cell C76), you have to compile the excel file adding circular=True to the finish method.

To reproduce the results you can use the modified excel file with the current dev (5ce4956) using the following code:

import formulas
xl_model = formulas.ExcelModel().loads("fund.xlsx").finish(circular=True)
xl_model.calculate()
xl_model.write(dirpath='output')  # Write/save the computed results.

[vinci1it2000]

Closing because version 0.4.0 solve the issue.

I am having the same issue in the new build also. The file results in a '#VALUE' if I do "=1+"string" so I don't have that setting turned on (or so I believe).

Attached is a quick test spreadsheet.

Arraytest v1.xlsx

[vinci1it2000]

I checked your input file. The problem is the defined names contained in the excel file, like IQ_CH, IQ_CQ, etc.. The defined names, currently, are not fully supported, I will work to solve the issue.

[xiaochaowu]

sample1.xlsx
0.4.0 I got this same issue.

[hoffman32]

hello
I have the same trouble you library is amazing
please help me

[vinci1it2000]

The new release v1.0.0 solves this issue.

[Yadhu0307]

HI,

I am facing the same issue post using the circular=True argument. Error message provided below .

``AttributeError:` 'NoneType' object has no attribute 'groupdict'

---

AttributeError Traceback (most recent call last)

/tmp/ipykernel_129/3801371762.py in

  1 fpath = 'MTOOutput.xlsx'

  2 dirname = 'MTOOutput'

----> 3 xl_model = formulas.ExcelModel().loads(fpath).finish(circular=True)

  4 xl_model.calculate()

  5 xl_model.write(dirpath=dirname)

~/.local/lib/python3.8/site-packages/formulas/excel/init.py in loads(self, *file_names)

 93     def loads(self, *file_names):

 94         for filename in file_names:

---> 95 self.load(filename)

 96         return self

 97

~/.local/lib/python3.8/site-packages/formulas/excel/init.py in load(self, filename)

 98     def load(self, filename):

 99         book, context = self.add_book(filename)

--> 100 self.pushes(*book.worksheets, context=context)

101         return self

102

~/.local/lib/python3.8/site-packages/formulas/excel/init.py in pushes(self, context, *worksheets)

106     def pushes(self, *worksheets, context=None):

107         for ws in worksheets:

--> 108 self.push(ws, context=context)

109         return self

110

~/.local/lib/python3.8/site-packages/formulas/excel/init.py in push(self, worksheet, context)

119             for c in row:

120                 if hasattr(c, 'value'):

--> 121 self.add_cell(

122                         c, context, references=references,

123                         formula_references=formula_references,

~/.local/lib/python3.8/site-packages/formulas/excel/init.py in add_cell(self, cell, context, references, formula_references,
formula_ranges, external_links)

232         val = cell.data_type == 'f' and val[:2] == '==' and val[1:] or val

233         check_formula = cell.data_type != 's'

--> 234 cell = Cell(crd, val, context=ctx, check_formula=check_formula).compile(

235             references=references, context=ctx

236         )

~/.local/lib/python3.8/site-packages/formulas/cell.py in compile(self, references, context)

 88     def compile(self, references=None, context=None):

 89         if self.builder:

---> 90 func = self.builder.compile(

 91                 references=references, context=context, **{CELL: self.range}

 92             )

~/.local/lib/python3.8/site-packages/formulas/builder.py in compile(self, references, context, **inputs)

127                 else:

128                     try:

--> 129 i[k] = Ranges().push(k, context=context)

130                     except ValueError:

131                         i[k] = None

~/.local/lib/python3.8/site-packages/formulas/ranges.py in push(self, ref, value, context)

167

168     def push(self, ref, value=sh.EMPTY, context=None):

--> 169 rng = self.get_range(self.format_range, ref, context)

170         return self.set_value(rng, value)

171

~/.local/lib/python3.8/site-packages/formulas/ranges.py in get_range(format_range, ref, context)

159     def get_range(format_range, ref, context=None):

160         ctx = (context or {}).copy()

--> 161 for k, v in _re_range.match(ref).groupdict().items():

162             if v is not None:

163                 if k == 'ref':

AttributeError: 'NoneType' object has no attribute 'groupdict'

``

Requesting assistance.

Thanks and Regards,

Yadhu

[revilo951]

I'm getting this error as well:

my code:

import os
import formulas

fpath, dir = 'input2.xlsx', 'output'

xl_model = formulas.ExcelModel().loads(fpath).finish(circular=True)
xl_model.calculate(
    inputs={
        "'[input2.xlsx]Input'C3": "A Test"
    },
    outputs=[
        "'[input2.xlsx]Output'!C3"
    ])

xl_model.write(fpath)

  File ".\excel.py", line 15, in <module>
    xl_model.write(fpath)
  File ".\lib\site-packages\formulas\excel\__init__.py", line 447, in write
    r = Ranges().push(k, r)
  File ".\lib\site-packages\formulas\ranges.py", line 166, in push
    rng = self.get_range(self.format_range, ref, context)
  File ".\lib\site-packages\formulas\ranges.py", line 158, in get_range
    for k, v in _re_range.match(ref).groupdict().items():
AttributeError: 'NoneType' object has no attribute 'groupdict'

Is there a way I can identify what in my spreadsheet is causing this? It's a huge spreadsheet, and I haven't created it myself.