之前 导出excel中的公式,生成python代码 提到的方法,对表格的格式有比较强的要求.本文进行了优化:
一.目的:
1.将excel表格中的公式以python的形式显现,方便web交互
二.操作步骤:
1.openpyxl解析excel表格,获取所有单元格的值或表达式
2.根据单元格之间的依赖顺序,生成python代码
3.用input_vars_map和output_vars_map生成setter、getter接口,方便用户修改输入获取输出
4.在构造函数中对常量进行初始化
5.在run函数中计算所有的表达式
6.在print函数中调用getter接口获取计算后的值

一.代码

# -*- coding: utf-8 -*-
'''
一.目的:
1.将excel表格中的公式以python的形式显现,方便web交互
二.操作步骤:
1.openpyxl解析excel表格,获取所有单元格的值或表达式
2.根据单元格之间的依赖顺序,生成python代码
3.用input_vars_map和output_vars_map生成setter、getter接口,方便用户修改输入获取输出
4.在构造函数中对常量进行初始化
5.在run函数中计算所有的表达式
6.在print函数中调用getter接口获取计算后的值
'''

import openpyxl
from collections import OrderedDict
import re
import numpy as np
import networkx as nx
import matplotlib.pyplot as plt
    
def transform_expr(ws,expr):
    if isinstance(expr,str):
        # 1.展开SUM中的区域
        for beg,end in re.findall('SUM\(([A-Z]+[0-9]+):([A-Z]+[0-9]+)\)',expr):
            cells=[]
            for row in ws[f'{beg}:{end}']:
                for cell in row:
                    cells.append(cell.coordinate)
            expand="+".join(cells)
            expr=expr.replace(f"SUM({beg}:{end})",f"({expand})")
        # 2.替换等号
        expr=re.sub(r"\b=\b","==",expr)
        # 3.替换指数
        expr=expr.replace("^","**")
    return expr

def main():

    wb = openpyxl.load_workbook("llm.xlsx", data_only=False)
    ws = wb.active
    
    # 输入--变量名与单元格坐标映射表
    input_vars_map=OrderedDict()
    input_vars_map["device_count"]="B9"

    # 输出--变量名与单元格坐标映射表
    output_vars_map=OrderedDict()
    output_vars_map["train_hours"]="B350"
    
    # 名字替换--单元格名与单元格坐标映射表
    rename_vars=OrderedDict()
    def replace_cellname_with_coordinate(value):
        for k,v in rename_vars.items():
            value=value.replace(k,v)
        return value
        
    cells={} #key:坐标 value:(值,表达式,表达式输入列表)
    for row in ws:
        for cell in row:   
            coordinate=cell.coordinate
            value_or_expr=cell.value
            
            if isinstance(value_or_expr,int):
                cells[coordinate]=(value_or_expr,None,[])
            elif isinstance(value_or_expr,float):
                cells[coordinate]=(value_or_expr,None,[])   
            elif value_or_expr is None:
                cells[coordinate]=(0,None,[])
            elif isinstance(value_or_expr,str) and value_or_expr.startswith("="):#解析表达式
                # 1.剔除等号
                value_or_expr=value_or_expr[1:]
                # 2.去掉$符号
                value_or_expr=value_or_expr.replace("$","")
                # 3.将已命名的单元格还原为单元格坐标(因为openpyxl无法获取到单元格名)
                value_or_expr=replace_cellname_with_coordinate(value_or_expr)
                # 4.转换表达式
                value_or_expr=transform_expr(ws,value_or_expr)                
                input_vars=re.findall('[A-Z]+[0-9]+',value_or_expr)               
                cells[coordinate]=(None,value_or_expr,input_vars)
    
    # 将所有的变量按依赖顺序排序(拓扑排序)
    G=nx.DiGraph()
    for coordinate,value in cells.items():
        _,_,input_vars=value
        G.add_node(coordinate)
        for cord in input_vars:
            G.add_node(cord)
            G.add_edge(cord,coordinate)    
    order=list(nx.topological_sort(G))
    
    python_common_template='''
import math  
import numpy as np
def IF(a,b,c):
    return b if a else c
def AND(a,b):
    return a and b
def MOD(a,b):
    return a%b 
def MAX(a,b):
    return a if a>b  else b    
def MIN(a,b):
    return a if a<b  else b          
def SUM(a):
    return a
def FLOOR(number, significance=1.0):
    return math.floor(number / significance) * significance
def CEILING(number, significance=1):
    return math.ceil(number / significance) * significance
'''

    def _print(*args,enable=False):
        if enable:
            print(*args)
            
    def add_prefix(value):   
        '''在所有变量名前面加上self.'''
        match=set([x.strip() for x in re.findall('([A-Z]+[0-9]+)',value) if len(x.strip())>0])
        enable=False#value.find("IF(AND(C101")>=0
        _print(value,match,enable=enable)
        for k in match:
            _print("key:",k,enable=enable)
            exp=r'\b'+k+r'\b'
            _print("exp:",exp,enable=enable)
            value=re.sub(exp,"self."+k,value)    
            _print("result",value,enable=enable)
        return value
    
    init_section=[]
    run_section=[]
    output_section=[]
    
    #按依赖顺序,生成python代码
    for var_name in order:    
        var_value,var_expr,inputs=cells[var_name]
        var_name=add_prefix(var_name)
        python_code=""
        if var_value:
            init_section.append(f"{var_name}={var_value}*1.0")
        elif var_expr:
            var_expr=add_prefix(var_expr)
            if var_expr.find("self")<0:
                init_section.append(f"{var_name}={var_expr}")             
            else:
                run_section.append(f"{var_name}={var_expr}")
        else:
            init_section.append(f"{var_name}=0")

    for k,v in output_vars_map.items():
        output_section.append(f"print(\"{{:<64s}} {{:.2f}}\".format(\"{k}\",self.get_{k}()))")

    init_section="\n        ".join(init_section)
    run_section="\n        ".join(run_section)
    output_section="\n        ".join(output_section)
    
    python_class_template=f'''
class llm_calculator(object):
    def __init__(self):
        {init_section}
    def run(self):
        {run_section}
    def print(self):
        print("-----------------------------------RESULT-------------------------------------")
        {output_section}
        
'''

    python_setter_template='''
    def set_{}(self,value):
        self.{}=value
'''

    python_getter_template='''
    def get_{}(self):
        return self.{}
'''

    python_demo_template='''
obj=llm_calculator()
obj.run()
obj.print()   
'''

    with open("llm_cacl.py","w") as f:
        f.write(python_common_template)   
        f.write(python_class_template)
        for k,v in input_vars_map.items():
            f.write(python_setter_template.format(k,v))
        for k,v in output_vars_map.items():
            f.write(python_getter_template.format(k,v))         
        f.write(python_demo_template)
    
if __name__ == "__main__":  
    main()