xl2py 2.0b

说明

将xl结构转换为py，并使用任意优化算法

现在，使用面向对象的公式

当前项目使用xl com接口（win32com库）来：

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
4. AVERAGE
5. STDEV.P
6. TRANSPOSE
7. ABS
8. MMULT
9. IFERROR
10. SUM
11. COUNT
12. SQRT

已在最新更新中处理

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

正在路上

1. Object serialization
2. CVS outputs
3. A conceptual example with corresponding XL file. (reach me for further assistance)

指令

• Installation

pipinstallxl2py==version_no

• Example: I/O object creation

importxl2pyBuilder=xl2py.builder()# creates a xl2py builder object# place the path of your XL filepath=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 objectBuilder.connect_com(path,pwd)# declare your input cell/range referencesinputs=xl2py.xlref(<Workbookstrorint>, \
    <Worksheetint>,<A1-orR1C1-typeXLreferences>)# inputs include other inputs to the xlref objectinputs+=xl2py.xlref(<strorint>,<int>,<str>)# output must be a single cell referenceoutput=xl2py.xlref(<strorint>,<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 arraysBuilder.set_input_values(vals)# grab the output (objective fun) value as numpy arrayoutput_val=Builder.get_output_value()# Grab the new output value

你可以找到我@gabriel s.gusm_o<；gusmaogabriels@gmail.com>；

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