xl2py 2.0b

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

|star| |watch| |fork| |github|

.. image:: https://img.shields.io/pypi/l/xl2py.svg
:height: 100px
:width: 200 px
:scale: 50 %
:alt: alternate text
:align: right


.. image:: https://img.shields.io/pypi/v/xl2py.svg
:height: 100px
:width: 200 px
:scale: 50 %
:alt: alternate text
:align: right

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

|ld|

|rg|

----------------
**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:

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
3. A conceptual example with corresponding XL file. (*reach me for further assistance*)

----------------
**Instructions**
----------------

- **Installation**

.. code-block:: python

pip install xl2py==version_no

- **Example**: I/O object creation

.. code-block:: python

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 <[e-mail]>

- https://www.researchgate.net/profile/Gabriel_Gusmao
- https://github.com/gusmaogabriels

.. |github| image:: https://img.shields.io/github/followers/gusmaogabriels.svg?style=social&label=Follow
:scale: 25%
:target: https://github.com/gusmaogabriels

.. |fork| image:: https://img.shields.io/github/forks/gusmaogabriels/xl2py.svg?style=social&label=Fork
:scale: 25%
:target: https://github.com/gusmaogabriels/xl2py/fork

.. |star| image:: https://img.shields.io/github/stars/gusmaogabriels/xl2py.svg?style=social&label=Star
:scale: 25%
:target: https://github.com/gusmaogabriels/xl2py/stargazers

.. |watch| image:: https://img.shields.io/github/watchers/gusmaogabriels/xl2py.svg?style=social&label=Watch
:scale: 25%
:target: https://github.com/gusmaogabriels/xl2py/watchers

.. |rg| image:: https://www.researchgate.net/images/public/profile_share_badge.png
:height: 55 px
:width: 166 px
:scale: 30 %
:target: https://www.researchgate.net/profile/Gabriel_Gusmao?cp=shp

.. |ld| image:: https://static.licdn.com/scds/common/u/img/webpromo/btn_viewmy_160x25.png
:height: 25 px
:width: 160 px
:scale: 50 %
:target: https://br.linkedin.com/pub/gabriel-saben%C3%A7a-gusm%C3%A3o/115/aa6/aa8