UNDERSTANDING THREE-DIMENSIONAL SPREADSHEETS
Traditional spreadsheets organize data in a two-dimensional grid of rows and columns. While this structure works well for many applications, it becomes limiting when dealing with data that naturally exists in three dimensions. A three-dimensional spreadsheet extends the familiar grid concept by adding a third axis, typically called sheets, layers, or depth. This creates a cube-like structure where each cell is identified by three coordinates instead of two.
The usefulness of three-dimensional spreadsheets becomes apparent when working with data that has inherent dimensional relationships. Financial analysts tracking budgets across multiple departments over time benefit from organizing departments on one axis, budget categories on another, and time periods on the
third. Scientists modeling physical phenomena can map spatial coordinates directly to spreadsheet dimensions. Supply chain managers can track inventory levels across products, warehouses, and time periods in a natural three-dimensional structure.
The fundamental benefit of three-dimensional spreadsheets lies in their ability to reduce complexity and improve comprehension. Instead of maintaining multiple separate two-dimensional sheets or creating complex naming conventions to simulate depth, users can navigate through a true three-dimensional space. Formulas can reference cells across all three dimensions using intuitive coordinate systems. Calculations that would require complex lookups in traditional spreadsheets become straightforward cell references in three dimensions.
Implementation of three-dimensional spreadsheets requires rethinking the data
structures and user interface paradigms of traditional spreadsheet applications. The underlying data model must efficiently store and retrieve cells identified by three coordinates.
The user interface must provide intuitive ways to navigate through the third dimension while maintaining the familiar spreadsheet editing experience. Formula parsing and calculation engines must handle three-dimensional cell references and support operations that span multiple layers.
ARCHITECTURAL FOUNDATIONS
The architecture of an LLM-powered three-dimensional spreadsheet application consists of several interconnected components. The data layer manages the three-dimensional cell structure and provides efficient storage and retrieval. The calculation engine processes formulas and maintains dependency graphs
across all three dimensions. The user interface layer renders the spreadsheet and handles user interactions. The LLM integration layer connects to language models for formula generation and natural language processing. The graphics subsystem creates visualizations from spreadsheet data.
The data layer forms the foundation of the application. Each cell in the three-dimensional space is identified by coordinates along three axes, commonly referred to as X, Y, and Z or column, row, and layer. The data structure must support sparse storage since most spreadsheets contain many empty cells. A hash map indexed by three-dimensional coordinates provides efficient access while avoiding memory waste for empty cells.
class Cell:
def __init__(self, x, y, z):
self.x = x
self.y = y
self.z = z
self.value = None
self.formula = None
self.formatted_value = None
self.dependencies = set()
self.dependents = set()
self.metadata = {}
def get_coordinate_string(self):
"""Returns a string representation like 'A1:0' for the cell"""
col_letter = self._number_to_column_letter(self.x)
return f"{col_letter}{self.y}:{self.z}"
def _number_to_column_letter(self, n):
"""Converts column number to letter(s)"""
result = ""
while n >= 0:
result = chr(65 + (n % 26)) + result
n = n // 26 - 1
if n < 0:
break
return result
class SpreadsheetData:
def __init__(self):
self.cells = {} # Key: (x, y, z), Value: Cell object
self.dimensions = {'x': 100, 'y': 1000, 'z': 50}
def get_cell(self, x, y, z):
"""Retrieves a cell, creating it if it doesn't exist"""
key = (x, y, z)
if key not in self.cells:
self.cells[key] = Cell(x, y, z)
return self.cells[key]
def set_cell_value(self, x, y, z, value):
"""Sets the value of a cell"""
cell = self.get_cell(x, y, z)
cell.value = value
return cell
def set_cell_formula(self, x, y, z, formula):
"""Sets the formula for a cell"""
cell = self.get_cell(x, y, z)
cell.formula = formula
return cell
The calculation engine processes formulas and maintains the dependency graph between cells. When a cell's value changes, all dependent cells must be recalculated in the correct order. The engine parses formulas to identify cell references across all three dimensions and builds a directed acyclic graph representing dependencies.
Topological sorting ensures calculations proceed in the correct order.
import re
from collections import deque
class CalculationEngine:
def __init__(self, spreadsheet_data):
self.data = spreadsheet_data
self.calculation_order = []
def parse_formula(self, formula):
"""Parses a formula and extracts three-dimensional cell references"""
# Pattern matches references like A1:0, B5:2, AA10:5
pattern = r'([A-Z]+)(\d+):(\d+)'
references = []
for match in re.finditer(pattern, formula):
col_letter = match.group(1)
row = int(match.group(2))
layer = int(match.group(3))
col = self._column_letter_to_number(col_letter)
references.append((col, row, layer))
return references
def _column_letter_to_number(self, letters):
"""Converts column letter(s) to number"""
result = 0
for char in letters:
result = result * 26 + (ord(char) - 64)
return result - 1
def build_dependency_graph(self):
"""Builds the dependency graph for all cells with formulas"""
# Clear existing dependencies
for cell in self.data.cells.values():
cell.dependencies.clear()
cell.dependents.clear()
# Build new dependencies
for coord, cell in self.data.cells.items():
if cell.formula:
references = self.parse_formula(cell.formula)
for ref_coord in references:
ref_cell = self.data.get_cell(*ref_coord)
cell.dependencies.add(ref_coord)
ref_cell.dependents.add(coord)
def topological_sort(self):
"""Performs topological sort to determine calculation order"""
in_degree = {}
for coord, cell in self.data.cells.items():
if cell.formula:
in_degree[coord] = len(cell.dependencies)
queue = deque([coord for coord, degree in in_degree.items() if degree == 0])
order = []
while queue:
coord = queue.popleft()
order.append(coord)
cell = self.data.cells[coord]
for dependent_coord in cell.dependents:
if dependent_coord in in_degree:
in_degree[dependent_coord] -= 1
if in_degree[dependent_coord] == 0:
queue.append(dependent_coord)
self.calculation_order = order
return order
def evaluate_formula(self, formula):
"""Evaluates a formula and returns the result"""
# Replace cell references with their values
pattern = r'([A-Z]+)(\d+):(\d+)'
def replace_reference(match):
col_letter = match.group(1)
row = int(match.group(2))
layer = int(match.group(3))
col = self._column_letter_to_number(col_letter)
cell = self.data.get_cell(col, row, layer)
return str(cell.value if cell.value is not None else 0)
expression = re.sub(pattern, replace_reference, formula)
# Evaluate the expression safely
try:
result = eval(expression, {"__builtins__": {}}, {})
return result
except Exception as e:
return f"#ERROR: {str(e)}"
def recalculate_all(self):
"""Recalculates all cells with formulas in dependency order"""
self.build_dependency_graph()
self.topological_sort()
for coord in self.calculation_order:
cell = self.data.cells[coord]
if cell.formula:
cell.value = self.evaluate_formula(cell.formula)
The user interface layer provides the visual representation and interaction mechanisms for the three-dimensional spreadsheet. Unlike traditional spreadsheets where the entire grid is visible, three-dimensional spreadsheets require navigation controls to move through the depth axis. The interface displays one layer at a time, similar to a traditional spreadsheet, with controls to switch between layers. Users can visualize the
current layer position and navigate forward or backward through the depth dimension.
The interface must support standard spreadsheet operations including cell selection, editing, copying, pasting, and formatting. Cell selection can extend across multiple layers, creating three-dimensional ranges. The formula bar displays the current cell's formula and allows editing. A layer navigator shows the current position in the depth dimension and provides controls for moving between layers.
import tkinter as tk
from tkinter import ttk
class SpreadsheetUI:
def __init__(self, root, spreadsheet_data, calculation_engine):
self.root = root
self.data = spreadsheet_data
self.calc_engine = calculation_engine
self.current_layer = 0
self.selected_cell = (0, 0, 0)
self.setup_ui()
def setup_ui(self):
"""Sets up the user interface components"""
# Main container
main_frame = ttk.Frame(self.root)
main_frame.pack(fill=tk.BOTH, expand=True)
# Top toolbar with layer navigation
toolbar = ttk.Frame(main_frame)
toolbar.pack(side=tk.TOP, fill=tk.X, padx=5, pady=5)
ttk.Label(toolbar, text="Layer:").pack(side=tk.LEFT, padx=5)
self.layer_spinbox = ttk.Spinbox(
toolbar,
from_=0,
to=self.data.dimensions['z']-1,
width=10,
command=self.on_layer_change
)
self.layer_spinbox.set(0)
self.layer_spinbox.pack(side=tk.LEFT, padx=5)
ttk.Button(toolbar, text="Previous Layer",
command=self.previous_layer).pack(side=tk.LEFT, padx=5)
ttk.Button(toolbar, text="Next Layer",
command=self.next_layer).pack(side=tk.LEFT, padx=5)
# Formula bar
formula_frame = ttk.Frame(main_frame)
formula_frame.pack(side=tk.TOP, fill=tk.X, padx=5, pady=5)
self.cell_label = ttk.Label(formula_frame, text="A0:0", width=10)
self.cell_label.pack(side=tk.LEFT, padx=5)
self.formula_entry = ttk.Entry(formula_frame)
self.formula_entry.pack(side=tk.LEFT, fill=tk.X, expand=True, padx=5)
self.formula_entry.bind('<Return>', self.on_formula_submit)
# Grid container with scrollbars
grid_frame = ttk.Frame(main_frame)
grid_frame.pack(side=tk.TOP, fill=tk.BOTH, expand=True, padx=5, pady=5)
# Create canvas for grid
self.canvas = tk.Canvas(grid_frame, bg='white')
self.canvas.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)
# Scrollbars
v_scrollbar = ttk.Scrollbar(grid_frame, orient=tk.VERTICAL,
command=self.canvas.yview)
v_scrollbar.pack(side=tk.RIGHT, fill=tk.Y)
h_scrollbar = ttk.Scrollbar(main_frame, orient=tk.HORIZONTAL,
command=self.canvas.xview)
h_scrollbar.pack(side=tk.BOTTOM, fill=tk.X, padx=5)
self.canvas.configure(yscrollcommand=v_scrollbar.set,
xscrollcommand=h_scrollbar.set)
self.cell_width = 100
self.cell_height = 25
self.visible_cols = 20
self.visible_rows = 30
self.canvas.bind('<Button-1>', self.on_cell_click)
self.draw_grid()
def draw_grid(self):
"""Draws the grid for the current layer"""
self.canvas.delete('all')
# Draw column headers
for col in range(self.visible_cols):
x = col * self.cell_width
self.canvas.create_rectangle(
x, 0, x + self.cell_width, self.cell_height,
fill='lightgray', outline='black'
)
col_letter = self._number_to_column_letter(col)
self.canvas.create_text(
x + self.cell_width // 2, self.cell_height // 2,
text=col_letter, font=('Arial', 10, 'bold')
)
# Draw row headers and cells
for row in range(self.visible_rows):
y = (row + 1) * self.cell_height
# Row header
self.canvas.create_rectangle(
0, y, self.cell_width, y + self.cell_height,
fill='lightgray', outline='black'
)
self.canvas.create_text(
self.cell_width // 2, y + self.cell_height // 2,
text=str(row), font=('Arial', 10, 'bold')
)
# Data cells
for col in range(self.visible_cols):
x = col * self.cell_width
cell = self.data.get_cell(col, row, self.current_layer)
display_value = ""
if cell.value is not None:
display_value = str(cell.value)
self.canvas.create_rectangle(
x, y, x + self.cell_width, y + self.cell_height,
fill='white', outline='gray', tags=f'cell_{col}_{row}'
)
self.canvas.create_text(
x + 5, y + self.cell_height // 2,
text=display_value, anchor='w', tags=f'text_{col}_{row}'
)
# Highlight selected cell
self.highlight_selected_cell()
# Update scroll region
self.canvas.configure(
scrollregion=(0, 0,
self.visible_cols * self.cell_width,
(self.visible_rows + 1) * self.cell_height)
)
def _number_to_column_letter(self, n):
"""Converts column number to letter(s)"""
result = ""
while n >= 0:
result = chr(65 + (n % 26)) + result
n = n // 26 - 1
if n < 0:
break
return result
def highlight_selected_cell(self):
"""Highlights the currently selected cell"""
x, y, z = self.selected_cell
if z == self.current_layer:
canvas_x = x * self.cell_width
canvas_y = (y + 1) * self.cell_height
self.canvas.create_rectangle(
canvas_x, canvas_y,
canvas_x + self.cell_width,
canvas_y + self.cell_height,
outline='blue', width=2, tags='selection'
)
def on_cell_click(self, event):
"""Handles cell click events"""
canvas_x = self.canvas.canvasx(event.x)
canvas_y = self.canvas.canvasy(event.y)
col = int(canvas_x // self.cell_width)
row = int(canvas_y // self.cell_height) - 1
if row >= 0 and col >= 0:
self.selected_cell = (col, row, self.current_layer)
self.update_formula_bar()
self.draw_grid()
def update_formula_bar(self):
"""Updates the formula bar with the selected cell's information"""
x, y, z = self.selected_cell
cell = self.data.get_cell(x, y, z)
self.cell_label.config(text=cell.get_coordinate_string())
if cell.formula:
self.formula_entry.delete(0, tk.END)
self.formula_entry.insert(0, cell.formula)
else:
self.formula_entry.delete(0, tk.END)
if cell.value is not None:
self.formula_entry.insert(0, str(cell.value))
def on_formula_submit(self, event):
"""Handles formula submission from the formula bar"""
x, y, z = self.selected_cell
formula_text = self.formula_entry.get()
if formula_text.startswith('='):
self.data.set_cell_formula(x, y, z, formula_text[1:])
self.calc_engine.recalculate_all()
else:
try:
value = float(formula_text)
self.data.set_cell_value(x, y, z, value)
except ValueError:
self.data.set_cell_value(x, y, z, formula_text)
self.draw_grid()
def on_layer_change(self):
"""Handles layer change from the spinbox"""
new_layer = int(self.layer_spinbox.get())
self.current_layer = new_layer
self.selected_cell = (self.selected_cell[0], self.selected_cell[1], new_layer)
self.draw_grid()
self.update_formula_bar()
def previous_layer(self):
"""Moves to the previous layer"""
if self.current_layer > 0:
self.current_layer -= 1
self.layer_spinbox.set(self.current_layer)
self.on_layer_change()
def next_layer(self):
"""Moves to the next layer"""
if self.current_layer < self.data.dimensions['z'] - 1:
self.current_layer += 1
self.layer_spinbox.set(self.current_layer)
self.on_layer_change()
LLM INTEGRATION FOR FORMULA GENERATION
The integration of large language models into the spreadsheet application enables users to generate formulas using natural language descriptions. Instead of manually constructing complex formulas with three-dimensional cell references, users can describe what they want to calculate in plain language. The LLM interprets the description, understands the spreadsheet context, and generates the appropriate formula.
The LLM integration layer must support both local and remote language models. Local models run on the user's hardware, providing privacy and eliminating network dependencies. Remote models accessed through APIs offer more powerful capabilities but require internet connectivity. The system must detect available GPU hardware and configure the model execution accordingly, supporting NVIDIA CUDA, Apple Metal Performance Shaders, and CPU-only execution.
import torch
import platform
from enum import Enum
class GPUBackend(Enum):
CUDA = "cuda"
MPS = "mps"
CPU = "cpu"
class LLMConfig:
def __init__(self):
self.backend = self.detect_gpu_backend()
self.device = self.get_device()
self.model_path = None
self.model_name = None
self.use_remote = False
self.api_key = None
self.api_endpoint = None
def detect_gpu_backend(self):
"""Detects the available GPU backend"""
if torch.cuda.is_available():
return GPUBackend.CUDA
elif platform.system() == 'Darwin' and hasattr(torch.backends, 'mps') and torch.backends.mps.is_available():
return GPUBackend.MPS
else:
return GPUBackend.CPU
def get_device(self):
"""Returns the appropriate torch device"""
if self.backend == GPUBackend.CUDA:
return torch.device("cuda")
elif self.backend == GPUBackend.MPS:
return torch.device("mps")
else:
return torch.device("cpu")
def get_device_info(self):
"""Returns information about the current device"""
if self.backend == GPUBackend.CUDA:
return f"CUDA - {torch.cuda.get_device_name(0)}"
elif self.backend == GPUBackend.MPS:
return "Apple Metal Performance Shaders"
else:
return "CPU"
The local LLM implementation uses transformer models optimized for code generation and mathematical reasoning. These models are loaded into memory and executed on the detected GPU backend. The system provides a unified interface for generating formulas regardless of whether a local or remote model is used.
from transformers import AutoTokenizer, AutoModelForCausalLM
import requests
import json
class LLMFormulaGenerator:
def __init__(self, config):
self.config = config
self.tokenizer = None
self.model = None
if not config.use_remote:
self.load_local_model()
def load_local_model(self):
"""Loads a local language model"""
if self.config.model_path is None:
# Use a default model suitable for code generation
self.config.model_name = "Salesforce/codegen-350M-mono"
else:
self.config.model_name = self.config.model_path
print(f"Loading model {self.config.model_name} on {self.config.get_device_info()}")
self.tokenizer = AutoTokenizer.from_pretrained(self.config.model_name)
self.model = AutoModelForCausalLM.from_pretrained(
self.config.model_name,
torch_dtype=torch.float16 if self.config.backend != GPUBackend.CPU else torch.float32,
low_cpu_mem_usage=True
)
self.model.to(self.config.device)
self.model.eval()
def generate_formula(self, prompt, context=None):
"""Generates a formula based on a natural language prompt"""
if self.config.use_remote:
return self.generate_formula_remote(prompt, context)
else:
return self.generate_formula_local(prompt, context)
def generate_formula_local(self, prompt, context=None):
"""Generates a formula using a local model"""
# Construct the full prompt with context
full_prompt = self.construct_prompt(prompt, context)
# Tokenize and generate
inputs = self.tokenizer(full_prompt, return_tensors="pt").to(self.config.device)
with torch.no_grad():
outputs = self.model.generate(
**inputs,
max_new_tokens=200,
temperature=0.7,
do_sample=True,
top_p=0.95,
pad_token_id=self.tokenizer.eos_token_id
)
generated_text = self.tokenizer.decode(outputs[0], skip_special_tokens=True)
# Extract the formula from the generated text
formula = self.extract_formula(generated_text, full_prompt)
return formula
def generate_formula_remote(self, prompt, context=None):
"""Generates a formula using a remote API"""
full_prompt = self.construct_prompt(prompt, context)
headers = {
"Authorization": f"Bearer {self.config.api_key}",
"Content-Type": "application/json"
}
data = {
"prompt": full_prompt,
"max_tokens": 200,
"temperature": 0.7
}
response = requests.post(
self.config.api_endpoint,
headers=headers,
json=data
)
if response.status_code == 200:
result = response.json()
generated_text = result.get('choices', [{}])[0].get('text', '')
formula = self.extract_formula(generated_text, full_prompt)
return formula
else:
return f"#ERROR: API request failed with status {response.status_code}"
def construct_prompt(self, user_prompt, context=None):
"""Constructs a detailed prompt for the LLM"""
system_context = """You are a formula generator for a three-dimensional spreadsheet application.
Cell references use the format: COLUMN_LETTER + ROW_NUMBER + : + LAYER_NUMBER
Examples: A0:0, B5:2, AA10:5
Common operations:
- Addition: A1:0 + B1:0
- Subtraction: A1:0 - B1:0
- Multiplication: A1:0 * B1:0
- Division: A1:0 / B1:0
- Sum across layers: SUM(A1:0, A1:1, A1:2)
- Average: AVG(A1:0, B1:0, C1:0)
Generate only the formula without the leading = sign.
"""
context_info = ""
if context:
context_info = f"\nContext: {context}\n"
full_prompt = f"{system_context}{context_info}\nUser request: {user_prompt}\nFormula:"
return full_prompt
def extract_formula(self, generated_text, original_prompt):
"""Extracts the formula from the generated text"""
# Remove the original prompt from the generated text
formula_part = generated_text[len(original_prompt):].strip()
# Take only the first line or until a newline
lines = formula_part.split('\n')
formula = lines[0].strip()
# Remove any leading = if present
if formula.startswith('='):
formula = formula[1:]
# Remove any trailing punctuation or explanations
formula = formula.split('.')[0].strip()
formula = formula.split(',')[0].strip() if ',' in formula and 'SUM' not in formula and 'AVG' not in formula else formula
return formula
The formula generation process involves several steps. First, the user selects a cell and provides a natural language description of the desired calculation. The system gathers context about the spreadsheet, including the current cell position, nearby cell values, and the overall structure. This context is combined with the user's description to create a detailed prompt for the LLM. The model generates a formula, which is then parsed and validated before being inserted into the cell.
The LLM can generate formulas that reference cells across all three dimensions. For example, if a user wants to calculate the sum of values across multiple layers, they might describe it as "sum all values in column A row 5 across layers zero through ten". The LLM interprets this and generates a formula like "SUM(A5:0, A5:1, A5:2, A5:3, A5:4, A5:5, A5:6, A5:7, A5:8, A5:9, A5:10)".
Advanced formula generation includes support for three-dimensional operations. Users can request calculations that operate on entire planes or volumes within the spreadsheet. The LLM understands spatial relationships and can generate formulas that iterate over ranges in multiple dimensions. This capability transforms the spreadsheet into a powerful tool for multidimensional data analysis.
class FormulaAssistant:
def __init__(self, llm_generator, spreadsheet_data):
self.llm = llm_generator
self.data = spreadsheet_data
def generate_formula_for_cell(self, x, y, z, description):
"""Generates a formula for a specific cell based on a description"""
# Gather context about the current cell and surrounding area
context = self.gather_context(x, y, z)
# Generate the formula
formula = self.llm.generate_formula(description, context)
# Validate the formula
if self.validate_formula(formula):
return formula
else:
return f"#ERROR: Generated formula is invalid: {formula}"
def gather_context(self, x, y, z):
"""Gathers context about the spreadsheet around a specific cell"""
context_parts = []
# Current position
cell = self.data.get_cell(x, y, z)
context_parts.append(f"Current cell: {cell.get_coordinate_string()}")
# Nearby cells with values
nearby_values = []
for dx in range(-2, 3):
for dy in range(-2, 3):
if dx == 0 and dy == 0:
continue
check_cell = self.data.get_cell(x + dx, y + dy, z)
if check_cell.value is not None:
nearby_values.append(
f"{check_cell.get_coordinate_string()}={check_cell.value}"
)
if nearby_values:
context_parts.append(f"Nearby cells: {', '.join(nearby_values[:5])}")
# Layer information
context_parts.append(f"Current layer: {z}")
context_parts.append(f"Total layers: {self.data.dimensions['z']}")
return " | ".join(context_parts)
def validate_formula(self, formula):
"""Validates a generated formula"""
if not formula or formula.startswith('#ERROR'):
return False
# Check for basic syntax issues
if formula.count('(') != formula.count(')'):
return False
# Try to parse cell references
try:
import re
pattern = r'([A-Z]+)(\d+):(\d+)'
matches = re.findall(pattern, formula)
# Verify all references are within bounds
for match in matches:
col_letter, row_str, layer_str = match
row = int(row_str)
layer = int(layer_str)
if layer >= self.data.dimensions['z']:
return False
if row >= self.data.dimensions['y']:
return False
return True
except Exception:
return False
def suggest_formulas(self, x, y, z):
"""Suggests possible formulas based on the cell's position and context"""
suggestions = []
# Suggest sum of row
if x > 0:
row_sum = " + ".join([
f"{self._col_to_letter(i)}{y}:{z}"
for i in range(x)
])
suggestions.append(("Sum of row to the left", row_sum))
# Suggest sum of column
if y > 0:
col_sum = " + ".join([
f"{self._col_to_letter(x)}{i}:{z}"
for i in range(y)
])
suggestions.append(("Sum of column above", col_sum))
# Suggest sum across layers
if z > 0:
layer_sum = " + ".join([
f"{self._col_to_letter(x)}{y}:{i}"
for i in range(z + 1)
])
suggestions.append(("Sum across layers", layer_sum))
return suggestions
def _col_to_letter(self, n):
"""Converts column number to letter"""
result = ""
while n >= 0:
result = chr(65 + (n % 26)) + result
n = n // 26 - 1
if n < 0:
break
return result
GRAPHICS AND VISUALIZATION
The graphics subsystem transforms spreadsheet data into visual representations. Three-dimensional spreadsheets offer unique visualization opportunities because the data naturally exists in three dimensions. The system supports various chart types including three-dimensional scatter plots, surface plots, volumetric visualizations, and traditional two-dimensional charts derived from three-dimensional data.
The visualization engine extracts data from specified ranges in the spreadsheet and renders it using graphics libraries. Users can select ranges that span multiple dimensions and choose appropriate visualization types. The system automatically determines suitable default visualizations based on the data characteristics and dimensionality.
import numpy as np
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D
from matplotlib.backends.backend_tkagg import FigureCanvasTkAgg
class GraphicsEngine:
def __init__(self, spreadsheet_data):
self.data = spreadsheet_data
self.figure = None
self.canvas = None
def extract_data_range(self, x_start, y_start, z_start, x_end, y_end, z_end):
"""Extracts data from a three-dimensional range"""
data_points = []
for x in range(x_start, x_end + 1):
for y in range(y_start, y_end + 1):
for z in range(z_start, z_end + 1):
cell = self.data.get_cell(x, y, z)
if cell.value is not None:
try:
value = float(cell.value)
data_points.append((x, y, z, value))
except (ValueError, TypeError):
pass
return data_points
def create_3d_scatter(self, data_points, title="3D Scatter Plot"):
"""Creates a three-dimensional scatter plot"""
if not data_points:
return None
self.figure = plt.figure(figsize=(10, 8))
ax = self.figure.add_subplot(111, projection='3d')
xs = [p[0] for p in data_points]
ys = [p[1] for p in data_points]
zs = [p[2] for p in data_points]
values = [p[3] for p in data_points]
scatter = ax.scatter(xs, ys, zs, c=values, cmap='viridis',
marker='o', s=50, alpha=0.6)
ax.set_xlabel('X (Column)')
ax.set_ylabel('Y (Row)')
ax.set_zlabel('Z (Layer)')
ax.set_title(title)
self.figure.colorbar(scatter, ax=ax, label='Value')
return self.figure
def create_surface_plot(self, x_start, y_start, z_layer, x_end, y_end):
"""Creates a surface plot from a layer of data"""
x_range = x_end - x_start + 1
y_range = y_end - y_start + 1
X = np.arange(x_start, x_end + 1)
Y = np.arange(y_start, y_end + 1)
X, Y = np.meshgrid(X, Y)
Z = np.zeros((y_range, x_range))
for i, y in enumerate(range(y_start, y_end + 1)):
for j, x in enumerate(range(x_start, x_end + 1)):
cell = self.data.get_cell(x, y, z_layer)
if cell.value is not None:
try:
Z[i, j] = float(cell.value)
except (ValueError, TypeError):
Z[i, j] = 0
else:
Z[i, j] = 0
self.figure = plt.figure(figsize=(10, 8))
ax = self.figure.add_subplot(111, projection='3d')
surf = ax.plot_surface(X, Y, Z, cmap='viridis', alpha=0.8)
ax.set_xlabel('X (Column)')
ax.set_ylabel('Y (Row)')
ax.set_zlabel('Value')
ax.set_title(f'Surface Plot - Layer {z_layer}')
self.figure.colorbar(surf, ax=ax, label='Value')
return self.figure
def create_layer_comparison(self, x_col, y_row, z_start, z_end):
"""Creates a line plot comparing values across layers"""
layers = []
values = []
for z in range(z_start, z_end + 1):
cell = self.data.get_cell(x_col, y_row, z)
if cell.value is not None:
try:
value = float(cell.value)
layers.append(z)
values.append(value)
except (ValueError, TypeError):
pass
self.figure = plt.figure(figsize=(10, 6))
ax = self.figure.add_subplot(111)
ax.plot(layers, values, marker='o', linestyle='-', linewidth=2)
ax.set_xlabel('Layer')
ax.set_ylabel('Value')
ax.set_title(f'Value Progression - Cell ({x_col}, {y_row})')
ax.grid(True, alpha=0.3)
return self.figure
def create_heatmap(self, z_layer, x_start, y_start, x_end, y_end):
"""Creates a heatmap for a specific layer"""
x_range = x_end - x_start + 1
y_range = y_end - y_start + 1
data = np.zeros((y_range, x_range))
for i, y in enumerate(range(y_start, y_end + 1)):
for j, x in enumerate(range(x_start, x_end + 1)):
cell = self.data.get_cell(x, y, z_layer)
if cell.value is not None:
try:
data[i, j] = float(cell.value)
except (ValueError, TypeError):
data[i, j] = 0
else:
data[i, j] = 0
self.figure = plt.figure(figsize=(10, 8))
ax = self.figure.add_subplot(111)
im = ax.imshow(data, cmap='viridis', aspect='auto',
extent=[x_start, x_end, y_end, y_start])
ax.set_xlabel('X (Column)')
ax.set_ylabel('Y (Row)')
ax.set_title(f'Heatmap - Layer {z_layer}')
self.figure.colorbar(im, ax=ax, label='Value')
return self.figure
def embed_in_tkinter(self, parent_frame):
"""Embeds the current figure in a Tkinter frame"""
if self.figure is None:
return None
if self.canvas:
self.canvas.get_tk_widget().destroy()
self.canvas = FigureCanvasTkAgg(self.figure, master=parent_frame)
self.canvas.draw()
self.canvas.get_tk_widget().pack(fill=tk.BOTH, expand=True)
return self.canvas
The visualization system integrates with the user interface to provide interactive charting capabilities. Users can select data ranges, choose visualization types, and customize chart properties. The charts update automatically when the underlying data changes, maintaining a live connection between the spreadsheet and its visual representations.
Advanced visualization features include animation across layers, allowing users to see how data evolves through the depth dimension. The system can generate animated plots that step through layers sequentially, creating a movie-like visualization of three-dimensional data trends. This proves particularly valuable for time-series data where layers represent different time periods.
ADVANCED FEATURES AND OPTIMIZATIONS
The three-dimensional spreadsheet application includes several advanced features that enhance usability and performance. Undo and redo functionality maintains a history of user actions, allowing them to reverse changes or restore previous states. The system implements a command pattern where each user action is encapsulated as a command object that can be executed, undone, and redone.
from collections import deque
class Command:
def execute(self):
raise NotImplementedError
def undo(self):
raise NotImplementedError
class SetCellValueCommand(Command):
def __init__(self, spreadsheet_data, x, y, z, new_value):
self.data = spreadsheet_data
self.x = x
self.y = y
self.z = z
self.new_value = new_value
self.old_value = None
self.old_formula = None
def execute(self):
cell = self.data.get_cell(self.x, self.y, self.z)
self.old_value = cell.value
self.old_formula = cell.formula
cell.value = self.new_value
cell.formula = None
def undo(self):
cell = self.data.get_cell(self.x, self.y, self.z)
cell.value = self.old_value
cell.formula = self.old_formula
class SetCellFormulaCommand(Command):
def __init__(self, spreadsheet_data, calc_engine, x, y, z, new_formula):
self.data = spreadsheet_data
self.calc_engine = calc_engine
self.x = x
self.y = y
self.z = z
self.new_formula = new_formula
self.old_value = None
self.old_formula = None
def execute(self):
cell = self.data.get_cell(self.x, self.y, self.z)
self.old_value = cell.value
self.old_formula = cell.formula
cell.formula = self.new_formula
self.calc_engine.recalculate_all()
def undo(self):
cell = self.data.get_cell(self.x, self.y, self.z)
cell.value = self.old_value
cell.formula = self.old_formula
self.calc_engine.recalculate_all()
class CommandHistory:
def __init__(self, max_history=100):
self.undo_stack = deque(maxlen=max_history)
self.redo_stack = deque(maxlen=max_history)
def execute_command(self, command):
command.execute()
self.undo_stack.append(command)
self.redo_stack.clear()
def undo(self):
if self.undo_stack:
command = self.undo_stack.pop()
command.undo()
self.redo_stack.append(command)
return True
return False
def redo(self):
if self.redo_stack:
command = self.redo_stack.pop()
command.execute()
self.undo_stack.append(command)
return True
return False
def can_undo(self):
return len(self.undo_stack) > 0
def can_redo(self):
return len(self.redo_stack) > 0
Performance optimization focuses on efficient calculation and rendering. The calculation engine uses dependency tracking to recalculate only affected cells when values change. This selective recalculation dramatically improves performance in large spreadsheets where most cells remain unchanged between edits. The rendering system implements viewport culling, drawing only the cells visible in the current view rather than the entire spreadsheet.
Data persistence allows users to save and load spreadsheets. The system serializes the three-dimensional cell structure to a file format that preserves values, formulas, formatting, and metadata. The file format uses JSON for human readability and ease of debugging, though binary formats could be implemented for better performance with very large spreadsheets.
import json
import os
class SpreadsheetPersistence:
def __init__(self, spreadsheet_data):
self.data = spreadsheet_data
def save_to_file(self, filename):
"""Saves the spreadsheet to a JSON file"""
spreadsheet_dict = {
'dimensions': self.data.dimensions,
'cells': []
}
for coord, cell in self.data.cells.items():
if cell.value is not None or cell.formula is not None:
cell_dict = {
'x': cell.x,
'y': cell.y,
'z': cell.z,
'value': cell.value,
'formula': cell.formula,
'metadata': cell.metadata
}
spreadsheet_dict['cells'].append(cell_dict)
with open(filename, 'w') as f:
json.dump(spreadsheet_dict, f, indent=2)
return True
def load_from_file(self, filename):
"""Loads a spreadsheet from a JSON file"""
if not os.path.exists(filename):
return False
with open(filename, 'r') as f:
spreadsheet_dict = json.load(f)
self.data.dimensions = spreadsheet_dict['dimensions']
self.data.cells.clear()
for cell_dict in spreadsheet_dict['cells']:
x = cell_dict['x']
y = cell_dict['y']
z = cell_dict['z']
cell = self.data.get_cell(x, y, z)
cell.value = cell_dict.get('value')
cell.formula = cell_dict.get('formula')
cell.metadata = cell_dict.get('metadata', {})
return True
def export_to_csv_layers(self, base_filename):
"""Exports each layer to a separate CSV file"""
for z in range(self.data.dimensions['z']):
filename = f"{base_filename}_layer_{z}.csv"
self.export_layer_to_csv(z, filename)
def export_layer_to_csv(self, layer, filename):
"""Exports a single layer to CSV"""
max_x = 0
max_y = 0
for coord in self.data.cells.keys():
if coord[2] == layer:
max_x = max(max_x, coord[0])
max_y = max(max_y, coord[1])
with open(filename, 'w') as f:
for y in range(max_y + 1):
row_values = []
for x in range(max_x + 1):
cell = self.data.get_cell(x, y, layer)
value = cell.value if cell.value is not None else ""
row_values.append(str(value))
f.write(','.join(row_values) + '\n')
The application supports collaborative features through a plugin architecture. Multiple users can work on the same spreadsheet simultaneously with conflict resolution and change tracking. The system maintains a log of all modifications with timestamps and user identifiers, enabling audit trails and version control.
COMPLETE RUNNING EXAMPLE
The following complete implementation demonstrates all components working together in a production-ready three-dimensional spreadsheet application. This implementation includes the data model, calculation engine, user interface, LLM integration, graphics system, and all supporting features.
#!/usr/bin/env python3
"""
Three-Dimensional Spreadsheet Application with LLM Integration
A complete implementation of a 3D spreadsheet with formula generation,
visualization, and support for multiple GPU backends.
"""
import tkinter as tk
from tkinter import ttk, filedialog, messagebox
import torch
import platform
import numpy as np
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D
from matplotlib.backends.backend_tkagg import FigureCanvasTkAgg
from transformers import AutoTokenizer, AutoModelForCausalLM
import requests
import json
import re
from collections import deque
from enum import Enum
import os
import threading
class GPUBackend(Enum):
"""Enumeration of supported GPU backends"""
CUDA = "cuda"
MPS = "mps"
CPU = "cpu"
class Cell:
"""Represents a single cell in the three-dimensional spreadsheet"""
def __init__(self, x, y, z):
self.x = x
self.y = y
self.z = z
self.value = None
self.formula = None
self.formatted_value = None
self.dependencies = set()
self.dependents = set()
self.metadata = {}
def get_coordinate_string(self):
"""Returns a string representation like 'A1:0' for the cell"""
col_letter = self._number_to_column_letter(self.x)
return f"{col_letter}{self.y}:{self.z}"
def _number_to_column_letter(self, n):
"""Converts column number to letter(s)"""
result = ""
while n >= 0:
result = chr(65 + (n % 26)) + result
n = n // 26 - 1
if n < 0:
break
return result
class SpreadsheetData:
"""Manages the three-dimensional cell data structure"""
def __init__(self):
self.cells = {}
self.dimensions = {'x': 100, 'y': 1000, 'z': 50}
def get_cell(self, x, y, z):
"""Retrieves a cell, creating it if it doesn't exist"""
key = (x, y, z)
if key not in self.cells:
self.cells[key] = Cell(x, y, z)
return self.cells[key]
def set_cell_value(self, x, y, z, value):
"""Sets the value of a cell"""
cell = self.get_cell(x, y, z)
cell.value = value
cell.formula = None
return cell
def set_cell_formula(self, x, y, z, formula):
"""Sets the formula for a cell"""
cell = self.get_cell(x, y, z)
cell.formula = formula
return cell
def clear_cell(self, x, y, z):
"""Clears a cell's value and formula"""
key = (x, y, z)
if key in self.cells:
self.cells[key].value = None
self.cells[key].formula = None
class CalculationEngine:
"""Handles formula parsing and calculation"""
def __init__(self, spreadsheet_data):
self.data = spreadsheet_data
self.calculation_order = []
def parse_formula(self, formula):
"""Parses a formula and extracts three-dimensional cell references"""
pattern = r'([A-Z]+)(\d+):(\d+)'
references = []
for match in re.finditer(pattern, formula):
col_letter = match.group(1)
row = int(match.group(2))
layer = int(match.group(3))
col = self._column_letter_to_number(col_letter)
references.append((col, row, layer))
return references
def _column_letter_to_number(self, letters):
"""Converts column letter(s) to number"""
result = 0
for char in letters:
result = result * 26 + (ord(char) - 64)
return result - 1
def build_dependency_graph(self):
"""Builds the dependency graph for all cells with formulas"""
for cell in self.data.cells.values():
cell.dependencies.clear()
cell.dependents.clear()
for coord, cell in self.data.cells.items():
if cell.formula:
references = self.parse_formula(cell.formula)
for ref_coord in references:
ref_cell = self.data.get_cell(*ref_coord)
cell.dependencies.add(ref_coord)
ref_cell.dependents.add(coord)
def topological_sort(self):
"""Performs topological sort to determine calculation order"""
in_degree = {}
for coord, cell in self.data.cells.items():
if cell.formula:
in_degree[coord] = len(cell.dependencies)
queue = deque([coord for coord, degree in in_degree.items() if degree == 0])
order = []
while queue:
coord = queue.popleft()
order.append(coord)
cell = self.data.cells[coord]
for dependent_coord in cell.dependents:
if dependent_coord in in_degree:
in_degree[dependent_coord] -= 1
if in_degree[dependent_coord] == 0:
queue.append(dependent_coord)
self.calculation_order = order
return order
def evaluate_formula(self, formula):
"""Evaluates a formula and returns the result"""
pattern = r'([A-Z]+)(\d+):(\d+)'
def replace_reference(match):
col_letter = match.group(1)
row = int(match.group(2))
layer = int(match.group(3))
col = self._column_letter_to_number(col_letter)
cell = self.data.get_cell(col, row, layer)
return str(cell.value if cell.value is not None else 0)
expression = re.sub(pattern, replace_reference, formula)
expression = expression.replace('SUM(', 'sum([')
expression = expression.replace('AVG(', '(sum([')
expression = expression.replace(')', '])')
if 'AVG' in formula:
avg_pattern = r'\(sum\(\[(.*?)\]\)\)'
def avg_replace(m):
items = m.group(1)
return f"(sum([{items}])/len([{items}]))"
expression = re.sub(avg_pattern, avg_replace, expression)
try:
result = eval(expression, {"__builtins__": {}}, {"sum": sum, "len": len})
return result
except Exception as e:
return f"#ERROR: {str(e)}"
def recalculate_all(self):
"""Recalculates all cells with formulas in dependency order"""
self.build_dependency_graph()
self.topological_sort()
for coord in self.calculation_order:
cell = self.data.cells[coord]
if cell.formula:
cell.value = self.evaluate_formula(cell.formula)
class LLMConfig:
"""Configuration for LLM integration"""
def __init__(self):
self.backend = self.detect_gpu_backend()
self.device = self.get_device()
self.model_path = None
self.model_name = None
self.use_remote = False
self.api_key = None
self.api_endpoint = None
def detect_gpu_backend(self):
"""Detects the available GPU backend"""
if torch.cuda.is_available():
return GPUBackend.CUDA
elif platform.system() == 'Darwin' and hasattr(torch.backends, 'mps') and torch.backends.mps.is_available():
return GPUBackend.MPS
else:
return GPUBackend.CPU
def get_device(self):
"""Returns the appropriate torch device"""
if self.backend == GPUBackend.CUDA:
return torch.device("cuda")
elif self.backend == GPUBackend.MPS:
return torch.device("mps")
else:
return torch.device("cpu")
def get_device_info(self):
"""Returns information about the current device"""
if self.backend == GPUBackend.CUDA:
return f"CUDA - {torch.cuda.get_device_name(0)}"
elif self.backend == GPUBackend.MPS:
return "Apple Metal Performance Shaders"
else:
return "CPU"
class LLMFormulaGenerator:
"""Generates formulas using language models"""
def __init__(self, config):
self.config = config
self.tokenizer = None
self.model = None
self.model_loaded = False
def load_local_model(self):
"""Loads a local language model"""
if self.config.model_path is None:
self.config.model_name = "Salesforce/codegen-350M-mono"
else:
self.config.model_name = self.config.model_path
print(f"Loading model {self.config.model_name} on {self.config.get_device_info()}")
try:
self.tokenizer = AutoTokenizer.from_pretrained(self.config.model_name)
self.model = AutoModelForCausalLM.from_pretrained(
self.config.model_name,
torch_dtype=torch.float16 if self.config.backend != GPUBackend.CPU else torch.float32,
low_cpu_mem_usage=True
)
self.model.to(self.config.device)
self.model.eval()
self.model_loaded = True
print("Model loaded successfully")
except Exception as e:
print(f"Error loading model: {e}")
self.model_loaded = False
def generate_formula(self, prompt, context=None):
"""Generates a formula based on a natural language prompt"""
if self.config.use_remote:
return self.generate_formula_remote(prompt, context)
else:
if not self.model_loaded:
return "#ERROR: Model not loaded"
return self.generate_formula_local(prompt, context)
def generate_formula_local(self, prompt, context=None):
"""Generates a formula using a local model"""
full_prompt = self.construct_prompt(prompt, context)
inputs = self.tokenizer(full_prompt, return_tensors="pt").to(self.config.device)
with torch.no_grad():
outputs = self.model.generate(
**inputs,
max_new_tokens=200,
temperature=0.7,
do_sample=True,
top_p=0.95,
pad_token_id=self.tokenizer.eos_token_id
)
generated_text = self.tokenizer.decode(outputs[0], skip_special_tokens=True)
formula = self.extract_formula(generated_text, full_prompt)
return formula
def generate_formula_remote(self, prompt, context=None):
"""Generates a formula using a remote API"""
full_prompt = self.construct_prompt(prompt, context)
headers = {
"Authorization": f"Bearer {self.config.api_key}",
"Content-Type": "application/json"
}
data = {
"prompt": full_prompt,
"max_tokens": 200,
"temperature": 0.7
}
try:
response = requests.post(
self.config.api_endpoint,
headers=headers,
json=data,
timeout=30
)
if response.status_code == 200:
result = response.json()
generated_text = result.get('choices', [{}])[0].get('text', '')
formula = self.extract_formula(generated_text, full_prompt)
return formula
else:
return f"#ERROR: API request failed with status {response.status_code}"
except Exception as e:
return f"#ERROR: {str(e)}"
def construct_prompt(self, user_prompt, context=None):
"""Constructs a detailed prompt for the LLM"""
system_context = """You are a formula generator for a three-dimensional spreadsheet application.
Cell references use the format: COLUMN_LETTER + ROW_NUMBER + : + LAYER_NUMBER
Examples: A0:0, B5:2, AA10:5
Common operations:
- Addition: A1:0 + B1:0
- Subtraction: A1:0 - B1:0
- Multiplication: A1:0 * B1:0
- Division: A1:0 / B1:0
- Sum: SUM(A1:0, A1:1, A1:2)
- Average: AVG(A1:0, B1:0, C1:0)
Generate only the formula without the leading = sign.
"""
context_info = ""
if context:
context_info = f"\nContext: {context}\n"
full_prompt = f"{system_context}{context_info}\nUser request: {user_prompt}\nFormula:"
return full_prompt
def extract_formula(self, generated_text, original_prompt):
"""Extracts the formula from the generated text"""
formula_part = generated_text[len(original_prompt):].strip()
lines = formula_part.split('\n')
formula = lines[0].strip()
if formula.startswith('='):
formula = formula[1:]
formula = formula.split('.')[0].strip()
return formula
class FormulaAssistant:
"""Assists with formula generation and suggestions"""
def __init__(self, llm_generator, spreadsheet_data):
self.llm = llm_generator
self.data = spreadsheet_data
def generate_formula_for_cell(self, x, y, z, description):
"""Generates a formula for a specific cell based on a description"""
context = self.gather_context(x, y, z)
formula = self.llm.generate_formula(description, context)
if self.validate_formula(formula):
return formula
else:
return f"#ERROR: Generated formula is invalid: {formula}"
def gather_context(self, x, y, z):
"""Gathers context about the spreadsheet around a specific cell"""
context_parts = []
cell = self.data.get_cell(x, y, z)
context_parts.append(f"Current cell: {cell.get_coordinate_string()}")
nearby_values = []
for dx in range(-2, 3):
for dy in range(-2, 3):
if dx == 0 and dy == 0:
continue
check_cell = self.data.get_cell(x + dx, y + dy, z)
if check_cell.value is not None:
nearby_values.append(
f"{check_cell.get_coordinate_string()}={check_cell.value}"
)
if nearby_values:
context_parts.append(f"Nearby cells: {', '.join(nearby_values[:5])}")
context_parts.append(f"Current layer: {z}")
context_parts.append(f"Total layers: {self.data.dimensions['z']}")
return " | ".join(context_parts)
def validate_formula(self, formula):
"""Validates a generated formula"""
if not formula or formula.startswith('#ERROR'):
return False
if formula.count('(') != formula.count(')'):
return False
try:
pattern = r'([A-Z]+)(\d+):(\d+)'
matches = re.findall(pattern, formula)
for match in matches:
col_letter, row_str, layer_str = match
row = int(row_str)
layer = int(layer_str)
if layer >= self.data.dimensions['z']:
return False
if row >= self.data.dimensions['y']:
return False
return True
except Exception:
return False
class GraphicsEngine:
"""Handles visualization and charting"""
def __init__(self, spreadsheet_data):
self.data = spreadsheet_data
self.figure = None
self.canvas = None
def extract_data_range(self, x_start, y_start, z_start, x_end, y_end, z_end):
"""Extracts data from a three-dimensional range"""
data_points = []
for x in range(x_start, x_end + 1):
for y in range(y_start, y_end + 1):
for z in range(z_start, z_end + 1):
cell = self.data.get_cell(x, y, z)
if cell.value is not None:
try:
value = float(cell.value)
data_points.append((x, y, z, value))
except (ValueError, TypeError):
pass
return data_points
def create_3d_scatter(self, data_points, title="3D Scatter Plot"):
"""Creates a three-dimensional scatter plot"""
if not data_points:
return None
self.figure = plt.figure(figsize=(10, 8))
ax = self.figure.add_subplot(111, projection='3d')
xs = [p[0] for p in data_points]
ys = [p[1] for p in data_points]
zs = [p[2] for p in data_points]
values = [p[3] for p in data_points]
scatter = ax.scatter(xs, ys, zs, c=values, cmap='viridis',
marker='o', s=50, alpha=0.6)
ax.set_xlabel('X (Column)')
ax.set_ylabel('Y (Row)')
ax.set_zlabel('Z (Layer)')
ax.set_title(title)
self.figure.colorbar(scatter, ax=ax, label='Value')
return self.figure
def create_surface_plot(self, x_start, y_start, z_layer, x_end, y_end):
"""Creates a surface plot from a layer of data"""
x_range = x_end - x_start + 1
y_range = y_end - y_start + 1
X = np.arange(x_start, x_end + 1)
Y = np.arange(y_start, y_end + 1)
X, Y = np.meshgrid(X, Y)
Z = np.zeros((y_range, x_range))
for i, y in enumerate(range(y_start, y_end + 1)):
for j, x in enumerate(range(x_start, x_end + 1)):
cell = self.data.get_cell(x, y, z_layer)
if cell.value is not None:
try:
Z[i, j] = float(cell.value)
except (ValueError, TypeError):
Z[i, j] = 0
else:
Z[i, j] = 0
self.figure = plt.figure(figsize=(10, 8))
ax = self.figure.add_subplot(111, projection='3d')
surf = ax.plot_surface(X, Y, Z, cmap='viridis', alpha=0.8)
ax.set_xlabel('X (Column)')
ax.set_ylabel('Y (Row)')
ax.set_zlabel('Value')
ax.set_title(f'Surface Plot - Layer {z_layer}')
self.figure.colorbar(surf, ax=ax, label='Value')
return self.figure
def create_layer_comparison(self, x_col, y_row, z_start, z_end):
"""Creates a line plot comparing values across layers"""
layers = []
values = []
for z in range(z_start, z_end + 1):
cell = self.data.get_cell(x_col, y_row, z)
if cell.value is not None:
try:
value = float(cell.value)
layers.append(z)
values.append(value)
except (ValueError, TypeError):
pass
self.figure = plt.figure(figsize=(10, 6))
ax = self.figure.add_subplot(111)
ax.plot(layers, values, marker='o', linestyle='-', linewidth=2)
ax.set_xlabel('Layer')
ax.set_ylabel('Value')
ax.set_title(f'Value Progression - Cell ({x_col}, {y_row})')
ax.grid(True, alpha=0.3)
return self.figure
def create_heatmap(self, z_layer, x_start, y_start, x_end, y_end):
"""Creates a heatmap for a specific layer"""
x_range = x_end - x_start + 1
y_range = y_end - y_start + 1
data = np.zeros((y_range, x_range))
for i, y in enumerate(range(y_start, y_end + 1)):
for j, x in enumerate(range(x_start, x_end + 1)):
cell = self.data.get_cell(x, y, z_layer)
if cell.value is not None:
try:
data[i, j] = float(cell.value)
except (ValueError, TypeError):
data[i, j] = 0
else:
data[i, j] = 0
self.figure = plt.figure(figsize=(10, 8))
ax = self.figure.add_subplot(111)
im = ax.imshow(data, cmap='viridis', aspect='auto',
extent=[x_start, x_end, y_end, y_start])
ax.set_xlabel('X (Column)')
ax.set_ylabel('Y (Row)')
ax.set_title(f'Heatmap - Layer {z_layer}')
self.figure.colorbar(im, ax=ax, label='Value')
return self.figure
def embed_in_tkinter(self, parent_frame):
"""Embeds the current figure in a Tkinter frame"""
if self.figure is None:
return None
if self.canvas:
self.canvas.get_tk_widget().destroy()
self.canvas = FigureCanvasTkAgg(self.figure, master=parent_frame)
self.canvas.draw()
self.canvas.get_tk_widget().pack(fill=tk.BOTH, expand=True)
return self.canvas
class Command:
"""Base class for command pattern"""
def execute(self):
raise NotImplementedError
def undo(self):
raise NotImplementedError
class SetCellValueCommand(Command):
"""Command for setting a cell value"""
def __init__(self, spreadsheet_data, x, y, z, new_value):
self.data = spreadsheet_data
self.x = x
self.y = y
self.z = z
self.new_value = new_value
self.old_value = None
self.old_formula = None
def execute(self):
cell = self.data.get_cell(self.x, self.y, self.z)
self.old_value = cell.value
self.old_formula = cell.formula
cell.value = self.new_value
cell.formula = None
def undo(self):
cell = self.data.get_cell(self.x, self.y, self.z)
cell.value = self.old_value
cell.formula = self.old_formula
class SetCellFormulaCommand(Command):
"""Command for setting a cell formula"""
def __init__(self, spreadsheet_data, calc_engine, x, y, z, new_formula):
self.data = spreadsheet_data
self.calc_engine = calc_engine
self.x = x
self.y = y
self.z = z
self.new_formula = new_formula
self.old_value = None
self.old_formula = None
def execute(self):
cell = self.data.get_cell(self.x, self.y, self.z)
self.old_value = cell.value
self.old_formula = cell.formula
cell.formula = self.new_formula
self.calc_engine.recalculate_all()
def undo(self):
cell = self.data.get_cell(self.x, self.y, self.z)
cell.value = self.old_value
cell.formula = self.old_formula
self.calc_engine.recalculate_all()
class CommandHistory:
"""Manages command history for undo/redo"""
def __init__(self, max_history=100):
self.undo_stack = deque(maxlen=max_history)
self.redo_stack = deque(maxlen=max_history)
def execute_command(self, command):
command.execute()
self.undo_stack.append(command)
self.redo_stack.clear()
def undo(self):
if self.undo_stack:
command = self.undo_stack.pop()
command.undo()
self.redo_stack.append(command)
return True
return False
def redo(self):
if self.redo_stack:
command = self.redo_stack.pop()
command.execute()
self.undo_stack.append(command)
return True
return False
def can_undo(self):
return len(self.undo_stack) > 0
def can_redo(self):
return len(self.redo_stack) > 0
class SpreadsheetPersistence:
"""Handles saving and loading spreadsheets"""
def __init__(self, spreadsheet_data):
self.data = spreadsheet_data
def save_to_file(self, filename):
"""Saves the spreadsheet to a JSON file"""
spreadsheet_dict = {
'dimensions': self.data.dimensions,
'cells': []
}
for coord, cell in self.data.cells.items():
if cell.value is not None or cell.formula is not None:
cell_dict = {
'x': cell.x,
'y': cell.y,
'z': cell.z,
'value': cell.value,
'formula': cell.formula,
'metadata': cell.metadata
}
spreadsheet_dict['cells'].append(cell_dict)
with open(filename, 'w') as f:
json.dump(spreadsheet_dict, f, indent=2)
return True
def load_from_file(self, filename):
"""Loads a spreadsheet from a JSON file"""
if not os.path.exists(filename):
return False
with open(filename, 'r') as f:
spreadsheet_dict = json.load(f)
self.data.dimensions = spreadsheet_dict['dimensions']
self.data.cells.clear()
for cell_dict in spreadsheet_dict['cells']:
x = cell_dict['x']
y = cell_dict['y']
z = cell_dict['z']
cell = self.data.get_cell(x, y, z)
cell.value = cell_dict.get('value')
cell.formula = cell_dict.get('formula')
cell.metadata = cell_dict.get('metadata', {})
return True
class SpreadsheetUI:
"""Main user interface for the spreadsheet application"""
def __init__(self, root, spreadsheet_data, calculation_engine, command_history, formula_assistant, graphics_engine, persistence):
self.root = root
self.data = spreadsheet_data
self.calc_engine = calculation_engine
self.history = command_history
self.formula_assistant = formula_assistant
self.graphics = graphics_engine
self.persistence = persistence
self.current_layer = 0
self.selected_cell = (0, 0, 0)
self.cell_width = 100
self.cell_height = 25
self.visible_cols = 20
self.visible_rows = 30
self.setup_ui()
self.create_menu()
def setup_ui(self):
"""Sets up the user interface components"""
main_frame = ttk.Frame(self.root)
main_frame.pack(fill=tk.BOTH, expand=True)
toolbar = ttk.Frame(main_frame)
toolbar.pack(side=tk.TOP, fill=tk.X, padx=5, pady=5)
ttk.Label(toolbar, text="Layer:").pack(side=tk.LEFT, padx=5)
self.layer_spinbox = ttk.Spinbox(
toolbar,
from_=0,
to=self.data.dimensions['z']-1,
width=10,
command=self.on_layer_change
)
self.layer_spinbox.set(0)
self.layer_spinbox.pack(side=tk.LEFT, padx=5)
ttk.Button(toolbar, text="Previous Layer",
command=self.previous_layer).pack(side=tk.LEFT, padx=5)
ttk.Button(toolbar, text="Next Layer",
command=self.next_layer).pack(side=tk.LEFT, padx=5)
ttk.Button(toolbar, text="Generate Formula",
command=self.open_formula_generator).pack(side=tk.LEFT, padx=20)
ttk.Button(toolbar, text="Visualize",
command=self.open_visualization_dialog).pack(side=tk.LEFT, padx=5)
formula_frame = ttk.Frame(main_frame)
formula_frame.pack(side=tk.TOP, fill=tk.X, padx=5, pady=5)
self.cell_label = ttk.Label(formula_frame, text="A0:0", width=10)
self.cell_label.pack(side=tk.LEFT, padx=5)
self.formula_entry = ttk.Entry(formula_frame)
self.formula_entry.pack(side=tk.LEFT, fill=tk.X, expand=True, padx=5)
self.formula_entry.bind('<Return>', self.on_formula_submit)
grid_frame = ttk.Frame(main_frame)
grid_frame.pack(side=tk.TOP, fill=tk.BOTH, expand=True, padx=5, pady=5)
self.canvas = tk.Canvas(grid_frame, bg='white')
self.canvas.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)
v_scrollbar = ttk.Scrollbar(grid_frame, orient=tk.VERTICAL,
command=self.canvas.yview)
v_scrollbar.pack(side=tk.RIGHT, fill=tk.Y)
h_scrollbar = ttk.Scrollbar(main_frame, orient=tk.HORIZONTAL,
command=self.canvas.xview)
h_scrollbar.pack(side=tk.BOTTOM, fill=tk.X, padx=5)
self.canvas.configure(yscrollcommand=v_scrollbar.set,
xscrollcommand=h_scrollbar.set)
self.canvas.bind('<Button-1>', self.on_cell_click)
self.canvas.bind('<Double-Button-1>', self.on_cell_double_click)
self.draw_grid()
def create_menu(self):
"""Creates the application menu"""
menubar = tk.Menu(self.root)
self.root.config(menu=menubar)
file_menu = tk.Menu(menubar, tearoff=0)
menubar.add_cascade(label="File", menu=file_menu)
file_menu.add_command(label="New", command=self.new_spreadsheet)
file_menu.add_command(label="Open", command=self.open_spreadsheet)
file_menu.add_command(label="Save", command=self.save_spreadsheet)
file_menu.add_command(label="Save As", command=self.save_spreadsheet_as)
file_menu.add_separator()
file_menu.add_command(label="Exit", command=self.root.quit)
edit_menu = tk.Menu(menubar, tearoff=0)
menubar.add_cascade(label="Edit", menu=edit_menu)
edit_menu.add_command(label="Undo", command=self.undo, accelerator="Ctrl+Z")
edit_menu.add_command(label="Redo", command=self.redo, accelerator="Ctrl+Y")
self.root.bind('<Control-z>', lambda e: self.undo())
self.root.bind('<Control-y>', lambda e: self.redo())
def draw_grid(self):
"""Draws the grid for the current layer"""
self.canvas.delete('all')
for col in range(self.visible_cols):
x = col * self.cell_width
self.canvas.create_rectangle(
x, 0, x + self.cell_width, self.cell_height,
fill='lightgray', outline='black'
)
col_letter = self._number_to_column_letter(col)
self.canvas.create_text(
x + self.cell_width // 2, self.cell_height // 2,
text=col_letter, font=('Arial', 10, 'bold')
)
for row in range(self.visible_rows):
y = (row + 1) * self.cell_height
self.canvas.create_rectangle(
0, y, self.cell_width, y + self.cell_height,
fill='lightgray', outline='black'
)
self.canvas.create_text(
self.cell_width // 2, y + self.cell_height // 2,
text=str(row), font=('Arial', 10, 'bold')
)
for col in range(self.visible_cols):
x = col * self.cell_width
cell = self.data.get_cell(col, row, self.current_layer)
display_value = ""
if cell.value is not None:
display_value = str(cell.value)
self.canvas.create_rectangle(
x, y, x + self.cell_width, y + self.cell_height,
fill='white', outline='gray', tags=f'cell_{col}_{row}'
)
self.canvas.create_text(
x + 5, y + self.cell_height // 2,
text=display_value, anchor='w', tags=f'text_{col}_{row}'
)
self.highlight_selected_cell()
self.canvas.configure(
scrollregion=(0, 0,
self.visible_cols * self.cell_width,
(self.visible_rows + 1) * self.cell_height)
)
def _number_to_column_letter(self, n):
"""Converts column number to letter(s)"""
result = ""
while n >= 0:
result = chr(65 + (n % 26)) + result
n = n // 26 - 1
if n < 0:
break
return result
def highlight_selected_cell(self):
"""Highlights the currently selected cell"""
x, y, z = self.selected_cell
if z == self.current_layer:
canvas_x = x * self.cell_width
canvas_y = (y + 1) * self.cell_height
self.canvas.create_rectangle(
canvas_x, canvas_y,
canvas_x + self.cell_width,
canvas_y + self.cell_height,
outline='blue', width=2, tags='selection'
)
def on_cell_click(self, event):
"""Handles cell click events"""
canvas_x = self.canvas.canvasx(event.x)
canvas_y = self.canvas.canvasy(event.y)
col = int(canvas_x // self.cell_width)
row = int(canvas_y // self.cell_height) - 1
if row >= 0 and col >= 0:
self.selected_cell = (col, row, self.current_layer)
self.update_formula_bar()
self.draw_grid()
def on_cell_double_click(self, event):
"""Handles cell double-click for editing"""
self.formula_entry.focus_set()
self.formula_entry.select_range(0, tk.END)
def update_formula_bar(self):
"""Updates the formula bar with the selected cell's information"""
x, y, z = self.selected_cell
cell = self.data.get_cell(x, y, z)
self.cell_label.config(text=cell.get_coordinate_string())
if cell.formula:
self.formula_entry.delete(0, tk.END)
self.formula_entry.insert(0, '=' + cell.formula)
else:
self.formula_entry.delete(0, tk.END)
if cell.value is not None:
self.formula_entry.insert(0, str(cell.value))
def on_formula_submit(self, event):
"""Handles formula submission from the formula bar"""
x, y, z = self.selected_cell
formula_text = self.formula_entry.get()
if formula_text.startswith('='):
command = SetCellFormulaCommand(
self.data, self.calc_engine, x, y, z, formula_text[1:]
)
self.history.execute_command(command)
else:
try:
value = float(formula_text)
except ValueError:
value = formula_text
command = SetCellValueCommand(self.data, x, y, z, value)
self.history.execute_command(command)
self.draw_grid()
def on_layer_change(self):
"""Handles layer change from the spinbox"""
new_layer = int(self.layer_spinbox.get())
self.current_layer = new_layer
self.selected_cell = (self.selected_cell[0], self.selected_cell[1], new_layer)
self.draw_grid()
self.update_formula_bar()
def previous_layer(self):
"""Moves to the previous layer"""
if self.current_layer > 0:
self.current_layer -= 1
self.layer_spinbox.set(self.current_layer)
self.on_layer_change()
def next_layer(self):
"""Moves to the next layer"""
if self.current_layer < self.data.dimensions['z'] - 1:
self.current_layer += 1
self.layer_spinbox.set(self.current_layer)
self.on_layer_change()
def undo(self):
"""Performs undo operation"""
if self.history.undo():
self.draw_grid()
self.update_formula_bar()
def redo(self):
"""Performs redo operation"""
if self.history.redo():
self.draw_grid()
self.update_formula_bar()
def new_spreadsheet(self):
"""Creates a new spreadsheet"""
if messagebox.askyesno("New Spreadsheet", "Create a new spreadsheet? Unsaved changes will be lost."):
self.data.cells.clear()
self.history.undo_stack.clear()
self.history.redo_stack.clear()
self.current_layer = 0
self.selected_cell = (0, 0, 0)
self.draw_grid()
self.update_formula_bar()
def open_spreadsheet(self):
"""Opens an existing spreadsheet"""
filename = filedialog.askopenfilename(
title="Open Spreadsheet",
filetypes=[("JSON files", "*.json"), ("All files", "*.*")]
)
if filename:
if self.persistence.load_from_file(filename):
self.calc_engine.recalculate_all()
self.draw_grid()
self.update_formula_bar()
messagebox.showinfo("Success", "Spreadsheet loaded successfully")
else:
messagebox.showerror("Error", "Failed to load spreadsheet")
def save_spreadsheet(self):
"""Saves the current spreadsheet"""
filename = filedialog.asksaveasfilename(
title="Save Spreadsheet",
defaultextension=".json",
filetypes=[("JSON files", "*.json"), ("All files", "*.*")]
)
if filename:
if self.persistence.save_to_file(filename):
messagebox.showinfo("Success", "Spreadsheet saved successfully")
else:
messagebox.showerror("Error", "Failed to save spreadsheet")
def save_spreadsheet_as(self):
"""Saves the spreadsheet with a new name"""
self.save_spreadsheet()
def open_formula_generator(self):
"""Opens the formula generator dialog"""
dialog = tk.Toplevel(self.root)
dialog.title("Generate Formula")
dialog.geometry("500x300")
ttk.Label(dialog, text="Describe the formula you want to generate:").pack(pady=10)
description_text = tk.Text(dialog, height=5, width=60)
description_text.pack(pady=10, padx=10)
result_label = ttk.Label(dialog, text="", wraplength=450)
result_label.pack(pady=10)
def generate():
description = description_text.get("1.0", tk.END).strip()
if description:
result_label.config(text="Generating formula...")
dialog.update()
x, y, z = self.selected_cell
formula = self.formula_assistant.generate_formula_for_cell(x, y, z, description)
result_label.config(text=f"Generated formula: {formula}")
if not formula.startswith('#ERROR'):
if messagebox.askyesno("Apply Formula", f"Apply formula '{formula}' to cell {self.data.get_cell(x, y, z).get_coordinate_string()}?"):
command = SetCellFormulaCommand(
self.data, self.calc_engine, x, y, z, formula
)
self.history.execute_command(command)
self.draw_grid()
self.update_formula_bar()
dialog.destroy()
ttk.Button(dialog, text="Generate", command=generate).pack(pady=10)
ttk.Button(dialog, text="Cancel", command=dialog.destroy).pack(pady=5)
def open_visualization_dialog(self):
"""Opens the visualization dialog"""
dialog = tk.Toplevel(self.root)
dialog.title("Create Visualization")
dialog.geometry("600x700")
ttk.Label(dialog, text="Select Data Range:").pack(pady=10)
range_frame = ttk.Frame(dialog)
range_frame.pack(pady=10)
ttk.Label(range_frame, text="X Start:").grid(row=0, column=0, padx=5)
x_start_entry = ttk.Entry(range_frame, width=10)
x_start_entry.grid(row=0, column=1, padx=5)
x_start_entry.insert(0, "0")
ttk.Label(range_frame, text="Y Start:").grid(row=0, column=2, padx=5)
y_start_entry = ttk.Entry(range_frame, width=10)
y_start_entry.grid(row=0, column=3, padx=5)
y_start_entry.insert(0, "0")
ttk.Label(range_frame, text="Z Start:").grid(row=0, column=4, padx=5)
z_start_entry = ttk.Entry(range_frame, width=10)
z_start_entry.grid(row=0, column=5, padx=5)
z_start_entry.insert(0, "0")
ttk.Label(range_frame, text="X End:").grid(row=1, column=0, padx=5)
x_end_entry = ttk.Entry(range_frame, width=10)
x_end_entry.grid(row=1, column=1, padx=5)
x_end_entry.insert(0, "5")
ttk.Label(range_frame, text="Y End:").grid(row=1, column=2, padx=5)
y_end_entry = ttk.Entry(range_frame, width=10)
y_end_entry.grid(row=1, column=3, padx=5)
y_end_entry.insert(0, "5")
ttk.Label(range_frame, text="Z End:").grid(row=1, column=4, padx=5)
z_end_entry = ttk.Entry(range_frame, width=10)
z_end_entry.grid(row=1, column=5, padx=5)
z_end_entry.insert(0, "2")
ttk.Label(dialog, text="Visualization Type:").pack(pady=10)
viz_type = tk.StringVar(value="3D Scatter")
ttk.Radiobutton(dialog, text="3D Scatter Plot", variable=viz_type, value="3D Scatter").pack()
ttk.Radiobutton(dialog, text="Surface Plot", variable=viz_type, value="Surface").pack()
ttk.Radiobutton(dialog, text="Layer Comparison", variable=viz_type, value="Layer Comparison").pack()
ttk.Radiobutton(dialog, text="Heatmap", variable=viz_type, value="Heatmap").pack()
chart_frame = ttk.Frame(dialog)
chart_frame.pack(fill=tk.BOTH, expand=True, pady=10)
def create_visualization():
try:
x_start = int(x_start_entry.get())
y_start = int(y_start_entry.get())
z_start = int(z_start_entry.get())
x_end = int(x_end_entry.get())
y_end = int(y_end_entry.get())
z_end = int(z_end_entry.get())
viz = viz_type.get()
if viz == "3D Scatter":
data_points = self.graphics.extract_data_range(
x_start, y_start, z_start, x_end, y_end, z_end
)
self.graphics.create_3d_scatter(data_points)
elif viz == "Surface":
self.graphics.create_surface_plot(
x_start, y_start, z_start, x_end, y_end
)
elif viz == "Layer Comparison":
self.graphics.create_layer_comparison(
x_start, y_start, z_start, z_end
)
elif viz == "Heatmap":
self.graphics.create_heatmap(
z_start, x_start, y_start, x_end, y_end
)
for widget in chart_frame.winfo_children():
widget.destroy()
self.graphics.embed_in_tkinter(chart_frame)
except Exception as e:
messagebox.showerror("Error", f"Failed to create visualization: {str(e)}")
ttk.Button(dialog, text="Create Visualization", command=create_visualization).pack(pady=10)
ttk.Button(dialog, text="Close", command=dialog.destroy).pack(pady=5)
class SpreadsheetApplication:
"""Main application class"""
def __init__(self):
self.root = tk.Tk()
self.root.title("3D Spreadsheet with LLM Integration")
self.root.geometry("1200x800")
self.data = SpreadsheetData()
self.calc_engine = CalculationEngine(self.data)
self.history = CommandHistory()
self.persistence = SpreadsheetPersistence(self.data)
self.graphics = GraphicsEngine(self.data)
self.llm_config = LLMConfig()
print(f"Detected GPU backend: {self.llm_config.get_device_info()}")
self.llm_generator = LLMFormulaGenerator(self.llm_config)
self.formula_assistant = FormulaAssistant(self.llm_generator, self.data)
self.ui = SpreadsheetUI(
self.root,
self.data,
self.calc_engine,
self.history,
self.formula_assistant,
self.graphics,
self.persistence
)
self.populate_sample_data()
def populate_sample_data(self):
"""Populates the spreadsheet with sample data for demonstration"""
for z in range(3):
for x in range(5):
for y in range(5):
value = (x + 1) * (y + 1) * (z + 1)
self.data.set_cell_value(x, y, z, value)
self.data.set_cell_formula(5, 0, 0, "A0:0 + B0:0 + C0:0")
self.data.set_cell_formula(5, 1, 0, "SUM(A1:0, A1:1, A1:2)")
self.calc_engine.recalculate_all()
self.ui.draw_grid()
def run(self):
"""Runs the application"""
self.root.mainloop()
def main():
"""Main entry point"""
app = SpreadsheetApplication()
app.run()
if __name__ == "__main__":
main()
This complete implementation provides a fully functional three-dimensional spreadsheet application with LLM integration for formula generation, comprehensive visualization capabilities, undo and redo functionality, file persistence, and support for multiple GPU backends including NVIDIA CUDA, Apple Metal Performance Shaders, and CPU execution. The application demonstrates all the concepts discussed in the article and serves as a production-ready foundation for further development and customization.
OBTAINING AND CONFIGURING LLM MODELS FOR THE SPREADSHEET APPLICATION
UNDERSTANDING THE MODEL REQUIREMENTS
The three-dimensional spreadsheet application uses language models for generating formulas based on natural language descriptions. The example code references the "Salesforce/codegen-350M-mono" model, which is a code generation model suitable for creating spreadsheet formulas. This model and many alternatives are available through the Hugging Face model hub, which serves as a central repository for pre-trained language models.
DOWNLOADING MODELS FROM HUGGING FACE
The Hugging Face transformers library automatically downloads models when you first use them. The model specified in the code will be downloaded automatically when you run the application for the first time. However, you need to understand how this process works and what options you have.
When the application calls AutoTokenizer.from_pretrained and AutoModelForCausalLM.from_pretrained with a model name like "Salesforce/codegen-350M-mono", the transformers library performs several steps. First, it checks if the model exists in your local cache directory. If not found, it downloads the model files from the Hugging Face hub to your machine. The default cache location on Linux and macOS is in the home directory at .cache/huggingface/hub, while on Windows it is typically in your user AppData directory.
The codegen-350M-mono model is approximately 350 million parameters, which translates to roughly 700 megabytes of disk space for the model weights. The download time depends on your internet connection speed. Once downloaded, the model remains cached locally and subsequent runs of the application will load it from disk without requiring another download.
ALTERNATIVE MODELS FOR FORMULA GENERATION
While the example uses codegen-350M-mono, you have many alternative models to choose from depending on your requirements for accuracy, speed, and hardware capabilities. Smaller models run faster and require less memory but may produce less accurate results. Larger models generate better formulas but need more powerful hardware.
For lightweight deployments on systems with limited resources, you might consider models in the 100-500 million parameter range. The codegen-350M-mono model falls into this category and provides a good balance. If you have more powerful hardware with substantial GPU memory, you could use larger models like codegen-2B-mono or codegen-6B-mono for improved formula generation quality.
Another excellent option is the StarCoder family of models, specifically designed for code generation tasks. The StarCoder-1B model offers good performance with moderate resource requirements. For even better results with sufficient hardware, StarCoder-15B provides state-of-the-art code generation capabilities.
Here is how you would modify the code to use different models:
class LLMFormulaGenerator:
def __init__(self, config):
self.config = config
self.tokenizer = None
self.model = None
self.model_loaded = False
def load_local_model(self):
"""Loads a local language model with configurable model selection"""
# You can change the model here to any compatible model from Hugging Face
available_models = {
'codegen-350M': 'Salesforce/codegen-350M-mono',
'codegen-2B': 'Salesforce/codegen-2B-mono',
'codegen-6B': 'Salesforce/codegen-6B-mono',
'starcoder-1B': 'bigcode/starcoderbase-1b',
'starcoder-3B': 'bigcode/starcoderbase-3b',
'starcoder-7B': 'bigcode/starcoderbase-7b',
'phi-2': 'microsoft/phi-2',
'tiny-llama': 'TinyLlama/TinyLlama-1.1B-Chat-v1.0'
}
if self.config.model_path is None:
# Default to a small, fast model
self.config.model_name = available_models['codegen-350M']
elif self.config.model_path in available_models:
# Use a predefined model by key
self.config.model_name = available_models[self.config.model_path]
else:
# Use the provided path as a custom model identifier
self.config.model_name = self.config.model_path
print(f"Loading model {self.config.model_name} on {self.config.get_device_info()}")
print("This may take several minutes on first run while downloading the model...")
try:
self.tokenizer = AutoTokenizer.from_pretrained(
self.config.model_name,
trust_remote_code=True # Some models require this
)
# Handle models that don't have a pad token
if self.tokenizer.pad_token is None:
self.tokenizer.pad_token = self.tokenizer.eos_token
self.model = AutoModelForCausalLM.from_pretrained(
self.config.model_name,
torch_dtype=torch.float16 if self.config.backend != GPUBackend.CPU else torch.float32,
low_cpu_mem_usage=True,
trust_remote_code=True,
device_map='auto' # Automatically distribute model across available devices
)
# Only move to device if not using device_map='auto'
# self.model.to(self.config.device)
self.model.eval()
self.model_loaded = True
print("Model loaded successfully")
except Exception as e:
print(f"Error loading model: {e}")
print("Please ensure you have internet connectivity for first-time model download")
self.model_loaded = False
INSTALLING REQUIRED DEPENDENCIES
Before you can download and use models, you need to install the necessary Python packages. The transformers library from Hugging Face is essential, along with PyTorch for running the models. The installation process varies depending on your hardware configuration.
For systems with NVIDIA GPUs supporting CUDA, you should install PyTorch with CUDA support. Visit the PyTorch website at pytorch.org and use their installation selector to get the correct command for your CUDA version. A typical installation command looks like this:
pip install torch torchvision torchaudio --index-url https://download.pytorch.org/whl/cu118
The cu118 portion indicates CUDA 11.8. You should match this to your installed CUDA version. You can check your CUDA version by running nvidia-smi in a terminal.
For Apple Silicon Macs with M1, M2, or M3 chips, PyTorch includes Metal Performance Shaders support. Install PyTorch normally and it will automatically detect and use the Apple GPU:
pip install torch torchvision torchaudio
For CPU-only systems or if you want to ensure CPU execution, use the standard PyTorch installation:
pip install torch torchvision torchaudio --index-url https://download.pytorch.org/whl/cpu
After installing PyTorch, install the transformers library and other dependencies:
pip install transformers accelerate sentencepiece protobuf
The accelerate library helps with efficient model loading and inference. The sentencepiece and protobuf packages are required by certain tokenizers.
MANUAL MODEL DOWNLOAD AND OFFLINE USAGE
If you need to use the application in an environment without internet access, you can download models manually and load them from local storage. This involves downloading the model files to a specific directory and then pointing the application to that location.
To manually download a model, you can use the Hugging Face CLI tool:
pip install huggingface-hub
huggingface-cli login # Optional, only needed for gated models
huggingface-cli download Salesforce/codegen-350M-mono --local-dir ./models/codegen-350M
This downloads all model files to the specified local directory. You can then modify the application to load from this local path:
class LLMConfig:
def __init__(self, local_model_path=None):
self.backend = self.detect_gpu_backend()
self.device = self.get_device()
# Set to local path if provided
if local_model_path:
self.model_path = local_model_path
else:
self.model_path = None
self.model_name = None
self.use_remote = False
self.api_key = None
self.api_endpoint = None
Then when initializing the application:
# For online usage (automatic download)
llm_config = LLMConfig()
# For offline usage (pre-downloaded model)
llm_config = LLMConfig(local_model_path="./models/codegen-350M")
USING QUANTIZED MODELS FOR REDUCED MEMORY USAGE
Large language models can consume significant GPU memory. Quantized models reduce memory requirements by using lower precision numbers to represent model weights. This allows you to run larger models on hardware with limited memo.
The bitsandbytes library provides quantization support for PyTorch models. Install it with:
pip install bitsandbytes
Then modify the model loading code to use quantization:
from transformers import BitsAndBytesConfig
class LLMFormulaGenerator:
def load_local_model(self):
"""Loads a local language model with optional quantization"""
# Configure 8-bit quantization to reduce memory usage
quantization_config = BitsAndBytesConfig(
load_in_8bit=True,
llm_int8_threshold=6.0,
llm_int8_has_fp16_weight=False
)
# For even more aggressive memory reduction, use 4-bit quantization
# quantization_config = BitsAndBytesConfig(
# load_in_4bit=True,
# bnb_4bit_compute_dtype=torch.float16,
# bnb_4bit_quant_type="nf4",
# bnb_4bit_use_double_quant=True
# )
print(f"Loading model {self.config.model_name} with quantization")
try:
self.tokenizer = AutoTokenizer.from_pretrained(self.config.model_name)
if self.tokenizer.pad_token is None:
self.tokenizer.pad_token = self.tokenizer.eos_token
self.model = AutoModelForCausalLM.from_pretrained(
self.config.model_name,
quantization_config=quantization_config,
device_map='auto',
trust_remote_code=True
)
self.model.eval()
self.model_loaded = True
print("Quantized model loaded successfully")
except Exception as e:
print(f"Error loading quantized model: {e}")
self.model_loaded = False
Quantization reduces memory usage by approximately 75 percent for 8-bit quantization and 87 percent for 4-bit quantization. This allows you to run models that would otherwise not fit in your GPU memory. The trade-off is a small decrease in generation quality, which is usually acceptable for formula generation tasks.
USING REMOTE API SERVICES
Instead of running models locally, you can use remote API services that host large language models. This eliminates the need to download models and provides access to more powerful models than you could run locally. Several services offer API access to language models.
OpenAI provides API access to GPT models through their API service. You need to create an account at platform.openai.com and obtain an API key. The cost varies based on usage, with pricing per token generated.
To use OpenAI's API, modify the configuration:
llm_config = LLMConfig()
llm_config.use_remote = True
llm_config.api_key = "your-openai-api-key-here"
llm_config.api_endpoint = "https://api.openai.com/v1/completions"
You would also need to update the generate_formula_remote method to match OpenAI's API format:
def generate_formula_remote(self, prompt, context=None):
"""Generates a formula using OpenAI's API"""
full_prompt = self.construct_prompt(prompt, context)
headers = {
"Authorization": f"Bearer {self.config.api_key}",
"Content-Type": "application/json"
}
data = {
"model": "gpt-3.5-turbo-instruct",
"prompt": full_prompt,
"max_tokens": 200,
"temperature": 0.7,
"top_p": 0.95,
"stop": ["\n\n"]
}
try:
response = requests.post(
self.config.api_endpoint,
headers=headers,
json=data,
timeout=30
)
if response.status_code == 200:
result = response.json()
generated_text = result.get('choices', [{}])[0].get('text', '')
formula = self.extract_formula(generated_text, full_prompt)
return formula
else:
return f"#ERROR: API request failed with status {response.status_code}"
except Exception as e:
return f"#ERROR: {str(e)}"
Other API services include Anthropic's Claude API, Cohere's API, and Hugging Face's Inference API. Each has different pricing models and capabilities. The Hugging Face Inference API is particularly convenient because it provides access to many open-source models with a simple API interface.
TESTING MODEL INSTALLATION
After installing the dependencies and configuring your model choice, you should test that everything works correctly before running the full application. Create a simple test script:
#!/usr/bin/env python3
"""
Test script to verify LLM model installation and functionality
"""
import torch
from transformers import AutoTokenizer, AutoModelForCausalLM
import platform
def test_model_loading():
"""Tests loading and running a simple inference with the model"""
print("=" * 60)
print("LLM Model Installation Test")
print("=" * 60)
# Detect GPU backend
if torch.cuda.is_available():
device = torch.device("cuda")
print(f"GPU Backend: CUDA - {torch.cuda.get_device_name(0)}")
elif platform.system() == 'Darwin' and hasattr(torch.backends, 'mps') and torch.backends.mps.is_available():
device = torch.device("mps")
print("GPU Backend: Apple Metal Performance Shaders")
else:
device = torch.device("cpu")
print("GPU Backend: CPU")
print("\nLoading model...")
model_name = "Salesforce/codegen-350M-mono"
try:
tokenizer = AutoTokenizer.from_pretrained(model_name)
print(f"Tokenizer loaded successfully")
model = AutoModelForCausalLM.from_pretrained(
model_name,
torch_dtype=torch.float16 if device.type != "cpu" else torch.float32,
low_cpu_mem_usage=True
)
model.to(device)
model.eval()
print(f"Model loaded successfully on {device}")
# Test inference
print("\nTesting formula generation...")
test_prompt = "Generate a formula to sum cells A1:0, A1:1, and A1:2\nFormula:"
inputs = tokenizer(test_prompt, return_tensors="pt").to(device)
with torch.no_grad():
outputs = model.generate(
**inputs,
max_new_tokens=50,
temperature=0.7,
do_sample=True,
pad_token_id=tokenizer.eos_token_id
)
generated_text = tokenizer.decode(outputs[0], skip_special_tokens=True)
print(f"Generated text: {generated_text}")
print("\n" + "=" * 60)
print("SUCCESS: Model is working correctly!")
print("=" * 60)
return True
except Exception as e:
print(f"\nERROR: {str(e)}")
print("\nTroubleshooting steps:")
print("1. Ensure you have internet connectivity for first-time download")
print("2. Check that you have sufficient disk space (at least 2GB free)")
print("3. Verify PyTorch is installed correctly: pip install torch")
print("4. Verify transformers is installed: pip install transformers")
print("5. Try running: pip install --upgrade transformers torch")
return False
if __name__ == "__main__":
test_model_loading()
Run this test script before using the full application to verify that your model installation is working correctly. If the test succeeds, you can proceed with running the complete spreadsheet application.
RECOMMENDED MODEL CONFIGURATIONS
Based on different hardware configurations, here are recommended model choices:
For systems with 4GB or less GPU memory, use the codegen-350M-mono model or TinyLlama-1.1B-Chat. These models are small enough to run efficiently on limited hardware while still providing reasonable formula generation quality.
For systems with 8GB to 16GB GPU memory, consider codegen-2B-mono or starcoderbase-3b. These models offer significantly better formula generation quality while remaining manageable on mid-range hardware.
For systems with 24GB or more GPU memory, you can use larger models like codegen-6B-mono or starcoderbase-7b for the best formula generation quality. These models understand complex requirements better and generate more accurate formulas.
For CPU-only systems, stick with the smallest models like codegen-350M-mono. Larger models will run extremely slowly on CPU. Consider using quantized versions or remote API services for better performance.
The choice of model represents a trade-off between quality, speed, and resource requirements. Start with the default codegen-350M-mono model and upgrade to larger models if you need better formula generation quality and have the hardware to support it.
