Analyse et Évaluation d’Expressions Excel en Python

Un guide complet pour intégrer et évaluer des formules Excel avec Python

Principaux Points à Retenir

Analyse lexicale précise : Identification et classification des éléments syntaxiques des formules Excel.
Évaluation sémantique robuste : Exécution correcte des opérations arithmétiques et des fonctions intégrées.
Gestion des plages de cellules : Manipulation efficace des références de cellules et des plages dans les formules.

Introduction

L'analyse et l'évaluation d'expressions Excel en Python sont essentielles pour automatiser et étendre les capacités des feuilles de calcul. Cette démarche se divise en deux étapes principales : l'analyse lexicale et syntaxique (Partie A), et l'évaluation sémantique (Partie B). Ce guide détaillé présente une approche modulaire utilisant des bibliothèques Python telles que PLY, Pandas et NumPy.

Partie A : Analyse Lexicale et Syntaxique

Éléments Lexicaux

Pour analyser une expression Excel, il est crucial de décomposer la formule en éléments lexicaux distincts. Les principaux éléments à identifier sont :

Références de cellules : Identifient des cellules spécifiques comme A1, B2, AA10.
Nombres : Incluent les entiers et les décimaux présents dans la formule.
Opérateurs arithmétiques : Symboles tels que +, -, *, /, ^ utilisés pour les calculs.
Fonctions Excel de base : Fonctions intégrées telles que SUM(), AVERAGE(), COUNT(), MAX(), MIN().
Séparateurs : Parenthèses et virgules utilisées pour structurer les fonctions.
Plages de cellules : Représentent une série de cellules, par exemple A1:A10.

Exigences de l’Analyseur Lexical

Détection des erreurs lexicales : L'analyseur doit identifier et signaler les erreurs avec des messages explicites.
Lecture des formules : Capacité à lire les formules depuis la ligne de commande ou un fichier.
Gestion des espaces et tabulations : Ignorer les espaces et tabulations pour une analyse précise.

Implémentation en Python

L'analyse lexicale et syntaxique peut être réalisée en utilisant la bibliothèque PLY (Python Lex-Yacc), qui fournit des outils pour construire des analyseurs lexicaux et syntaxiques.

Code d’Analyse Lexicale et Syntaxique


import ply.lex as lex
import ply.yacc as yacc

# Définition des tokens
tokens = (
    'CELL',
    'NUMBER',
    'PLUS',
    'MINUS',
    'TIMES',
    'DIVIDE',
    'POWER',
    'LPAREN',
    'RPAREN',
    'COMMA',
    'SUM',
    'AVERAGE',
    'COUNT',
    'MAX',
    'MIN',
    'RANGE',
)

# Règles de tokens
t_PLUS = r'\+'
t_MINUS = r'-'
t_TIMES = r'\*'
t_DIVIDE = r'/'
t_POWER = r'\^'
t_LPAREN = r'\('
t_RPAREN = r'\)'
t_COMMA = r','
t_SUM = r'SUM'
t_AVERAGE = r'AVERAGE'
t_COUNT = r'COUNT'
t_MAX = r'MAX'
t_MIN = r'MIN'

def t_RANGE(t):
    r'[A-Z]+[0-9]+:[A-Z]+[0-9]+'
    return t

def t_CELL(t):
    r'[A-Z]+[0-9]+'
    return t

def t_NUMBER(t):
    r'\d+(\.\d+)?'
    t.value = float(t.value)
    return t

# Ignorer les espaces et tabulations
t_ignore = ' \t'

# Gestion des erreurs lexicales
def t_error(t):
    print(f"Erreur lexicale à '{t.value[0]}'")
    t.lexer.skip(1)

# Analyseur syntaxique
def p_expression(p):
    '''
    expression : expression PLUS term
               | expression MINUS term
               | term
    '''
    if len(p) == 4:
        if p[2] == '+':
            p[0] = p[1] + p[3]
        elif p[2] == '-':
            p[0] = p[1] - p[3]
    else:
        p[0] = p[1]

def p_term(p):
    '''
    term : term TIMES factor
         | term DIVIDE factor
         | term POWER factor
         | factor
    '''
    if len(p) == 4:
        if p[2] == '*':
            p[0] = p[1] * p[3]
        elif p[2] == '/':
            p[0] = p[1] / p[3]
        elif p[2] == '^':
            p[0] = p[1] ** p[3]
    else:
        p[0] = p[1]

def p_factor(p):
    '''
    factor : NUMBER
           | CELL
           | RANGE
           | function
           | LPAREN expression RPAREN
    '''
    if isinstance(p[1], float):
        p[0] = p[1]
    elif isinstance(p[1], str):
        p[0] = p[1]
    elif isinstance(p[1], list):
        p[0] = p[1]
    elif len(p) == 4:
        p[0] = p[2]
    else:
        p[0] = p[1]

def p_function(p):
    '''
    function : SUM LPAREN args RPAREN
             | AVERAGE LPAREN args RPAREN
             | COUNT LPAREN args RPAREN
             | MAX LPAREN args RPAREN
             | MIN LPAREN args RPAREN
    '''
    if p[1] == 'SUM':
        p[0] = sum(p[3])
    elif p[1] == 'AVERAGE':
        p[0] = sum(p[3]) / len(p[3]) if p[3] else 0
    elif p[1] == 'COUNT':
        p[0] = len(p[3])
    elif p[1] == 'MAX':
        p[0] = max(p[3])
    elif p[1] == 'MIN':
        p[0] = min(p[3])

def p_args(p):
    '''
    args : expression
         | args COMMA expression
    '''
    if len(p) == 2:
        p[0] = [p[1]]
    else:
        p[0] = p[1] + [p[3]]

def p_error(p):
    if p:
        print(f"Erreur syntaxique à '{p.value}'")
    else:
        print("Erreur syntaxique à la fin de l'entrée")

# Construction des analyseurs
lexer = lex.lex()
parser = yacc.yacc()

# Exemple d'utilisation
if __name__ == "__main__":
    while True:
        try:
            s = input('Expression Excel > ')
        except EOFError:
            break
        if not s:
            continue
        result = parser.parse(s)
        print(result)

Ce script Python utilise PLY pour identifier et parser les éléments d'une formule Excel. Les règles définissent comment chaque type de token est reconnu et comment les expressions sont évaluées.

Partie B : Évaluation Sémantique

Objectifs de l'Évaluation Sémantique

Évaluer les expressions arithmétiques simples : Effectuer des calculs basés sur les opérateurs identifiés.
Gérer les fonctions Excel imbriquées : Permettre l'utilisation de fonctions au sein d'autres fonctions.
Supporter les plages de cellules : Manipuler des séries de cellules pour des calculs agrégés.
Gérer les priorités des opérations : Respecter l'ordre des opérations mathématiques.

Implémentation de l'Évaluation Sémantique

L'évaluation sémantique est responsable de l'exécution des opérations identifiées lors de l'analyse lexicale et syntaxique. Elle doit interpréter correctement les références de cellules, les plages, et les fonctions pour produire le résultat final de la formule.

Code d’Évaluation Sémantique


import re
import sys

# Simuler une feuille de calcul avec quelques valeurs
CELL_VALUES = {
    'A1': 10, 'A2': 20, 'A3': 30, 'A4': 40, 'A5': 50,
    'B1': 5, 'B2': 15, 'B3': 25,
    'C1': 3, 'C2': 6, 'C3': 9,
    'D1': 12,
    # Ajoutez d'autres cellules si nécessaire
}

class AST:
    pass

class Number(AST):
    def __init__(self, value):
        self.value = float(value)
    def eval(self):
        return self.value

class Cell(AST):
    def __init__(self, name):
        self.name = name
    def eval(self):
        if self.name in CELL_VALUES:
            return CELL_VALUES[self.name]
        else:
            raise ValueError(f"Valeur de cellule inconnue: {self.name}")

class Range(AST):
    def __init__(self, start, end):
        self.start = start
        self.end = end
    def eval(self):
        col = re.match(r"([A-Z]+)", self.start).group(1)
        start_row = int(re.match(r"[A-Z]+([1-9]\d*)", self.start).group(1))
        end_row = int(re.match(r"[A-Z]+([1-9]\d*)", self.end).group(1))
        values = []
        for r in range(start_row, end_row+1):
            cell_name = f"{col}{r}"
            if cell_name in CELL_VALUES:
                values.append(CELL_VALUES[cell_name])
            else:
                raise ValueError(f"Valeur de cellule inconnue: {cell_name}")
        return values

class BinOp(AST):
    def __init__(self, left, op, right):
        self.left = left
        self.op = op
        self.right = right
    def eval(self):
        lval = self.left.eval()
        rval = self.right.eval()
        if self.op == '+':
            return lval + rval
        elif self.op == '-':
            return lval - rval
        elif self.op == '*':
            return lval * rval
        elif self.op == '/':
            return lval / rval
        elif self.op == '^':
            return lval ** rval
        else:
            raise ValueError(f"Opérateur inconnu: {self.op}")

class Function(AST):
    def __init__(self, name, args):
        self.name = name.upper()
        self.args = args
    def eval(self):
        evaluated_args = []
        for arg in self.args:
            res = arg.eval()
            if isinstance(res, list):
                evaluated_args.extend(res)
            else:
                evaluated_args.append(res)
        if self.name == "SUM":
            return sum(evaluated_args)
        elif self.name == "AVERAGE":
            return sum(evaluated_args) / len(evaluated_args) if evaluated_args else 0
        elif self.name == "COUNT":
            return len(evaluated_args)
        elif self.name == "MAX":
            return max(evaluated_args) if evaluated_args else 0
        elif self.name == "MIN":
            return min(evaluated_args) if evaluated_args else 0
        else:
            raise ValueError(f"Fonction inconnue: {self.name}")

class Parser:
    def __init__(self, tokens):
        self.tokens = tokens
        self.pos = 0

    def current(self):
        if self.pos < len(self.tokens):
            return self.tokens[self.pos]
        return None

    def eat(self, token_type):
        token = self.current()
        if token is not None and token.type == token_type:
            self.pos += 1
            return token
        else:
            raise ValueError(f"Erreur syntaxique: attendu {token_type} mais trouvé {token}")

    def parse(self):
        if self.current() and self.current().type == "EQUAL":
            self.eat("EQUAL")
        node = self.expr()
        if self.current() is not None:
            raise ValueError(f"Erreur syntaxique: token inattendu {self.current()}")
        return node

    def expr(self):
        node = self.term()
        while self.current() and self.current().type in ("PLUS", "MINUS"):
            op = self.eat(self.current().type).value
            right = self.term()
            node = BinOp(node, op, right)
        return node

    def term(self):
        node = self.factor()
        while self.current() and self.current().type in ("TIMES", "DIVIDE", "POWER"):
            op = self.eat(self.current().type).value
            right = self.factor()
            node = BinOp(node, op, right)
        return node

    def factor(self):
        token = self.current()
        if token.type == "NUMBER":
            self.eat("NUMBER")
            return Number(token.value)
        elif token.type == "CELL":
            self.eat("CELL")
            return Cell(token.value)
        elif token.type == "RANGE":
            self.eat("RANGE")
            start, end = token.value.split(":")
            return Range(start, end)
        elif token.type in ("SUM", "AVERAGE", "COUNT", "MAX", "MIN"):
            return self.function()
        elif token.type == "LPAREN":
            self.eat("LPAREN")
            node = self.expr()
            self.eat("RPAREN")
            return node
        else:
            raise ValueError(f"Erreur syntaxique: token inattendu {token}")

    def function(self):
        func_token = self.eat(self.current().type)
        self.eat("LPAREN")
        args = self.args()
        self.eat("RPAREN")
        return Function(func_token.type, args)

    def args(self):
        args = [self.expr()]
        while self.current() and self.current().type == "COMMA":
            self.eat("COMMA")
            args.append(self.expr())
        return args

def lex_expression(expression):
    lexer.input(expression)
    tokens = []
    for tok in lexer:
        tokens.append(tok)
    return tokens

def evaluate_expression(expr_str):
    try:
        tokens = lex_expression(expr_str)
        parser_obj = Parser(tokens)
        ast = parser_obj.parse()
        result = ast.eval()
        return result
    except Exception as e:
        print(f"Erreur: {e}")
        sys.exit(1)

# Exemple d’utilisation
if __name__ == "__main__":
    expressions = [
        "=SUM(A1:A5) + MAX(B1, AVERAGE(C1:C3, D1))",
        "=COUNT(A1:A10) * SUM(B1, B2, MIN(C1:C5))"
    ]
    for expr in expressions:
        print(f"Expression: {expr}")
        print(f"Résultat: {evaluate_expression(expr)}\n")

Ce module définit une structure AST (Abstract Syntax Tree) pour représenter les éléments de la formule Excel. Chaque classe possède une méthode eval() qui exécute l'opération correspondante. Le Parser construit l'arbre syntaxique en analysant les tokens fournis par l’analyse lexicale.

Tableau Comparatif des Bibliothèques Python pour les Formules Excel

Bibliothèque	Caractéristiques	Avantages	Limitations
PLY (Python Lex-Yacc)	Outil de construction d'analyseurs lexicaux et syntaxiques	Flexible, permet une personnalisation complète	Complexité de mise en œuvre pour des formules complexes
Pandas	Manipulation avancée de données tabulaires	Gestion efficace des plages de cellules, intégration facile avec Excel	Peut nécessiter une conversion des formules en opérations Pandas
NumPy	Calculs numériques performants	Optimisé pour les opérations mathématiques et statistiques	Moins adapté pour l’analyse syntaxique des formules
xlcalculator	Interprétation des formules Excel	Supporte de nombreuses fonctions Excel, facile à intégrer	Peut ne pas couvrir toutes les fonctionnalités avancées d’Excel
Pycel	Évaluation des formules Excel	Facile à utiliser, bonnes capacités d’interprétation	Limité par rapport aux formules les plus complexes

Conclusion

L'analyse et l'évaluation d'expressions Excel en Python nécessitent une approche méthodique impliquant une analyse lexicale et syntaxique précise, suivie d'une évaluation sémantique robuste. En utilisant des bibliothèques comme PLY, Pandas et NumPy, il est possible de créer des outils puissants capables de simuler et d'étendre les fonctionnalités des feuilles de calcul Excel. Bien que l'implémentation initiale puisse sembler complexe, la modularité et la flexibilité de ces bibliothèques permettent une personnalisation approfondie pour répondre à des besoins spécifiques.

Pour améliorer davantage ce système, il est recommandé d'intégrer des fonctionnalités supplémentaires telles que la gestion des plages intercolonnes, l'extension du support des fonctions Excel et l'amélioration des messages d'erreur pour une meilleure expérience utilisateur.

Références

Last updated February 14, 2025