Is your feature request related to a problem? Please describe.
I am looking to get the formulas stored along with the evaluated value in each cell for the saved files using xl_model.write(dirpath=dir_output)

Describe the solution you'd like
A clear and concise description of what you want to happen.
Currently only the evaluated value is getting stored in the new file but it would be really helpful to stored the formulas as well.

My use case is that user can again and again modify the excel sheet formulas and on each new save, I want to evaluate new values and replace the original file with updated formula / updated value in original file.

Describe alternatives you've considered
I am trying to implement something using formulas and openpyxl but would need some suggestions, how this could be implemented?

Much appreciated!

Describe the bug
When using an 8-value SWITCH statement, model.calculate(inputs=inputs, outputs=outputs) fails to set these SWITCH values, causing cascading errors.
To Reproduce
(Files attached)
bot.xlsx
Code:

import formulas
import os
import itertools

from rlbot.agents.base_agent import BaseAgent, SimpleControllerState
from rlbot.utils.structures.game_data_struct import GameTickPacket


fname = os.path.join(os.path.dirname(os.path.abspath(__file__)), "bot.xlsx")
model = formulas.ExcelModel().loads(fname).finish()


class ExcelAgent(BaseAgent):
    def __init__(self, sheet_name="bot.xlsx"):
        self.model = model
        self.sheet_name = sheet_name
        self.chars = [self.add_prefix(f"{char}{num}") for char, num in
                      itertools.product("ABCDEFGH", [27])]
    
    def add_prefix(self, cell):
        return f"'[{self.sheet_name.upper()}]SHEET1'!{cell}"
        
    def get_inputs(self, packet):
        ball_data = {
            "B2": packet.game_ball.physics.location.x,
            "B3": packet.game_ball.physics.location.y,
            "B4": packet.game_ball.physics.location.z,
            "B5": packet.game_ball.physics.velocity.x,
            "B6": packet.game_ball.physics.velocity.y,
            "B7": packet.game_ball.physics.velocity.z,
            "B8": packet.game_ball.physics.rotation.pitch,
            "B9": packet.game_ball.physics.rotation.roll,
            "B10": packet.game_ball.physics.rotation.yaw,
            "B11": packet.game_ball.physics.angular_velocity.x,
            "B12": packet.game_ball.physics.angular_velocity.y,
            "B13": packet.game_ball.physics.angular_velocity.z,
        }
        
        cars = [
            {
                chr(67+i) + "2": car.physics.location.x,
                chr(67+i) + "3": car.physics.location.y,
                chr(67+i) + "4": car.physics.location.z,
                chr(67+i) + "5": car.physics.velocity.x,
                chr(67+i) + "6": car.physics.velocity.y,
                chr(67+i) + "7": car.physics.velocity.z,
                chr(67+i) + "8": car.physics.rotation.pitch,
                chr(67+i) + "9": car.physics.rotation.roll,
                chr(67+i) + "10": car.physics.rotation.yaw,
                chr(67+i) + "11": car.physics.angular_velocity.x,
                chr(67+i) + "12": car.physics.angular_velocity.y,
                chr(67+i) + "13": car.physics.angular_velocity.z,
                chr(67+i) + "14": car.team,
                chr(67+i) + "15": int(car.jumped),
                chr(67+i) + "16": int(car.double_jumped),
                chr(67+i) + "17": car.boost
            }
            for i, car in enumerate(packet.game_cars) if i < 8
        ]
        
        other = {
            "A23": packet.num_cars,
            "B23": self.index
        }
        
        full = {}
        full.update(ball_data)
        for car in cars:
            full.update(car)
        full.update(other)
        
        return {self.add_prefix(k): v for k, v in full.items()}
    
    def get_out(self, solution):
        out = []
            
        for cellname in self.chars:
            print(cellname, cellname in solution.outputs)
            
            out.append(solution[cellname].value[0][0])
        
        return SimpleControllerState(*out)
        
    def get_output(self, packet: GameTickPacket) -> SimpleControllerState:
        inputs = self.get_inputs(packet)
        
        print("IN:", inputs)
        
        solution = self.model.calculate(
            inputs = inputs,
        
            outputs = self.chars
        )
        
        print("OUT:", self.chars)
        print(solution.outputs)
        
        return self.get_out(solution)


if __name__ == "__main__":  
    sheet_name="bot.xlsx"
    # fname = os.path.join(os.path.dirname(os.path.abspath(__file__)), sheet_name)
    inputs = {"'[BOT.XLSX]Sheet1'!B2": 0.0, "'[BOT.XLSX]Sheet1'!B3": 0.0, "'[BOT.XLSX]Sheet1'!B4": 92.73999786376953, "'[BOT.XLSX]Sheet1'!B5": 0.0, "'[BOT.XLSX]Sheet1'!B6": 0.0, "'[BOT.XLSX]Sheet1'!B7": 0.0, "'[BOT.XLSX]Sheet1'!B8": 0.0, "'[BOT.XLSX]Sheet1'!B9": 0.0, "'[BOT.XLSX]Sheet1'!B10": 0.0, "'[BOT.XLSX]Sheet1'!B11": 0.0, "'[BOT.XLSX]Sheet1'!B12": 0.0, "'[BOT.XLSX]Sheet1'!B13": 0.0, "'[BOT.XLSX]Sheet1'!C2": 1951.989990234375, "'[BOT.XLSX]Sheet1'!C3": -2463.969970703125, "'[BOT.XLSX]Sheet1'!C4": 17.049999237060547, "'[BOT.XLSX]Sheet1'!C5": 0.0, "'[BOT.XLSX]Sheet1'!C6": 0.0, "'[BOT.XLSX]Sheet1'!C7": 8.361000061035156, "'[BOT.XLSX]Sheet1'!C8": -0.016682041808962822, "'[BOT.XLSX]Sheet1'!C9": 0.0, "'[BOT.XLSX]Sheet1'!C10": 2.356194496154785, "'[BOT.XLSX]Sheet1'!C11": -0.00030999997397884727, "'[BOT.XLSX]Sheet1'!C12": -0.0002099999983329326, "'[BOT.XLSX]Sheet1'!C13": 0.0, "'[BOT.XLSX]Sheet1'!C14": 0, "'[BOT.XLSX]Sheet1'!C15": 0, "'[BOT.XLSX]Sheet1'!C16": 0, "'[BOT.XLSX]Sheet1'!C17": 34, "'[BOT.XLSX]Sheet1'!D2": -1951.969970703125, "'[BOT.XLSX]Sheet1'!D3": 2463.9599609375, "'[BOT.XLSX]Sheet1'!D4": 77.98670959472656, "'[BOT.XLSX]Sheet1'!D5": 0.22100000083446503, "'[BOT.XLSX]Sheet1'!D6": -0.23099999129772186, "'[BOT.XLSX]Sheet1'!D7": 248.65525817871094, "'[BOT.XLSX]Sheet1'!D8": -0.009683254174888134, "'[BOT.XLSX]Sheet1'!D9": 0.0, "'[BOT.XLSX]Sheet1'!D10": -0.7853981852531433, "'[BOT.XLSX]Sheet1'!D11": -0.00010999999358318746, "'[BOT.XLSX]Sheet1'!D12": 0.0, "'[BOT.XLSX]Sheet1'!D13": 0.0, "'[BOT.XLSX]Sheet1'!D14": 1, "'[BOT.XLSX]Sheet1'!D15": 1, "'[BOT.XLSX]Sheet1'!D16": 0, "'[BOT.XLSX]Sheet1'!D17": 34, "'[BOT.XLSX]Sheet1'!E2": 0.0, "'[BOT.XLSX]Sheet1'!E3": 0.0, "'[BOT.XLSX]Sheet1'!E4": 0.0, "'[BOT.XLSX]Sheet1'!E5": 0.0, "'[BOT.XLSX]Sheet1'!E6": 0.0, "'[BOT.XLSX]Sheet1'!E7": 0.0, "'[BOT.XLSX]Sheet1'!E8": 0.0, "'[BOT.XLSX]Sheet1'!E9": 0.0, "'[BOT.XLSX]Sheet1'!E10": 0.0, "'[BOT.XLSX]Sheet1'!E11": 0.0, "'[BOT.XLSX]Sheet1'!E12": 0.0, "'[BOT.XLSX]Sheet1'!E13": 0.0, "'[BOT.XLSX]Sheet1'!E14": 0, "'[BOT.XLSX]Sheet1'!E15": 0, "'[BOT.XLSX]Sheet1'!E16": 0, "'[BOT.XLSX]Sheet1'!E17": 0, "'[BOT.XLSX]Sheet1'!F2": 0.0, "'[BOT.XLSX]Sheet1'!F3": 0.0, "'[BOT.XLSX]Sheet1'!F4": 0.0, "'[BOT.XLSX]Sheet1'!F5": 0.0, "'[BOT.XLSX]Sheet1'!F6": 0.0, "'[BOT.XLSX]Sheet1'!F7": 0.0, "'[BOT.XLSX]Sheet1'!F8": 0.0, "'[BOT.XLSX]Sheet1'!F9": 0.0, "'[BOT.XLSX]Sheet1'!F10": 0.0, "'[BOT.XLSX]Sheet1'!F11": 0.0, "'[BOT.XLSX]Sheet1'!F12": 0.0, "'[BOT.XLSX]Sheet1'!F13": 0.0, "'[BOT.XLSX]Sheet1'!F14": 0, "'[BOT.XLSX]Sheet1'!F15": 0, "'[BOT.XLSX]Sheet1'!F16": 0, "'[BOT.XLSX]Sheet1'!F17": 0, "'[BOT.XLSX]Sheet1'!G2": 0.0, "'[BOT.XLSX]Sheet1'!G3": 0.0, "'[BOT.XLSX]Sheet1'!G4": 0.0, "'[BOT.XLSX]Sheet1'!G5": 0.0, "'[BOT.XLSX]Sheet1'!G6": 0.0, "'[BOT.XLSX]Sheet1'!G7": 0.0, "'[BOT.XLSX]Sheet1'!G8": 0.0, "'[BOT.XLSX]Sheet1'!G9": 0.0, "'[BOT.XLSX]Sheet1'!G10": 0.0, "'[BOT.XLSX]Sheet1'!G11": 0.0, "'[BOT.XLSX]Sheet1'!G12": 0.0, "'[BOT.XLSX]Sheet1'!G13": 0.0, "'[BOT.XLSX]Sheet1'!G14": 0, "'[BOT.XLSX]Sheet1'!G15": 0, "'[BOT.XLSX]Sheet1'!G16": 0, "'[BOT.XLSX]Sheet1'!G17": 0, "'[BOT.XLSX]Sheet1'!H2": 0.0, "'[BOT.XLSX]Sheet1'!H3": 0.0, "'[BOT.XLSX]Sheet1'!H4": 0.0, "'[BOT.XLSX]Sheet1'!H5": 0.0, "'[BOT.XLSX]Sheet1'!H6": 0.0, "'[BOT.XLSX]Sheet1'!H7": 0.0, "'[BOT.XLSX]Sheet1'!H8": 0.0, "'[BOT.XLSX]Sheet1'!H9": 0.0, "'[BOT.XLSX]Sheet1'!H10": 0.0, "'[BOT.XLSX]Sheet1'!H11": 0.0, "'[BOT.XLSX]Sheet1'!H12": 0.0, "'[BOT.XLSX]Sheet1'!H13": 0.0, "'[BOT.XLSX]Sheet1'!H14": 0, "'[BOT.XLSX]Sheet1'!H15": 0, "'[BOT.XLSX]Sheet1'!H16": 0, "'[BOT.XLSX]Sheet1'!H17": 0, "'[BOT.XLSX]Sheet1'!I2": 0.0, "'[BOT.XLSX]Sheet1'!I3": 0.0, "'[BOT.XLSX]Sheet1'!I4": 0.0, "'[BOT.XLSX]Sheet1'!I5": 0.0, "'[BOT.XLSX]Sheet1'!I6": 0.0, "'[BOT.XLSX]Sheet1'!I7": 0.0, "'[BOT.XLSX]Sheet1'!I8": 0.0, "'[BOT.XLSX]Sheet1'!I9": 0.0, "'[BOT.XLSX]Sheet1'!I10": 0.0, "'[BOT.XLSX]Sheet1'!I11": 0.0, "'[BOT.XLSX]Sheet1'!I12": 0.0, "'[BOT.XLSX]Sheet1'!I13": 0.0, "'[BOT.XLSX]Sheet1'!I14": 0, "'[BOT.XLSX]Sheet1'!I15": 0, "'[BOT.XLSX]Sheet1'!I16": 0, "'[BOT.XLSX]Sheet1'!I17": 0, "'[BOT.XLSX]Sheet1'!J2": 0.0, "'[BOT.XLSX]Sheet1'!J3": 0.0, "'[BOT.XLSX]Sheet1'!J4": 0.0, "'[BOT.XLSX]Sheet1'!J5": 0.0, "'[BOT.XLSX]Sheet1'!J6": 0.0, "'[BOT.XLSX]Sheet1'!J7": 0.0, "'[BOT.XLSX]Sheet1'!J8": 0.0, "'[BOT.XLSX]Sheet1'!J9": 0.0, "'[BOT.XLSX]Sheet1'!J10": 0.0, "'[BOT.XLSX]Sheet1'!J11": 0.0, "'[BOT.XLSX]Sheet1'!J12": 0.0, "'[BOT.XLSX]Sheet1'!J13": 0.0, "'[BOT.XLSX]Sheet1'!J14": 0, "'[BOT.XLSX]Sheet1'!J15": 0, "'[BOT.XLSX]Sheet1'!J16": 0, "'[BOT.XLSX]Sheet1'!J17": 0, "'[BOT.XLSX]Sheet1'!A23": 2, "'[BOT.XLSX]Sheet1'!B23": 0}
    outputs = ["'[BOT.XLSX]Sheet1'!A27", "'[BOT.XLSX]Sheet1'!B27", "'[BOT.XLSX]Sheet1'!C27", "'[BOT.XLSX]Sheet1'!D27", "'[BOT.XLSX]Sheet1'!E27", "'[BOT.XLSX]Sheet1'!F27", "'[BOT.XLSX]Sheet1'!G27", "'[BOT.XLSX]Sheet1'!H27"]
    solution = model.calculate(inputs=inputs, outputs=outputs)
    # model.dsp.plot(view=True)
    print(ExcelAgent().get_out(solution))

Desktop:

• OS: Windows
• Version 10

Is your feature request related to a problem? Please describe.
It would be better for newcommers to know how to install the dev version of the package to assist with testing

Describe the solution you'd like
Add something along these lines on the READ ME at formulas homepage

To install the development version of formulas you can use:

pip install https://github.com/vinci1it2000/formulas/archive/dev.zip

Describe alternatives you've considered
This is the easiest way that I found to install a branch version via pip. I tested it just now and it works just fine. You can find more suggestions here.

Additional context
This might help people to test and debug the package.

The structure of formulas\excel module seems to be prohibiting building of an exe (at least with pyinstaller on Windows).
The ExcelModel class is in excel_init_.py rather than in a file ExcelModel or similar.
This structure seems to cause a blindspot for pyinstaller, whereas VSCode can import ExcelModel.
Attempts with alternative importation statements, such as "from formulas.excel.init import ExcelModel" seem to work in both the VSCode IDE and pyinstaller (according to the latter's diagnostics), but the relative import paths in ExcelModel (such as ..ranges) fail in pyinstaller because of the bizarre import path used.
Attempts at using --hidden-import= workarounds on the pyinstaller command line do not fix the problem either.

To reproduce, use one of the import statement formats mentioned below, instantiate ExcelModel from formulas, open a file & worksheet and call the calculate method.
This functions correctly in VSCode.
Attempt to build a Windows exe using pyinstaller.
Run. (A console build is best to see errors). ModuleNotFound... (the module concerned depends on which import statement was used.)

When using: import formulas statement...
Exception in ... No module named 'formulas.functions.info'
Exception in ... object has no attribute 'calculate'

When using: from formulas.excel.init import ExcelModel statement...
File "site-packages\formulas\excel_init_.py", line 29, in
ModuleNotFoundError: No module named 'formulas.excel.ranges'

Pyinstaller should be assisted/allowed to find class ExcelModel, or at least recognise that it is in module formulas.excel and the relative-path import statements inside formulas\excel files should resolve correctly in an executable created by pyinstaller.

I have passed the same info to the pyinstaller forum, https://groups.google.com/forum/#!forum/pyinstaller .
See subject containing 'formulas' and 'pyinstaller'.
My impression is that there might be two problems - perhaps a structural issue in formulas and a blind spot in pyinstaller relating to classes defined in init.py files .

• OS: Windows 10
• Python 3.8
• formulas 1.0.0
• pyinstaller 3.6

Thank you for the quick response to my last issue. And my apologies for sending another, but I am discovering these as I parse a large workbook.

Parsing "=10^-2" is also giving an error:

---------------------------------------------------------------------------
FormulaError                              Traceback (most recent call last)
<ipython-input-17-b1d71417b7f0> in <module>()
      4 parser = Parser()
      5 
----> 6 parser.ast(expression='=10^-2')

c:\sandboxes\excel2py\formulas\formulas\parser.py in ast(self, expression, context)
     54                     pass
     55             else:
---> 56                 raise FormulaError(expression)
     57         Parenthesis(')').ast(tokens, stack, builder)
     58         tokens = tokens[1:-1]

FormulaError: ('Not a valid formula:\n%s', '=10^-2')

> c:\sandboxes\excel2py\formulas\formulas\parser.py(56)ast()
     54                     pass
     55             else:
---> 56                 raise FormulaError(expression)
     57         Parenthesis(')').ast(tokens, stack, builder)
     58         tokens = tokens[1:-1]

Wrapping the negative exponent in parenthesis fixes the problem, but it can be a chore to go into the workbook and fix all the cases where there is a X^-Y.

I hope to put in a pull request with some of the pythonized Excel functions I have shortly. I'll make sure to add tests and follow your convention.

Is there any chace you could support one of the Apache approved license (https://apache.org/legal/resolved.html#category-a) instead of EUPL ?

Package cannot handle cells in which a single quote is used to escape an equal sign at the beginning of the cell, i.e. in my excel file there is a cell with content "'= this is a test" (please note single quote at the beginning of the string) which raises the error "formulas.errors.FormulaError: ('Not a valid formula:\n%s', '= this is a test')".

Describe the solution you'd like
Implement INDEX, MATCH, and XOR functions.

This is using formulas 0.4.0 under python 3.7.3.

Steps to reproduce:

pip install formulas
python
>>> import formulas
>>> formulas.get_functions()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/albert/.virtualenvs/formulas/lib/python3.7/site-packages/formulas/functions/__init__.py", line 310, in get_functions
    functions.update(importlib.import_module(name, __name__).FUNCTIONS)
  File "/home/albert/.virtualenvs/formulas/lib/python3.7/importlib/__init__.py", line 127, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 1006, in _gcd_import
  File "<frozen importlib._bootstrap>", line 983, in _find_and_load
  File "<frozen importlib._bootstrap>", line 967, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 677, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 728, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/home/albert/.virtualenvs/formulas/lib/python3.7/site-packages/formulas/functions/eng.py", line 142, in <module>
    FUNCTIONS[k] = wrap_func(hex2dec2bin2oct(k))
  File "/home/albert/.virtualenvs/formulas/lib/python3.7/site-packages/formulas/functions/eng.py", line 133, in hex2dec2bin2oct
    _func = sh.DispatchPipe(dsp, function_id, [i, 'places'], [o])
  File "/home/albert/.virtualenvs/formulas/lib/python3.7/site-packages/schedula/utils/dsp.py", line 1081, in __init__
    self.pipe = self._set_pipe()
  File "/home/albert/.virtualenvs/formulas/lib/python3.7/site-packages/schedula/utils/dsp.py", line 1097, in _set_pipe
    return [_make_tks(*v['task'][-1]) for v in self._sol.pipe.values()]
  File "/home/albert/.virtualenvs/formulas/lib/python3.7/site-packages/schedula/utils/sol.py", line 345, in pipe
    return get_full_pipe(self)
  File "/home/albert/.virtualenvs/formulas/lib/python3.7/site-packages/schedula/utils/alg.py", line 462, in get_full_pipe
    sub_sol = s.workflow.node[path[-1]]['solution']
AttributeError: 'DiGraph' object has no attribute 'node'

How does this compare to Pycel feature-wise?
https://github.com/dgorissen/pycel

Package cannot handle cells with formulas that include multiple line breaks in a row, i.e. in my excel file there is a cell with the following content:

=IF(

A1=1;

1;

2 )

The error that is raised is:

formulas.errors.FormulaError: ('Not a valid formula:\n%s', '=IF(\n\nA1=1,\n\n1,\n\n2)')

Program execution encountered this formula, the program reported an error

Steps to reproduce the behavior:

1. xl_model = ExcelModel().loads(filename).finish()
2. xl_model.calculate()
3. See error
   

I'm working on converting a large excel workbook into a python tool. The workbook has gotten complex enough that it takes a long time to run, and maintaining it is painstaking. This great package you've built largely solves my first problem - I could convert the workbook into an ExcelModel to be used behind an API, which would speed things up and enable parallelization. However, it doesn't make my tool easier to maintain.

I would like to be able to convert the DAGs that ExcelModel and Parser build into a readable python module. Each function in the DAG would become an atomic function in the module. As an example: if we had an excel sheet where the D1 cell contains the formula =SUM(A1:C1), this tool might produce a function like this:

def d1(a1_to_c1: list) -> float:
    return sum(a1_to_c1)

This would lay out working (albeit not very clear) code that would be the starting point for turning a workbook into a clean python project.

I started fiddling with this idea, but my ignorance of the inner workings of formulas and schedula made this a very slow-going process. I was hoping someone might have some insights into how this could be done. Totally understood if you think this shouldn't be a feature of this package, but I would really appreciate some ideas regardless.

Describe the bug

The formula looks valid and is valid in LibreOffice Calc 6.4.4.2
if the sign '>=' is replaced to '>' or to '=' the formula turns to be validated.

To Reproduce

import formulas
formulas.Parser().ast('=IF(A1>=-1,1,0)')

formulas.errors.FormulaError: ('Not a valid formula:\n%s', '=IF(A1>=-2,1,0)')

Desktop (please complete the following information):

• OS: Ubuntu 20.04
• Python 3.8.2
• Version formulas-1.0.0

Is your feature request related to a problem? Please describe.
A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

Describe the solution you'd like
A clear and concise description of what you want to happen.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

I dont see support for the STDEV excel function, any possibility to add it?

Thanks!

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()

Steps to reproduce:

1. mkdir formulas-too-large && cd formulas-too-large
2. pipenv install
3. pipenv install formulas
4. du -sh ~/.local/share/virtualenvs/formulas-too-large-*/lib/python3.6/site-packages/
5. pipenv graph

Expected results: the packages formulas would reasonably need to fulfil its function.

Actual results: 150M of dependencies (including *.pyc, excluding pip itself)

formulas==0.0.10
  - numpy [required: Any, installed: 1.14.5]
  - openpyxl [required: Any, installed: 2.5.4]
    - et-xmlfile [required: Any, installed: 1.0.1]
    - jdcal [required: Any, installed: 1.4]
  - regex [required: Any, installed: 2018.6.21]
  - schedula [required: >=0.1.18, installed: 0.1.19]
    - bs4 [required: Any, installed: 0.0.1]
      - beautifulsoup4 [required: Any, installed: 4.6.0]
    - dill [required: !=0.2.7, installed: 0.2.8.2]
    - docutils [required: Any, installed: 0.14]
    - flask [required: Any, installed: 1.0.2]
      - click [required: >=5.1, installed: 6.7]
      - itsdangerous [required: >=0.24, installed: 0.24]
      - Jinja2 [required: >=2.10, installed: 2.10]
        - MarkupSafe [required: >=0.23, installed: 1.0]
      - Werkzeug [required: >=0.14, installed: 0.14.1]
    - graphviz [required: Any, installed: 0.8.4]
    - jinja2 [required: Any, installed: 2.10]
      - MarkupSafe [required: >=0.23, installed: 1.0]
    - lxml [required: Any, installed: 4.2.3]
    - networkx [required: >=2.0.0, installed: 2.1]
      - decorator [required: >=4.1.0, installed: 4.3.0]
    - Pygments [required: Any, installed: 2.2.0]
    - regex [required: Any, installed: 2018.6.21]
    - sphinx [required: Any, installed: 1.7.5]
      - alabaster [required: >=0.7,<0.8, installed: 0.7.11]
      - babel [required: !=2.0,>=1.3, installed: 2.6.0]
        - pytz [required: >=0a, installed: 2018.5]
      - docutils [required: >=0.11, installed: 0.14]
      - imagesize [required: Any, installed: 1.0.0]
      - Jinja2 [required: >=2.3, installed: 2.10]
        - MarkupSafe [required: >=0.23, installed: 1.0]
      - packaging [required: Any, installed: 17.1]
        - pyparsing [required: >=2.0.2, installed: 2.2.0]
        - six [required: Any, installed: 1.11.0]
      - Pygments [required: >=2.0, installed: 2.2.0]
      - requests [required: >=2.0.0, installed: 2.19.1]
        - certifi [required: >=2017.4.17, installed: 2018.4.16]
        - chardet [required: <3.1.0,>=3.0.2, installed: 3.0.4]
        - idna [required: >=2.5,<2.8, installed: 2.7]
        - urllib3 [required: >=1.21.1,<1.24, installed: 1.23]
      - setuptools [required: Any, installed: 40.0.0]
      - six [required: >=1.5, installed: 1.11.0]
      - snowballstemmer [required: >=1.1, installed: 1.2.1]
      - sphinxcontrib-websupport [required: Any, installed: 1.1.0]
  - sphinx [required: Any, installed: 1.7.5]
    - alabaster [required: >=0.7,<0.8, installed: 0.7.11]
    - babel [required: !=2.0,>=1.3, installed: 2.6.0]
      - pytz [required: >=0a, installed: 2018.5]
    - docutils [required: >=0.11, installed: 0.14]
    - imagesize [required: Any, installed: 1.0.0]
    - Jinja2 [required: >=2.3, installed: 2.10]
      - MarkupSafe [required: >=0.23, installed: 1.0]
    - packaging [required: Any, installed: 17.1]
      - pyparsing [required: >=2.0.2, installed: 2.2.0]
      - six [required: Any, installed: 1.11.0]
    - Pygments [required: >=2.0, installed: 2.2.0]
    - requests [required: >=2.0.0, installed: 2.19.1]
      - certifi [required: >=2017.4.17, installed: 2018.4.16]
      - chardet [required: <3.1.0,>=3.0.2, installed: 3.0.4]
      - idna [required: >=2.5,<2.8, installed: 2.7]
      - urllib3 [required: >=1.21.1,<1.24, installed: 1.23]
    - setuptools [required: Any, installed: 40.0.0]
    - six [required: >=1.5, installed: 1.11.0]
    - snowballstemmer [required: >=1.1, installed: 1.2.1]
    - sphinxcontrib-websupport [required: Any, installed: 1.1.0]

The following dependencies seem unreasonable:

• bs4 (0.7M)
• docutils (3.8M)
• flask (4.0M)
• graphviz (0.1M)
• jinja2
• lxml (14.0M)
• Pygments (6.1M)
• sphinx (36M, avoiding double-counting)

And the following seem questionable, so perhaps their necessity should be documented?

• openpyxl (2.6M)
• networkx (14M, seems like overkill)

Numpy is 57MB -- that explains the rest.

This bug report implicates schedula's dependencies, too.

Describe the bug
Formulas fails to parse a sum with a range pointing to other worksheets in the same workbook. I`m using formulas version 1.0.0

To Reproduce
I attached a minimal example example file Test.xlsx that reproduces the bug. Just download it and run with the basic code below.

Steps to reproduce the behavior:

1. Run the following code:

import formulas
print(formulas.__version__)

xl_model = formulas.ExcelModel().loads('Test.xlsx').finish()

2. I get the following output with a error message:

1.0.0
---------------------------------------------------------------------------
FormulaError                              Traceback (most recent call last)
<ipython-input-6-138aad8c29be> in <module>
      2 print(formulas.__version__)
      3 
----> 4 xl_model = formulas.ExcelModel().loads('Test.xlsx').finish()

~\Anaconda3\envs\economy\lib\site-packages\formulas\excel\__init__.py in loads(self, *file_names)
     85     def loads(self, *file_names):
     86         for filename in file_names:
---> 87             self.load(filename)
     88         return self
     89 

~\Anaconda3\envs\economy\lib\site-packages\formulas\excel\__init__.py in load(self, filename)
     89 
     90     def load(self, filename):
---> 91         book, context = self.add_book(filename)
     92         self.pushes(*book.worksheets, context=context)
     93         return self

~\Anaconda3\envs\economy\lib\site-packages\formulas\excel\__init__.py in add_book(self, book, context, data_only)
    139 
    140         if 'references' not in data:
--> 141             data['references'] = self.add_references(book, context=context)
    142 
    143         return book, context

~\Anaconda3\envs\economy\lib\site-packages\formulas\excel\__init__.py in add_references(self, book, context)
     72         refs, nodes = {}, set()
     73         for n in book.defined_names.definedName:
---> 74             ref = Ref(n.name.upper(), '=%s' % n.value, context).compile()
     75             nodes.update(ref.add(self.dsp, context=context))
     76             refs[ref.output] = None

~\Anaconda3\envs\economy\lib\site-packages\formulas\cell.py in __init__(self, reference, value, context)
    158 class Ref(Cell):
    159     def __init__(self, reference, value, context=None):
--> 160         super(Ref, self).__init__(None, value, context)
    161         self.output = range2parts(None, ref=reference, **context)['name']
    162 

~\Anaconda3\envs\economy\lib\site-packages\formulas\cell.py in __init__(self, reference, value, context)
     71         self.tokens, self.builder, self.value = (), None, sh.EMPTY
     72         if isinstance(value, str) and self.parser.is_formula(value):
---> 73             self.tokens, self.builder = self.parser.ast(value, context=context)
     74         elif value is not None:
     75             self.value = value

~\Anaconda3\envs\economy\lib\site-packages\formulas\parser.py in ast(self, expression, context)
     57                     pass
     58             else:
---> 59                 raise FormulaError(expression)
     60         Parenthesis(')').ast(tokens, stack, builder)
     61         tokens = tokens[1:-1]

FormulaError: ('Not a valid formula:\n%s', "='Sheet 1'!#REF!")

Expected behavior
Calculate the sum as shown in the screenshot.

Screenshots

Desktop (please complete the following information):

• OS: Windows
• Version 7

Additional context
I installed the most recent version of formulas with conda install -c conda-forge/label/cf202003 formulas. The problem also happens with formulas 0.4.0, so this is not a regression.

I've run into a change from formulas 0.0.7 to 0.0.10 which is causing me trouble. The result type no longer seems to depend on the input type, or really have a type at all.

Old: multiplication on int returned int

$ pip list | grep formulas
formulas                 0.0.7    
$ python3
Python 3.6.2 |Continuum Analytics, Inc.| (default, Jul 20 2017, 13:14:59) 
[GCC 4.2.1 Compatible Apple LLVM 6.0 (clang-600.0.57)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> from formulas import Parser
>>> code = Parser().ast('=B1*2')[1].compile()
>>> out = code(10)
>>> out
20
>>> type(out)
<class 'int'>
>>> 

New: multiplication on int returns OperatorArray

$ pip list | grep formulas
formulas                 0.0.10    
$ python3
Python 3.6.2 |Continuum Analytics, Inc.| (default, Jul 20 2017, 13:14:59) 
[GCC 4.2.1 Compatible Apple LLVM 6.0 (clang-600.0.57)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> from formulas import Parser
>>> code = Parser().ast('=B1*2')[1].compile()
>>> out = code(10)
>>> out
OperatorArray(20.0, dtype=object)
>>> type(out)
<class 'formulas.functions.operators.OperatorArray'>

The output seems to be OperatorArray regardless of whether the actual output is int, float, or string. Is there any way to recover the result type from the OperatorArray?

Thanks for all your work on this project. We're very happy to be using it for Workbench.

Looks like functions with numbers in their names cannot be parsed correctly, e.g., ATAN2, DAYS360.

Trying to parse a sheet with the equation =ATAN2(10,2) yields:

FormulaError: ('Not a valid formula:\n%s', '=ATAN2(10,2)')

Describe the bug
Named cells that start with a real cell name such as CG2033_TEST are not supported. The parser finds CG2033 and _TEST as different portions when running through the _re_range regular expression. Other named cells that do not start with a regex that could be interpreted as a valid cell work fine.

To Reproduce
Simply name a cell CG2033_TEST and then in another cell enter the formula =CG2033_TEST. This works fine in excel, the second cell will always equal whatever the input is for the first cell. This fails in formulas. This did work in previous version 0.3.

Expected behavior
Named cell is interpretted as a ref rather than a cell.

Additional context
I tried adjusting the regular expression in operand.py with many iterations. I was unable to get it to both work for the named cell case and normal cells. If you let me know which part of the regex I should update I am happy to give it a try. I was trying to get negative lookahead to rule out the case a cell name is followed by underscore or letter but couldn't get it to work.

Describe the bug
Trying to to load a Spreadsheet with a formula containing a linebreak fails with an exception:

File "/home/ccaveayl/projects/formulas/model2050.py", line 36, in init
self.model = formulas.ExcelModel().loads(str(xlsx_path)).finish()
File "/home/ccaveayl/projects/formulas/.venv/lib/python3.9/site-packages/formulas/excel/init.py", line 87, in loads
self.load(filename)
File "/home/ccaveayl/projects/formulas/.venv/lib/python3.9/site-packages/formulas/excel/init.py", line 92, in load
self.pushes(*book.worksheets, context=context)
File "/home/ccaveayl/projects/formulas/.venv/lib/python3.9/site-packages/formulas/excel/init.py", line 97, in pushes
self.push(ws, context=context)
File "/home/ccaveayl/projects/formulas/.venv/lib/python3.9/site-packages/formulas/excel/init.py", line 110, in push
self.add_cell(
File "/home/ccaveayl/projects/formulas/.venv/lib/python3.9/site-packages/formulas/excel/init.py", line 209, in add_cell
cell = Cell(crd, val, context=ctx).compile(references=references)
File "/home/ccaveayl/projects/formulas/.venv/lib/python3.9/site-packages/formulas/cell.py", line 73, in init
self.tokens, self.builder = self.parser.ast(value, context=context)
File "/home/ccaveayl/projects/formulas/.venv/lib/python3.9/site-packages/formulas/parser.py", line 53, in ast
token.ast(tokens, stack, builder)
File "/home/ccaveayl/projects/formulas/.venv/lib/python3.9/site-packages/formulas/tokens/operator.py", line 91, in ast
pred = self.pred
File "/home/ccaveayl/projects/formulas/.venv/lib/python3.9/site-packages/formulas/tokens/operator.py", line 76, in pred
return self._precedences[self.name]
KeyError: '\n'

To Reproduce

A minimal example is provided by test_line_break.xlsx.

Simply try to load this file with formulas.

Expected behavior

Spreadsheet should load correctly with line break ignored

Screenshots

N/A

Desktop (please complete the following information):

• OS: Arch Linux
• Version: N/A

Additional context

I've temporarily resolved this by changing:

to

    self.tokens, self.builder = self.parser.ast(value.replace("\n", ""), context=context) 

I suspect there is a better approach to resolving however. If you're able to advise on the best approach I'd be happy to put together a PR for this.

VLOOKUP doesn't work
Vlookup from one sheet to another throws 'negative dimensions are not allowed'

Steps to reproduce the behavior:

1. Sample file
   Book1.xlsx
2. Sample Code:
   sample.txt
3. Error - Stack trace
   Failed DISPATCHING '='[BOOK.XLSX]SHEET2'!A:B' due to:
   ValueError('negative dimensions are not allowed',)
   Traceback (most recent call last):
   File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/schedula/utils/sol.py", line 748, in _set_function_node_output
   res = self._evaluate_function(args, node_id, node_attr, attr)
   File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/schedula/utils/sol.py", line 533, in _evaluate_function
   res = func(*args)
   File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/formulas/cell.py", line 166, in call
   return _assemble_values(base, values, sh.EMPTY)
   File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/formulas/ranges.py", line 103, in _assemble_values
   res = np.empty(_shape(**base), object)
   ValueError: negative dimensions are not allowed

Expected behavior
VLOOKUP is to be evaluated

I have an excel file with some customized functions written in vba, I'm trying to map them to python function. After a quick looking at the formulas codes, I guess I could do something like

class MyBuilder(formulas.builder.AstBuilder):
    def __init__(self, *args, **kwargs):
        dsp = schedula.Dispatcher(raises=True)
        dsp.add_function('func1', lambda x: x+1)
        kwargs['dsp'] = dsp
        super(MyBuilder, self).__init__(*args, **kwargs)

formulas.parser.Parser.ast_builder = MyBuilder

But looks it isn't as easy as that. @vinci1it2000 really appreciate if you could give me some hints.

Describe the bug
Formulas like =5% cannot be parsed.

To Reproduce

>>>import formulas
>>> formulas.Parser().ast('=5%')
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "D:\Apps\Continuum\miniconda3\lib\site-packages\formulas\parser.py", line 68, in ast
    builder.finish()
  File "D:\Apps\Continuum\miniconda3\lib\site-packages\formulas\builder.py", line 80, in finish
    self.get_node_id(token)
  File "D:\Apps\Continuum\miniconda3\lib\site-packages\formulas\builder.py", line 71, in get_node_id
    kw['default_value'] = token.compile()
  File "D:\Apps\Continuum\miniconda3\lib\site-packages\formulas\tokens\operand.py", line 66, in compile
    return eval(self.name.capitalize())
  File "<string>", line 1
    5%
     ^
SyntaxError: unexpected EOF while parsing

With the move to 0.0.8, constructing an IRR function from an excel expression e.g. =IRR(A1:A3) and then trying to utilise that function produces a shapeless array. It contains the correct answer, but A) it shouldn't be an array B) because it is shapeless you can't index in to retrieve the answer.

Currently hacking around it for my own purposes by doing something like:

result = constructed_irr_function(*args)
if type(result) == formulas.formulas.Array and not result.shape:
    result = result.reshape(1,)[0]

But I will try to look into the underlying reason over the next couple of days.

Describe the bug
The IF() method as implemented only allows as True and False return expressions those that evaluate as number but not strings.

To Reproduce

    >>> import formulas
    >>> parser = formulas.Parser()
    >>> f = parser.ast('=IF(A1="a", "1", "2")')[1].compile()
    >>> f("a")
    IfArray(1.0, dtype=object)
    >>> f = parser.ast('=IF(A1="a", "1a", "2")')[1].compile()
    >>> f("a")
    IfArray(#VALUE!, dtype=object)

As you can see, when the second or third parameters of the IF() function are strings, the formula retunrs a #VALUE! error instead of the string.

Expected behavior
In the given example we should get: "1a" as return value.

Describe the bug
ModuleNotFoundError: No module named 'regex._regex'

To Reproduce

>>> import formulas
>>> func = formulas.Parser().ast('=(1 + 1) + B3 / A2')[1].compile()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Users\XXX\AppData\Local\Programs\Python\Python38\lib\site-packages\formulas\__init__.py", line 68, in __getattr__
    obj = getattr(importlib.import_module(_all[name], __name__), name)
  File "C:\Users\XXX\AppData\Local\Programs\Python\Python38\lib\importlib\__init__.py", line 127, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 1014, in _gcd_import
  File "<frozen importlib._bootstrap>", line 991, in _find_and_load
  File "<frozen importlib._bootstrap>", line 975, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 671, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 783, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "C:\Users\XXX\AppData\Local\Programs\Python\Python38\lib\site-packages\formulas\parser.py", line 14, in <module>
    import regex
  File "C:\Users\XXX\AppData\Roaming\Python\Python38\site-packages\regex\__init__.py", line 1, in <module>
    from .regex import *
  File "C:\Users\XXX\AppData\Roaming\Python\Python38\site-packages\regex\regex.py", line 403, in <module>
    import regex._regex_core as _regex_core
  File "C:\Users\XXX\AppData\Roaming\Python\Python38\site-packages\regex\_regex_core.py", line 21, in <module>
    import regex._regex as _regex
ModuleNotFoundError: No module named 'regex._regex'

Desktop:

• OS: Windows 10, python 3.8 x64
• Version 1.0

Currently no wheel has been published in PyPi repo - only sources.
What you have been using to rlease your projects is outdated!

Always use these commands when releasing a project:

python setup.py sdist bdist_wheel ## Build sources and whl into ./dist/ dir
twine upload dist/*                ## Release built wheel

Hi @vinci1it2000, I'm looking to integrate this library into CJ Workbench and I could use some help understanding all of the pieces.

In our case we need to be able to both convert Excel formulas to Python functions, and select data in a Pandas dataframe based on the provided range to provide it to the function.

We would be happy to contribute back documentation, examples and Excel formulas that are currently missing from this library if you could give us some help understanding it.

a = '=DATE(1,1,1)'
func = formulas.Parser().ast(a)[1].compile()
print("inputs\t" + str(list(func.inputs)))

inputs []
Failed DISPATCHING 'DATE' due to:
FunctionError('Function not implemented!',)
Traceback (most recent call last):
File "C:\Python\Python36\lib\site-packages\schedula\utils\sol.py", line 620, in _evaluate_node
**kw)
File "C:\Python\Python36\lib\site-packages\schedula\utils\sol.py", line 594, in _evaluate_function
sol=self, callback=_callback, sol_name=self.full_name + (node_id,)
File "C:\Python\Python36\lib\site-packages\schedula\utils\asy.py", line 167, in async_process
name, funcs, executor, *args, **kw
File "C:\Python\Python36\lib\site-packages\schedula\utils\asy.py", line 124, in process_funcs
r['res'] = e.process(fn, *args, **kw) if e else fn(*args, **kw)
File "C:\Python\Python36\lib\site-packages\formulas-0.3.0-py3.6.egg\formulas\functions_init.py", line 81, in not_implemented
raise FunctionError()
formulas.errors.FunctionError: Function not implemented!

Is your feature request related to a problem? Please describe.
Google Sheets-specific formulas in sheets exported to Excel are wrapped with ISERROR(__xludf.DUMMYFUNCTION(original_formula), computed_value) where __xludf.DUMMYFUNCTION is a non-existent formula that always gives an error resulting in returning the computed value.

As __xludf.DUMMYFUNCTION is not implemented yet, files with Google Sheets-specific formulas can't be handled by formulas raising NotImplementedError exception.

Describe the solution you'd like
Implement __xludf.DUMMYFUNCTION which always returns an error.

i was using this package and it work like a charm. Then someone added a formula that contain HEX2DEC() and my application crashed. please can someone add these functions to it?

Hi @vinci1it2000,

I am evaluating formulas and in the process added a dozen or so of excel functions. Should I just open a PR, are there any guidelines to contribuitions?

Also, when adding ROW() which gets the current cells row number I wasn't sure the best way to do it. I added the ability to bind functions which would in turn pass in the Cell instance as the first argument which allowed me to get the range. Not being familiar with the code, I wasn't sure if there was a better way to do it.

Hello,

Thank you for your software.
I could slog through the API, but I have only two long astronomy formulas to convert from excel to python.
A couple of examples would help new users.

Describe the bug
I have a big file with numerous references to other files and some links as well for data, when I try to read that using formulas library it fails with :

FormulaError: ('Not a valid formula:\n%s', '=[1]Sheet3!#REF!')

Even though I don't have any Sheet3 in the excel, I still see this error.
Can library by-pass such occurrences and put the value as #REF itself on calculation?

To Reproduce
Read any excel file with incorrect formula.

Expected behavior
Would like if the library can imitate Excel behavior and just say #REF as the value of such cells.

Describe the bug
excel mode crashes when parsing named ranges that point to a different workbook

To Reproduce
add a named range pointing to a different workbook

Expected behavior
Raise an NotImplemented Error or something.

I am working with a very large excel worksheet and I was constantly getting the error "Unreachable output-targets".

After debugging I realized it was due to a cycle in the graph. In schedula/sol.py:run all inputs were never satisfied for the output. This is because the output itself had a reference to itself and it was stuck waiting for more inputs that were never visited.

Excel has no problem with circular references if they are never evaluated. A very simple example is:

A1: 5
A2: IF(A1>10, A2, A1)

Excel will output an error if A1 is greater than 10 (due to A2 referencing self), but it will output A1 correctly if <=10.

When you run this through formulas with input of A1 and output of A2, you will get "Unreachable output-targets". I was able to find all circular references with the following code snippet:

        import networkx
        for x in networkx.algorithms.simple_cycles(dsp.dmap):
            print(x)

This was enough to find the references and change the excel worksheet to not have circular references. However there are some cases where it is quite hard to workaround this by changing the worksheet. I wasn't sure if there was an easy way to allow schedula to correctly handle a case with circular reference. I tried setting the output as an input also, and it actually allowed avoided the error. But it was not functional since it took the input provided for the output in some cases rather than computing it based on the formulas.

If nothing else this ticket can hopefully help people debug Unreachable output-targets error since I spent a lot of time figuring this one out.

When I run this excel: test.xlsx I get the following output:

It's stripped of the first text in the column, basically it only keeps the values I gave in as input. Everything in column A is missing, B1 and B4 are also missing.
My code looks like this:

        xl_model = formulas.ExcelModel().loads(fpath).finish()
        wb = load_workbook(filename=options['file'][0])
        inputs = {
            #"'[TEST.XLSX]DATA'!B1": wb.worksheets[0]['B1'].value,
            "'[TEST.XLSX]DATA'!B2": wb.worksheets[0]['B2'].value,
            "'[TEST.XLSX]DATA'!B3": wb.worksheets[0]['B3'].value+"+B1",
            "'[TEST.XLSX]DATA'!B5": "=B1+B3",
            
        }
        solution = xl_model.calculate(inputs)
        xl_model.write(dirpath='output')

Extra weirdness: on other, more complicated test files I do get some text (but without the formatting, which is specified in another issue open on this repo)

What am I missing?

Currently packaged 'wheels' are for the specific platform and python-version,
but actually this project contains pure-python code so it can "universal".

Please the following section in setup.py:

[bdist_wheel]
universal = 1

I get a mismatched or misplaced parenthesis error when trying to parse this expression: "=0.1234*(A1/1000/(Sheet1!B1*Sheet1!B2))"

(Actually, this expression fails as well: "=1000/(Sheet1!B1+Sheet1!B2)")

---------------------------------------------------------------------------
ParenthesesError                          Traceback (most recent call last)
<ipython-input-4-f6dfb1afa0bf> in <module>()
----> 1 parser.ast(expression="=0.1234*(A1/1000/(Sheet1!B1*Sheet1!B2))")

c:\sandboxes\excel2py\formulas\formulas\parser.py in ast(self, expression, context)
     55             else:
     56                 raise FormulaError(expression)
---> 57         Parenthesis(')').ast(tokens, stack, builder)
     58         tokens = tokens[1:-1]
     59         while stack:

c:\sandboxes\excel2py\formulas\formulas\tokens\parenthesis.py in ast(self, tokens, stack, builder)
     35 
     36             if not stack or self.opens[self.name] != stack[-1].name:
---> 37                 raise ParenthesesError()
     38             token = stack.pop()
     39             if not token.get_check_n(token):

ParenthesesError: Mismatched or misplaced parentheses!

Looks like in the parsing, the parenthesis after the 1000 is not being parsed:
[( <Parenthesis>, 0.1234 <Number>, * <Operator>, ( <Parenthesis>, A1 <Range>, / <Operator>, 1000 <Number>, / <Operator>, (SHEET1!B1 <Range>, * <Operator>, SHEET1!B2 <Range>, ) <Parenthesis>, ) <Parenthesis>, ) <Parenthesis>]

Maybe because the / is perceived as escaping it?

Here is some code that should reproduce the error:

from formulas import Parser
parser = Parser()
parser.ast(expression="=0.1234*(A1/1000/(Sheet1!B1*Sheet1!B2))")

I'm very impressed with this project, and if you are looking for help, I'd be glad to help and try to figure out this bug. I've been working on a fork of Pycel but this project looks to be in better shape. At the very least, I can offer to port some of the Excel formulas I have already 'pythonized'.

Cheers!

Describe the bug
The following formula:

func = formulas.Parser().ast('=MYFUNC(1)')[1].compile()

raises DispatcherError: ("Failed DISPATCHING '%s' due to:\n %r", 'MYFUNC', FunctionError('Function not implemented!',))

but:

func = formulas.Parser().ast('=MYFUNC(a)')[1].compile()

does not. It looks like the parser should always be able to detect that the function "MYFUNC" does not exist.

This is using version 0.1.3

The Excel function IFS() appears to be not implemented, but this is not reported unless the IFS expression is very simple. If IFS() is actually not implemented then please consider this a feature request. Alternatively, please consider this a bug report.
Thanks.

The cases that I am seeing are:
import formulas as fs
…
ocell = “’[FILENAME]SHEETNAME’!J11”
xl_model = fs.ExcelModel().loads(filename).finish()
soln = xl_model.calculate(outputs=[ocell])
result = soln[ocell].value[0][0]
print(str(result))

if cell J11 contents are any of:
=IFS(G11<=100,"FIRST",G11<=150,"SECOND")
=IFS(G11<=100,"FIRST",G11<=150,"SECOND",G11<=180,"THIRD")
Then the returned value is:
#NAME?
which is of type: <class 'formulas.tokens.operand.XlError'>

If the =IFS expression is simplified down to:
=IFS(TRUE,"TRUE")
Then a NotImplementedError exception results:
Failed DISPATCHING '_xlfn.IFS' due to:
NotImplementedError()
Traceback (most recent call last):
File "C:\Users\widde\AppData\Local\Programs\Python\Python38\lib\site-packages\schedula\utils\sol.py", line 631, in _evaluate_node
value = self._evaluate_function(args, node_id, node_attr, attr,
File "C:\Users\widde\AppData\Local\Programs\Python\Python38\lib\site-packages\schedula\utils\sol.py", line 604, in evaluate_function
res = async_process(
File "C:\Users\widde\AppData\Local\Programs\Python\Python38\lib\site-packages\schedula\utils\asy_init.py", line 177, in async_process
res = (exe and exe.process_funcs or process_funcs)(
File "C:\Users\widde\AppData\Local\Programs\Python\Python38\lib\site-packages\schedula\utils\asy_init.py", line 135, in process_funcs
r['res'] = e.process(sid, fn, *args, **kw) if e else fn(*args, **kw)
File "C:\Users\widde\AppData\Local\Programs\Python\Python38\lib\site-packages\formulas\functions_init.py", line 98, in not_implemented
raise NotImplementedError
NotImplementedError

By contrast, with an =IF function:
=IF(TRUE,"TRUE","FALSE")
The reported result is:
TRUE

Describe the bug
When running formulas.ExcelModel().loads("easy.xlsx").finish() I'm getting

Traceback (most recent call last):
  File "main.py", line 5, in <module>
    xls = formulas.ExcelModel().loads("fund.xlsx").finish()
  File "/Users/lukaskoebis/py_excel/.env/lib/python3.7/site-packages/formulas/excel.py", line 66, in loads
    self.load(filename)
  File "/Users/lukaskoebis/py_excel/.env/lib/python3.7/site-packages/formulas/excel.py", line 70, in load
    book, context = self.add_book(filename)
  File "/Users/lukaskoebis/py_excel/.env/lib/python3.7/site-packages/formulas/excel.py", line 122, in add_book
    default=lambda: dict(self._yield_refs(book, context=context))
  File "/Users/lukaskoebis/py_excel/.env/lib/python3.7/site-packages/schedula/utils/dsp.py", line 538, in get_nested_dicts
    init_nesting=init_nesting)
  File "/Users/lukaskoebis/py_excel/.env/lib/python3.7/site-packages/schedula/utils/dsp.py", line 535, in get_nested_dicts
    d = default() if len(keys) == 1 else init_nesting()
  File "/Users/lukaskoebis/py_excel/.env/lib/python3.7/site-packages/formulas/excel.py", line 122, in <lambda>
    default=lambda: dict(self._yield_refs(book, context=context))
  File "/Users/lukaskoebis/py_excel/.env/lib/python3.7/site-packages/formulas/excel.py", line 56, in _yield_refs
    rng = Ranges().push(n.value, context=context).ranges[0]['name']
  File "/Users/lukaskoebis/py_excel/.env/lib/python3.7/site-packages/formulas/ranges.py", line 175, in push
    rng = self.get_range(self.format_range, ref, context)
  File "/Users/lukaskoebis/py_excel/.env/lib/python3.7/site-packages/formulas/ranges.py", line 167, in get_range
    for k, v in _re_range.match(ref).groupdict().items():
AttributeError: 'NoneType' object has no attribute 'groupdict'

To Reproduce
See above.

Expected behavior
I guess reading a valid Excel file should never throw an exception.

I attached the spreadsheet.

fund.xlsx

Nice package!

One really simple question. What about space-contained sheets names ? I mean

>>> import formulas
>>> formulas.Parser().ast("=SUM(sheetname!A3)")[1].compile().inputs
OrderedDict([('SHEETNAME!A3', <Ranges>(SHEETNAME!A3))])

works fine. But,

>>> problematic = "'sheet name'!A3"
>>> formulas.Parser().ast(f"=SUM({problematic})")[1].compile().inputs
OrderedDict([('SHEET NAME!A3', None)])
                               ^^^^

doesn't. What's strange is that

>>> formulas.Ranges().push(problematic)
<Ranges>(SHEET NAME!A3)

does the job for what I can tell.

Describe the bug
The SUM function returns a #VALUE! error if the number is passed in as a string.

To Reproduce

formula = "=SUM( HEADER_FLOW_USED )"
 var_values = {
        "HEADER_FLOW_USED": [
                "1094.5729399720826",
                "123.4567896654",
            ]
 }
func = formulas.Parser().ast(formula)[1].compile()
func_value = func(**var_values)

Steps to reproduce the behavior:
See above

Expected behavior
I expected that the function would recognize that the string being passed in is parseable to a float and could be summed

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: [e.g. iOS]
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

I ran pip install formulas[all]
And got the following output:
`Traceback (most recent call last):
File "", line 1, in
File "/tmp/pip-install-G63Y__/formulas/setup.py", line 62, in
proj_ver = read_project_version()
File "/tmp/pip-install-G63Y__/formulas/setup.py", line 26, in read_project_version
exec(fd.read(), fglobals) # To read version
File "", line 0
SyntaxError: encoding declaration in Unicode string

Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-install-G63Y__/formulas/`

Things that I have tried which did not work (with and without sudo access, in and outside the virtual environment):

• sudo pip install --upgrade setuptools
• sudo apt-get install libmysqlclient-dev
• pip install --upgrade pip
• pip install formulas

I also tried installing it by cloning the repository and running the setup.py file.

The error does not occur with python3. My project only runs on 2.7. Does this repository only support python3?

Cool library! Many thanks.

Is your feature request related to a problem? Please describe.
My use case is a workbook with a sub-model that has 10-20 inputs and 5-10 outputs, and I'd like to write a python program to optimize the outputs by changing the inputs.
The docs describe how to retrieve a calculation from an Excel sub-model, but there is currently no indication of whether the library can allow changing the inputs to an excel sub-model.

Describe the solution you'd like
If this feature is already supported, I'd like it if it were documented with an example. If the feature is not already supported, I'd like it to be mentioned in the examples page stating that it is not currently supported.