excel-tools
Public
Project Files
dist / tools / data.js
"use strict";
var __createBinding = (this && this.__createBinding) || (Object.create ? (function(o, m, k, k2) {
if (k2 === undefined) k2 = k;
var desc = Object.getOwnPropertyDescriptor(m, k);
if (!desc || ("get" in desc ? !m.__esModule : desc.writable || desc.configurable)) {
desc = { enumerable: true, get: function() { return m[k]; } };
}
Object.defineProperty(o, k2, desc);
}) : (function(o, m, k, k2) {
if (k2 === undefined) k2 = k;
o[k2] = m[k];
}));
var __setModuleDefault = (this && this.__setModuleDefault) || (Object.create ? (function(o, v) {
Object.defineProperty(o, "default", { enumerable: true, value: v });
}) : function(o, v) {
o["default"] = v;
});
var __importStar = (this && this.__importStar) || (function () {
var ownKeys = function(o) {
ownKeys = Object.getOwnPropertyNames || function (o) {
var ar = [];
for (var k in o) if (Object.prototype.hasOwnProperty.call(o, k)) ar[ar.length] = k;
return ar;
};
return ownKeys(o);
};
return function (mod) {
if (mod && mod.__esModule) return mod;
var result = {};
if (mod != null) for (var k = ownKeys(mod), i = 0; i < k.length; i++) if (k[i] !== "default") __createBinding(result, mod, k[i]);
__setModuleDefault(result, mod);
return result;
};
})();
var __importDefault = (this && this.__importDefault) || function (mod) {
return (mod && mod.__esModule) ? mod : { "default": mod };
};
Object.defineProperty(exports, "__esModule", { value: true });
exports.getDataTools = getDataTools;
const sdk_1 = require("@lmstudio/sdk");
const zod_1 = require("zod");
const fs = __importStar(require("fs"));
const path = __importStar(require("path"));
const exceljs_1 = __importDefault(require("exceljs"));
const workbook_1 = require("../utils/workbook");
const config_1 = require("../utils/config");
const filterSchema = zod_1.z.array(zod_1.z.object({
column: zod_1.z.string(),
operator: zod_1.z.enum(["=", "!=", ">", "<", ">=", "<=", "contains", "startsWith", "endsWith", "regex", "isNull", "isNotNull"]),
value: zod_1.z.union([zod_1.z.string(), zod_1.z.number()]).optional(),
})).describe("Filter conditions (AND logic)");
function getDataTools() {
return [
// ── 1. excel_read ────────────────────────────────────────────────────────
(0, sdk_1.tool)({
name: "excel_read",
description: "Read rows from any Excel or CSV file (.xlsx .xlsm .xls .ods .csv .tsv). Supports row range and pagination.",
parameters: {
filePath: zod_1.z.string(),
sheet: zod_1.z.string().optional().describe("Sheet name (default: first sheet)"),
startRow: zod_1.z.number().optional().describe("First data row index, 0-based (default: 0)"),
endRow: zod_1.z.number().optional().describe("Last row index exclusive (default: all)"),
maxRows: zod_1.z.number().optional().describe("Max rows to return (default: 200)"),
},
implementation: async ({ filePath, sheet, startRow, endRow, maxRows }) => {
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 all = (0, workbook_1.worksheetToJson)(ws);
const s = startRow ?? 0;
const e = endRow ?? all.length;
const rows = all.slice(s, e).slice(0, maxRows ?? 200);
return JSON.stringify({ file: filePath, sheet: ws.name, total_rows: all.length,
returned_rows: rows.length, range: `${s}–${Math.min(e, all.length) - 1}`,
columns: rows[0] ? Object.keys(rows[0]) : [], data: rows }, null, 2);
}
catch (e) {
return `Error: ${e}`;
}
},
}),
// ── 2. excel_write ───────────────────────────────────────────────────────
(0, sdk_1.tool)({
name: "excel_write",
description: "Create a new .xlsx file from row objects. Applies bold headers, auto-width columns. String values starting with '=' are Excel formulas.",
parameters: {
filePath: zod_1.z.string(),
data: zod_1.z.array(zod_1.z.record(zod_1.z.union([zod_1.z.string(), zod_1.z.number(), zod_1.z.null()]))),
sheetName: zod_1.z.string().optional().describe("Sheet name (default: Sheet1)"),
boldHeaders: zod_1.z.boolean().optional(),
autoWidth: zod_1.z.boolean().optional(),
},
implementation: async ({ filePath, data, sheetName, boldHeaders, autoWidth }) => {
try {
(0, config_1.ensureDir)(filePath);
const wb = new exceljs_1.default.Workbook();
const ws = wb.addWorksheet(sheetName ?? "Sheet1");
(0, workbook_1.jsonToWorksheet)(ws, data, { boldHeaders: boldHeaders ?? true, autoWidth: autoWidth ?? true });
await (0, workbook_1.saveWorkbook)(wb, filePath);
return JSON.stringify({ success: true, file: filePath, sheet: ws.name,
rows_written: data.length, columns: data[0] ? Object.keys(data[0]) : [],
preview: (0, workbook_1.preview)(data) });
}
catch (e) {
return `Error: ${e}`;
}
},
}),
// ── 3. excel_append_rows ─────────────────────────────────────────────────
(0, sdk_1.tool)({
name: "excel_append_rows",
description: "Append rows to an existing sheet. Validates that column names match existing headers.",
parameters: {
filePath: zod_1.z.string(),
rows: zod_1.z.array(zod_1.z.record(zod_1.z.union([zod_1.z.string(), zod_1.z.number(), zod_1.z.null()]))),
sheet: zod_1.z.string().optional(),
},
implementation: async ({ filePath, rows, 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 existing = (0, workbook_1.worksheetToJson)(ws);
if (existing.length > 0 && rows.length > 0) {
const known = new Set(Object.keys(existing[0]));
const unknown = Object.keys(rows[0]).filter(k => !known.has(k));
if (unknown.length)
return `Error: Unknown columns: ${unknown.join(", ")}. Expected: ${[...known].join(", ")}`;
}
const updated = [...existing, ...rows];
ws.spliceRows(1, ws.rowCount); // clear
(0, workbook_1.jsonToWorksheet)(ws, updated);
await (0, workbook_1.saveWorkbook)(wb, filePath);
return JSON.stringify({ success: true, file: filePath, sheet: ws.name,
rows_before: existing.length, rows_appended: rows.length, total_rows: updated.length,
preview_appended: (0, workbook_1.preview)(rows) });
}
catch (e) {
return `Error: ${e}`;
}
},
}),
// ── 4. excel_filter ──────────────────────────────────────────────────────
(0, sdk_1.tool)({
name: "excel_filter",
description: "Return rows matching filter conditions. Supports =, !=, >, <, >=, <=, contains, startsWith, endsWith, regex, isNull, isNotNull.",
parameters: {
filePath: zod_1.z.string(),
filters: filterSchema,
sheet: zod_1.z.string().optional(),
},
implementation: async ({ filePath, filters, 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 rows = (0, workbook_1.worksheetToJson)(ws);
const matched = rows.filter(r => (0, workbook_1.matchesFilters)(r, filters));
return JSON.stringify({ file: filePath, sheet: ws.name, filters,
total_rows: rows.length, matched_rows: matched.length, data: matched }, null, 2);
}
catch (e) {
return `Error: ${e}`;
}
},
}),
// ── 5. excel_sort ────────────────────────────────────────────────────────
(0, sdk_1.tool)({
name: "excel_sort",
description: "Sort a sheet by one or more columns and save.",
parameters: {
filePath: zod_1.z.string(),
sortBy: zod_1.z.array(zod_1.z.object({ column: zod_1.z.string(), direction: zod_1.z.enum(["asc", "desc"]).optional() })),
sheet: zod_1.z.string().optional(),
},
implementation: async ({ filePath, sortBy, sheet }) => {
const err = (0, config_1.checkReadable)(filePath);
if (err)
return `Error: ${err}`;
try {
(0, config_1.backupFile)(filePath);
const wb = await (0, workbook_1.loadWorkbook)(filePath);
const ws = (0, workbook_1.getSheet)(wb, sheet);
if (typeof ws === "string")
return `Error: ${ws}`;
const rows = (0, workbook_1.worksheetToJson)(ws);
const sorted = [...rows].sort((a, b) => {
for (const { column, direction } of sortBy) {
const d = direction === "desc" ? -1 : 1;
const av = a[column];
const bv = b[column];
if (av === bv)
continue;
if (typeof av === "number" && typeof bv === "number")
return (av - bv) * d;
return String(av ?? "").localeCompare(String(bv ?? "")) * d;
}
return 0;
});
ws.spliceRows(1, ws.rowCount);
(0, workbook_1.jsonToWorksheet)(ws, sorted);
await (0, workbook_1.saveWorkbook)(wb, filePath);
return JSON.stringify({ success: true, file: filePath, sheet: ws.name,
sorted_by: sortBy, total_rows: sorted.length, preview: (0, workbook_1.preview)(sorted) });
}
catch (e) {
return `Error: ${e}`;
}
},
}),
// ── 6. excel_delete_rows ─────────────────────────────────────────────────
(0, sdk_1.tool)({
name: "excel_delete_rows",
description: "Delete rows matching filter conditions. Creates a .bak backup before deleting.",
parameters: {
filePath: zod_1.z.string(),
filters: filterSchema,
sheet: zod_1.z.string().optional(),
},
implementation: async ({ filePath, filters, sheet }) => {
const err = (0, config_1.checkReadable)(filePath);
if (err)
return `Error: ${err}`;
try {
(0, config_1.backupFile)(filePath);
const wb = await (0, workbook_1.loadWorkbook)(filePath);
const ws = (0, workbook_1.getSheet)(wb, sheet);
if (typeof ws === "string")
return `Error: ${ws}`;
const rows = (0, workbook_1.worksheetToJson)(ws);
const kept = rows.filter(r => !(0, workbook_1.matchesFilters)(r, filters));
ws.spliceRows(1, ws.rowCount);
(0, workbook_1.jsonToWorksheet)(ws, kept);
await (0, workbook_1.saveWorkbook)(wb, filePath);
return JSON.stringify({ success: true, file: filePath, sheet: ws.name,
rows_before: rows.length, rows_deleted: rows.length - kept.length, rows_after: kept.length,
preview_remaining: (0, workbook_1.preview)(kept) });
}
catch (e) {
return `Error: ${e}`;
}
},
}),
// ── 7. excel_update_rows ─────────────────────────────────────────────────
(0, sdk_1.tool)({
name: "excel_update_rows",
description: "Update cell values on rows matching filter conditions (like SQL UPDATE ... WHERE). Creates a .bak backup.",
parameters: {
filePath: zod_1.z.string(),
filters: filterSchema,
updates: zod_1.z.record(zod_1.z.union([zod_1.z.string(), zod_1.z.number(), zod_1.z.null()])).describe("Column/value pairs to set on matched rows"),
sheet: zod_1.z.string().optional(),
},
implementation: async ({ filePath, filters, updates, sheet }) => {
const err = (0, config_1.checkReadable)(filePath);
if (err)
return `Error: ${err}`;
try {
(0, config_1.backupFile)(filePath);
const wb = await (0, workbook_1.loadWorkbook)(filePath);
const ws = (0, workbook_1.getSheet)(wb, sheet);
if (typeof ws === "string")
return `Error: ${ws}`;
const rows = (0, workbook_1.worksheetToJson)(ws);
let count = 0;
const newRows = rows.map(r => {
if (!(0, workbook_1.matchesFilters)(r, filters))
return r;
count++;
return { ...r, ...updates };
});
ws.spliceRows(1, ws.rowCount);
(0, workbook_1.jsonToWorksheet)(ws, newRows);
await (0, workbook_1.saveWorkbook)(wb, filePath);
return JSON.stringify({ success: true, file: filePath, sheet: ws.name,
filters, updates, rows_updated: count });
}
catch (e) {
return `Error: ${e}`;
}
},
}),
// ── 8. excel_merge_files ─────────────────────────────────────────────────
(0, sdk_1.tool)({
name: "excel_merge_files",
description: "Merge multiple Excel/CSV files into a single workbook. Each source file becomes a sheet.",
parameters: {
inputFiles: zod_1.z.array(zod_1.z.string()),
outputFile: zod_1.z.string(),
},
implementation: async ({ inputFiles, outputFile }) => {
const missing = inputFiles.filter(f => !fs.existsSync(f));
if (missing.length)
return `Error: Files not found: ${missing.join(", ")}`;
try {
const outWb = new exceljs_1.default.Workbook();
const summary = [];
for (const fp of inputFiles) {
const wb = await (0, workbook_1.loadWorkbook)(fp);
for (const ws of wb.worksheets) {
const rows = (0, workbook_1.worksheetToJson)(ws);
let name = `${path.basename(fp, path.extname(fp))}_${ws.name}`.slice(0, 31);
let safe = name;
let i = 2;
while (outWb.worksheets.some(s => s.name === safe))
safe = `${name}_${i++}`;
const newWs = outWb.addWorksheet(safe);
(0, workbook_1.jsonToWorksheet)(newWs, rows);
summary.push({ file: fp, sheet: safe, rows: rows.length });
}
}
(0, config_1.ensureDir)(outputFile);
await (0, workbook_1.saveWorkbook)(outWb, outputFile);
return JSON.stringify({ success: true, output: outputFile, sheets_created: summary });
}
catch (e) {
return `Error: ${e}`;
}
},
}),
// ── 9. excel_find_replace ────────────────────────────────────────────────
(0, sdk_1.tool)({
name: "excel_find_replace",
description: "Find and replace values across an entire sheet or a specific column.",
parameters: {
filePath: zod_1.z.string(),
find: zod_1.z.string().describe("Value to search for (case-insensitive)"),
replace: zod_1.z.string().describe("Replacement value"),
column: zod_1.z.string().optional().describe("Restrict to this column (default: all columns)"),
sheet: zod_1.z.string().optional(),
exactMatch: zod_1.z.boolean().optional().describe("Match entire cell value only (default: false = substring)"),
},
implementation: async ({ filePath, find, replace, column, sheet, exactMatch }) => {
const err = (0, config_1.checkReadable)(filePath);
if (err)
return `Error: ${err}`;
try {
(0, config_1.backupFile)(filePath);
const wb = await (0, workbook_1.loadWorkbook)(filePath);
const ws = (0, workbook_1.getSheet)(wb, sheet);
if (typeof ws === "string")
return `Error: ${ws}`;
const rows = (0, workbook_1.worksheetToJson)(ws);
let count = 0;
const regex = new RegExp(exactMatch ? `^${find}$` : find, "gi");
const newRows = rows.map(r => {
const nr = { ...r };
const cols = column ? [column] : Object.keys(nr);
for (const c of cols) {
const old = String(nr[c] ?? "");
const replaced = old.replace(regex, replace);
if (replaced !== old) {
nr[c] = replaced;
count++;
}
}
return nr;
});
ws.spliceRows(1, ws.rowCount);
(0, workbook_1.jsonToWorksheet)(ws, newRows);
await (0, workbook_1.saveWorkbook)(wb, filePath);
return JSON.stringify({ success: true, file: filePath, sheet: ws.name,
find, replace, replacements_made: count });
}
catch (e) {
return `Error: ${e}`;
}
},
}),
// ── 10. excel_deduplicate ────────────────────────────────────────────────
(0, sdk_1.tool)({
name: "excel_deduplicate",
description: "Remove duplicate rows. Can deduplicate on specific columns only, or the full row.",
parameters: {
filePath: zod_1.z.string(),
columns: zod_1.z.array(zod_1.z.string()).optional().describe("Columns to check for duplicates (default: all columns)"),
keep: zod_1.z.enum(["first", "last"]).optional().describe("Which occurrence to keep (default: first)"),
sheet: zod_1.z.string().optional(),
},
implementation: async ({ filePath, columns, keep, sheet }) => {
const err = (0, config_1.checkReadable)(filePath);
if (err)
return `Error: ${err}`;
try {
(0, config_1.backupFile)(filePath);
const wb = await (0, workbook_1.loadWorkbook)(filePath);
const ws = (0, workbook_1.getSheet)(wb, sheet);
if (typeof ws === "string")
return `Error: ${ws}`;
const rows = (0, workbook_1.worksheetToJson)(ws);
const seen = new Set();
const keepLast = keep === "last";
const source = keepLast ? [...rows].reverse() : rows;
const deduped = source.filter(r => {
const key = JSON.stringify(columns ? columns.map(c => r[c]) : r);
if (seen.has(key))
return false;
seen.add(key);
return true;
});
const result = keepLast ? deduped.reverse() : deduped;
ws.spliceRows(1, ws.rowCount);
(0, workbook_1.jsonToWorksheet)(ws, result);
await (0, workbook_1.saveWorkbook)(wb, filePath);
return JSON.stringify({ success: true, file: filePath, sheet: ws.name,
rows_before: rows.length, duplicates_removed: rows.length - result.length,
rows_after: result.length });
}
catch (e) {
return `Error: ${e}`;
}
},
}),
// ── 11. excel_rename_columns ─────────────────────────────────────────────
(0, sdk_1.tool)({
name: "excel_rename_columns",
description: "Rename one or more column headers without modifying data.",
parameters: {
filePath: zod_1.z.string(),
renames: zod_1.z.record(zod_1.z.string()).describe("{ oldName: newName } pairs"),
sheet: zod_1.z.string().optional(),
},
implementation: async ({ filePath, renames, 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 rows = (0, workbook_1.worksheetToJson)(ws);
const newRows = rows.map(r => {
const nr = {};
for (const [k, v] of Object.entries(r))
nr[renames[k] ?? k] = v;
return nr;
});
ws.spliceRows(1, ws.rowCount);
(0, workbook_1.jsonToWorksheet)(ws, newRows);
await (0, workbook_1.saveWorkbook)(wb, filePath);
return JSON.stringify({ success: true, file: filePath, sheet: ws.name, renames,
columns_after: newRows[0] ? Object.keys(newRows[0]) : [] });
}
catch (e) {
return `Error: ${e}`;
}
},
}),
// ── 12. excel_add_column ─────────────────────────────────────────────────
(0, sdk_1.tool)({
name: "excel_add_column",
description: "Add a computed column. The expression is evaluated as JS with 'row' as the current row object. Example: 'row.Price * row.Qty' or 'row.First + \" \" + row.Last'.",
parameters: {
filePath: zod_1.z.string(),
columnName: zod_1.z.string().describe("Name of the new column"),
expression: zod_1.z.string().describe("JS expression using 'row', e.g. 'row.Price * 1.2'"),
sheet: zod_1.z.string().optional(),
},
implementation: async ({ filePath, columnName, expression, 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 rows = (0, workbook_1.worksheetToJson)(ws);
// eslint-disable-next-line no-new-func
const fn = new Function("row", `return (${expression})`);
const newRows = rows.map(row => ({ ...row, [columnName]: fn(row) }));
ws.spliceRows(1, ws.rowCount);
(0, workbook_1.jsonToWorksheet)(ws, newRows);
await (0, workbook_1.saveWorkbook)(wb, filePath);
return JSON.stringify({ success: true, file: filePath, sheet: ws.name,
new_column: columnName, expression, preview: (0, workbook_1.preview)(newRows) });
}
catch (e) {
return `Error: ${e}`;
}
},
}),
];
}
//# sourceMappingURL=data.js.map