excel-tools

"use strict";
Object.defineProperty(exports, "__esModule", { value: true });
exports.getFormattingTools = getFormattingTools;
const sdk_1 = require("@lmstudio/sdk");
const zod_1 = require("zod");
const workbook_1 = require("../utils/workbook");
const config_1 = require("../utils/config");
/** Parse "A1" or "A1:C10" into individual cell addresses */
function* rangeAddresses(ws, ref) {
    const match = ref.match(/^([A-Z]+)(\d+)(?::([A-Z]+)(\d+))?$/i);
    if (!match)
        return;
    const colLetter = (s) => s.toUpperCase().split("").reduce((a, c) => a * 26 + c.charCodeAt(0) - 64, 0);
    const c1 = colLetter(match[1]);
    const r1 = parseInt(match[2]);
    const c2 = match[3] ? colLetter(match[3]) : c1;
    const r2 = match[4] ? parseInt(match[4]) : r1;
    for (let r = r1; r <= r2; r++)
        for (let c = c1; c <= c2; c++)
            yield ws.getCell(r, c);
}
function getFormattingTools() {
    return [
        // ── 20. excel_format_cells ───────────────────────────────────────────────
        (0, sdk_1.tool)({
            name: "excel_format_cells",
            description: "Apply rich formatting to a cell range: font (bold/italic/size/color), background fill, borders, alignment.",
            parameters: {
                filePath: zod_1.z.string(),
                range: zod_1.z.string().describe("Cell or range, e.g. 'A1' or 'A1:D10'"),
                sheet: zod_1.z.string().optional(),
                font: zod_1.z.object({
                    bold: zod_1.z.boolean().optional(),
                    italic: zod_1.z.boolean().optional(),
                    size: zod_1.z.number().optional(),
                    color: zod_1.z.string().optional().describe("ARGB hex, e.g. 'FFFF0000' for red"),
                    underline: zod_1.z.boolean().optional(),
                }).optional(),
                fill: zod_1.z.object({
                    color: zod_1.z.string().describe("ARGB hex background color, e.g. 'FFFFFF00' for yellow"),
                }).optional(),
                border: zod_1.z.enum(["thin", "medium", "thick", "none"]).optional().describe("Apply border style on all 4 sides"),
                alignment: zod_1.z.object({
                    horizontal: zod_1.z.enum(["left", "center", "right", "fill", "justify"]).optional(),
                    vertical: zod_1.z.enum(["top", "middle", "bottom"]).optional(),
                    wrapText: zod_1.z.boolean().optional(),
                }).optional(),
                numberFormat: zod_1.z.string().optional().describe("Excel number format string, e.g. '#,##0.00' or '0%'"),
            },
            implementation: async ({ filePath, range, sheet, font, fill, border, alignment, numberFormat }) => {
                const err = (0, config_1.checkReadable)(filePath);
                if (err)
                    return `Error: ${err}`;
                try {
                    const wb = await (0, workbook_1.loadWorkbook)(filePath);
                    const ws = (0, workbook_1.getSheet)(wb, sheet);
                    if (typeof ws === "string")
                        return `Error: ${ws}`;
                    let count = 0;
                    for (const cell of rangeAddresses(ws, range)) {
                        if (font)
                            cell.font = { ...cell.font, ...font, color: font.color ? { argb: font.color } : cell.font?.color };
                        if (fill)
                            cell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: fill.color } };
                        if (border && border !== "none") {
                            const s = { style: border };
                            cell.border = { top: s, bottom: s, left: s, right: s };
                        }
                        if (border === "none")
                            cell.border = {};
                        if (alignment)
                            cell.alignment = { ...cell.alignment, ...alignment };
                        if (numberFormat)
                            cell.numFmt = numberFormat;
                        count++;
                    }
                    await (0, workbook_1.saveWorkbook)(wb, filePath);
                    return JSON.stringify({ success: true, file: filePath, sheet: typeof ws === "string" ? ws : ws.name,
                        range, cells_formatted: count });
                }
                catch (e) {
                    return `Error: ${e}`;
                }
            },
        }),
        // ── 21. excel_conditional_format ─────────────────────────────────────────
        (0, sdk_1.tool)({
            name: "excel_conditional_format",
            description: "Add conditional formatting rules to a range. Supports cellIs (>, <, =, between) and colorScale (gradient).",
            parameters: {
                filePath: zod_1.z.string(),
                range: zod_1.z.string().describe("Cell range, e.g. 'B2:B100'"),
                type: zod_1.z.enum(["greaterThan", "lessThan", "equal", "between", "top10", "colorScale"]),
                value: zod_1.z.number().optional().describe("Threshold value (for greaterThan/lessThan/equal)"),
                value2: zod_1.z.number().optional().describe("Second value (for between)"),
                fillColor: zod_1.z.string().optional().describe("ARGB fill color for matched cells, e.g. 'FFC6EFCE'"),
                fontColor: zod_1.z.string().optional().describe("ARGB font color for matched cells"),
                sheet: zod_1.z.string().optional(),
            },
            implementation: async ({ filePath, range, type, value, value2, fillColor, fontColor, sheet }) => {
                const err = (0, config_1.checkReadable)(filePath);
                if (err)
                    return `Error: ${err}`;
                try {
                    const wb = await (0, workbook_1.loadWorkbook)(filePath);
                    const ws = (0, workbook_1.getSheet)(wb, sheet);
                    if (typeof ws === "string")
                        return `Error: ${ws}`;
                    const style = {};
                    if (fillColor)
                        style.fill = { type: "pattern", pattern: "solid", bgColor: { argb: fillColor } };
                    if (fontColor)
                        style.font = { color: { argb: fontColor } };
                    if (type === "colorScale") {
                        ws.addConditionalFormatting({ ref: range, rules: [{
                                    type: "colorScale", priority: 1,
                                    cfvo: [{ type: "min" }, { type: "max" }],
                                    color: [{ argb: "FFF8696B" }, { argb: "FF63BE7B" }],
                                }] });
                    }
                    else {
                        const opMap = {
                            greaterThan: "greaterThan", lessThan: "lessThan",
                            equal: "equal", between: "between", top10: "greaterThan",
                        };
                        ws.addConditionalFormatting({ ref: range, rules: [{
                                    type: "cellIs", priority: 1,
                                    operator: opMap[type],
                                    formulae: type === "between" ? [value ?? 0, value2 ?? 0] : [value ?? 0],
                                    style,
                                }] });
                    }
                    await (0, workbook_1.saveWorkbook)(wb, filePath);
                    return JSON.stringify({ success: true, file: filePath, range, type, rule_added: true });
                }
                catch (e) {
                    return `Error: ${e}`;
                }
            },
        }),
        // ── 22. excel_freeze_panes ───────────────────────────────────────────────
        (0, sdk_1.tool)({
            name: "excel_freeze_panes",
            description: "Freeze rows and/or columns so they stay visible while scrolling.",
            parameters: {
                filePath: zod_1.z.string(),
                rows: zod_1.z.number().optional().describe("Number of rows to freeze from the top (e.g. 1 to freeze header)"),
                columns: zod_1.z.number().optional().describe("Number of columns to freeze from the left"),
                sheet: zod_1.z.string().optional(),
            },
            implementation: async ({ filePath, rows, columns, sheet }) => {
                const err = (0, config_1.checkReadable)(filePath);
                if (err)
                    return `Error: ${err}`;
                try {
                    const wb = await (0, workbook_1.loadWorkbook)(filePath);
                    const ws = (0, workbook_1.getSheet)(wb, sheet);
                    if (typeof ws === "string")
                        return `Error: ${ws}`;
                    const r = rows ?? 0;
                    const c = columns ?? 0;
                    ws.views = [{
                            state: "frozen", xSplit: c, ySplit: r,
                            topLeftCell: `${c > 0 ? String.fromCharCode(65 + c) : "A"}${r + 1}`,
                            activeCell: "A1",
                        }];
                    await (0, workbook_1.saveWorkbook)(wb, filePath);
                    return JSON.stringify({ success: true, file: filePath, sheet: ws.name,
                        frozen_rows: r, frozen_columns: c });
                }
                catch (e) {
                    return `Error: ${e}`;
                }
            },
        }),
        // ── 23. excel_set_dimensions ─────────────────────────────────────────────
        (0, sdk_1.tool)({
            name: "excel_set_dimensions",
            description: "Set precise column widths and row heights.",
            parameters: {
                filePath: zod_1.z.string(),
                columnWidths: zod_1.z.array(zod_1.z.object({ column: zod_1.z.string().describe("Column letter, e.g. 'A'"), width: zod_1.z.number() })).optional(),
                rowHeights: zod_1.z.array(zod_1.z.object({ row: zod_1.z.number().describe("1-based row number"), height: zod_1.z.number() })).optional(),
                sheet: zod_1.z.string().optional(),
            },
            implementation: async ({ filePath, columnWidths, rowHeights, sheet }) => {
                const err = (0, config_1.checkReadable)(filePath);
                if (err)
                    return `Error: ${err}`;
                try {
                    const wb = await (0, workbook_1.loadWorkbook)(filePath);
                    const ws = (0, workbook_1.getSheet)(wb, sheet);
                    if (typeof ws === "string")
                        return `Error: ${ws}`;
                    columnWidths?.forEach(({ column, width }) => { ws.getColumn(column).width = width; });
                    rowHeights?.forEach(({ row, height }) => { ws.getRow(row).height = height; });
                    await (0, workbook_1.saveWorkbook)(wb, filePath);
                    return JSON.stringify({ success: true, file: filePath, sheet: ws.name,
                        columns_resized: columnWidths?.length ?? 0, rows_resized: rowHeights?.length ?? 0 });
                }
                catch (e) {
                    return `Error: ${e}`;
                }
            },
        }),
        // ── 24. excel_add_data_validation ────────────────────────────────────────
        (0, sdk_1.tool)({
            name: "excel_add_data_validation",
            description: "Add data validation to a cell range: dropdown lists, numeric ranges, date constraints, or custom formulas.",
            parameters: {
                filePath: zod_1.z.string(),
                range: zod_1.z.string().describe("Cell or range, e.g. 'C2:C100'"),
                type: zod_1.z.enum(["list", "whole", "decimal", "date", "textLength", "custom"]),
                options: zod_1.z.object({
                    list: zod_1.z.array(zod_1.z.string()).optional().describe("Dropdown options (for type=list)"),
                    formula: zod_1.z.string().optional().describe("Excel formula for custom validation"),
                    operator: zod_1.z.enum(["between", "notBetween", "equal", "notEqual", "greaterThan", "lessThan", "greaterThanOrEqual", "lessThanOrEqual"]).optional(),
                    value1: zod_1.z.union([zod_1.z.string(), zod_1.z.number()]).optional(),
                    value2: zod_1.z.union([zod_1.z.string(), zod_1.z.number()]).optional(),
                    prompt: zod_1.z.string().optional().describe("Input message shown when cell is selected"),
                    error: zod_1.z.string().optional().describe("Error message shown on invalid input"),
                }).optional(),
                sheet: zod_1.z.string().optional(),
            },
            implementation: async ({ filePath, range, type, options, sheet }) => {
                const err = (0, config_1.checkReadable)(filePath);
                if (err)
                    return `Error: ${err}`;
                try {
                    const wb = await (0, workbook_1.loadWorkbook)(filePath);
                    const ws = (0, workbook_1.getSheet)(wb, sheet);
                    if (typeof ws === "string")
                        return `Error: ${ws}`;
                    const validation = { type: type, allowBlank: true,
                        formulae: [],
                        showInputMessage: !!options?.prompt, showErrorMessage: !!options?.error };
                    if (type === "list" && options?.list) {
                        validation.formulae = [`"${options.list.join(",")}"`];
                    }
                    else if (type === "custom" && options?.formula) {
                        validation.formulae = [options.formula];
                    }
                    else if (options?.value1 !== undefined) {
                        validation.operator = options.operator ?? "between";
                        validation.formulae = options.value2 !== undefined
                            ? [options.value1, options.value2]
                            : [options.value1];
                    }
                    if (options?.prompt) {
                        validation.promptTitle = "Input";
                        validation.prompt = options.prompt;
                    }
                    if (options?.error) {
                        validation.errorTitle = "Invalid";
                        validation.error = options.error;
                    }
                    for (const cell of rangeAddresses(ws, range))
                        cell.dataValidation = validation;
                    await (0, workbook_1.saveWorkbook)(wb, filePath);
                    return JSON.stringify({ success: true, file: filePath, range, validation_type: type });
                }
                catch (e) {
                    return `Error: ${e}`;
                }
            },
        }),
        // ── 25. excel_add_comment ────────────────────────────────────────────────
        (0, sdk_1.tool)({
            name: "excel_add_comment",
            description: "Add or replace a comment/note on a specific cell.",
            parameters: {
                filePath: zod_1.z.string(),
                cell: zod_1.z.string().describe("Cell address, e.g. 'B5'"),
                comment: zod_1.z.string(),
                author: zod_1.z.string().optional().describe("Author name shown in the comment (default: Excel Tools)"),
                sheet: zod_1.z.string().optional(),
            },
            implementation: async ({ filePath, cell, comment, author, sheet }) => {
                const err = (0, config_1.checkReadable)(filePath);
                if (err)
                    return `Error: ${err}`;
                try {
                    const wb = await (0, workbook_1.loadWorkbook)(filePath);
                    const ws = (0, workbook_1.getSheet)(wb, sheet);
                    if (typeof ws === "string")
                        return `Error: ${ws}`;
                    ws.getCell(cell).note = {
                        texts: [{ font: { bold: false, size: 10 }, text: comment }],
                        editAs: "oneCells",
                    };
                    await (0, workbook_1.saveWorkbook)(wb, filePath);
                    return JSON.stringify({ success: true, file: filePath, cell, comment, author: author ?? "Excel Tools" });
                }
                catch (e) {
                    return `Error: ${e}`;
                }
            },
        }),
        // ── 26. excel_add_hyperlink ──────────────────────────────────────────────
        (0, sdk_1.tool)({
            name: "excel_add_hyperlink",
            description: "Add a clickable hyperlink to a cell. Supports URLs and internal sheet references.",
            parameters: {
                filePath: zod_1.z.string(),
                cell: zod_1.z.string().describe("Cell address, e.g. 'A1'"),
                url: zod_1.z.string().describe("URL (https://...) or internal ref (e.g. 'Sheet2!A1')"),
                displayText: zod_1.z.string().optional().describe("Text shown in cell (default: the URL)"),
                tooltip: zod_1.z.string().optional(),
                sheet: zod_1.z.string().optional(),
            },
            implementation: async ({ filePath, cell, url, displayText, tooltip, sheet }) => {
                const err = (0, config_1.checkReadable)(filePath);
                if (err)
                    return `Error: ${err}`;
                try {
                    const wb = await (0, workbook_1.loadWorkbook)(filePath);
                    const ws = (0, workbook_1.getSheet)(wb, sheet);
                    if (typeof ws === "string")
                        return `Error: ${ws}`;
                    const c = ws.getCell(cell);
                    c.value = { text: displayText ?? url, hyperlink: url, tooltip };
                    c.font = { color: { argb: "FF0563C1" }, underline: true };
                    await (0, workbook_1.saveWorkbook)(wb, filePath);
                    return JSON.stringify({ success: true, file: filePath, cell, url, display: displayText ?? url });
                }
                catch (e) {
                    return `Error: ${e}`;
                }
            },
        }),
    ];
}
//# sourceMappingURL=formatting.js.map