An Excel (XL) 2 Python (Py) structure retriever for optimization. Convert the I/O of XL files into Python.
Project description
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 optimization algorithm of your will
Now, with object-oriented formulas
The current project makes use of the XL COM interface (win32com library) to:
Read an objective function cell
- 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
XL cell formulas are translated to object oriented calculation blocks (no more evals as of this update).
- 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
Standard operators: +, -, /, *, ^
Logical operators: <, >, <=, >=, <>, =
IF
AVERAGE
STDEV.P
TRANSPOSE
ABS
MMULT
IFERROR
SUM
COUNT
SQRT
Tackled in the latest update
No more evals -> formulas are object oriented (Calculation-, Formula- and Reference- and Numeric-Blocks)
- 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
Object serialization
CVS outputs
A conceptual example with corresponding XL file. (reach me for further assistance)
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
You can find me @ Gabriel S. Gusmão <gusmaogabriels@gmail.com>