Spreadsheet Formula Compiler

Project Overview

The Spreadsheet Formula Compiler is a sophisticated tool designed to parse, analyze, compile, and execute Excel-style spreadsheet formulas. It transforms spreadsheet logic into executable code in multiple programming languages (Python, JavaScript, Java, C#) and provides a powerful web-based visualization interface to understand the compilation process.

This project serves as both a practical tool for formula conversion and an educational resource for understanding compiler design concepts, including lexical analysis, parsing, abstract syntax trees (AST), and code generation.

Key Features

Core Compiler Capabilities

• Lexical Analysis: Breaks down formulas into a stream of tokens (identifiers, numbers, operators, functions).
• Parsing: Constructs an Abstract Syntax Tree (AST) to represent the hierarchical structure of the formula.
• Multi-Language Code Generation: Translates Excel formulas into equivalent code for:
  • Python
  • JavaScript
  • Java
  • C#
• Execution Engine: securely executes the compiled logic to generate results.
• Error Handling: Provides detailed feedback on syntax errors and invalid operations.

Web Interface Features

• Interactive Formula Editor: A dedicated input area for writing and editing formulas.
• Visualization Panels:
  • Tokens Panel: Displays the sequence of tokens generated by the scanner.
  • AST Panel: Renders a graphical tree structure of the parsed formula, allowing users to zoom, pan, and inspect nodes.
  • Code Panel: Shows the generated code in the selected target language.
• Cell Grid Editor: A 5x5 mini-spreadsheet (A1:E5) to input variables and test formulas dynamically.
• Step-by-Step Debugger: Allows users to step through the execution of the AST, visualizing how intermediate values are computed.
• Performance Metrics: Real-time display of token count, AST depth, and parsing time.
• Export Options: Capabilities to export tokens (JSON), AST (PNG/SVG), and generated code (source files).
• Theme Support: Toggle between Light and Dark modes for comfortable viewing.

Directory Structure

• /src: Contains the core python source code for the compiler.
  • spreadsheet_compiler/: The main package containing the compiler logic.
    • scanner.py: Handles lexical analysis.
    • parser.py: Handles syntactic analysis and AST construction.
    • codegen.py: Handles generation of target code.
• /webapp: Contains the source code for the web interface.
  • /api: Python Flask backend API.
    • server.py: Entry point for the web server and API endpoints.
  • /css: Cascading Style Sheets for the UI.
  • /js: JavaScript files for frontend logic and interactivity.
  • index.html: The main HTML entry point for the application.
• /tests: Contains unit tests for the compiler components.

Installation and Setup

Prerequisites

• Python 3.8 or higher
• pip (Python package installer)

Steps

1. Clone the Repository Navigate to the project directory on your local machine.

2. Install Dependencies Install the required Python packages using the provided requirements file.

   pip install -r requirements.txt

   If you do not have a requirements.txt, ensure you have flask and flask-cors installed:

   pip install flask flask-cors

3. Run the Application Navigate to the project root and execute the server script.

   python webapp/api/server.py

4. Access the Interface Open your web browser and navigate to: http://localhost:5000

Working of the Application

Architecture Diagram

graph TD
    A[User Input Formula] -->|Raw String| B(Scanner)
    B -->|Tokens| C(Parser)
    C -->|Abstract Syntax Tree| D(Code Generator)
    D -->|Python/JS/Java/C# Code| E[Output Code]
    
    subgraph Execution
    D --> F(Execution Engine)
    G[Cell Grid Data] --> F
    F -->|Result| H[Live Result]
    end
    
    style A fill:#f9f,stroke:#333,stroke-width:2px
    style E fill:#bfb,stroke:#333,stroke-width:2px
    style H fill:#bfb,stroke:#333,stroke-width:2px

Loading

1. Tokenization Phase

When a formula is input (e.g., =SUM(A1:B1) + 10), the Scanner reads the raw string and converts it into a list of tokens.

• IDENTIFIER: SUM
• LPAREN: (
• RANGE: A1:B1
• RPAREN: )
• PLUS: +
• NUMBER: 10

2. Parsing Phase

The Parser takes the list of tokens and uses a recursive descent algorithm to build an Abstract Syntax Tree (AST). This tree represents the operations in a hierarchical order, ensuring correct precedence (e.g., multiplication before addition).

3. Compilation Phase

The Code Generator traverses the AST. Depending on the selected target language, it emits the corresponding syntax. It handles function mapping (e.g., converting distinct range syntaxes into array logic) and ensures necessary helper functions are included in the output.

4. Execution Phase

For live results, the Python backend executes the generated Python code in a safe environment. It resolves cell references (like A1) using the data provided in the Cell Grid Editor and calculates the final result.

Usage Guide

Writing Formulas

Enter formulas starting with an equals sign (=) in the input box. Supported operations include:

• Arithmetic: +, -, *, /, ^
• Functions: SUM, MAX, MIN, AVERAGE, COUNT, SQRT, IF, AND, OR
• Cell References: A1, B2, Z99
• Ranges: A1:B5

Using the Grid

• The "Cell Values" section allows you to define values for variables used in your formula.
• Enter numbers into the cells (A1 through E5).
• The system automatically recalculates the formula result as you type.
• You can Clear, Import, or Export this grid data.

Debugging

• Switch to the "Debugger" tab.
• Click "Start Debug" to begin.
• Use "Next Step" to watch the compiler evaluate the formula one node at a time.
• The current node is highlighted in the AST, and its value is displayed.

Exporting

• Tokens: Copy to clipboard or download as JSON.
• AST: Download as a high-quality PNG or SVG image.
• Code: Download the generated source code file (.py, .js, etc.).

Extending the Compiler

To support new functions or languages:

1. Scanner: Add new token types for different function names (if necessary).
2. Parser: detailed the grammar rules to accept the new syntax.
3. AST: Define new node types for the operations.
4. Code Generator: Add handler methods to translate the new AST nodes into the target language strings.

License

This project is available for educational and personal use.

Contributors

1.Alice