Skip to content

An Excel (XL) 2 Python (Py) structure retriever for optimization. Convert the I/O of XL files into Python.

License

Notifications You must be signed in to change notification settings

gusmaogabriels/xl2py

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

2 Commits
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

xl2py

Copyright © 2015 - Gabriel Sabença Gusmão

linkedin

license pypi version

An Excel (XL) 2 Python (Py) structure retriever for optimization. Convert the I/O of XL files into Python.


Description

Convert an XL structure to Py and use any minimization algorithm of your will

Now, with object-oriented formulas.

The current project makes use of the XL COM interface (win32com library) to:

  1. Read an objective function cell
  2. Recursively build its dependent structure as of its formula
    • The XL structure is represented in Py as a dict() object
    • The structure is referenced to as:
      • dictobj[Workbook number as int][Worksheet number as int][Row as int][Column as int]
    • Whereby it handles:
      • multi-XL workbook/worksheet references
      • single worksheet multirange retrieval
  3. XL cell formulas are translated to object oriented calculation blocks (no more evals as of this update).
  4. The calculation structure is determinded by cell-dependency trees, which have been already stored during the conversion (2)
    • Handling of circular references

Ongoing development: A simple evolutionary algorithm that runs based off the abovementioned structure.


Features

  • Conversion Library

    The following XL functions can be currently handled by xl2py. xl2py is capable of undertaking single-cells, arrays and array/matrix operations

    1. Standard operators: \+, \-, \/, \*, \^
    2. Logical operators: \<, \>, \<=, \>=, \<>, \=
    3. IF
    4. AVERAGE
    5. STDEV.P
    6. TRANSPOSE
    7. ABS
    8. MMULT
    9. IFERROR
    10. SUM
    11. COUNT
    12. SQRT
    

Tackled in the latest update

  1. No more evals -> formulas are object oriented (Calculation-, Formula- and Reference- and Numeric-Blocks)
  2. by-operand handling Over the latest update development, by-operand handling of formulas took place of RPN (reverse-polish notation). For additional details, viz. github repository

On the way

  1. Object serialization
  2. CVS outputs

Instructions

  • Installation

    pip install xl2py==version_no

  • Example: I/O object creation

    import xl2py 
    
    Builder = xl2py.builder() # creates a xl2py builder object
    # place the path of your XL file 
    path = r'C:\\User\\DEFAULT\\WHATEVER\\...' 
    # define your XL file password (if it exists)
    pwd = 'password' 
    # opens up a XL COM interface and attach it to the Builder object
    Builder.connect_com(path,pwd) 
    # declare your input cell/range references
    inputs = xl2py.xlref(<Workbook str or int>, \
        <Worksheet int>, <A1- or R1C1-type XL references>)
    # inputs include other inputs to the xlref object
    inputs += xl2py.xlref(<str or int>, <int>, <str>)
    # output must be a single cell reference
    output = xl2py.xlref(<str or int>, <int>, <str>) 
    # Now you are all set. You shall translate the XL structure to python.
    Builder.set_structure(inputs,output)
    # If you want to change the input cell/range values...
    # vals must be of the shape of the inputs 
    # and must be parsed as a list of lists or numpy arrays
    Builder.set_input_values(vals) 
    # grab the output (objective fun) value as numpy array
    output_val = Builder.get_output_value() # Grab the new output value
    

About

An Excel (XL) 2 Python (Py) structure retriever for optimization. Convert the I/O of XL files into Python.

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published

Languages