This page lists files in the current directory. You can view content, get download/execute commands for Wget, Curl, or PowerShell, or filter the list using wildcards (e.g., `*.sh`).
wget 'https://sme10.lists2.roe3.org/spreadsheet/modules/constants.js'/**
* Constants Module for Spreadsheet Application
* Contains all configuration constants used throughout the app
*/
// ========== Layout & Sizing Limits ==========
export const MAX_ROWS = 1000;
export const MAX_COLS = 1000;
export const DEBOUNCE_DELAY = 200;
export const ACTIVE_HEADER_CLASS = "header-active";
export const ROW_HEADER_WIDTH = 40;
export const HEADER_ROW_HEIGHT = 32;
export const DEFAULT_COL_WIDTH = 100;
export const MIN_COL_WIDTH = 80;
// Mobile-responsive layout detection
export const IS_MOBILE_LAYOUT = typeof window !== "undefined" && window.matchMedia && window.matchMedia("(max-width: 768px)").matches;
export const DEFAULT_ROW_HEIGHT = IS_MOBILE_LAYOUT ? 44 : 32;
export const MIN_ROW_HEIGHT = DEFAULT_ROW_HEIGHT;
// Default starting grid size
export const DEFAULT_ROWS = 10;
export const DEFAULT_COLS = 10;
// ========== URL Length Thresholds ==========
export const URL_LENGTH_WARNING = 2000; // Yellow - some older browsers may truncate
export const URL_LENGTH_CAUTION = 4000; // Orange - URL shorteners may fail
export const URL_LENGTH_CRITICAL = 8000; // Red - some browsers may fail
export const URL_LENGTH_MAX_DISPLAY = 10000; // For progress bar scaling
// ========== Key Minification Mapping for URL Compression ==========
export const KEY_MAP = {
rows: "r",
cols: "c",
theme: "t",
data: "d",
formulas: "f",
cellStyles: "s",
colWidths: "w",
rowHeights: "h",
readOnly: "ro",
embed: "e",
};
export const KEY_MAP_REVERSE = Object.fromEntries(Object.entries(KEY_MAP).map(([k, v]) => [v, k]));
export const STYLE_KEY_MAP = {
align: "a",
bg: "b",
color: "c",
fontSize: "z",
};
export const STYLE_KEY_MAP_REVERSE = Object.fromEntries(Object.entries(STYLE_KEY_MAP).map(([k, v]) => [v, k]));
// ========== Formula Configuration ==========
export const FORMULA_SUGGESTIONS = [
{ name: "SUM", signature: "SUM(range)", description: "Adds numbers in a range" },
{ name: "AVG", signature: "AVG(range)", description: "Average of numbers in a range" },
];
// Valid formula patterns (security whitelist)
export const VALID_FORMULA_PATTERNS = [
/^=\s*SUM\s*\(\s*[A-Z]+\d+\s*:\s*[A-Z]+\d+\s*\)\s*$/i,
/^=\s*AVG\s*\(\s*[A-Z]+\d+\s*:\s*[A-Z]+\d+\s*\)\s*$/i,
/^=\s*PROGRESS\s*\(\s*[^)]+\s*\)\s*$/i,
/^=\s*TAG\s*\(\s*[^)]+\s*\)\s*$/i,
/^=\s*RATING\s*\(\s*[^)]+\s*\)\s*$/i,
];
export const FONT_SIZE_OPTIONS = [10, 12, 14, 16, 18, 24];
// ========== HTML Sanitization ==========
// Allowed HTML tags for sanitization (preserves basic formatting)
export const ALLOWED_TAGS = ["B", "I", "U", "STRONG", "EM", "SPAN", "BR"];
export const ALLOWED_SPAN_STYLES = ["font-weight", "font-style", "text-decoration", "color", "background-color"];
// ========== Toast Notification System ==========
export const TOAST_DURATION = 3000; // Default duration in ms
export const TOAST_ICONS = {
success: "fa-check",
error: "fa-xmark",
warning: "fa-exclamation",
info: "fa-info",
};
wget 'https://sme10.lists2.roe3.org/spreadsheet/modules/csvManager.js'import { MAX_COLS, MAX_ROWS } from "./constants.js";
import { isValidFormula } from "./formulaManager.js";
import { escapeHTML } from "./security.js";
import { createDefaultColumnWidths, createDefaultRowHeights, createEmptyCellStyles, createEmptyData } from "./urlManager.js";
function csvEscape(value) {
const text = String(value);
const needsQuotes = /[",\r\n]/.test(text) || /^\s|\s$/.test(text);
const escaped = text.replace(/"/g, '""');
return needsQuotes ? `"${escaped}"` : escaped;
}
export function parseCSV(text) {
if (!text) return [];
const rows = [];
let row = [];
let field = "";
let inQuotes = false;
for (let i = 0; i < text.length; i++) {
const char = text[i];
if (inQuotes) {
if (char === '"') {
if (text[i + 1] === '"') {
field += '"';
i++;
} else {
inQuotes = false;
}
} else {
field += char;
}
continue;
}
if (char === '"') {
inQuotes = true;
} else if (char === ",") {
row.push(field);
field = "";
} else if (char === "\r") {
if (text[i + 1] === "\n") {
i++;
}
row.push(field);
rows.push(row);
row = [];
field = "";
} else if (char === "\n") {
row.push(field);
rows.push(row);
row = [];
field = "";
} else {
field += char;
}
}
row.push(field);
rows.push(row);
if (rows.length && rows[0].length && rows[0][0]) {
rows[0][0] = rows[0][0].replace(/^\uFEFF/, "");
}
if (/\r?\n$/.test(text)) {
const lastRow = rows[rows.length - 1];
if (lastRow && lastRow.length === 1 && lastRow[0] === "") {
rows.pop();
}
}
return rows;
}
export const CSVManager = {
callbacks: {
getState: () => ({ rows: 0, cols: 0 }),
getDataArray: () => [],
setDataArray: () => {},
setFormulasArray: () => {},
setCellStylesArray: () => {},
setState: () => {},
renderGrid: () => {},
recalculateFormulas: () => {},
debouncedUpdateURL: () => {},
showToast: () => {},
extractPlainText: (value) => (value === null || value === undefined ? "" : String(value)),
onImport: null,
},
init(callbacks = {}) {
CSVManager.callbacks = { ...CSVManager.callbacks, ...callbacks };
},
buildCSV() {
const { getState, getDataArray, extractPlainText } = CSVManager.callbacks;
if (!getState || !getDataArray || !extractPlainText) return "";
const { rows, cols } = getState();
const data = getDataArray();
const lines = [];
for (let r = 0; r < rows; r++) {
const rowValues = [];
for (let c = 0; c < cols; c++) {
const raw = data[r] && data[r][c] !== undefined ? data[r][c] : "";
const text = extractPlainText(raw);
rowValues.push(csvEscape(text));
}
lines.push(rowValues.join(","));
}
return lines.join("\r\n");
},
downloadCSV() {
const { recalculateFormulas, showToast } = CSVManager.callbacks;
if (recalculateFormulas) {
recalculateFormulas();
}
const csv = CSVManager.buildCSV();
const blob = new Blob([csv], { type: "text/csv;charset=utf-8" });
const url = URL.createObjectURL(blob);
const link = document.createElement("a");
link.href = url;
link.download = "spreadsheet.csv";
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
URL.revokeObjectURL(url);
if (showToast) {
showToast("CSV downloaded", "success");
}
},
importCSVText(text) {
const parsedRows = parseCSV(text);
if (!parsedRows.length) {
if (CSVManager.callbacks.showToast) {
CSVManager.callbacks.showToast("CSV file is empty", "error");
}
return;
}
const maxColsInFile = parsedRows.reduce((max, row) => Math.max(max, row.length), 0);
const nextRows = Math.min(Math.max(parsedRows.length, 1), MAX_ROWS);
const nextCols = Math.min(Math.max(maxColsInFile, 1), MAX_COLS);
const truncated = parsedRows.length > MAX_ROWS || maxColsInFile > MAX_COLS;
CSVManager.callbacks.setState("rows", nextRows);
CSVManager.callbacks.setState("cols", nextCols);
CSVManager.callbacks.setState("colWidths", createDefaultColumnWidths(nextCols));
CSVManager.callbacks.setState("rowHeights", createDefaultRowHeights(nextRows));
const data = createEmptyData(nextRows, nextCols);
const formulas = createEmptyData(nextRows, nextCols);
const cellStyles = createEmptyCellStyles(nextRows, nextCols);
for (let r = 0; r < nextRows; r++) {
const sourceRow = Array.isArray(parsedRows[r]) ? parsedRows[r] : [];
for (let c = 0; c < nextCols; c++) {
const raw = sourceRow[c] !== undefined ? String(sourceRow[c]) : "";
if (raw.startsWith("=")) {
if (isValidFormula(raw)) {
formulas[r][c] = raw;
data[r][c] = raw;
} else {
formulas[r][c] = "";
data[r][c] = escapeHTML(raw);
}
} else {
data[r][c] = escapeHTML(raw);
}
}
}
CSVManager.callbacks.setDataArray(data);
CSVManager.callbacks.setFormulasArray(formulas);
CSVManager.callbacks.setCellStylesArray(cellStyles);
if (CSVManager.callbacks.renderGrid) {
CSVManager.callbacks.renderGrid();
}
if (CSVManager.callbacks.recalculateFormulas) {
CSVManager.callbacks.recalculateFormulas();
}
if (CSVManager.callbacks.debouncedUpdateURL) {
CSVManager.callbacks.debouncedUpdateURL();
}
if (CSVManager.callbacks.onImport) {
CSVManager.callbacks.onImport({
truncated,
rows: nextRows,
cols: nextCols,
});
}
if (CSVManager.callbacks.showToast) {
if (truncated) {
CSVManager.callbacks.showToast("CSV imported (some data truncated due to size limits)", "warning");
} else {
CSVManager.callbacks.showToast("CSV imported successfully", "success");
}
}
},
};
wget 'https://sme10.lists2.roe3.org/spreadsheet/modules/dependencyTracer.js'/**
* Visual Formula Dependency Tracer
* Draws Bezier curves between formula cells and their data sources.
*/
import { parseRange, parseCellRef } from "./formulaManager.js";
import { getState, getCellElement } from "./rowColManager.js";
const SVG_NS = "http://www.w3.org/2000/svg";
const ARROW_ID = "dependency-arrowhead";
const DOT_ID = "dependency-dot";
const LINE_COLOR = "#2196F3"; // Professional Blue
function normalizeRef(ref) {
return ref.replace(/\$/g, "").toUpperCase();
}
function extractRefs(formula) {
if (!formula || typeof formula !== "string") return [];
if (!formula.startsWith("=")) return [];
const matches = formula.match(/(\$?[A-Z]+\$?\d+)(?::(\$?[A-Z]+\$?\d+))?/gi);
return matches || [];
}
function isInBounds(row, col, rows, cols) {
return row >= 0 && col >= 0 && row < rows && col < cols;
}
export const DependencyTracer = {
isActive: false,
container: null,
svg: null,
resizeObserver: null,
lastFormulas: null,
init() {
const container = document.getElementById("spreadsheet");
if (!container) return;
this.container = container;
if (!this.svg || !container.contains(this.svg)) {
this.svg = this.createLayer();
this.container.appendChild(this.svg);
}
if (this.isActive) {
this.svg.classList.remove("hidden");
} else {
this.svg.classList.add("hidden");
}
this.updateSvgSize();
if (this.resizeObserver) {
this.resizeObserver.disconnect();
this.resizeObserver = null;
}
if (typeof ResizeObserver !== "undefined") {
this.resizeObserver = new ResizeObserver(() => {
if (this.isActive) {
this.draw(this.lastFormulas);
}
});
this.resizeObserver.observe(this.container);
}
},
createLayer() {
const svg = document.createElementNS(SVG_NS, "svg");
svg.classList.add("dependency-layer", "hidden");
svg.setAttribute("aria-hidden", "true");
const defs = document.createElementNS(SVG_NS, "defs");
// Arrow Marker (End) - Smaller Size
const arrowMarker = document.createElementNS(SVG_NS, "marker");
arrowMarker.setAttribute("id", ARROW_ID);
arrowMarker.setAttribute("markerWidth", "6");
arrowMarker.setAttribute("markerHeight", "6");
arrowMarker.setAttribute("refX", "5"); // Tip
arrowMarker.setAttribute("refY", "3");
arrowMarker.setAttribute("orient", "auto");
const arrowPath = document.createElementNS(SVG_NS, "path");
arrowPath.setAttribute("d", "M0,0 L6,3 L0,6 L1.5,3 z"); // Sharper, smaller arrow
arrowPath.setAttribute("fill", LINE_COLOR);
arrowMarker.appendChild(arrowPath);
// Dot Marker (Start)
const dotMarker = document.createElementNS(SVG_NS, "marker");
dotMarker.setAttribute("id", DOT_ID);
dotMarker.setAttribute("markerWidth", "8");
dotMarker.setAttribute("markerHeight", "8");
dotMarker.setAttribute("refX", "4"); // Center of dot
dotMarker.setAttribute("refY", "4");
dotMarker.setAttribute("orient", "auto");
const dotCircle = document.createElementNS(SVG_NS, "circle");
dotCircle.setAttribute("cx", "4");
dotCircle.setAttribute("cy", "4");
dotCircle.setAttribute("r", "3");
dotCircle.setAttribute("fill", LINE_COLOR);
dotMarker.appendChild(dotCircle);
defs.appendChild(arrowMarker);
defs.appendChild(dotMarker);
svg.appendChild(defs);
return svg;
},
ensureLayer() {
const container = document.getElementById("spreadsheet");
if (!container) return false;
if (this.container !== container) {
this.container = container;
}
if (!this.svg || !container.contains(this.svg)) {
this.svg = this.createLayer();
this.container.appendChild(this.svg);
}
if (this.isActive) {
this.svg.classList.remove("hidden");
} else {
this.svg.classList.add("hidden");
}
this.updateSvgSize();
return true;
},
updateSvgSize() {
if (!this.container || !this.svg) return;
const width = Math.max(this.container.scrollWidth, this.container.clientWidth);
const height = Math.max(this.container.scrollHeight, this.container.clientHeight);
this.svg.setAttribute("width", width);
this.svg.setAttribute("height", height);
this.svg.setAttribute("viewBox", `0 0 ${width} ${height}`);
},
toggle() {
this.isActive = !this.isActive;
if (!this.ensureLayer()) return this.isActive;
if (this.isActive) {
this.svg.classList.remove("hidden");
this.draw(this.lastFormulas);
} else {
this.svg.classList.add("hidden");
this.clear();
}
return this.isActive;
},
clear() {
if (!this.svg) return;
this.svg.querySelectorAll("path").forEach((path) => path.remove());
this.clearHighlights();
},
clearHighlights() {
if (!this.container) return;
const sources = this.container.querySelectorAll(".dependency-source");
const targets = this.container.querySelectorAll(".dependency-target");
sources.forEach((el) => el.classList.remove("dependency-source"));
targets.forEach((el) => el.classList.remove("dependency-target"));
},
getCellCenter(row, col) {
return this.getCellAnchor(row, col, 'center');
},
getCellAnchor(row, col, side = 'center') {
const cell = getCellElement(row, col);
if (!cell || !this.container) return null;
const rect = cell.getBoundingClientRect();
const containerRect = this.container.getBoundingClientRect();
const topOffset = rect.top - containerRect.top + 8; // Near top (corner-like)
if (side === 'left') {
return {
x: rect.left - containerRect.left + 2, // Left edge
y: topOffset,
};
}
if (side === 'right') {
return {
x: rect.right - containerRect.left - 2, // Right edge
y: topOffset,
};
}
// Default center
return {
x: rect.left - containerRect.left + rect.width / 2,
y: rect.top - containerRect.top + rect.height / 2,
};
},
getRangeCenter(startRow, startCol, endRow, endCol) {
// For ranges, we'll just use the visual center for now to avoid complexity
const topLeft = this.getCellCenter(startRow, startCol);
const bottomRight = this.getCellCenter(endRow, endCol);
if (!topLeft || !bottomRight) return null;
return {
x: (topLeft.x + bottomRight.x) / 2,
y: (topLeft.y + bottomRight.y) / 2,
};
},
highlightCell(row, col, type) {
const cell = getCellElement(row, col);
if (cell) {
cell.classList.add(type === "source" ? "dependency-source" : "dependency-target");
}
},
draw(formulas) {
if (!this.isActive) return;
if (!this.ensureLayer()) return;
const formulaData = Array.isArray(formulas) ? formulas : this.lastFormulas;
if (Array.isArray(formulas)) {
this.lastFormulas = formulas;
}
this.clear(); // This now calls clearHighlights too
const { rows, cols } = getState();
if (!Array.isArray(formulaData) || rows <= 0 || cols <= 0) return;
const drawPath = (source, target, direction) => {
if (!this.svg) return;
if (!source || !target) return;
if (source.x === target.x && source.y === target.y) return;
const path = document.createElementNS(SVG_NS, "path");
const deltaX = target.x - source.x;
const deltaY = target.y - source.y;
let c1x, c1y, c2x, c2y;
if (direction === 'right-to-left') {
// R -> L: Curve out to left from source, enter from right to target
c1x = source.x - 30;
c1y = source.y;
c2x = target.x + 30;
c2y = target.y;
} else if (direction === 'vertical') {
// Same column: Curve out to right and back in
c1x = source.x + 40;
c1y = source.y + deltaY * 0.2;
c2x = target.x + 40;
c2y = target.y - deltaY * 0.2;
} else {
// L -> R (Standard): Curve right from source, enter left to target
c1x = source.x + 30;
c1y = source.y;
c2x = target.x - 30;
c2y = target.y;
}
const d = `M ${source.x} ${source.y} C ${c1x} ${c1y}, ${c2x} ${c2y}, ${target.x} ${target.y}`;
path.setAttribute("d", d);
path.setAttribute("stroke", LINE_COLOR);
path.setAttribute("stroke-width", "2");
path.setAttribute("fill", "none");
path.setAttribute("opacity", "0.8");
path.setAttribute("marker-start", `url(#${DOT_ID})`);
path.setAttribute("marker-end", `url(#${ARROW_ID})`);
path.classList.add("dependency-line");
this.svg.appendChild(path);
};
for (let r = 0; r < rows; r++) {
const rowFormulas = formulaData[r];
if (!Array.isArray(rowFormulas)) continue;
for (let c = 0; c < cols; c++) {
const formula = rowFormulas[c];
if (!formula || typeof formula !== "string" || !formula.startsWith("=")) continue;
const refs = extractRefs(formula);
if (!refs.length) continue;
// Highlight target (the cell containing the formula)
this.highlightCell(r, c, "target");
refs.forEach((refStr) => {
const cleaned = normalizeRef(refStr);
// Handle Range refs (keeping simple center logic or default L->R for now to avoid complexity explosion)
if (cleaned.includes(":")) {
const range = parseRange(cleaned);
if (!range) return;
if (!isInBounds(range.startRow, range.startCol, rows, cols)) return;
if (!isInBounds(range.endRow, range.endCol, rows, cols)) return;
for (let rr = range.startRow; rr <= range.endRow; rr++) {
for (let cc = range.startCol; cc <= range.endCol; cc++) {
this.highlightCell(rr, cc, "source");
}
}
// For ranges, we just draw from center of range to target 'left' default
const sourceCenter = this.getRangeCenter(range.startRow, range.startCol, range.endRow, range.endCol);
const targetCenter = this.getCellAnchor(r, c, 'left');
drawPath(sourceCenter, targetCenter, 'left-to-right');
return;
}
const cellRef = parseCellRef(cleaned);
if (!cellRef) return;
if (!isInBounds(cellRef.row, cellRef.col, rows, cols)) return;
// Highlight source cell
this.highlightCell(cellRef.row, cellRef.col, "source");
// Determine Direction
let sourceSide, targetSide, direction;
if (c > cellRef.col) {
// Target is to the RIGHT of Source (Standard Reading Order)
// Source -> [Right Edge] ..... [Left Edge] -> Target
sourceSide = 'right';
targetSide = 'left';
direction = 'left-to-right';
} else if (c < cellRef.col) {
// Target is to the LEFT of Source (Reverse Flow)
// Source -> [Left Edge] ..... [Right Edge] -> Target
sourceSide = 'left';
targetSide = 'right';
direction = 'right-to-left';
} else {
// Same Column (Vertical)
// Use Right-to-Right loop to avoid text
sourceSide = 'right';
targetSide = 'right';
direction = 'vertical';
}
const sourcePoint = this.getCellAnchor(cellRef.row, cellRef.col, sourceSide);
const targetPoint = this.getCellAnchor(r, c, targetSide);
drawPath(sourcePoint, targetPoint, direction);
});
}
}
},
};
wget 'https://sme10.lists2.roe3.org/spreadsheet/modules/encryption.js'/**
* Encryption Module for Spreadsheet Application
* Provides AES-GCM 256-bit encryption with PBKDF2 key derivation
*/
// ========== Encryption Module (AES-GCM 256-bit) ==========
export const CryptoUtils = {
algo: { name: "AES-GCM", length: 256 },
kdf: { name: "PBKDF2", hash: "SHA-256", iterations: 100000 },
// Derive a cryptographic key from a password using PBKDF2
async deriveKey(password, salt) {
const enc = new TextEncoder();
const keyMaterial = await window.crypto.subtle.importKey("raw", enc.encode(password), "PBKDF2", false, ["deriveKey"]);
return window.crypto.subtle.deriveKey({ ...this.kdf, salt: salt }, keyMaterial, this.algo, false, ["encrypt", "decrypt"]);
},
// Encrypt data string with password, returns Base64 string
async encrypt(dataString, password) {
const enc = new TextEncoder();
const salt = window.crypto.getRandomValues(new Uint8Array(16));
const iv = window.crypto.getRandomValues(new Uint8Array(12));
const key = await this.deriveKey(password, salt);
const encodedData = enc.encode(dataString);
const encryptedContent = await window.crypto.subtle.encrypt({ name: "AES-GCM", iv: iv }, key, encodedData);
// Pack: Salt (16) + IV (12) + EncryptedData
const buffer = new Uint8Array(salt.byteLength + iv.byteLength + encryptedContent.byteLength);
buffer.set(salt, 0);
buffer.set(iv, salt.byteLength);
buffer.set(new Uint8Array(encryptedContent), salt.byteLength + iv.byteLength);
return this.bufferToBase64(buffer);
},
// Decrypt Base64 string with password, returns original data string
async decrypt(base64String, password) {
const buffer = this.base64ToBuffer(base64String);
// Extract: Salt (16) + IV (12) + EncryptedData
const salt = buffer.slice(0, 16);
const iv = buffer.slice(16, 28);
const data = buffer.slice(28);
const key = await this.deriveKey(password, salt);
const decryptedContent = await window.crypto.subtle.decrypt({ name: "AES-GCM", iv: iv }, key, data);
const dec = new TextDecoder();
return dec.decode(decryptedContent);
},
// Convert Uint8Array to URL-safe Base64
bufferToBase64(buffer) {
let binary = "";
const bytes = new Uint8Array(buffer);
for (let i = 0; i < bytes.byteLength; i++) {
binary += String.fromCharCode(bytes[i]);
}
// Use URL-safe Base64 (replace + with -, / with _, remove padding)
return btoa(binary).replace(/\+/g, "-").replace(/\//g, "_").replace(/=+$/, "");
},
// Convert URL-safe Base64 to Uint8Array
base64ToBuffer(base64) {
// Restore standard Base64
let standardBase64 = base64.replace(/-/g, "+").replace(/_/g, "/");
// Add padding if needed
while (standardBase64.length % 4) {
standardBase64 += "=";
}
const binaryString = atob(standardBase64);
const bytes = new Uint8Array(binaryString.length);
for (let i = 0; i < binaryString.length; i++) {
bytes[i] = binaryString.charCodeAt(i);
}
return bytes;
},
};
// ========== Encryption Codec (Wrapper for CryptoUtils) ==========
// Handles encryption prefix and provides clean interface for encrypt/decrypt
export const EncryptionCodec = {
PREFIX: "ENC:",
// Check if a URL hash represents encrypted data
isEncrypted(hash) {
return hash && hash.startsWith(this.PREFIX);
},
// Wrap encrypted data with the encryption prefix
wrap(encryptedData) {
return this.PREFIX + encryptedData;
},
// Unwrap the prefix from encrypted data
unwrap(prefixedData) {
return prefixedData.slice(this.PREFIX.length);
},
// Encrypt a payload string and wrap with prefix
async encrypt(payload, password) {
const encrypted = await CryptoUtils.encrypt(payload, password);
return this.wrap(encrypted);
},
// Decrypt data (handles both wrapped and unwrapped formats)
async decrypt(wrappedData, password) {
const data = this.isEncrypted(wrappedData) ? this.unwrap(wrappedData) : wrappedData;
return await CryptoUtils.decrypt(data, password);
},
};
wget 'https://sme10.lists2.roe3.org/spreadsheet/modules/formulaManager.js'import { FORMULA_SUGGESTIONS } from "./constants.js";
// ==========================================
// Formula Helper Functions
// ==========================================
// Convert column index to letters (0 = A, 25 = Z, 26 = AA)
export function colToLetter(col) {
if (!Number.isInteger(col) || col < 0) return "";
let n = col;
let letters = "";
while (n >= 0) {
const remainder = n % 26;
letters = String.fromCharCode(65 + remainder) + letters;
n = Math.floor(n / 26) - 1;
}
return letters;
}
// Convert column letter(s) to index: A=0, B=1, ..., O=14
export function letterToCol(letters) {
letters = letters.toUpperCase();
let col = 0;
for (let i = 0; i < letters.length; i++) {
col = col * 26 + (letters.charCodeAt(i) - 64);
}
return col - 1;
}
// Parse cell reference "A1" → { row: 0, col: 0 }
export function parseCellRef(ref) {
const match = ref.toUpperCase().match(/^([A-Z]+)(\d+)$/);
if (!match) return null;
return {
col: letterToCol(match[1]),
row: parseInt(match[2], 10) - 1,
};
}
// Parse range "A1:B5" → { startRow, startCol, endRow, endCol }
export function parseRange(range) {
const parts = range.split(":");
if (parts.length !== 2) return null;
const start = parseCellRef(parts[0].trim());
const end = parseCellRef(parts[1].trim());
if (!start || !end) return null;
return {
startRow: Math.min(start.row, end.row),
startCol: Math.min(start.col, end.col),
endRow: Math.max(start.row, end.row),
endCol: Math.max(start.col, end.col),
};
}
// Build cell reference string like "A1"
export function buildCellRef(row, col) {
return colToLetter(col) + (row + 1);
}
// Build range reference string like "A1:B5"
export function buildRangeRef(startRow, startCol, endRow, endCol) {
const minRow = Math.min(startRow, endRow);
const maxRow = Math.max(startRow, endRow);
const minCol = Math.min(startCol, endCol);
const maxCol = Math.max(startCol, endCol);
if (minRow === maxRow && minCol === maxCol) {
// Single cell
return buildCellRef(minRow, minCol);
}
return buildCellRef(minRow, minCol) + ":" + buildCellRef(maxRow, maxCol);
}
const VISUAL_FORMULA_REGEX = /^=\s*(PROGRESS|TAG|RATING)\s*\((.*)\)\s*$/i;
export function isVisualFormula(formula) {
if (!formula || typeof formula !== "string") return false;
const match = formula.match(VISUAL_FORMULA_REGEX);
if (!match) return false;
return match[2].trim().length > 0;
}
// Check if a formula string is valid (arithmetic or supported functions)
export function isValidFormula(formula) {
if (!formula || !formula.startsWith("=")) return false;
if (isVisualFormula(formula)) return true;
// Check supported functions
if (/^=SUM\([A-Z]+\d+:[A-Z]+\d+\)$/i.test(formula)) return true;
if (/^=AVG\([A-Z]+\d+:[A-Z]+\d+\)$/i.test(formula)) return true;
// Check arithmetic
return FormulaEvaluator.isArithmeticFormula(formula);
}
// ==========================================
// Formula Evaluation Logic
// ==========================================
export const FormulaEvaluator = {
// Tokenize arithmetic expression into array of tokens
tokenizeArithmetic(expr) {
const tokens = [];
let i = 0;
while (i < expr.length) {
// Skip whitespace
if (/\s/.test(expr[i])) {
i++;
continue;
}
// Numbers (including decimals)
if (/[\d.]/.test(expr[i])) {
let num = "";
while (i < expr.length && /[\d.]/.test(expr[i])) {
num += expr[i++];
}
if (!/^\d+\.?\d*$|^\d*\.\d+$/.test(num)) {
return { error: "Invalid number: " + num };
}
tokens.push({ type: "NUMBER", value: parseFloat(num) });
continue;
}
// Cell references (A1, B2, AA99, etc.)
if (/[A-Z]/i.test(expr[i])) {
let ref = "";
while (i < expr.length && /[A-Z]/i.test(expr[i])) {
ref += expr[i++];
}
while (i < expr.length && /\d/.test(expr[i])) {
ref += expr[i++];
}
if (!/^[A-Z]+\d+$/i.test(ref)) {
return { error: "Invalid cell reference: " + ref };
}
tokens.push({ type: "CELL_REF", value: ref.toUpperCase() });
continue;
}
// Operators
if ("+-*/".includes(expr[i])) {
tokens.push({ type: "OPERATOR", value: expr[i] });
i++;
continue;
}
// Parentheses
if (expr[i] === "(") {
tokens.push({ type: "LPAREN" });
i++;
continue;
}
if (expr[i] === ")") {
tokens.push({ type: "RPAREN" });
i++;
continue;
}
// Unknown character
return { error: "Unexpected character: " + expr[i] };
}
return { tokens };
},
// Parse and evaluate arithmetic expression with proper precedence
evaluateArithmeticExpr(tokens, context) {
let pos = 0;
const { getCellValue, rows, cols } = context;
function peek() {
return tokens[pos];
}
function consume() {
return tokens[pos++];
}
function parseExpr() {
let left = parseTerm();
if (left.error) return left;
while (peek() && peek().type === "OPERATOR" && (peek().value === "+" || peek().value === "-")) {
const op = consume().value;
const right = parseTerm();
if (right.error) return right;
left = { value: op === "+" ? left.value + right.value : left.value - right.value };
}
return left;
}
function parseTerm() {
let left = parseFactor();
if (left.error) return left;
while (peek() && peek().type === "OPERATOR" && (peek().value === "*" || peek().value === "/")) {
const op = consume().value;
const right = parseFactor();
if (right.error) return right;
if (op === "/") {
if (right.value === 0) {
return { error: "#DIV/0!" };
}
left = { value: left.value / right.value };
} else {
left = { value: left.value * right.value };
}
}
return left;
}
function parseFactor() {
const token = peek();
if (!token) {
return { error: "#ERROR!" };
}
// Unary minus
if (token.type === "OPERATOR" && token.value === "-") {
consume();
const factor = parseFactor();
if (factor.error) return factor;
return { value: -factor.value };
}
// Unary plus (just consume)
if (token.type === "OPERATOR" && token.value === "+") {
consume();
return parseFactor();
}
// Number literal
if (token.type === "NUMBER") {
consume();
return { value: token.value };
}
// Cell reference
if (token.type === "CELL_REF") {
consume();
const parsed = parseCellRef(token.value);
if (!parsed) {
return { error: "#REF!" };
}
if (parsed.row >= rows || parsed.col >= cols || parsed.row < 0 || parsed.col < 0) {
return { error: "#REF!" };
}
return { value: getCellValue(parsed.row, parsed.col) };
}
// Parenthesized expression
if (token.type === "LPAREN") {
consume();
const result = parseExpr();
if (result.error) return result;
if (!peek() || peek().type !== "RPAREN") {
return { error: "#ERROR!" };
}
consume();
return result;
}
return { error: "#ERROR!" };
}
const result = parseExpr();
// Check for leftover tokens (malformed expression)
if (!result.error && pos < tokens.length) {
return { error: "#ERROR!" };
}
return result;
},
// Evaluate arithmetic expression string (without leading =)
evaluateArithmetic(expr, context) {
const tokenResult = this.tokenizeArithmetic(expr);
if (tokenResult.error) {
return tokenResult.error;
}
if (tokenResult.tokens.length === 0) {
return "#ERROR!";
}
const evalResult = this.evaluateArithmeticExpr(tokenResult.tokens, context);
if (evalResult.error) {
return evalResult.error;
}
// Format result (avoid floating point display issues)
const value = evalResult.value;
if (Number.isInteger(value)) {
return value;
}
// Round to reasonable precision
return Math.round(value * 1e10) / 1e10;
},
// Check if expression is a valid arithmetic formula
isArithmeticFormula(formula) {
if (!formula || !formula.startsWith("=")) return false;
const expr = formula.substring(1).trim();
if (expr.length === 0) return false;
const tokenResult = this.tokenizeArithmetic(expr);
if (tokenResult.error) return false;
if (tokenResult.tokens.length === 0) return false;
// Validate balanced parentheses
let parenDepth = 0;
for (const token of tokenResult.tokens) {
if (token.type === "LPAREN") {
parenDepth++;
} else if (token.type === "RPAREN") {
parenDepth--;
if (parenDepth < 0) return false;
}
}
return parenDepth === 0;
},
// Evaluate SUM(range)
evaluateSUM(rangeStr, context) {
const { getCellValue, rows, cols } = context;
const range = parseRange(rangeStr);
if (!range) return "#REF!";
// Check if range is within grid bounds
if (range.endRow >= rows || range.endCol >= cols) return "#REF!";
let sum = 0;
for (let r = range.startRow; r <= range.endRow; r++) {
for (let c = range.startCol; c <= range.endCol; c++) {
sum += getCellValue(r, c);
}
}
return sum;
},
// Evaluate AVG(range)
evaluateAVG(rangeStr, context) {
const { getCellValue, data, rows, cols } = context;
const range = parseRange(rangeStr);
if (!range) return "#REF!";
// Check if range is within grid bounds
if (range.endRow >= rows || range.endCol >= cols) return "#REF!";
let sum = 0;
let count = 0;
for (let r = range.startRow; r <= range.endRow; r++) {
for (let c = range.startCol; c <= range.endCol; c++) {
// Need raw data for AVG to skip empty cells properly
// If we use getCellValue it returns 0 for empty, which might affect avg?
// script.js checked raw data for null/empty.
// We need 'data' access in context for this precise logic.
const raw = data[r][c];
if (raw === null || raw === undefined) continue;
const stripped = String(raw)
.replace(/<[^>]*>/g, "")
.trim();
if (stripped === "") continue;
const normalized = stripped.replace(/,/g, "");
const num = parseFloat(normalized);
if (isNaN(num)) continue;
sum += num;
count++;
}
}
return count === 0 ? 0 : sum / count;
},
// Main formula evaluator
evaluate(formula, context) {
if (!formula || !formula.startsWith("=")) return formula;
if (isVisualFormula(formula)) return formula;
const expr = formula.substring(1).trim();
const exprUpper = expr.toUpperCase();
// Match SUM(range)
const sumMatch = exprUpper.match(/^SUM\(([A-Z]+\d+:[A-Z]+\d+)\)$/);
if (sumMatch) {
return this.evaluateSUM(sumMatch[1], context);
}
// Match AVG(range)
const avgMatch = exprUpper.match(/^AVG\(([A-Z]+\d+:[A-Z]+\d+)\)$/);
if (avgMatch) {
return this.evaluateAVG(avgMatch[1], context);
}
// Try arithmetic expression
if (this.isArithmeticFormula(formula)) {
return this.evaluateArithmetic(expr, context);
}
// Unknown formula
return "#ERROR!";
},
};
// ==========================================
// Formula Dropdown UI Manager
// ==========================================
export const FormulaDropdownManager = {
element: null,
items: [],
activeIndex: -1,
anchor: null,
onSelect: null, // Callback function(formulaName)
init(onSelectCallback) {
if (this.element) return;
this.onSelect = onSelectCallback;
const dropdown = document.createElement("div");
dropdown.className = "formula-dropdown";
dropdown.setAttribute("role", "listbox");
dropdown.setAttribute("aria-hidden", "true");
dropdown.addEventListener("mousedown", (event) => {
event.preventDefault(); // Prevent blur
});
dropdown.addEventListener("click", (event) => {
const item = event.target.closest(".formula-item");
if (!item) return;
const formulaName = item.dataset.formula;
if (formulaName && this.onSelect) {
this.onSelect(formulaName);
}
});
document.body.appendChild(dropdown);
this.element = dropdown;
},
getFormulaQuery(rawValue) {
const match = rawValue.match(/^=\s*([A-Z]*)$/i);
if (!match) return null;
return match[1].toUpperCase();
},
getSuggestions(query) {
if (query === null) return [];
if (query === "") return FORMULA_SUGGESTIONS.slice();
return FORMULA_SUGGESTIONS.filter((item) => item.name.startsWith(query));
},
isOpen() {
return !!(this.element && this.element.classList.contains("open"));
},
setActiveItem(index) {
this.activeIndex = index;
this.items.forEach((item, idx) => {
if (idx === index) {
item.classList.add("active");
item.setAttribute("aria-selected", "true");
item.scrollIntoView({ block: "nearest" });
} else {
item.classList.remove("active");
item.setAttribute("aria-selected", "false");
}
});
},
position(anchor) {
if (!this.element || !anchor) return;
const rect = anchor.getBoundingClientRect();
const viewportWidth = window.innerWidth;
const viewportHeight = window.innerHeight;
const padding = 6;
this.element.style.left = `${rect.left}px`;
this.element.style.top = `${rect.bottom + 4}px`;
const dropdownRect = this.element.getBoundingClientRect();
let left = rect.left;
let top = rect.bottom + 4;
if (left + dropdownRect.width > viewportWidth - padding) {
left = Math.max(padding, viewportWidth - dropdownRect.width - padding);
}
if (top + dropdownRect.height > viewportHeight - padding) {
const above = rect.top - dropdownRect.height - 4;
if (above > padding) {
top = above;
}
}
this.element.style.left = `${left}px`;
this.element.style.top = `${top}px`;
},
show() {
if (!this.element) return;
this.element.classList.add("open");
this.element.setAttribute("aria-hidden", "false");
},
hide() {
if (!this.element) return;
this.element.classList.remove("open");
this.element.setAttribute("aria-hidden", "true");
this.items = [];
this.activeIndex = -1;
this.anchor = null;
},
update(anchor, rawValue) {
this.init(this.onSelect); // Ensure initialized
const query = this.getFormulaQuery(rawValue);
const suggestions = this.getSuggestions(query);
if (!anchor || suggestions.length === 0 || query === null) {
this.hide();
return;
}
this.anchor = anchor;
this.element.innerHTML = "";
suggestions.forEach((item) => {
const option = document.createElement("div");
option.className = "formula-item";
option.dataset.formula = item.name;
option.setAttribute("role", "option");
option.setAttribute("aria-selected", "false");
const nameEl = document.createElement("div");
nameEl.className = "formula-name";
nameEl.textContent = item.name;
const hintEl = document.createElement("div");
hintEl.className = "formula-hint";
hintEl.textContent = `${item.signature} - ${item.description}`;
option.appendChild(nameEl);
option.appendChild(hintEl);
this.element.appendChild(option);
});
this.items = Array.from(this.element.querySelectorAll(".formula-item"));
this.setActiveItem(0);
this.show();
this.position(anchor);
},
moveSelection(delta) {
if (!this.items.length) return;
let nextIndex = this.activeIndex + delta;
if (nextIndex < 0) nextIndex = this.items.length - 1;
if (nextIndex >= this.items.length) nextIndex = 0;
this.setActiveItem(nextIndex);
},
// Get currently active item's formula name
getActiveFormulaName() {
if (this.activeIndex >= 0 && this.activeIndex < this.items.length) {
return this.items[this.activeIndex].dataset.formula;
}
return null;
},
};
wget 'https://sme10.lists2.roe3.org/spreadsheet/modules/jsonManager.js'const defaultCallbacks = {
buildCurrentState: () => ({}),
recalculateFormulas: () => {},
showToast: () => {},
};
const elements = {
modal: null,
textarea: null,
errorEl: null,
copyBtn: null,
};
function clearJSONError() {
if (elements.errorEl) {
elements.errorEl.classList.add("hidden");
elements.errorEl.textContent = "";
}
}
export const JSONManager = {
callbacks: { ...defaultCallbacks },
init(callbacks = {}) {
this.callbacks = { ...defaultCallbacks, ...callbacks };
elements.modal = document.getElementById("json-modal");
elements.textarea = document.getElementById("json-editor");
elements.errorEl = document.getElementById("json-error");
elements.copyBtn = document.getElementById("json-copy-btn");
},
openModal() {
if (!elements.modal || !elements.textarea) return;
if (this.callbacks.recalculateFormulas) {
this.callbacks.recalculateFormulas();
}
const exportState = this.callbacks.buildCurrentState ? this.callbacks.buildCurrentState() : {};
elements.textarea.value = JSON.stringify(exportState, null, 2);
elements.textarea.scrollTop = 0;
clearJSONError();
elements.modal.classList.remove("hidden");
elements.textarea.focus();
},
closeModal() {
if (elements.modal) {
elements.modal.classList.add("hidden");
}
clearJSONError();
},
async copyJSONToClipboard() {
if (!elements.textarea) return;
try {
await navigator.clipboard.writeText(elements.textarea.value);
if (elements.copyBtn) {
const original = elements.copyBtn.innerHTML;
elements.copyBtn.innerHTML = '<i class="fa-solid fa-check"></i> Copied!';
setTimeout(() => {
if (elements.copyBtn) {
elements.copyBtn.innerHTML = original;
}
}, 1800);
}
if (this.callbacks.showToast) {
this.callbacks.showToast("JSON copied to clipboard", "success");
}
} catch (err) {
elements.textarea.select();
try {
document.execCommand("copy");
if (this.callbacks.showToast) {
this.callbacks.showToast("JSON copied to clipboard", "success");
}
} catch (e) {
if (this.callbacks.showToast) {
this.callbacks.showToast("Press Ctrl+C to copy the JSON", "warning");
}
}
}
},
};
wget 'https://sme10.lists2.roe3.org/spreadsheet/modules/p2pManager.js'import { showToast } from "./toastManager.js";
// Default fallback ICE servers (STUN only)
const FALLBACK_ICE_SERVERS = {
iceServers: [
{ urls: 'stun:stun.l.google.com:19302' },
{ urls: 'stun:stun1.l.google.com:19302' }
]
};
// Cached ICE configuration
let cachedIceConfig = null;
let cacheTimestamp = 0;
const CACHE_DURATION = 3600000; // 1 hour in milliseconds
// Fetch TURN credentials from Netlify Function (keeps API key secure)
async function fetchIceServers() {
const now = Date.now();
// Return cached config if still valid
if (cachedIceConfig && (now - cacheTimestamp) < CACHE_DURATION) {
return cachedIceConfig;
}
try {
const response = await fetch('/api/turn-credentials');
if (!response.ok) {
throw new Error(`HTTP ${response.status}`);
}
const data = await response.json();
if (data.fallback) {
console.warn('Using fallback STUN-only configuration');
}
cachedIceConfig = { iceServers: data.iceServers };
cacheTimestamp = now;
return cachedIceConfig;
} catch (error) {
console.error('Failed to fetch ICE servers:', error);
showToast('Using fallback connection (may have limited connectivity)', 'warning');
return FALLBACK_ICE_SERVERS;
}
}
const defaultCallbacks = {
onHostReady: () => {},
onConnectionOpened: () => {},
onInitialSync: () => {},
onRemoteCellUpdate: () => {},
onRemoteCursorMove: () => {},
onSyncRequest: () => {},
onConnectionClosed: () => {},
onConnectionError: () => {},
};
function getPeerConstructor() {
if (typeof window === "undefined") return null;
return window.Peer || null;
}
export const P2PManager = {
peer: null,
conn: null,
isHost: false,
myPeerId: null,
callbacks: { ...defaultCallbacks },
init(callbacks = {}) {
this.callbacks = { ...defaultCallbacks, ...callbacks };
},
canSend() {
return !!(this.conn && this.conn.open);
},
async startHosting() {
const PeerCtor = getPeerConstructor();
if (!PeerCtor) {
showToast("PeerJS not available. Check network or CSP.", "error");
return false;
}
this.disconnect({ silent: true });
this.isHost = true;
// Fetch ICE servers dynamically
const iceConfig = await fetchIceServers();
this.peer = new PeerCtor({ config: iceConfig });
this.peer.on("open", (id) => {
this.myPeerId = id;
this.callbacks.onHostReady(id);
});
this.peer.on("connection", (conn) => {
if (this.conn && this.conn.open) {
showToast("Already connected to a peer", "warning");
conn.close();
return;
}
this.handleConnection(conn);
});
this.peer.on("error", (err) => {
console.error(err);
showToast(`P2P Error: ${err.type || "unknown"}`, "error");
this.callbacks.onConnectionError(err);
});
this.peer.on("disconnected", () => {
showToast("Disconnected from signaling server", "warning");
});
return true;
},
async joinSession(hostId) {
const PeerCtor = getPeerConstructor();
if (!PeerCtor) {
showToast("PeerJS not available. Check network or CSP.", "error");
return false;
}
if (!hostId) {
showToast("Host ID required", "warning");
return false;
}
this.disconnect({ silent: true });
this.isHost = false;
// Fetch ICE servers dynamically
const iceConfig = await fetchIceServers();
this.peer = new PeerCtor({ config: iceConfig });
this.peer.on("open", () => {
const conn = this.peer.connect(hostId);
this.handleConnection(conn);
});
this.peer.on("error", (err) => {
console.error(err);
const msg = err && err.type === "peer-unavailable" ? "Host not found. Check the ID." : "Could not connect to host.";
showToast(msg, "error");
this.callbacks.onConnectionError(err);
});
return true;
},
handleConnection(conn) {
this.conn = conn;
conn.on("open", () => {
this.callbacks.onConnectionOpened(this.isHost);
});
conn.on("data", (payload) => {
this.handleIncomingData(payload);
});
conn.on("close", () => {
this.conn = null;
this.callbacks.onConnectionClosed();
});
conn.on("error", (err) => {
console.error(err);
showToast("Connection error", "error");
this.callbacks.onConnectionError(err);
});
},
handleIncomingData(payload) {
if (!payload || typeof payload !== "object") return;
const type = payload.type;
if (!type) return;
switch (type) {
case "INITIAL_SYNC":
case "FULL_SYNC":
this.callbacks.onInitialSync(payload.data, type);
break;
case "UPDATE_CELL":
this.callbacks.onRemoteCellUpdate(payload.data || {});
break;
case "UPDATE_CURSOR":
this.callbacks.onRemoteCursorMove(payload.data || {});
break;
case "SYNC_REQUEST":
this.callbacks.onSyncRequest();
break;
default:
console.warn("Unknown P2P message:", type);
}
},
sendPayload(payload) {
if (!this.canSend()) return false;
try {
this.conn.send(payload);
return true;
} catch (err) {
console.error(err);
showToast("Failed to send update", "error");
return false;
}
},
sendInitialSync(fullState) {
return this.sendPayload({ type: "INITIAL_SYNC", data: fullState });
},
sendFullSync(fullState) {
return this.sendPayload({ type: "FULL_SYNC", data: fullState });
},
requestFullSync() {
return this.sendPayload({ type: "SYNC_REQUEST" });
},
broadcastCellUpdate(row, col, value, formula) {
return this.sendPayload({
type: "UPDATE_CELL",
data: { row, col, value, formula },
});
},
broadcastCursor(row, col, color) {
return this.sendPayload({
type: "UPDATE_CURSOR",
data: { row, col, color },
});
},
disconnect({ silent = false } = {}) {
if (this.conn) {
try {
this.conn.close();
} catch (e) {}
}
if (this.peer) {
try {
this.peer.destroy();
} catch (e) {}
}
this.peer = null;
this.conn = null;
this.myPeerId = null;
this.isHost = false;
if (!silent) {
this.callbacks.onConnectionClosed();
}
},
};
wget 'https://sme10.lists2.roe3.org/spreadsheet/modules/passwordManager.js'/**
* Password Manager Module
* Handles all password-related state, UI, and interactions.
*/
export const PasswordManager = {
// State
currentPassword: null,
pendingEncryptedData: null,
modalMode: "set",
// Callbacks provided by main script
callbacks: {
decryptAndDecode: async () => null,
onDecryptSuccess: () => {},
updateURL: () => {},
showToast: () => {},
validateState: () => {},
},
/**
* Initialize the password manager
* @param {Object} callbacks - Functions required for integration
*/
init(callbacks) {
this.callbacks = { ...this.callbacks, ...callbacks };
this.attachEventListeners();
this.updateLockButtonUI();
},
/**
* Get the current password
* @returns {string|null}
*/
getPassword() {
return this.currentPassword;
},
/**
* Set the current password (e.g. after successful decryption or setting new)
* @param {string|null} password
*/
setPassword(password) {
this.currentPassword = password;
this.updateLockButtonUI();
},
/**
* Handle encrypted data found during load
* @param {string} encryptedData
*/
handleEncryptedData(encryptedData) {
this.pendingEncryptedData = encryptedData;
// Show password modal after a short delay to let UI render
setTimeout(() => this.showPasswordModal("decrypt"), 100);
},
// ========== UI Functions ==========
showPasswordModal(mode) {
this.modalMode = mode;
const modal = document.getElementById("password-modal");
const title = document.getElementById("modal-title");
const description = document.getElementById("modal-description");
const confirmInput = document.getElementById("password-confirm");
const submitBtn = document.getElementById("modal-submit");
const passwordInput = document.getElementById("password-input");
const errorEl = document.getElementById("modal-error");
if (!modal) return;
// Reset form
if (passwordInput) passwordInput.value = "";
if (confirmInput) confirmInput.value = "";
if (errorEl) {
errorEl.classList.add("hidden");
errorEl.textContent = "";
}
if (mode === "set") {
if (title) title.textContent = "Set Password";
if (description) description.textContent = "Enter a password to encrypt this spreadsheet. Anyone with the link will need this password to view it.";
if (confirmInput) {
confirmInput.style.display = "";
confirmInput.placeholder = "Confirm password";
}
if (submitBtn) submitBtn.textContent = "Set Password";
} else if (mode === "decrypt") {
if (title) title.textContent = "Enter Password";
if (description) description.textContent = "This spreadsheet is password-protected. Enter the password to view it.";
if (confirmInput) confirmInput.style.display = "none";
if (submitBtn) submitBtn.textContent = "Unlock";
} else if (mode === "remove") {
if (title) title.textContent = "Remove Password";
if (description) description.textContent = "Enter the current password to remove encryption from this spreadsheet.";
if (confirmInput) confirmInput.style.display = "none";
if (submitBtn) submitBtn.textContent = "Remove Password";
}
modal.classList.remove("hidden");
if (passwordInput) passwordInput.focus();
},
hidePasswordModal() {
const modal = document.getElementById("password-modal");
if (modal) modal.classList.add("hidden");
// Clear pending data if we cancelled decryption (though usually we'd want to keep it if they just closed modal?)
// Original logic cleared it:
// this.pendingEncryptedData = null;
// But if we cancel decryption, we probably can't view the file anyway.
},
showModalError(message) {
const errorEl = document.getElementById("modal-error");
if (errorEl) {
errorEl.textContent = message;
errorEl.classList.remove("hidden");
}
},
updateLockButtonUI() {
const lockBtn = document.getElementById("lock-btn");
if (!lockBtn) return;
const icon = lockBtn.querySelector("i");
if (this.currentPassword) {
lockBtn.classList.add("locked");
lockBtn.title = "Remove Password Protection";
if (icon) icon.className = "fa-solid fa-lock";
} else {
lockBtn.classList.remove("locked");
lockBtn.title = "Password Protection";
if (icon) icon.className = "fa-solid fa-lock-open";
}
},
handleLockButtonClick() {
if (this.currentPassword) {
// Already encrypted - offer to remove password
this.showPasswordModal("remove");
} else {
// Not encrypted - set password
this.showPasswordModal("set");
}
},
async handleModalSubmit() {
const passwordInput = document.getElementById("password-input");
const confirmInput = document.getElementById("password-confirm");
if (!passwordInput) return;
const password = passwordInput.value;
if (!password) {
this.showModalError("Please enter a password.");
return;
}
if (this.modalMode === "set") {
// Setting new password
const confirm = confirmInput ? confirmInput.value : "";
if (password !== confirm) {
this.showModalError("Passwords do not match.");
return;
}
if (password.length < 4) {
this.showModalError("Password must be at least 4 characters.");
return;
}
this.currentPassword = password;
this.updateLockButtonUI();
this.hidePasswordModal();
// Re-encode state with encryption
this.callbacks.updateURL();
this.callbacks.showToast("Password protection enabled", "success");
} else if (this.modalMode === "decrypt") {
// Decrypting loaded data
if (!this.pendingEncryptedData) {
this.showModalError("No encrypted data to decrypt.");
return;
}
try {
const rawState = await this.callbacks.decryptAndDecode(this.pendingEncryptedData, password);
if (!rawState) {
throw new Error("Invalid decrypted data");
}
// Validate state
const validatedState = this.callbacks.validateState(rawState);
if (!validatedState) {
throw new Error("Validation failed");
}
// Success
this.currentPassword = password;
this.pendingEncryptedData = null; // Clear it
this.callbacks.onDecryptSuccess(validatedState);
this.hidePasswordModal();
this.updateLockButtonUI();
this.callbacks.showToast("Spreadsheet unlocked", "success");
} catch (e) {
console.error("Decryption failed:", e);
this.showModalError("Incorrect password.");
}
} else if (this.modalMode === "remove") {
// For now, we trust they have the password if they are here (or we could verify it matches current)
// Note: Original code didn't strictly verify against currentPassword (it just checked if you entered *something*?)
// Wait, original: `if (modalMode === "remove") { ... currentPassword = null ... }`
// It didn't verify the typed password matches the active `currentPassword`.
// It prompted "Enter the current password", but didn't actually check it against `currentPassword`.
// The assumption is if you are viewing the file, you know the password (or it was just unlocked).
// However, for better security UX, we might want to check, but let's stick to original behavior to minimize regression risk.
// But wait, if I type "wrong" password to remove, it shouldn't work?
// Actually, since `currentPassword` is in memory, we CAN check it.
// If `currentPassword` is set, we SHOULD check.
if (this.currentPassword && password !== this.currentPassword) {
this.showModalError("Incorrect password.");
return;
}
this.currentPassword = null;
this.updateLockButtonUI();
this.hidePasswordModal();
// Re-encode state without encryption
this.callbacks.updateURL();
this.callbacks.showToast("Password protection removed", "success");
}
},
attachEventListeners() {
const lockBtn = document.getElementById("lock-btn");
const modalCancel = document.getElementById("modal-cancel");
const modalSubmit = document.getElementById("modal-submit");
const modalBackdrop = document.querySelector(".modal-backdrop");
const passwordInput = document.getElementById("password-input");
const passwordConfirm = document.getElementById("password-confirm");
if (lockBtn) {
// Use logical wrapper
lockBtn.addEventListener("click", () => this.handleLockButtonClick());
}
if (modalCancel) {
modalCancel.addEventListener("click", () => this.hidePasswordModal());
}
if (modalSubmit) {
modalSubmit.addEventListener("click", () => this.handleModalSubmit());
}
if (modalBackdrop) {
modalBackdrop.addEventListener("click", () => {
// Only allow closing if not in decrypt mode
if (this.modalMode !== "decrypt") {
this.hidePasswordModal();
}
});
}
if (passwordInput) {
passwordInput.addEventListener("keydown", (e) => {
if (e.key === "Enter") {
e.preventDefault();
// If confirm is visible and empty, focus it; otherwise submit
if (passwordConfirm && passwordConfirm.style.display !== "none" && !passwordConfirm.value) {
passwordConfirm.focus();
} else {
this.handleModalSubmit();
}
}
});
}
if (passwordConfirm) {
passwordConfirm.addEventListener("keydown", (e) => {
if (e.key === "Enter") {
e.preventDefault();
this.handleModalSubmit();
}
});
}
},
};
wget 'https://sme10.lists2.roe3.org/spreadsheet/modules/presentationManager.js'import { getState } from "./rowColManager.js";
import { sanitizeHTML } from "./security.js";
import { showToast } from "./toastManager.js";
import { VisualFunctions } from "./visualFunctions.js";
function extractPlainText(value) {
if (value === null || value === undefined) return "";
const parser = new DOMParser();
const doc = parser.parseFromString("<body>" + String(value) + "</body>", "text/html");
const text = doc.body.textContent || "";
return text.replace(/\u00a0/g, " ");
}
export const PresentationManager = {
overlay: null,
isActive: false,
slideCount: 0,
slides: [],
currentIndex: 0,
lastFocus: null,
_initialized: false,
_scrollHandler: null,
_resizeHandler: null,
_controlsEl: null,
_scrollRaf: null,
init() {
if (this._initialized) return;
this._initialized = true;
document.addEventListener("keydown", (event) => {
if (!this.isActive) return;
if (event.key === "Escape") {
event.preventDefault();
this.stop();
return;
}
if (this._handleNavigationKey(event)) {
return;
}
});
},
start(data, formulas, context = {}) {
if (this.isActive) return;
if (this.overlay) {
this.stop();
}
const { rows, cols } = getState();
const rowCount = Number.isFinite(rows) && rows > 0 ? rows : 0;
const colCount = Number.isFinite(cols) && cols > 0 ? cols : 0;
const safeData = Array.isArray(data) ? data : [];
const safeFormulas = Array.isArray(formulas) ? formulas : [];
const overlay = document.createElement("div");
overlay.className = "presentation-overlay";
overlay.setAttribute("tabindex", "0");
overlay.setAttribute("role", "dialog");
overlay.setAttribute("aria-modal", "true");
overlay.setAttribute("aria-label", "Presentation mode");
const slides = [];
let slideCount = 0;
for (let r = 0; r < rowCount; r++) {
const rowData = Array.isArray(safeData[r]) ? safeData[r] : [];
const rowFormulas = Array.isArray(safeFormulas[r]) ? safeFormulas[r] : [];
if (!this._rowHasContent(rowData, rowFormulas, colCount)) {
continue;
}
const slide = document.createElement("section");
slide.className = "slide";
slide.setAttribute("data-slide-index", String(slideCount));
const contentDiv = document.createElement("div");
contentDiv.className = "slide-content";
let titleFound = false;
for (let c = 0; c < colCount; c++) {
const rawValue = rowData[c];
const formula = rowFormulas[c];
const hasFormula = typeof formula === "string" && formula.trim().startsWith("=");
const hasValue = this._cellHasText(rawValue);
if (!hasFormula && !hasValue) continue;
const cellWrapper = document.createElement("div");
let isVisual = false;
if (hasFormula) {
try {
const visualEl = VisualFunctions.process(String(formula), context);
if (visualEl) {
cellWrapper.appendChild(visualEl);
cellWrapper.classList.add("slide-visual");
isVisual = true;
}
} catch (err) {
console.warn("Presentation visual error:", err);
}
}
if (!isVisual) {
const displayValue = this._getDisplayValue(rawValue, formula);
cellWrapper.innerHTML = sanitizeHTML(displayValue);
}
if (!titleFound) {
cellWrapper.classList.add("slide-title");
titleFound = true;
} else {
cellWrapper.classList.add("slide-text");
}
contentDiv.appendChild(cellWrapper);
}
slide.appendChild(contentDiv);
overlay.appendChild(slide);
slides.push(slide);
slideCount++;
}
if (slideCount === 0) {
showToast("Spreadsheet is empty. Add data to present.", "warning");
return;
}
const controls = document.createElement("div");
controls.className = "presentation-controls";
controls.innerHTML = `
<span class="slide-counter">1 / ${slideCount}</span>
<button id="exit-pres-btn" type="button"><i class="fa-solid fa-xmark"></i> Exit</button>
`;
overlay.appendChild(controls);
document.body.appendChild(overlay);
document.body.classList.add("presentation-mode-active");
this.lastFocus = document.activeElement;
overlay.focus();
this.overlay = overlay;
this.isActive = true;
this.slides = slides;
this.slideCount = slideCount;
this._controlsEl = controls;
this.currentIndex = 0;
const exitBtn = overlay.querySelector("#exit-pres-btn");
if (exitBtn) {
exitBtn.addEventListener("click", () => this.stop());
}
this._scrollHandler = () => {
if (this._scrollRaf) return;
this._scrollRaf = requestAnimationFrame(() => {
this._scrollRaf = null;
this._updateCounter();
});
};
this._resizeHandler = () => this._updateCounter();
overlay.addEventListener("scroll", this._scrollHandler);
window.addEventListener("resize", this._resizeHandler);
this._updateCounter();
},
stop() {
if (!this.overlay) return;
if (this._scrollHandler) {
this.overlay.removeEventListener("scroll", this._scrollHandler);
}
if (this._resizeHandler) {
window.removeEventListener("resize", this._resizeHandler);
}
if (this._scrollRaf) {
cancelAnimationFrame(this._scrollRaf);
this._scrollRaf = null;
}
this.overlay.remove();
this.overlay = null;
this.isActive = false;
this.slideCount = 0;
this.slides = [];
this.currentIndex = 0;
this._controlsEl = null;
document.body.classList.remove("presentation-mode-active");
if (this.lastFocus && typeof this.lastFocus.focus === "function" && document.contains(this.lastFocus)) {
this.lastFocus.focus();
}
this.lastFocus = null;
},
_updateCounter() {
if (!this.overlay || !this._controlsEl || !this.slideCount) return;
const slideHeight = this.overlay.clientHeight || window.innerHeight || 1;
const index = clampIndex(Math.round(this.overlay.scrollTop / slideHeight), this.slideCount);
this.currentIndex = index;
const counter = this._controlsEl.querySelector(".slide-counter");
if (counter) {
counter.textContent = `${index + 1} / ${this.slideCount}`;
}
},
_scrollToSlide(index) {
if (!this.overlay || !this.slideCount) return;
const target = clampIndex(index, this.slideCount);
const slideHeight = this.overlay.clientHeight || window.innerHeight || 1;
this.overlay.scrollTo({ top: target * slideHeight, behavior: "smooth" });
},
_handleNavigationKey(event) {
if (!this.overlay || !this.slideCount) return false;
let nextIndex = null;
if (event.key === "ArrowDown" || event.key === "PageDown" || event.key === " ") {
nextIndex = this.currentIndex + 1;
} else if (event.key === "ArrowUp" || event.key === "PageUp") {
nextIndex = this.currentIndex - 1;
} else if (event.key === "Home") {
nextIndex = 0;
} else if (event.key === "End") {
nextIndex = this.slideCount - 1;
}
if (nextIndex === null) return false;
event.preventDefault();
this._scrollToSlide(nextIndex);
return true;
},
_rowHasContent(rowData, rowFormulas, colCount) {
for (let c = 0; c < colCount; c++) {
const formula = rowFormulas && rowFormulas[c];
if (typeof formula === "string" && formula.trim() !== "") return true;
if (this._cellHasText(rowData && rowData[c])) return true;
}
return false;
},
_cellHasText(value) {
const text = extractPlainText(value);
return text.trim() !== "";
},
_getDisplayValue(rawValue, formula) {
const rawText = rawValue === null || rawValue === undefined ? "" : String(rawValue);
if (rawText.trim() !== "") return rawText;
if (typeof formula === "string" && formula.trim() !== "") return String(formula);
return "";
},
};
function clampIndex(index, count) {
if (count <= 0) return 0;
if (index < 0) return 0;
if (index >= count) return count - 1;
return index;
}
wget 'https://sme10.lists2.roe3.org/spreadsheet/modules/rowColManager.js'// Row and Column Manager Module
// Handles grid rendering, selection, resizing, and row/column operations
import {
ACTIVE_HEADER_CLASS,
DEFAULT_COL_WIDTH,
DEFAULT_COLS,
DEFAULT_ROW_HEIGHT,
DEFAULT_ROWS,
HEADER_ROW_HEIGHT,
MAX_COLS,
MAX_ROWS,
MIN_COL_WIDTH,
MIN_ROW_HEIGHT,
ROW_HEADER_WIDTH,
} from "./constants.js";
import { colToLetter } from "./formulaManager.js";
import { sanitizeHTML } from "./security.js";
import { showToast } from "./toastManager.js";
import { createDefaultColumnWidths, createDefaultRowHeights, createEmptyCellStyle, createEmptyCellStyles, createEmptyData } from "./urlManager.js";
// ========== State ==========
const state = {
rows: DEFAULT_ROWS,
cols: DEFAULT_COLS,
colWidths: createDefaultColumnWidths(DEFAULT_COLS),
rowHeights: createDefaultRowHeights(DEFAULT_ROWS),
selectionStart: null,
selectionEnd: null,
isSelecting: false,
hoverRow: null,
hoverCol: null,
activeRow: null,
activeCol: null,
resizeState: null,
};
// ========== Callbacks ==========
let callbacks = {
debouncedUpdateURL: null,
recalculateFormulas: null,
getDataArray: null,
setDataArray: null,
getFormulasArray: null,
setFormulasArray: null,
getCellStylesArray: null,
setCellStylesArray: null,
PasswordManager: null,
getReadOnlyFlag: null,
// Formula mode callbacks
getFormulaEditMode: null,
getFormulaEditCell: null,
setFormulaRangeStart: null,
setFormulaRangeEnd: null,
getFormulaRangeStart: null,
getFormulaRangeEnd: null,
buildRangeRef: null,
insertTextAtCursor: null,
FormulaDropdownManager: null,
onSelectionChange: null,
onGridResize: null,
onFormulaChange: null,
};
// ========== State Accessors ==========
export function getState() {
return state;
}
export function setState(key, value) {
if (key in state) {
state[key] = value;
}
}
export function setCallbacks(cbs) {
callbacks = { ...callbacks, ...cbs };
}
// ========== UI Update ==========
export function updateUI() {
const addRowBtn = document.getElementById("add-row");
const addColBtn = document.getElementById("add-col");
const gridSizeEl = document.getElementById("grid-size");
if (addRowBtn) {
addRowBtn.disabled = state.rows >= MAX_ROWS;
}
if (addColBtn) {
addColBtn.disabled = state.cols >= MAX_COLS;
}
if (gridSizeEl) {
gridSizeEl.textContent = `${state.rows} × ${state.cols}`;
}
}
// ========== Grid Template ==========
export function applyGridTemplate() {
const container = document.getElementById("spreadsheet");
if (!container) return;
const columnSizes = state.colWidths.map((width) => `${width}px`).join(" ");
const rowSizes = state.rowHeights.map((height) => `${height}px`).join(" ");
container.style.gridTemplateColumns = `${ROW_HEADER_WIDTH}px ${columnSizes}`;
container.style.gridTemplateRows = `${HEADER_ROW_HEIGHT}px ${rowSizes}`;
}
// ========== Render Grid ==========
export function renderGrid() {
const container = document.getElementById("spreadsheet");
if (!container) return;
const isReadOnly = callbacks.getReadOnlyFlag ? callbacks.getReadOnlyFlag() : false;
const data = callbacks.getDataArray ? callbacks.getDataArray() : [];
const cellStyles = callbacks.getCellStylesArray ? callbacks.getCellStylesArray() : [];
// Clear selection when grid is re-rendered
state.selectionStart = null;
state.selectionEnd = null;
state.isSelecting = false;
state.hoverRow = null;
state.hoverCol = null;
if (callbacks.onSelectionChange) {
callbacks.onSelectionChange(null);
}
container.innerHTML = "";
applyGridTemplate();
// Corner cell (empty)
const corner = document.createElement("div");
corner.className = "corner-cell";
container.appendChild(corner);
// Column headers (A-Z) - sticky to top
for (let col = 0; col < state.cols; col++) {
const header = document.createElement("div");
header.className = "header-cell col-header";
header.textContent = colToLetter(col);
header.dataset.col = col;
const colResize = document.createElement("div");
colResize.className = "resize-handle col-resize";
colResize.dataset.col = col;
colResize.setAttribute("aria-hidden", "true");
header.appendChild(colResize);
container.appendChild(header);
}
// Rows
for (let row = 0; row < state.rows; row++) {
// Row header (1, 2, 3...) - sticky to left
const rowHeader = document.createElement("div");
rowHeader.className = "header-cell row-header";
rowHeader.textContent = row + 1;
rowHeader.dataset.row = row;
const rowResize = document.createElement("div");
rowResize.className = "resize-handle row-resize";
rowResize.dataset.row = row;
rowResize.setAttribute("aria-hidden", "true");
rowHeader.appendChild(rowResize);
container.appendChild(rowHeader);
// Data cells
for (let col = 0; col < state.cols; col++) {
const cell = document.createElement("div");
cell.className = "cell";
const contentDiv = document.createElement("div");
contentDiv.className = "cell-content";
contentDiv.contentEditable = isReadOnly ? "false" : "true";
contentDiv.dataset.row = row;
contentDiv.dataset.col = col;
contentDiv.innerHTML = sanitizeHTML(data[row] ? data[row][col] || "" : "");
contentDiv.setAttribute("aria-label", `Cell ${colToLetter(col)}${row + 1}`);
const style = cellStyles[row] ? cellStyles[row][col] : null;
if (style) {
contentDiv.style.textAlign = style.align || "";
contentDiv.style.color = style.color || "";
contentDiv.style.fontSize = style.fontSize ? `${style.fontSize}px` : "";
if (style.bg) {
cell.style.setProperty("--cell-bg", style.bg);
} else {
cell.style.removeProperty("--cell-bg");
}
} else {
contentDiv.style.textAlign = "";
contentDiv.style.color = "";
contentDiv.style.fontSize = "";
cell.style.removeProperty("--cell-bg");
}
cell.appendChild(contentDiv);
container.appendChild(cell);
}
}
updateUI();
}
// ========== Header Highlighting ==========
export function clearActiveHeaders() {
if (state.activeRow !== null) {
const rowHeader = document.querySelector(`.row-header[data-row="${state.activeRow}"]`);
if (rowHeader) rowHeader.classList.remove(ACTIVE_HEADER_CLASS);
}
if (state.activeCol !== null) {
const colHeader = document.querySelector(`.col-header[data-col="${state.activeCol}"]`);
if (colHeader) colHeader.classList.remove(ACTIVE_HEADER_CLASS);
}
state.activeRow = null;
state.activeCol = null;
}
export function setActiveHeaders(row, col) {
if (state.activeRow === row && state.activeCol === col) return;
clearActiveHeaders();
state.activeRow = row;
state.activeCol = col;
const rowHeader = document.querySelector(`.row-header[data-row="${row}"]`);
if (rowHeader) rowHeader.classList.add(ACTIVE_HEADER_CLASS);
const colHeader = document.querySelector(`.col-header[data-col="${col}"]`);
if (colHeader) colHeader.classList.add(ACTIVE_HEADER_CLASS);
}
export function setActiveHeadersForRange(minRow, maxRow, minCol, maxCol) {
// Clear existing header highlights
document.querySelectorAll(`.${ACTIVE_HEADER_CLASS}`).forEach((el) => {
el.classList.remove(ACTIVE_HEADER_CLASS);
});
// Highlight all row headers in range
for (let r = minRow; r <= maxRow; r++) {
const rowHeader = document.querySelector(`.row-header[data-row="${r}"]`);
if (rowHeader) rowHeader.classList.add(ACTIVE_HEADER_CLASS);
}
// Highlight all column headers in range
for (let c = minCol; c <= maxCol; c++) {
const colHeader = document.querySelector(`.col-header[data-col="${c}"]`);
if (colHeader) colHeader.classList.add(ACTIVE_HEADER_CLASS);
}
// Update active row/col tracking
state.activeRow = minRow;
state.activeCol = minCol;
}
// ========== Selection Functions ==========
export function getSelectionBounds() {
if (!state.selectionStart || !state.selectionEnd) return null;
return {
minRow: Math.min(state.selectionStart.row, state.selectionEnd.row),
maxRow: Math.max(state.selectionStart.row, state.selectionEnd.row),
minCol: Math.min(state.selectionStart.col, state.selectionEnd.col),
maxCol: Math.max(state.selectionStart.col, state.selectionEnd.col),
};
}
export function hasMultiSelection() {
if (!state.selectionStart || !state.selectionEnd) return false;
return state.selectionStart.row !== state.selectionEnd.row || state.selectionStart.col !== state.selectionEnd.col;
}
export function clearSelection() {
state.selectionStart = null;
state.selectionEnd = null;
state.isSelecting = false;
const container = document.getElementById("spreadsheet");
if (!container) return;
// Remove selection classes from all cells
container.querySelectorAll(".cell-selected").forEach((cell) => {
cell.classList.remove("cell-selected", "selection-top", "selection-bottom", "selection-left", "selection-right");
});
// Remove selecting mode from container
container.classList.remove("selecting");
if (callbacks.onSelectionChange) {
callbacks.onSelectionChange(null);
}
}
export function updateSelectionVisuals() {
const container = document.getElementById("spreadsheet");
if (!container) return;
const bounds = getSelectionBounds();
if (!bounds) {
clearSelection();
return;
}
// Clear previous selection classes
container.querySelectorAll(".cell-selected").forEach((cell) => {
cell.classList.remove("cell-selected", "selection-top", "selection-bottom", "selection-left", "selection-right");
});
const { minRow, maxRow, minCol, maxCol } = bounds;
// Apply selection classes to cells in range
for (let r = minRow; r <= maxRow; r++) {
for (let c = minCol; c <= maxCol; c++) {
const cellContent = container.querySelector(`.cell-content[data-row="${r}"][data-col="${c}"]`);
if (cellContent && cellContent.parentElement) {
const cell = cellContent.parentElement;
cell.classList.add("cell-selected");
// Add border classes for outer edges
if (r === minRow) cell.classList.add("selection-top");
if (r === maxRow) cell.classList.add("selection-bottom");
if (c === minCol) cell.classList.add("selection-left");
if (c === maxCol) cell.classList.add("selection-right");
}
}
}
// Highlight headers for the entire range
setActiveHeadersForRange(minRow, maxRow, minCol, maxCol);
if (callbacks.onSelectionChange) {
callbacks.onSelectionChange({ ...bounds });
}
}
export function clearSelectedCells() {
if (!state.selectionStart || !state.selectionEnd) return;
const bounds = getSelectionBounds();
const container = document.getElementById("spreadsheet");
const data = callbacks.getDataArray ? callbacks.getDataArray() : [];
const formulas = callbacks.getFormulasArray ? callbacks.getFormulasArray() : [];
for (let r = bounds.minRow; r <= bounds.maxRow; r++) {
for (let c = bounds.minCol; c <= bounds.maxCol; c++) {
// Clear data and formula
if (data[r]) data[r][c] = "";
if (formulas[r]) formulas[r][c] = "";
// Update DOM
const cell = container.querySelector(`.cell-content[data-row="${r}"][data-col="${c}"]`);
if (cell) {
cell.innerHTML = "";
}
}
}
if (callbacks.recalculateFormulas) callbacks.recalculateFormulas();
if (callbacks.debouncedUpdateURL) callbacks.debouncedUpdateURL();
if (callbacks.onSelectionChange) {
callbacks.onSelectionChange(getSelectionBounds());
}
}
// ========== Hover Functions ==========
export function getCellContentFromTarget(target) {
if (!(target instanceof Element)) return null;
if (target.classList.contains("cell-content")) {
return target;
}
if (target.classList.contains("cell")) {
return target.querySelector(".cell-content");
}
return target.closest(".cell-content");
}
export function addHoverRow(row) {
const container = document.getElementById("spreadsheet");
if (!container) return;
container.querySelectorAll(`.cell-content[data-row="${row}"]`).forEach((cellContent) => {
if (cellContent.parentElement) {
cellContent.parentElement.classList.add("hover-row");
}
});
const rowHeader = container.querySelector(`.row-header[data-row="${row}"]`);
if (rowHeader) {
rowHeader.classList.add("header-hover");
}
}
export function addHoverCol(col) {
const container = document.getElementById("spreadsheet");
if (!container) return;
container.querySelectorAll(`.cell-content[data-col="${col}"]`).forEach((cellContent) => {
if (cellContent.parentElement) {
cellContent.parentElement.classList.add("hover-col");
}
});
const colHeader = container.querySelector(`.col-header[data-col="${col}"]`);
if (colHeader) {
colHeader.classList.add("header-hover");
}
}
export function removeHoverRow(row) {
const container = document.getElementById("spreadsheet");
if (!container) return;
container.querySelectorAll(`.cell-content[data-row="${row}"]`).forEach((cellContent) => {
if (cellContent.parentElement) {
cellContent.parentElement.classList.remove("hover-row");
}
});
const rowHeader = container.querySelector(`.row-header[data-row="${row}"]`);
if (rowHeader) {
rowHeader.classList.remove("header-hover");
}
}
export function removeHoverCol(col) {
const container = document.getElementById("spreadsheet");
if (!container) return;
container.querySelectorAll(`.cell-content[data-col="${col}"]`).forEach((cellContent) => {
if (cellContent.parentElement) {
cellContent.parentElement.classList.remove("hover-col");
}
});
const colHeader = container.querySelector(`.col-header[data-col="${col}"]`);
if (colHeader) {
colHeader.classList.remove("header-hover");
}
}
export function clearHoverHighlights() {
if (state.hoverRow !== null) {
removeHoverRow(state.hoverRow);
}
if (state.hoverCol !== null) {
removeHoverCol(state.hoverCol);
}
state.hoverRow = null;
state.hoverCol = null;
}
export function setHoverHighlight(row, col) {
if (row === state.hoverRow && col === state.hoverCol) return;
if (state.hoverRow !== null && state.hoverRow !== row) {
removeHoverRow(state.hoverRow);
}
if (state.hoverCol !== null && state.hoverCol !== col) {
removeHoverCol(state.hoverCol);
}
state.hoverRow = row;
state.hoverCol = col;
if (state.hoverRow !== null) {
addHoverRow(state.hoverRow);
}
if (state.hoverCol !== null) {
addHoverCol(state.hoverCol);
}
}
export function updateHoverFromTarget(target) {
if (state.isSelecting || hasMultiSelection()) {
clearHoverHighlights();
return;
}
const cellContent = getCellContentFromTarget(target);
if (!cellContent || !cellContent.classList.contains("cell-content")) {
clearHoverHighlights();
return;
}
const row = parseInt(cellContent.dataset.row, 10);
const col = parseInt(cellContent.dataset.col, 10);
if (isNaN(row) || isNaN(col)) {
clearHoverHighlights();
return;
}
setHoverHighlight(row, col);
}
// ========== Cell Positioning ==========
export function getCellFromPoint(x, y) {
const element = document.elementFromPoint(x, y);
if (!element) return null;
// Check if it's a cell-content or its parent cell
let cellContent = element;
if (element.classList.contains("cell")) {
cellContent = element.querySelector(".cell-content");
}
if (!cellContent || !cellContent.classList.contains("cell-content")) return null;
const row = parseInt(cellContent.dataset.row, 10);
const col = parseInt(cellContent.dataset.col, 10);
if (isNaN(row) || isNaN(col)) return null;
return { row, col };
}
export function getCellContentElement(row, col) {
return document.querySelector(`.cell-content[data-row="${row}"][data-col="${col}"]`);
}
export function getCellElement(row, col) {
const cellContent = getCellContentElement(row, col);
return cellContent ? cellContent.parentElement : null;
}
export function focusCellAt(row, col) {
const cellContent = getCellContentElement(row, col);
if (!cellContent) return null;
cellContent.focus();
cellContent.scrollIntoView({ block: "nearest", inline: "nearest" });
return cellContent;
}
// ========== Resize Handlers ==========
export function handleResizeStart(event) {
if (event.button !== 0) return;
if (!(event.target instanceof Element)) return;
const handle = event.target.closest(".resize-handle");
if (!handle) return;
const isColResize = handle.classList.contains("col-resize");
const indexValue = isColResize ? handle.dataset.col : handle.dataset.row;
const index = parseInt(indexValue, 10);
if (isNaN(index)) return;
event.preventDefault();
event.stopImmediatePropagation();
state.resizeState = {
type: isColResize ? "col" : "row",
index,
startX: event.clientX,
startY: event.clientY,
startSize: isColResize ? state.colWidths[index] || DEFAULT_COL_WIDTH : state.rowHeights[index] || DEFAULT_ROW_HEIGHT,
};
state.isSelecting = false;
document.body.classList.add("resizing");
document.body.style.cursor = isColResize ? "col-resize" : "row-resize";
document.addEventListener("mousemove", handleResizeMove);
document.addEventListener("mouseup", handleResizeEnd);
}
export function handleResizeMove(event) {
if (!state.resizeState) return;
if (state.resizeState.type === "col") {
const delta = event.clientX - state.resizeState.startX;
const nextWidth = Math.max(MIN_COL_WIDTH, state.resizeState.startSize + delta);
state.colWidths[state.resizeState.index] = nextWidth;
} else {
const delta = event.clientY - state.resizeState.startY;
const nextHeight = Math.max(MIN_ROW_HEIGHT, state.resizeState.startSize + delta);
state.rowHeights[state.resizeState.index] = nextHeight;
}
applyGridTemplate();
}
export function handleResizeEnd() {
if (!state.resizeState) return;
document.removeEventListener("mousemove", handleResizeMove);
document.removeEventListener("mouseup", handleResizeEnd);
document.body.classList.remove("resizing");
document.body.style.cursor = "";
state.resizeState = null;
if (callbacks.debouncedUpdateURL) callbacks.debouncedUpdateURL();
if (callbacks.onGridResize) callbacks.onGridResize();
}
// ========== Mouse Event Handlers ==========
export function handleMouseDown(event) {
// Only handle left mouse button
if (event.button !== 0) return;
const target = event.target;
// Check if clicking on a cell
let cellContent = target;
if (target.classList.contains("cell")) {
cellContent = target.querySelector(".cell-content");
}
if (!cellContent || !cellContent.classList.contains("cell-content")) {
// Clicked outside cells - clear selection
clearSelection();
return;
}
const row = parseInt(cellContent.dataset.row, 10);
const col = parseInt(cellContent.dataset.col, 10);
if (isNaN(row) || isNaN(col)) return;
const container = document.getElementById("spreadsheet");
// If in formula edit mode and clicking on a different cell
const formulaEditMode = callbacks.getFormulaEditMode ? callbacks.getFormulaEditMode() : false;
const formulaEditCell = callbacks.getFormulaEditCell ? callbacks.getFormulaEditCell() : null;
if (formulaEditMode && formulaEditCell) {
// Don't process clicks on the formula cell itself
if (row !== formulaEditCell.row || col !== formulaEditCell.col) {
event.preventDefault();
event.stopPropagation();
if (callbacks.FormulaDropdownManager) {
callbacks.FormulaDropdownManager.hide();
}
// Start range selection for formula
if (callbacks.setFormulaRangeStart) callbacks.setFormulaRangeStart({ row, col });
if (callbacks.setFormulaRangeEnd) callbacks.setFormulaRangeEnd({ row, col });
state.isSelecting = true;
clearHoverHighlights();
// Show visual selection
state.selectionStart = { row, col };
state.selectionEnd = { row, col };
updateSelectionVisuals();
if (container) {
container.classList.add("selecting");
}
return;
}
}
// Shift+click: extend selection from anchor
if (event.shiftKey && state.selectionStart) {
state.selectionEnd = { row, col };
updateSelectionVisuals();
event.preventDefault();
return;
}
// Start new selection
state.selectionStart = { row, col };
state.selectionEnd = { row, col };
state.isSelecting = true;
clearHoverHighlights();
if (container) {
container.classList.add("selecting");
}
updateSelectionVisuals();
}
export function handleMouseMove(event) {
if (!state.isSelecting) {
updateHoverFromTarget(event.target);
return;
}
const cellCoords = getCellFromPoint(event.clientX, event.clientY);
if (!cellCoords) return;
// Only update if position changed
if (state.selectionEnd && cellCoords.row === state.selectionEnd.row && cellCoords.col === state.selectionEnd.col) {
return;
}
// If in formula edit mode, update formula range
const formulaEditMode = callbacks.getFormulaEditMode ? callbacks.getFormulaEditMode() : false;
const formulaRangeStart = callbacks.getFormulaRangeStart ? callbacks.getFormulaRangeStart() : null;
if (formulaEditMode && formulaRangeStart) {
if (callbacks.setFormulaRangeEnd) callbacks.setFormulaRangeEnd(cellCoords);
state.selectionEnd = cellCoords;
updateSelectionVisuals();
event.preventDefault();
return;
}
state.selectionEnd = cellCoords;
updateSelectionVisuals();
// Prevent text selection during drag
event.preventDefault();
}
export function handleMouseLeave() {
clearHoverHighlights();
}
export function handleMouseUp(event) {
if (!state.isSelecting) return;
state.isSelecting = false;
const container = document.getElementById("spreadsheet");
if (container) {
container.classList.remove("selecting");
}
// If in formula edit mode, insert the range reference
const formulaEditMode = callbacks.getFormulaEditMode ? callbacks.getFormulaEditMode() : false;
const formulaEditCell = callbacks.getFormulaEditCell ? callbacks.getFormulaEditCell() : null;
const formulaRangeStart = callbacks.getFormulaRangeStart ? callbacks.getFormulaRangeStart() : null;
const formulaRangeEnd = callbacks.getFormulaRangeEnd ? callbacks.getFormulaRangeEnd() : null;
if (formulaEditMode && formulaEditCell && formulaRangeStart) {
const rangeRef = callbacks.buildRangeRef
? callbacks.buildRangeRef(formulaRangeStart.row, formulaRangeStart.col, formulaRangeEnd.row, formulaRangeEnd.col)
: "";
// Focus back on formula cell and insert range
formulaEditCell.element.focus();
const data = callbacks.getDataArray ? callbacks.getDataArray() : [];
const formulas = callbacks.getFormulasArray ? callbacks.getFormulasArray() : [];
// Use setTimeout to ensure focus is established before inserting
setTimeout(function () {
if (callbacks.insertTextAtCursor) callbacks.insertTextAtCursor(rangeRef);
// Update stored formula
if (formulas[formulaEditCell.row]) {
formulas[formulaEditCell.row][formulaEditCell.col] = formulaEditCell.element.innerText;
}
if (data[formulaEditCell.row]) {
data[formulaEditCell.row][formulaEditCell.col] = formulaEditCell.element.innerText;
}
// Clear formula range selection but stay in formula edit mode
if (callbacks.setFormulaRangeStart) callbacks.setFormulaRangeStart(null);
if (callbacks.setFormulaRangeEnd) callbacks.setFormulaRangeEnd(null);
clearSelection();
if (callbacks.debouncedUpdateURL) callbacks.debouncedUpdateURL();
if (callbacks.onFormulaChange) callbacks.onFormulaChange();
}, 0);
return;
}
// If single cell selected, allow normal focus behavior
if (!hasMultiSelection()) {
// Let the cell receive focus for editing
const cellContent = document.querySelector(`.cell-content[data-row="${state.selectionStart.row}"][data-col="${state.selectionStart.col}"]`);
if (cellContent) {
cellContent.focus();
}
}
}
// ========== Touch Event Handlers ==========
export function handleTouchStart(event) {
// Only handle single touch
if (event.touches.length !== 1) return;
const touch = event.touches[0];
const cellCoords = getCellFromPoint(touch.clientX, touch.clientY);
if (!cellCoords) {
clearSelection();
return;
}
// Start new selection
state.selectionStart = cellCoords;
state.selectionEnd = cellCoords;
state.isSelecting = true;
const container = document.getElementById("spreadsheet");
if (container) {
container.classList.add("selecting");
}
updateSelectionVisuals();
}
export function handleTouchMove(event) {
if (!state.isSelecting) return;
if (event.touches.length !== 1) return;
const touch = event.touches[0];
const cellCoords = getCellFromPoint(touch.clientX, touch.clientY);
if (!cellCoords) return;
// Only update if position changed
if (state.selectionEnd && cellCoords.row === state.selectionEnd.row && cellCoords.col === state.selectionEnd.col) {
return;
}
state.selectionEnd = cellCoords;
updateSelectionVisuals();
// Prevent scrolling during selection
event.preventDefault();
}
export function handleTouchEnd(event) {
if (!state.isSelecting) return;
state.isSelecting = false;
const container = document.getElementById("spreadsheet");
if (container) {
container.classList.remove("selecting");
}
// If single cell selected, allow focus for editing
if (!hasMultiSelection() && state.selectionStart) {
const cellContent = document.querySelector(`.cell-content[data-row="${state.selectionStart.row}"][data-col="${state.selectionStart.col}"]`);
if (cellContent) {
cellContent.focus();
}
}
}
// ========== Add/Remove Row/Column ==========
export function addRow() {
if (state.rows >= MAX_ROWS) {
showToast(`Maximum ${MAX_ROWS} rows allowed`, "warning");
return;
}
const data = callbacks.getDataArray ? callbacks.getDataArray() : [];
const formulas = callbacks.getFormulasArray ? callbacks.getFormulasArray() : [];
const cellStyles = callbacks.getCellStylesArray ? callbacks.getCellStylesArray() : [];
state.rows++;
data.push(Array(state.cols).fill(""));
formulas.push(Array(state.cols).fill(""));
cellStyles.push(
Array(state.cols)
.fill(null)
.map(() => createEmptyCellStyle())
);
state.rowHeights.push(DEFAULT_ROW_HEIGHT);
renderGrid();
if (callbacks.debouncedUpdateURL) callbacks.debouncedUpdateURL();
showToast(`Row ${state.rows} added`, "success");
}
export function addColumn() {
if (state.cols >= MAX_COLS) {
showToast(`Maximum ${MAX_COLS} columns allowed`, "warning");
return;
}
const data = callbacks.getDataArray ? callbacks.getDataArray() : [];
const formulas = callbacks.getFormulasArray ? callbacks.getFormulasArray() : [];
const cellStyles = callbacks.getCellStylesArray ? callbacks.getCellStylesArray() : [];
state.cols++;
data.forEach((row) => row.push(""));
formulas.forEach((row) => row.push(""));
cellStyles.forEach((row) => row.push(createEmptyCellStyle()));
state.colWidths.push(DEFAULT_COL_WIDTH);
renderGrid();
if (callbacks.debouncedUpdateURL) callbacks.debouncedUpdateURL();
showToast(`Column ${colToLetter(state.cols - 1)} added`, "success");
}
// ========== Clear Spreadsheet ==========
export function clearSpreadsheet() {
if (!confirm("Clear all data and reset to 10×10 grid?")) {
return;
}
// Reset to default dimensions
state.rows = DEFAULT_ROWS;
state.cols = DEFAULT_COLS;
state.colWidths = createDefaultColumnWidths(state.cols);
state.rowHeights = createDefaultRowHeights(state.rows);
// Reset data arrays via callbacks
if (callbacks.setDataArray) callbacks.setDataArray(createEmptyData(state.rows, state.cols));
if (callbacks.setFormulasArray) callbacks.setFormulasArray(createEmptyData(state.rows, state.cols));
if (callbacks.setCellStylesArray) callbacks.setCellStylesArray(createEmptyCellStyles(state.rows, state.cols));
// Clear password
if (callbacks.PasswordManager) callbacks.PasswordManager.setPassword(null);
// Clear any selection
clearSelection();
// Re-render and update URL
renderGrid();
if (callbacks.debouncedUpdateURL) callbacks.debouncedUpdateURL();
showToast("Spreadsheet cleared", "success");
}
wget 'https://sme10.lists2.roe3.org/spreadsheet/modules/security.js'import { ALLOWED_SPAN_STYLES, ALLOWED_TAGS } from "./constants.js";
// Validate CSS color values to prevent CSS injection
export function isValidCSSColor(color) {
if (color === null || color === undefined) return false;
if (typeof color !== "string") return false;
// Allow empty string (to clear color)
if (color === "") return true;
// Validate hex colors (#RGB, #RRGGBB, #RRGGBBAA)
if (/^#([0-9A-Fa-f]{3}|[0-9A-Fa-f]{6}|[0-9A-Fa-f]{8})$/.test(color)) {
return true;
}
// Validate rgb/rgba with proper bounds
if (/^rgba?\(\s*\d{1,3}\s*,\s*\d{1,3}\s*,\s*\d{1,3}\s*(,\s*(0|1|0?\.\d+)\s*)?\)$/.test(color)) {
return true;
}
// Reject anything else (prevents CSS injection)
return false;
}
// Escape HTML entities for safe display (converts HTML to plain text display)
export function escapeHTML(str) {
if (!str || typeof str !== "string") return "";
return str.replace(/&/g, "&").replace(/</g, "<").replace(/>/g, ">").replace(/"/g, """).replace(/'/g, "'");
}
// Filter safe CSS styles for span elements
export function filterSafeStyles(styleString) {
if (!styleString) return "";
const safeStyles = [];
const parts = styleString.split(";");
for (const part of parts) {
const colonIndex = part.indexOf(":");
if (colonIndex === -1) continue;
const prop = part.substring(0, colonIndex).trim().toLowerCase();
if (ALLOWED_SPAN_STYLES.includes(prop)) {
safeStyles.push(part.trim());
}
}
return safeStyles.join("; ");
}
// Defense-in-depth: Check for dangerous patterns that might bypass sanitization
// Returns true if content appears safe, false if dangerous patterns detected
export function isContentSafe(html) {
if (!html || typeof html !== "string") return true;
// Dangerous patterns to reject (case-insensitive)
const dangerousPatterns = [
/<script/i, // Script tags
/javascript:/i, // JavaScript protocol
/on\w+\s*=/i, // Event handlers (onclick, onerror, etc.)
/data:\s*text\/html/i, // Data URLs with HTML
/<iframe/i, // Iframes
/<object/i, // Object embeds
/<embed/i, // Embed tags
/<link/i, // Link tags (can load external resources)
/<meta/i, // Meta tags (can redirect)
/<base/i, // Base tag (can change URL resolution)
/expression\s*\(/i, // CSS expressions (IE)
/url\s*\(\s*["']?\s*javascript:/i, // JavaScript in CSS url()
];
for (const pattern of dangerousPatterns) {
if (pattern.test(html)) {
console.warn("Blocked dangerous content pattern:", pattern.toString());
return false;
}
}
return true;
}
// Sanitize HTML using DOMParser (does NOT execute scripts/event handlers)
export function sanitizeHTML(html) {
if (!html || typeof html !== "string") return "";
// Defense-in-depth: Pre-check for obviously dangerous patterns
if (!isContentSafe(html)) {
// Return escaped version instead of potentially dangerous content
return escapeHTML(html);
}
// Use DOMParser - it does NOT execute scripts or event handlers
const parser = new DOMParser();
const doc = parser.parseFromString("<body>" + html + "</body>", "text/html");
function sanitizeNode(node) {
const childNodes = Array.from(node.childNodes);
for (const child of childNodes) {
if (child.nodeType === Node.TEXT_NODE) {
continue;
}
if (child.nodeType === Node.ELEMENT_NODE) {
const tagName = child.tagName.toUpperCase();
if (!ALLOWED_TAGS.includes(tagName)) {
// Replace disallowed tags with their text content
const textNode = document.createTextNode(child.textContent || "");
node.replaceChild(textNode, child);
} else {
// Remove all attributes except safe styles on SPAN
const attrs = Array.from(child.attributes);
for (const attr of attrs) {
if (tagName === "SPAN" && attr.name === "style") {
const safeStyle = filterSafeStyles(attr.value);
if (safeStyle) {
child.setAttribute("style", safeStyle);
} else {
child.removeAttribute("style");
}
} else {
child.removeAttribute(attr.name);
}
}
sanitizeNode(child);
}
} else {
// Remove comments and other node types
node.removeChild(child);
}
}
}
sanitizeNode(doc.body);
const result = doc.body.innerHTML;
// Defense-in-depth: Final verification of sanitized output
if (!isContentSafe(result)) {
console.warn("Sanitized output still contains dangerous patterns, escaping");
return escapeHTML(html);
}
return result;
}
wget 'https://sme10.lists2.roe3.org/spreadsheet/modules/toastManager.js'import { TOAST_DURATION, TOAST_ICONS } from "./constants.js";
/**
* Dismiss a toast with exit animation
* @param {HTMLElement} toast - The toast element to dismiss
*/
export function dismissToast(toast) {
if (!toast || toast.classList.contains("toast-exit")) return;
toast.classList.add("toast-exit");
toast.addEventListener(
"animationend",
() => {
toast.remove();
},
{ once: true }
);
}
/**
* Show a toast notification
* @param {string} message - The message to display
* @param {string} type - Type: 'success', 'error', 'warning', 'info'
* @param {number} duration - Duration in ms (0 for no auto-dismiss)
*/
export function showToast(message, type = "info", duration = TOAST_DURATION) {
const container = document.getElementById("toast-container");
if (!container) return;
// Create toast element
const toast = document.createElement("div");
toast.className = `toast toast-${type}`;
toast.setAttribute("role", "alert");
// Icon
const icon = document.createElement("div");
icon.className = "toast-icon";
icon.innerHTML = `<i class="fa-solid ${TOAST_ICONS[type] || TOAST_ICONS.info}"></i>`;
toast.appendChild(icon);
// Message
const msg = document.createElement("div");
msg.className = "toast-message";
msg.textContent = message;
toast.appendChild(msg);
// Close button
const closeBtn = document.createElement("button");
closeBtn.className = "toast-close";
closeBtn.innerHTML = '<i class="fa-solid fa-xmark"></i>';
closeBtn.setAttribute("aria-label", "Dismiss");
closeBtn.addEventListener("click", () => dismissToast(toast));
toast.appendChild(closeBtn);
// Progress bar (if auto-dismiss)
if (duration > 0) {
const progress = document.createElement("div");
progress.className = "toast-progress";
progress.style.animationDuration = `${duration}ms`;
toast.appendChild(progress);
// Auto-dismiss after duration
setTimeout(() => dismissToast(toast), duration);
}
// Add to container
container.appendChild(toast);
// Limit max toasts
const toasts = container.querySelectorAll(".toast:not(.toast-exit)");
if (toasts.length > 5) {
dismissToast(toasts[0]);
}
return toast;
}
wget 'https://sme10.lists2.roe3.org/spreadsheet/modules/urlManager.js'import {
DEFAULT_COL_WIDTH,
DEFAULT_COLS,
DEFAULT_ROW_HEIGHT,
DEFAULT_ROWS,
FONT_SIZE_OPTIONS,
KEY_MAP,
KEY_MAP_REVERSE,
MAX_COLS,
MAX_ROWS,
MIN_COL_WIDTH,
MIN_ROW_HEIGHT,
STYLE_KEY_MAP,
URL_LENGTH_CAUTION,
URL_LENGTH_CRITICAL,
URL_LENGTH_MAX_DISPLAY,
URL_LENGTH_WARNING,
} from "./constants.js";
import { CryptoUtils, EncryptionCodec } from "./encryption.js";
import { isValidFormula } from "./formulaManager.js";
import { escapeHTML, isValidCSSColor, sanitizeHTML } from "./security.js";
// Factory functions
export function createEmptyData(r, c) {
return Array(r)
.fill(null)
.map(() => Array(c).fill(""));
}
export function createEmptyCellStyle() {
return { align: "", bg: "", color: "", fontSize: "" };
}
export function createEmptyCellStyles(r, c) {
return Array(r)
.fill(null)
.map(() =>
Array(c)
.fill(null)
.map(() => createEmptyCellStyle())
);
}
export function createDefaultColumnWidths(count) {
return Array(count).fill(DEFAULT_COL_WIDTH);
}
export function createDefaultRowHeights(count) {
return Array(count).fill(DEFAULT_ROW_HEIGHT);
}
// Normalizers
export function normalizeColumnWidths(widths, count) {
const normalized = [];
for (let i = 0; i < count; i++) {
const raw = widths && widths[i];
const value = parseInt(raw, 10);
if (Number.isFinite(value) && value > 0) {
normalized.push(Math.max(MIN_COL_WIDTH, value));
} else {
normalized.push(DEFAULT_COL_WIDTH);
}
}
return normalized;
}
export function normalizeRowHeights(heights, count) {
const normalized = [];
for (let i = 0; i < count; i++) {
const raw = heights && heights[i];
const value = parseInt(raw, 10);
if (Number.isFinite(value) && value > 0) {
normalized.push(Math.max(MIN_ROW_HEIGHT, value));
} else {
normalized.push(DEFAULT_ROW_HEIGHT);
}
}
return normalized;
}
export function normalizeAlignment(value) {
if (value === "left" || value === "center" || value === "right") {
return value;
}
return "";
}
export function normalizeFontSize(value) {
if (value === null || value === undefined) return "";
let raw = String(value).trim();
if (raw === "") return "";
if (raw.endsWith("px")) {
raw = raw.slice(0, -2).trim();
}
const size = parseInt(raw, 10);
if (isNaN(size)) return "";
if (!FONT_SIZE_OPTIONS.includes(size)) return "";
return String(size);
}
export function normalizeCellStyles(styles, r, c) {
const normalized = createEmptyCellStyles(r, c);
if (!Array.isArray(styles)) return normalized;
for (let row = 0; row < r; row++) {
const sourceRow = Array.isArray(styles[row]) ? styles[row] : [];
for (let col = 0; col < c; col++) {
const cellStyle = sourceRow[col];
if (cellStyle && typeof cellStyle === "object") {
normalized[row][col] = {
align: normalizeAlignment(cellStyle.align),
bg: isValidCSSColor(cellStyle.bg) ? cellStyle.bg : "",
color: isValidCSSColor(cellStyle.color) ? cellStyle.color : "",
fontSize: normalizeFontSize(cellStyle.fontSize),
};
}
}
}
return normalized;
}
// Check if all cells in data array are empty
export function isDataEmpty(d) {
return d.every((row) => row.every((cell) => cell === ""));
}
// Check if all cells in formulas array are empty
export function isFormulasEmpty(f) {
return f.every((row) => row.every((cell) => cell === ""));
}
// Check if a cell style is default (all empty)
export function isCellStyleDefault(style) {
return !style || (style.align === "" && style.bg === "" && style.color === "" && style.fontSize === "");
}
// Check if all cell styles are default
export function isCellStylesDefault(styles) {
return styles.every((row) => row.every((cell) => isCellStyleDefault(cell)));
}
// Check if column widths are all default
export function isColWidthsDefault(widths, count) {
if (widths.length !== count) return false;
return widths.every((w) => w === DEFAULT_COL_WIDTH);
}
// Check if row heights are all default
export function isRowHeightsDefault(heights, count) {
if (heights.length !== count) return false;
return heights.every((h) => h === DEFAULT_ROW_HEIGHT);
}
// Safe JSON parse with prototype pollution protection
function safeJSONParse(jsonString) {
const parsed = JSON.parse(jsonString);
function createSafeCopy(obj) {
if (obj === null || typeof obj !== "object") return obj;
if (Array.isArray(obj)) {
return obj.map(createSafeCopy);
}
const safe = Object.create(null);
for (const key of Object.keys(obj)) {
// Block dangerous keys that could pollute prototypes
if (key === "__proto__" || key === "constructor" || key === "prototype") {
console.warn("Blocked prototype pollution attempt via key:", key);
continue;
}
// Block keys containing prototype chain accessor patterns
if (key.includes("__proto__") || key.includes("constructor.prototype")) {
console.warn("Blocked prototype pollution attempt via key pattern:", key);
continue;
}
safe[key] = createSafeCopy(obj[key]);
}
return safe;
}
return createSafeCopy(parsed);
}
// ========== Sparse Array Conversion Helpers ==========
// Detect whether an array is in sparse format [[row,col,val],...] or dense format [[val1,val2],...]
function detectArrayFormat(array) {
if (!array || array.length === 0) return "empty";
const first = array[0];
if (!Array.isArray(first)) return "invalid";
// Sparse format signature: first element is [number, number, anything]
// Dense format signature: first element is array of values (strings/objects)
if (first.length === 3 &&
typeof first[0] === "number" &&
typeof first[1] === "number" &&
Number.isInteger(first[0]) &&
Number.isInteger(first[1])) {
return "sparse";
}
return "dense";
}
// Convert dense 2D array to sparse triplet array [[row, col, value], ...]
function convertToSparse(array2D) {
if (!array2D || !Array.isArray(array2D)) return undefined;
const sparse = [];
for (let r = 0; r < array2D.length; r++) {
const row = array2D[r];
if (!Array.isArray(row)) continue;
for (let c = 0; c < row.length; c++) {
const val = row[c];
// Only include non-empty values
if (val !== "") {
sparse.push([r, c, val]);
}
}
}
return sparse.length > 0 ? sparse : undefined;
}
// Convert sparse triplet array to dense 2D array
function expandFromSparse(sparse, rows, cols, emptyValue = "") {
// Initialize dense array with empty values
const dense = Array(rows).fill(null).map(() => Array(cols).fill(emptyValue));
if (!sparse || sparse.length === 0) return dense;
for (const entry of sparse) {
// Validate triplet format
if (!Array.isArray(entry) || entry.length !== 3) {
console.warn("Invalid sparse triplet format:", entry);
continue;
}
const [r, c, val] = entry;
// Validate indices (type check and bounds check)
if (!Number.isInteger(r) || !Number.isInteger(c)) {
console.warn("Non-integer indices in sparse entry:", r, c);
continue;
}
if (r < 0 || r >= rows || c < 0 || c >= cols) {
console.warn("Out of bounds sparse entry (ignoring):", r, c, "grid size:", rows, "x", cols);
continue;
}
// Set value in dense array
dense[r][c] = val;
}
return dense;
}
// Convert cell styles dense 2D array to sparse format
function convertCellStylesToSparse(styles2D) {
if (!styles2D || !Array.isArray(styles2D)) return undefined;
const sparse = [];
for (let r = 0; r < styles2D.length; r++) {
const row = styles2D[r];
if (!Array.isArray(row)) continue;
for (let c = 0; c < row.length; c++) {
const style = row[c];
// Skip default/empty styles
if (isCellStyleDefault(style)) continue;
// Minify non-default style (remove empty properties)
const minified = {};
for (const [key, val] of Object.entries(style)) {
if (val !== "") {
minified[STYLE_KEY_MAP[key] || key] = val;
}
}
// Only add if there are non-empty properties
if (Object.keys(minified).length > 0) {
sparse.push([r, c, minified]);
}
}
}
return sparse.length > 0 ? sparse : undefined;
}
// Expand cell styles from sparse format to dense 2D array
function expandCellStylesFromSparse(sparse, rows, cols) {
// Initialize with default empty styles
const dense = createEmptyCellStyles(rows, cols);
if (!sparse || sparse.length === 0) return dense;
for (const entry of sparse) {
// Validate triplet format
if (!Array.isArray(entry) || entry.length !== 3) {
console.warn("Invalid sparse style triplet:", entry);
continue;
}
const [r, c, minStyle] = entry;
// Validate indices
if (!Number.isInteger(r) || !Number.isInteger(c)) {
console.warn("Non-integer indices in sparse style entry:", r, c);
continue;
}
if (r < 0 || r >= rows || c < 0 || c >= cols) {
console.warn("Out of bounds sparse style entry:", r, c);
continue;
}
// Expand minified style keys back to full names
if (minStyle && typeof minStyle === "object") {
dense[r][c] = {
align: minStyle.a || minStyle.align || "",
bg: minStyle.b || minStyle.bg || "",
color: minStyle.c || minStyle.color || "",
fontSize: minStyle.z || minStyle.fontSize || "",
};
}
}
return dense;
}
// ========== End Sparse Array Helpers ==========
// Minify state object keys for smaller URL payload
function minifyState(state) {
const minified = {};
for (const [key, value] of Object.entries(state)) {
const shortKey = KEY_MAP[key] || key;
// Convert data and formulas to sparse format
if ((key === "data" || key === "formulas") && Array.isArray(value)) {
const sparse = convertToSparse(value);
if (sparse !== undefined) {
minified[shortKey] = sparse;
}
// If sparse is undefined (no data), don't include the key at all
}
// Convert cellStyles to sparse format
else if (key === "cellStyles" && Array.isArray(value)) {
const sparse = convertCellStylesToSparse(value);
if (sparse !== undefined) {
minified[shortKey] = sparse;
}
// If sparse is undefined (no styles), don't include the key at all
}
// Pass through other keys unchanged
else {
minified[shortKey] = value;
}
}
return minified;
}
// Expose minify helper for consumers that need the hash-ready structure (e.g., JSON modal)
export function minifyStateForExport(state) {
return minifyState(state);
}
// Expand minified state keys back to full names (backward compatible)
function expandState(minified) {
// Handle null, non-objects, and arrays (legacy format) - return as-is
if (!minified || typeof minified !== "object" || Array.isArray(minified)) {
return minified;
}
const expanded = {};
for (const [key, value] of Object.entries(minified)) {
const fullKey = KEY_MAP_REVERSE[key] || key;
// Handle data: detect sparse vs dense format
if ((fullKey === "data" || key === "d") && Array.isArray(value)) {
const format = detectArrayFormat(value);
if (format === "sparse") {
// Mark as sparse for validateAndNormalizeState to expand later
expanded.data = { _format: "sparse", _data: value };
} else {
// Dense format (legacy) - pass through as-is
expanded.data = value;
}
}
// Handle formulas: detect sparse vs dense format
else if ((fullKey === "formulas" || key === "f") && Array.isArray(value)) {
const format = detectArrayFormat(value);
if (format === "sparse") {
expanded.formulas = { _format: "sparse", _data: value };
} else {
// Dense format (legacy) - pass through as-is
expanded.formulas = value;
}
}
// Handle cellStyles: detect sparse vs dense format
else if ((fullKey === "cellStyles" || key === "s") && Array.isArray(value)) {
const format = detectArrayFormat(value);
if (format === "sparse") {
// Mark as sparse for validateAndNormalizeState to expand later
expanded.cellStyles = { _format: "sparse", _data: value };
} else {
// Dense format (legacy) - expand minified style keys
expanded.cellStyles = value.map((row) =>
row.map((cell) => {
if (!cell || typeof cell !== "object") {
return { align: "", bg: "", color: "", fontSize: "" };
}
return {
align: cell.a || cell.align || "",
bg: cell.b || cell.bg || "",
color: cell.c || cell.color || "",
fontSize: cell.z || cell.fontSize || "",
};
})
);
}
}
// Pass through other keys unchanged
else {
expanded[fullKey] = value;
}
}
return expanded;
}
// ========== Serialization Codec (Wrapper for minify/expand + compression) ==========
const SerializationCodec = {
// Serialize a state object to a compressed string
serialize(state) {
const json = JSON.stringify(minifyState(state));
return LZString.compressToEncodedURIComponent(json);
},
// Deserialize a compressed string to a state object
// Returns null on failure
deserialize(compressed) {
try {
const json = LZString.decompressFromEncodedURIComponent(compressed);
if (!json || json.length === 0) return null;
return expandState(safeJSONParse(json));
} catch (e) {
console.error("Deserialization failed:", e);
return null;
}
},
};
// ========== URL Codec (Main Wrapper - combines serialization and encryption) ==========
const URLCodec = {
// Encode state to URL-ready string
async encode(state, options = {}) {
const serialized = SerializationCodec.serialize(state);
// Optionally encrypt
if (options.password) {
try {
return await EncryptionCodec.encrypt(serialized, options.password);
} catch (e) {
console.error("Encryption failed, falling back to unencrypted:", e);
return serialized;
}
}
return serialized;
},
// Decode URL hash - returns { state } or { encrypted: true, data }
decode(hash) {
// Security check
if (!hash || hash.length > 100000) {
if (hash && hash.length > 100000) {
console.warn("Hash too long, rejecting");
}
return null;
}
// Check for encrypted data
if (EncryptionCodec.isEncrypted(hash)) {
return {
encrypted: true,
data: EncryptionCodec.unwrap(hash),
};
}
// Try to deserialize
const state = SerializationCodec.deserialize(hash);
if (state) {
return { state };
}
// Try legacy format
return this._decodeLegacy(hash);
},
// Decrypt and decode an encrypted payload
async decryptAndDecode(encryptedData, password) {
const decrypted = await CryptoUtils.decrypt(encryptedData, password);
const state = SerializationCodec.deserialize(decrypted);
if (!state) {
throw new Error("Invalid decrypted data");
}
return state;
},
// Handle legacy uncompressed format
_decodeLegacy(hash) {
try {
// Only attempt legacy decode if it looks like valid URL-encoded JSON
if (hash.startsWith("%7B") || hash.startsWith("%5B") || hash.startsWith("{") || hash.startsWith("[")) {
const decoded = decodeURIComponent(hash);
return { state: expandState(safeJSONParse(decoded)) };
}
} catch (e) {
console.warn("Legacy decode failed:", e);
}
return null;
},
};
// Validate and normalize a parsed state object
export function validateAndNormalizeState(parsed) {
try {
// Handle new format (object with rows, cols, data)
if (parsed && typeof parsed === "object" && !Array.isArray(parsed)) {
const r = Math.min(Math.max(1, parsed.rows || DEFAULT_ROWS), MAX_ROWS);
const c = Math.min(Math.max(1, parsed.cols || DEFAULT_COLS), MAX_COLS);
// Validate and normalize data array
let d = parsed.data;
// Check if data is marked as sparse format by expandState
if (d && typeof d === "object" && d._format === "sparse") {
// Sparse format: expand to dense
d = expandFromSparse(d._data, r, c, "");
// Sanitize all cells
d = d.map((row) => row.map((cell) => sanitizeHTML(String(cell))));
}
// Handle direct sparse array (backward compatibility - no metadata wrapper)
else if (Array.isArray(d)) {
const format = detectArrayFormat(d);
if (format === "sparse") {
// Direct sparse array without metadata wrapper
d = expandFromSparse(d, r, c, "");
d = d.map((row) => row.map((cell) => sanitizeHTML(String(cell))));
} else {
// Dense format (legacy): existing logic
// Ensure correct dimensions
d = d.slice(0, r).map((row) => {
if (Array.isArray(row)) {
return row.slice(0, c).map((cell) => sanitizeHTML(String(cell || "")));
}
return Array(c).fill("");
});
// Pad rows if needed
while (d.length < r) {
d.push(Array(c).fill(""));
}
// Pad columns if needed
d = d.map((row) => {
while (row.length < c) row.push("");
return row;
});
}
} else {
d = createEmptyData(r, c);
}
// Load formulas (backward compatible - create empty if not present)
// Security: Validate formulas against whitelist to prevent injection
let f = parsed.formulas;
// Check if formulas is marked as sparse format by expandState
if (f && typeof f === "object" && f._format === "sparse") {
// Sparse format: expand to dense
f = expandFromSparse(f._data, r, c, "");
// Validate all formulas
f = f.map((row, rowIdx) => row.map((cell, colIdx) => {
const formula = String(cell);
if (formula.startsWith("=")) {
if (isValidFormula(formula)) {
return formula;
} else {
// Invalid formula - convert to escaped text in data
d[rowIdx][colIdx] = escapeHTML(formula);
return "";
}
}
return formula;
}));
}
// Handle direct sparse array (backward compatibility)
else if (Array.isArray(f)) {
const format = detectArrayFormat(f);
if (format === "sparse") {
// Direct sparse array without metadata wrapper
f = expandFromSparse(f, r, c, "");
// Validate all formulas
f = f.map((row, rowIdx) => row.map((cell, colIdx) => {
const formula = String(cell);
if (formula.startsWith("=")) {
if (isValidFormula(formula)) {
return formula;
} else {
// Invalid formula - convert to escaped text in data
d[rowIdx][colIdx] = escapeHTML(formula);
return "";
}
}
return formula;
}));
} else {
// Dense format (legacy): existing logic
f = f.slice(0, r).map((row, rowIdx) => {
if (Array.isArray(row)) {
return row.slice(0, c).map((cell, colIdx) => {
const formula = String(cell || "");
if (formula.startsWith("=")) {
// Validate formula against whitelist
if (isValidFormula(formula)) {
return formula;
} else {
// Invalid formula - convert to escaped text in data
d[rowIdx][colIdx] = escapeHTML(formula);
return "";
}
}
return formula;
});
}
return Array(c).fill("");
});
while (f.length < r) {
f.push(Array(c).fill(""));
}
f = f.map((row) => {
while (row.length < c) row.push("");
return row;
});
}
} else {
f = createEmptyData(r, c);
}
// Handle cellStyles: detect sparse vs dense format
let s;
const parsedStyles = parsed.cellStyles;
if (parsedStyles && typeof parsedStyles === "object" && parsedStyles._format === "sparse") {
// Sparse format: expand to dense first, then normalize/validate
const expanded = expandCellStylesFromSparse(parsedStyles._data, r, c);
s = normalizeCellStyles(expanded, r, c);
} else if (Array.isArray(parsedStyles)) {
const format = detectArrayFormat(parsedStyles);
if (format === "sparse") {
// Direct sparse array without metadata wrapper
const expanded = expandCellStylesFromSparse(parsedStyles, r, c);
s = normalizeCellStyles(expanded, r, c);
} else {
// Dense format (legacy): use normalizeCellStyles directly
s = normalizeCellStyles(parsedStyles, r, c);
}
} else {
// No styles or invalid format
s = normalizeCellStyles(parsedStyles, r, c);
}
const w = normalizeColumnWidths(parsed.colWidths, c);
const h = normalizeRowHeights(parsed.rowHeights, r);
return {
rows: r,
cols: c,
data: d,
formulas: f,
cellStyles: s,
colWidths: w,
rowHeights: h,
theme: parsed.theme || null,
readOnly: Boolean(parsed.readOnly),
embed: Boolean(parsed.embed),
};
}
// Handle legacy format (just array, assume 10x10)
if (Array.isArray(parsed)) {
const d = parsed.slice(0, DEFAULT_ROWS).map((row) => {
if (Array.isArray(row)) {
return row.slice(0, DEFAULT_COLS).map((cell) => sanitizeHTML(String(cell || "")));
}
return Array(DEFAULT_COLS).fill("");
});
while (d.length < DEFAULT_ROWS) {
d.push(Array(DEFAULT_COLS).fill(""));
}
const f = createEmptyData(DEFAULT_ROWS, DEFAULT_COLS);
const s = createEmptyCellStyles(DEFAULT_ROWS, DEFAULT_COLS);
const w = createDefaultColumnWidths(DEFAULT_COLS);
const h = createDefaultRowHeights(DEFAULT_ROWS);
return {
rows: DEFAULT_ROWS,
cols: DEFAULT_COLS,
data: d,
formulas: f,
cellStyles: s,
colWidths: w,
rowHeights: h,
theme: null,
readOnly: false,
};
}
} catch (e) {
console.warn("Failed to validate state:", e);
}
return null;
}
// Update URL length indicator in status bar
function updateURLLengthIndicator(length) {
const valueEl = document.getElementById("url-length-value");
const barEl = document.getElementById("url-progress-bar");
const msgEl = document.getElementById("url-length-message");
if (!valueEl || !barEl) return;
valueEl.textContent = length.toLocaleString();
// Calculate progress percentage (capped at 100%)
const percent = Math.min((length / URL_LENGTH_MAX_DISPLAY) * 100, 100);
barEl.style.width = percent + "%";
// Update color and message based on thresholds
barEl.classList.remove("warning", "caution", "critical");
if (msgEl) {
msgEl.classList.remove("warning", "caution", "critical");
msgEl.textContent = "";
}
if (length >= URL_LENGTH_CRITICAL) {
barEl.classList.add("critical");
if (msgEl) {
msgEl.classList.add("critical");
msgEl.textContent = "Some browsers may fail";
}
} else if (length >= URL_LENGTH_CAUTION) {
barEl.classList.add("caution");
if (msgEl) {
msgEl.classList.add("caution");
msgEl.textContent = "URL shorteners may fail";
}
} else if (length >= URL_LENGTH_WARNING) {
barEl.classList.add("warning");
if (msgEl) {
msgEl.classList.add("warning");
msgEl.textContent = "Some older browsers may truncate";
}
}
}
export const URLManager = {
// Decode URL hash to state object
// Returns { encrypted: true, data: base64String } if data is encrypted
// Returns { rows, ... } if success
// Returns null if failure
decodeState(hash) {
try {
const result = URLCodec.decode(hash);
if (!result) return null;
// If encrypted, return the encrypted marker
if (result.encrypted) {
return result;
}
// Validate and normalize the decoded state
return validateAndNormalizeState(result.state);
} catch (e) {
console.warn("Failed to decode state from URL:", e);
}
return null;
},
async encodeState(state, password) {
return await URLCodec.encode(state, { password });
},
async decryptAndDecode(encryptedData, password) {
return await URLCodec.decryptAndDecode(encryptedData, password);
},
// Update URL hash without page jump
async updateURL(state, password) {
const encoded = await this.encodeState(state, password);
const newHash = "#" + encoded;
// Update URL length indicator
updateURLLengthIndicator(encoded.length);
if (history.replaceState) {
history.replaceState(null, null, newHash);
} else {
location.hash = newHash;
}
},
updateURLLengthIndicator,
};
wget 'https://sme10.lists2.roe3.org/spreadsheet/modules/visualFunctions.js'import { isValidCSSColor } from "./security.js";
import { parseCellRef } from "./formulaManager.js";
const COLOR_ALIASES = {
green: "#22c55e",
red: "#ef4444",
blue: "#3b82f6",
orange: "#f97316",
yellow: "#facc15",
gray: "#9ca3af",
black: "#111111",
white: "#ffffff",
};
function splitArgs(raw) {
if (!raw) return [];
if (!raw.trim()) return [];
const args = [];
let current = "";
let quote = null;
for (let i = 0; i < raw.length; i++) {
const ch = raw[i];
if (quote) {
if (ch === "\\" && i + 1 < raw.length) {
current += raw[i + 1];
i++;
continue;
}
if (ch === quote) {
quote = null;
continue;
}
current += ch;
continue;
}
if (ch === "'" || ch === '"') {
quote = ch;
continue;
}
if (ch === ",") {
args.push(current.trim());
current = "";
continue;
}
current += ch;
}
if (current.length || raw.trim().length) {
args.push(current.trim());
}
return args;
}
function parseFormula(formula) {
if (!formula || typeof formula !== "string") return null;
const trimmed = formula.trim();
if (!trimmed.startsWith("=")) return null;
const body = trimmed.slice(1).trim();
const openIndex = body.indexOf("(");
const closeIndex = body.lastIndexOf(")");
if (openIndex <= 0 || closeIndex <= openIndex) return null;
const name = body.slice(0, openIndex).trim().toUpperCase();
const trailing = body.slice(closeIndex + 1).trim();
if (trailing) return null;
const argsRaw = body.slice(openIndex + 1, closeIndex);
return { name, args: splitArgs(argsRaw) };
}
function extractPlainText(value) {
if (value === null || value === undefined) return "";
const parser = new DOMParser();
const doc = parser.parseFromString("<body>" + String(value) + "</body>", "text/html");
const text = doc.body.textContent || "";
return text.replace(/\u00a0/g, " ");
}
function isCellRef(value) {
return /^[A-Z]+[1-9]\d*$/i.test(value);
}
function resolveNumberArg(arg, context) {
if (arg === null || arg === undefined) return null;
const trimmed = String(arg).trim();
if (!trimmed) return null;
if (isCellRef(trimmed)) {
const ref = parseCellRef(trimmed);
if (ref && context && typeof context.getCellValue === "function") {
const value = context.getCellValue(ref.row, ref.col);
return Number.isFinite(value) ? value : 0;
}
}
const percentMatch = trimmed.match(/^(-?\d+(?:\.\d+)?)%$/);
const numericText = percentMatch ? percentMatch[1] : trimmed.replace(/,/g, "");
const num = parseFloat(numericText);
return Number.isNaN(num) ? null : num;
}
function resolveTextArg(arg, context) {
if (arg === null || arg === undefined) return "";
const trimmed = String(arg).trim();
if (!trimmed) return "";
if (isCellRef(trimmed)) {
const ref = parseCellRef(trimmed);
if (ref && context && Array.isArray(context.data)) {
const value = context.data[ref.row] && context.data[ref.row][ref.col];
return extractPlainText(value);
}
}
return trimmed;
}
function resolveColor(value) {
if (!value) return "";
const trimmed = String(value).trim();
if (!trimmed) return "";
const lower = trimmed.toLowerCase();
if (COLOR_ALIASES[lower]) return COLOR_ALIASES[lower];
if (isValidCSSColor(trimmed)) return trimmed;
return "";
}
function clamp(value, min, max) {
return Math.min(Math.max(value, min), max);
}
function getContrastColor(color) {
if (!color) return "#ffffff";
let r = 0;
let g = 0;
let b = 0;
const hexMatch = color.match(/^#([0-9a-f]{3}|[0-9a-f]{6}|[0-9a-f]{8})$/i);
const rgbMatch = color.match(/^rgba?\((\d{1,3})\s*,\s*(\d{1,3})\s*,\s*(\d{1,3})/i);
if (hexMatch) {
const hex = hexMatch[1];
if (hex.length === 3) {
r = parseInt(hex[0] + hex[0], 16);
g = parseInt(hex[1] + hex[1], 16);
b = parseInt(hex[2] + hex[2], 16);
} else {
r = parseInt(hex.slice(0, 2), 16);
g = parseInt(hex.slice(2, 4), 16);
b = parseInt(hex.slice(4, 6), 16);
}
} else if (rgbMatch) {
r = parseInt(rgbMatch[1], 10);
g = parseInt(rgbMatch[2], 10);
b = parseInt(rgbMatch[3], 10);
} else {
return "#ffffff";
}
const luminance = (0.2126 * r + 0.7152 * g + 0.0722 * b) / 255;
return luminance > 0.6 ? "#111111" : "#ffffff";
}
function buildProgress(percent, label, color) {
const normalized = clamp(Number.isFinite(percent) ? percent : 0, 0, 100);
const wrapper = document.createElement("div");
wrapper.className = "visual-progress";
const track = document.createElement("div");
track.className = "visual-progress-track";
track.setAttribute("role", "progressbar");
track.setAttribute("aria-valuemin", "0");
track.setAttribute("aria-valuemax", "100");
track.setAttribute("aria-valuenow", String(Math.round(normalized)));
const fill = document.createElement("div");
fill.className = "visual-progress-fill";
fill.style.width = `${normalized}%`;
if (color) {
fill.style.backgroundColor = color;
fill.style.color = getContrastColor(color);
}
if (label) {
fill.textContent = label;
} else {
fill.textContent = `${Math.round(normalized)}%`;
}
track.appendChild(fill);
wrapper.appendChild(track);
if (label) {
const labelEl = document.createElement("div");
labelEl.className = "visual-progress-label";
labelEl.textContent = `${Math.round(normalized)}%`;
wrapper.appendChild(labelEl);
}
return wrapper;
}
function buildTag(label, color) {
if (!label) return null;
const tag = document.createElement("span");
tag.className = "visual-tag";
tag.textContent = label;
if (color) {
tag.style.backgroundColor = color;
tag.style.borderColor = color;
tag.style.color = getContrastColor(color);
}
return tag;
}
function buildRating(value, max) {
if (!Number.isFinite(value)) return null;
const total = clamp(Math.round(Number.isFinite(max) ? max : 5), 1, 10);
const score = clamp(Math.round(value), 0, total);
const rating = document.createElement("div");
rating.className = "visual-rating";
rating.setAttribute("role", "img");
rating.setAttribute("aria-label", `${score} of ${total}`);
for (let i = 0; i < total; i++) {
const star = document.createElement("i");
star.className = i < score ? "fa-solid fa-star" : "fa-regular fa-star";
rating.appendChild(star);
}
return rating;
}
export const VisualFunctions = {
process(formula, context = {}) {
const parsed = parseFormula(formula);
if (!parsed) return null;
const { name, args } = parsed;
if (name === "PROGRESS") {
const value = resolveNumberArg(args[0], context);
if (value === null) return null;
const secondNumeric = resolveNumberArg(args[1], context);
let percent = value;
let label = "";
let color = "";
if (secondNumeric !== null) {
percent = secondNumeric === 0 ? 0 : (value / secondNumeric) * 100;
label = resolveTextArg(args[2], context);
color = resolveColor(args[3]);
} else {
if (args.length === 1 && value >= 0 && value <= 1) {
percent = value * 100;
}
const maybeColor = resolveColor(args[1]);
if (maybeColor && args.length === 2) {
color = maybeColor;
} else {
label = resolveTextArg(args[1], context);
color = resolveColor(args[2]);
}
}
return buildProgress(percent, label, color);
}
if (name === "TAG") {
const label = resolveTextArg(args[0], context);
if (!label) return null;
const color = resolveColor(args[1]);
return buildTag(label, color);
}
if (name === "RATING") {
const value = resolveNumberArg(args[0], context);
if (value === null) return null;
const max = resolveNumberArg(args[1], context);
return buildRating(value, max);
}
return null;
},
};