xl2py 1.0.4b

Excel to Python I/O structure retriever

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

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]

   • Support for multi-XL workbook/worksheet references

   • Support for single worksheet multirange retrieval

3. XL cell formulas are translated to evaluable strings, which reference the already converted XL structure in Py

4. The calculation structure is determinded by cell-dependency trees, which have been already stored during the conversion (2)

   • Support for 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

On the way

1. RPN (reverse-polish notation) handling of formulas

2. Object oriented formulas (no more evals)

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