Skip to main content

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

Project description

star watch fork github

alternate text alternate text

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




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.


  • 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
  5. STDEV.P
  7. ABS
  8. MMULT
  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)


  • 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
# 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.
# 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
# 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 <>

Project details

Supported by

AWS AWS Cloud computing Datadog Datadog Monitoring Facebook / Instagram Facebook / Instagram PSF Sponsor Fastly Fastly CDN Google Google Object Storage and Download Analytics Huawei Huawei PSF Sponsor Microsoft Microsoft PSF Sponsor NVIDIA NVIDIA PSF Sponsor Pingdom Pingdom Monitoring Salesforce Salesforce PSF Sponsor Sentry Sentry Error logging StatusPage StatusPage Status page