Skip to content
TheChatArchiveThe Chat Archive
Geminigemini-2.5-flash· GoogleHugging Face LMArena Human Preference 140K

excel with complaex formulas to python and dag visualization

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Geminigemini-2.5-flash-preview-04-17’s answer.
  1. User

    excel with complaex formulas to python and dag visualization

  2. Gemini Assistant

    This is a fantastic and challenging problem! Converting complex Excel formulas to Python code and visualizing their dependencies as a Directed Acyclic Graph (DAG) involves several steps:

    1. Parsing Excel Formulas: Extracting dependencies and understanding the function calls.
    2. Representing the Sheet: How to store cell values and formulas in Python.
    3. Building the DAG: Identifying nodes (cells) and edges (dependencies).
    4. Converting to Python Logic: Translating Excel functions and references into Python code.
    5. Visualization: Using a library like Graphviz via networkx to draw the DAG.

    Let's break it down.


    Prerequisites

    You'll need the following Python libraries:

    • openpyxl: To read Excel files (.xlsx).
    • excel-formula-parser: A library to parse Excel formulas into an Abstract Syntax Tree (AST) and extract dependencies. Note: This library is not perfect for all Excel functions, especially array formulas or very obscure ones, but it's a great starting point.
    • networkx: For graph creation, manipulation, and topological sorting.
    • matplotlib: For basic plotting (optional, but sometimes networkx uses it).
    • graphviz: For rendering the graph. You'll need both the Python package (pygraphviz or graphviz) and the system-level Graphviz executable.

    Installation:

    pip install openpyxl excel-formula-parser networkx matplotlib graphviz
    # For graphviz, you might also need to install the system-level application:
    # On Debian/Ubuntu: sudo apt-get install graphviz
    # On macOS (Homebrew): brew install graphviz
    # On Windows: Download from graphviz.org and add to PATH.
    

    Step-by-Step Implementation

    We'll create a hypothetical Excel sheet, then read it, parse formulas, build the graph, convert to Python, and visualize.

    1. Create a Sample Excel File (sample_sheet.xlsx)

    Let's imagine a simple sheet with some formulas:

    CellValue/FormulaNotes
    A110Input
    B120Input
    C1=A1+B1Simple sum
    A25Input
    B23Input
    C2=IF(A1>A2, A1*B2, A2*B1)Conditional logic
    D1=SUM(A1:B2)Range sum
    D2=CONCATENATE("Result: ", C1, " and ", C2)String manipulation, depends on C1 and C2
    E1=D1/2Depends on D1
    E2=E1 + 5Depends on E1

    2. Python Script (excel_to_python_dag.py)

    import openpyxl
    import networkx as nx
    import graphviz
    from excel_formula_parser import ExcelFormulaParser, FormulaParseException
    from collections import defaultdict
    import re
    
    # --- Configuration ---
    EXCEL_FILE = 'sample_sheet.xlsx'
    OUTPUT_DAG_FILE = 'excel_dag' # .gv and .png will be added
    
    # --- Data Structures ---
    class CellInfo:
        """Stores information about an Excel cell."""
        def __init__(self, cell_id, value=None, formula=None):
            self.cell_id = cell_id # e.g., "A1"
            self.value = value     # Raw value if not a formula
            self.formula = formula # Formula string if present
            self.dependencies = set() # Set of cell_ids this cell depends on
            self.python_code = None   # Python representation of the formula
    
        def __repr__(self):
            return f"CellInfo(id='{self.cell_id}', formula='{self.formula}', deps={list(self.dependencies)})"
    
    # Global sheet data store
    sheet_data = {} # Maps cell_id (e.g., "A1") to CellInfo object
    
    # --- 1. Read Excel and Parse Formulas ---
    def read_excel_and_parse_formulas(filepath):
        """
        Reads an Excel file, extracts cell values and formulas,
        and parses formulas to determine dependencies.
        """
        wb = openpyxl.load_workbook(filepath, data_only=False) # data_only=False to get formulas
        sheet = wb.active # Get the active sheet
    
        print(f"Processing sheet: {sheet.title}")
    
        for row_idx in range(1, sheet.max_row + 1):
            for col_idx in range(1, sheet.max_column + 1):
                cell = sheet.cell(row=row_idx, column=col_idx)
                cell_id = cell.coordinate # e.g., "A1"
    
                cell_info = CellInfo(cell_id)
    
                if cell.data_type == 'f': # 'f' means it's a formula
                    cell_info.formula = cell.value
                    try:
                        parser = ExcelFormulaParser(cell_info.formula)
                        # Get direct cell references from the formula
                        refs = parser.get_dependencies()
                        for ref in refs:
                            # Normalize references: remove sheet names for simplicity in this example
                            # (e.g., 'Sheet1!A1' becomes 'A1')
                            normalized_ref = ref.split('!')[-1]
                            cell_info.dependencies.add(normalized_ref)
                    except FormulaParseException as e:
                        print(f"Warning: Could not parse formula for {cell_id}: '{cell_info.formula}' - {e}")
                    except Exception as e:
                        print(f"Error parsing formula for {cell_id}: '{cell_info.formula}' - {e}")
                else:
                    cell_info.value = cell.value
    
                sheet_data[cell_id] = cell_info
        print(f"Loaded {len(sheet_data)} cells.")
    
    # --- 2. Build the Dependency Graph (DAG) ---
    def build_dag():
        """
        Builds a directed graph where nodes are cells and edges represent dependencies.
        """
        G = nx.DiGraph()
    
        for cell_id, cell_info in sheet_data.items():
            G.add_node(cell_id) # Add all cells as nodes
    
            for dep_id in cell_info.dependencies:
                # Ensure dependency node exists, even if it's outside our current sheet scan bounds
                if dep_id not in sheet_data:
                    # This could happen if a formula references an empty cell or another sheet not scanned
                    sheet_data[dep_id] = CellInfo(dep_id, value=0) # Add a placeholder
                    print(f"Added placeholder for dependency: {dep_id}")
                G.add_edge(dep_id, cell_id) # Edge from dependency to the cell that uses it
        
        # Check for cycles
        if not nx.is_directed_acyclic_graph(G):
            print("Warning: Detected cycles in the graph! This might indicate a problem or intentional circularity.")
            # You can try to find and report cycles:
            # try:
            #     cycles = list(nx.find_cycles(G))
            #     print(f"Cycles found: {cycles}")
            # except nx.NetworkXNoCycle:
            #     pass # Should not happen if is_directed_acyclic_graph returned False
    
        return G
    
    # --- 3. Convert Excel Logic to Python ---
    
    # Simple mapping for common Excel functions to Python equivalents
    # This is a basic mapping and would need to be greatly expanded for full coverage.
    EXCEL_TO_PYTHON_FUNCTIONS = {
        "SUM": "sum",
        "IF": lambda condition, true_val, false_val: f"({true_val} if {condition} else {false_val})",
        "CONCATENATE": lambda *args: " + ".join(map(str, args)), # Python's + for string concat
        "AND": lambda *args: "(" + " and ".join(map(str, args)) + ")",
        "OR": lambda *args: "(" + " or ".join(map(str, args)) + ")",
        # Add more as needed: LEFT, RIGHT, MID, VLOOKUP, INDEX, MATCH, etc.
        # VLOOKUP, INDEX/MATCH would require more complex Python function definitions
    }
    
    def clean_excel_formula_part(part):
        """
        Cleans up parts of the formula, e.g., removes outer quotes from strings.
        """
        if isinstance(part, str):
            # Remove leading/trailing quotes from string literals
            if part.startswith('"') and part.endswith('"'):
                return part
        return str(part)
    
    def excel_expr_to_python(expr, cell_id_map):
        """
        Recursively converts an Excel formula expression (from parser AST) to Python.
        `cell_id_map` is a dict mapping cell IDs to Python variable names (e.g., 'A1' -> 'var_A1').
        """
        if isinstance(expr, str):
            # It's a cell reference, string literal, or number
            if re.match(r'^[A-Z]+\d+$', expr): # Looks like a cell reference (e.g., "A1")
                return cell_id_map.get(expr, f"'{expr}'") # Return variable name or string if not mapped
            elif expr.startswith('"') and expr.endswith('"'): # String literal
                return expr # Keep quotes for Python string
            try: # Try converting to number
                return str(float(expr)) if '.' in expr else str(int(expr))
            except ValueError:
                return f"'{expr}'" # Fallback for unhandled string literals
    
        # For functions and operators (parser returns these as tuples or lists)
        if isinstance(expr, (list, tuple)):
            op_or_func = expr[0]
            args = [excel_expr_to_python(arg, cell_id_map) for arg in expr[1:]]
    
            # Handle operators
            if op_or_func in ['+', '-', '*', '/', '^', '=', '<', '>', '<=', '>=', '<>', '&']:
                py_op = {
                    '+': '+', '-': '-', '*': '*', '/': '/', '^': '**',
                    '=': '==', '<': '<', '>': '>', '<=': '<=', '>=': '>=',
                    '<>': '!=', '&': '+' # Excel '&' is string concatenation
                }.get(op_or_func, op_or_func)
                return f"({args[0]} {py_op} {args[1]})"
            
            # Handle Excel functions
            if op_or_func in EXCEL_TO_PYTHON_FUNCTIONS:
                py_func = EXCEL_TO_PYTHON_FUNCTIONS[op_or_func]
                if callable(py_func): # If it's a lambda/function for custom logic
                    return py_func(*args)
                else: # If it's a direct string mapping (e.g., "sum")
                    return f"{py_func}({', '.join(args)})"
            
            # Handle ranges for functions like SUM(A1:B2)
            # The parser might return a range string "A1:B2" which needs expansion or specific handling.
            # For simplicity here, let's assume direct cell references are passed.
            # A more robust solution would interpret "A1:B2" into a list of cells.
            # This example won't fully auto-expand ranges into arguments for SUM, etc.
            # You would need a custom function that converts "A1:B2" into [A1, A2, B1, B2]
            # and then passes those as individual arguments to sum().
            # For now, if SUM takes a range, the parser would ideally break it down into individual
            # cell references, or we'd need a more advanced `get_dependencies` or AST traversal.
            # Example: `parser.get_dependencies()` *does* return individual cells from ranges,
            # so `SUM(A1:B2)` might give `A1, A2, B1, B2` as dependencies. But the `expr`
            # passed here might still contain "A1:B2" as a single arg. This part needs refinement
            # for full range support. For now, we'll assume `SUM(A1,B1,C1)` format or similar direct calls.
    
        return str(expr) # Fallback for anything else (e.g., plain numbers, unhandled literals)
    
    
    def generate_python_code(G):
        """
        Generates Python functions for each formula cell, ordered by topological sort.
        """
        python_output = []
        
        # Create a mapping from cell ID to a safe Python variable name
        cell_var_map = {cell_id: f"var_{cell_id.replace('$', '')}" for cell_id in sheet_data.keys()}
        
        # Store computed values
        computed_values = {}
    
        python_output.append("# --- Python representation of Excel Sheet Logic ---")
        python_output.append("import math # For functions like SQRT, POWER, etc.")
        python_output.append("")
        python_output.append("def excel_if(condition, true_val, false_val):")
        python_output.append("    return true_val if condition else false_val")
        python_output.append("")
        python_output.append("def excel_sum(*args):")
        python_output.append("    return sum(args)")
        python_output.append("")
        python_output.append("def excel_concatenate(*args):")
        python_output.append("    return ''.join(map(str, args))")
        python_output.append("")
        python_output.append("def excel_and(*args):")
        python_output.append("    return all(args)")
        python_output.append("")
        python_output.append("def excel_or(*args):")
        python_output.append("    return any(args)")
        python_output.append("")
    
        # Update EXCEL_TO_PYTHON_FUNCTIONS to use the generated functions
        global EXCEL_TO_PYTHON_FUNCTIONS
        EXCEL_TO_PYTHON_FUNCTIONS.update({
            "IF": "excel_if",
            "SUM": "excel_sum",
            "CONCATENATE": "excel_concatenate",
            "AND": "excel_and",
            "OR": "excel_or",
        })
    
        # Execute in topological order to ensure dependencies are met
        try:
            # Reverse topological sort to process dependencies first
            for cell_id in nx.topological_sort(G):
                cell_info = sheet_data[cell_id]
                py_var_name = cell_var_map[cell_id]
    
                if cell_info.formula:
                    # Get direct dependencies that are also inputs to this specific formula's evaluation
                    # These are arguments to the generated function
                    arg_names = [cell_var_map[dep] for dep in cell_info.dependencies if dep in cell_var_map]
    
                    # Parse the formula string into an AST
                    try:
                        parser = ExcelFormulaParser(cell_info.formula)
                        # The get_tokens_and_args() might give us a better representation for conversion
                        # For simplicity, let's try to convert the original formula string itself
                        # using the parser's internal representation if possible.
                        # A more robust solution would walk the AST from `parser.parse()`
                        
                        # For now, let's just use the tokens for simpler cases,
                        # or you'd need a more complex AST traversal for accurate conversion.
                        # As a workaround, we'll try to convert the *original formula* by
                        # replacing cell references with their var names and then try to evaluate it.
                        # This is brittle. A true AST walk is better.
    
                        # Let's refine excel_expr_to_python to use `parser.parse()` output (AST)
                        ast = parser.parse()
                        python_expression = excel_expr_to_python(ast, cell_var_map)
    
                        cell_info.python_code = f"{py_var_name} = {python_expression}"
                        python_output.append(cell_info.python_code)
                        
                        # For demonstration, store a placeholder value
                        computed_values[py_var_name] = f"CALCULATED_VALUE_FOR_{cell_id}"
    
                    except FormulaParseException as e:
                        print(f"Error parsing AST for {cell_id}: '{cell_info.formula}' - {e}")
                        cell_info.python_code = f"# Error converting {cell_id}: {cell_info.formula}"
                        python_output.append(cell_info.python_code)
                        computed_values[py_var_name] = f"ERROR_IN_FORMULA_{cell_id}"
                    except Exception as e:
                        print(f"Unexpected error during Python conversion for {cell_id}: {e}")
                        cell_info.python_code = f"# Unexpected error converting {cell_id}: {cell_info.formula}"
                        python_output.append(cell_info.python_code)
                        computed_values[py_var_name] = f"ERROR_IN_CONVERSION_{cell_id}"
    
                else:
                    # Input cell (no formula)
                    cell_info.python_code = f"{py_var_name} = {repr(cell_info.value)}" # Use repr for strings
                    python_output.append(cell_info.python_code)
                    computed_values[py_var_name] = cell_info.value
                
                # Add a print statement for debugging/output
                python_output.append(f"print(f'{cell_id}: {{ {py_var_name} = }}')")
            
            python_output.append("\n# --- Example of final computed values ---")
            python_output.append("final_results = {")
            for cell_id, var_name in cell_var_map.items():
                python_output.append(f"    '{cell_id}': {var_name},")
            python_output.append("}")
            python_output.append("print(final_results)")
    
    
        except nx.NetworkXNoCycle:
            print("Graph is acyclic, proceeding with topological sort.")
        except Exception as e:
            print(f"Error during topological sort: {e}")
    
        return "\n".join(python_output)
    
    
    # --- 4. Visualize the DAG ---
    def visualize_dag(G, output_filename):
        """
        Visualizes the dependency graph using Graphviz.
        Nodes are colored based on whether they are input cells or formula cells.
        """
        dot = graphviz.Digraph(comment='Excel Formula Dependencies', graph_attr={'rankdir': 'LR'})
    
        for node in G.nodes():
            cell_info = sheet_data.get(node)
            if cell_info:
                label = f"{node}\n"
                if cell_info.formula:
                    label += f"={cell_info.formula}"
                    node_color = "lightblue"
                    node_style = "filled"
                else:
                    label += f"Value: {cell_info.value}"
                    node_color = "lightgreen"
                    node_style = "filled"
                dot.node(node, label, color=node_color, style=node_style)
            else:
                # For dependencies that weren't in the initial scan (e.g., empty cells referenced)
                dot.node(node, f"{node}\n(Missing)", color="grey", style="filled")
    
        for u, v in G.edges():
            dot.edge(u, v)
    
        dot.render(output_filename, view=False, format='png')
        print(f"DAG visualization saved to {output_filename}.png")
    
    # --- Main Execution ---
    if __name__ == "__main__":
        print(f"Reading Excel file: {EXCEL_FILE}")
        read_excel_and_parse_formulas(EXCEL_FILE)
    
        print("\nBuilding DAG...")
        dependency_graph = build_dag()
    
        print("\nGenerating Python code...")
        python_code = generate_python_code(dependency_graph)
    
        with open("generated_excel_logic.py", "w") as f:
            f.write(python_code)
        print("\nGenerated Python code saved to generated_excel_logic.py")
    
        print("\nVisualizing DAG...")
        visualize_dag(dependency_graph, OUTPUT_DAG_FILE)
    
        print("\n--- To run the generated Python code: ---")
        print("python generated_excel_logic.py")
        print("\n--- To view the DAG: ---")
        print(f"Open {OUTPUT_DAG_FILE}.png")
    

    How to Run

    1. Save the Excel file: Create sample_sheet.xlsx as described above in the same directory as your Python script.
    2. Save the Python script: Save the code above as excel_to_python_dag.py.
    3. Run from terminal:
      python excel_to_python_dag.py
      

    Expected Output

    You will see:

    • Console output indicating parsing, graph building, and code generation.
    • A file named generated_excel_logic.py containing the translated Python code.
    • A file named excel_dag.png (and excel_dag.gv) showing the dependency graph.

    Example generated_excel_logic.py content (simplified):

    # --- Python representation of Excel Sheet Logic ---
    import math # For functions like SQRT, POWER, etc.
    
    def excel_if(condition, true_val, false_val):
        return true_val if condition else false_val
    
    def excel_sum(*args):
        return sum(args)
    
    def excel_concatenate(*args):
        return ''.join(map(str, args))
    
    def excel_and(*args):
        return all(args)
    
    def excel_or(*args):
        return any(args)
    
    var_A1 = 10
    print(f'A1: { var_A1 = }')
    var_B1 = 20
    print(f'B1: { var_B1 = }')
    var_A2 = 5
    print(f'A2: { var_A2 = }')
    var_B2 = 3
    print(f'B2: { var_B2 = }')
    var_C1 = (var_A1 + var_B1)
    print(f'C1: { var_C1 = }')
    var_D1 = excel_sum(var_A1, var_B1, var_A2, var_B2) # This might not be precise for ranges without range expansion logic
    print(f'D1: { var_D1 = }')
    var_C2 = (var_A1 if (var_A1 > var_A2) else (var_A2 if var_B1 else None)) # Simplified due to parser limitation
    print(f'C2: { var_C2 = }')
    var_E1 = (var_D1 / 2)
    print(f'E1: { var_E1 = }')
    var_D2 = excel_concatenate("Result: ", var_C1, " and ", var_C2)
    print(f'D2: { var_D2 = }')
    var_E2 = (var_E1 + 5)
    print(f'E2: { var_E2 = }')
    
    # --- Example of final computed values ---
    final_results = {
        'A1': var_A1,
        'B1': var_B1,
        'C1': var_C1,
        'A2': var_A2,
        'B2': var_B2,
        'C2': var_C2,
        'D1': var_D1,
        'D2': var_D2,
        'E1': var_E1,
        'E2': var_E2,
    }
    print(final_results)
    

    excel_dag.png will show:

    • Nodes for each cell (A1, B1, C1, etc.).
    • Green nodes for input cells (A1, B1, A2, B2).
    • Blue nodes for formula cells (C1, C2, D1, D2, E1, E2).
    • Arrows showing dependencies (e.g., A1 -> C1, C1 -> D2).

    Important Considerations and Limitations

    1. Formula Parsing Complexity:

      • excel-formula-parser is good but might not handle all Excel formula nuances (e.g., array formulas {}, volatile functions, implicit intersection, structured references [#All]).
      • SUM(A1:B2): While get_dependencies() will return A1, A2, B1, B2, the AST representation passed to excel_expr_to_python might still treat "A1:B2" as a single argument. A robust solution needs a dedicated range expansion mechanism.
      • Named Ranges: Not handled in this example. You'd need to extend openpyxl reading to get named ranges and then map them during formula parsing.
      • Cross-Sheet References: Sheet2!A1 is normalized to A1 for simplicity. For multi-sheet support, you'd need a dictionary of sheet_name -> {cell_id -> CellInfo}.
    2. Excel Function Mapping:

      • The EXCEL_TO_PYTHON_FUNCTIONS is very basic. Many Excel functions (VLOOKUP, INDEX, MATCH, XLOOKUP, SUMIF, COUNTIFS, TEXT, DATE, TIME etc.) have no direct Python equivalent or require custom implementations.
      • IF: The parser represents IF(cond, val_if_true, val_if_false). My excel_expr_to_python for IF converts this to a Python ternary operator (val_if_true if cond else val_if_false).
      • CONCATENATE: Excel uses & or CONCATENATE. Python uses +. My code maps both.
    3. Error Handling: The current script has basic try-except blocks for formula parsing but doesn't handle runtime errors during Python execution of the generated code (e.g., division by zero, type mismatches).

    4. Performance: For extremely large sheets (thousands of cells), reading, parsing, and graph building can be slow. Optimizations like caching parsed formulas or incremental updates might be needed.

    5. Evaluation Order: networkx.topological_sort ensures the correct order for calculation if there are no cycles. If cycles exist, this will fail (and indicate a circular reference, which Excel also flags).

    This solution provides a solid foundation for migrating Excel logic to Python and visualizing dependencies. For a production-grade system, a more comprehensive Excel formula parsing library or a custom ANTLR grammar would be necessary, along with a much richer library of Excel function emulations in Python.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.