excel-tools
Public
Project Files
dist / tools / charts.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.getChartTools = getChartTools;
const sdk_1 = require("@lmstudio/sdk");
const zod_1 = require("zod");
const jszip_1 = __importDefault(require("jszip"));
const fs = __importStar(require("fs"));
const workbook_1 = require("../utils/workbook");
const config_1 = require("../utils/config");
// ─── XML helpers ──────────────────────────────────────────────────────────────
function escXml(s) {
return s.replace(/&/g, "&").replace(/</g, "<").replace(/>/g, ">").replace(/"/g, """);
}
function serXml(idx, catRef, valRef, nameRef, color) {
const solidFill = color ? `<c:solidFill><a:srgbClr val="${color}"/></c:solidFill>` : "";
return `<c:ser><c:idx val="${idx}"/><c:order val="${idx}"/>
<c:tx><c:strRef><c:f>${escXml(nameRef)}</c:f></c:strRef></c:tx>
${solidFill ? `<c:spPr><a:solidFill><a:srgbClr val="${color}"/></a:solidFill></c:spPr>` : ""}
<c:cat><c:strRef><c:f>${escXml(catRef)}</c:f></c:strRef></c:cat>
<c:val><c:numRef><c:f>${escXml(valRef)}</c:f></c:numRef></c:val>
</c:ser>`;
}
function scatterSerXml(idx, xRef, yRef, nameRef) {
return `<c:ser><c:idx val="${idx}"/><c:order val="${idx}"/>
<c:tx><c:strRef><c:f>${escXml(nameRef)}</c:f></c:strRef></c:tx>
<c:xVal><c:numRef><c:f>${escXml(xRef)}</c:f></c:numRef></c:xVal>
<c:yVal><c:numRef><c:f>${escXml(yRef)}</c:f></c:numRef></c:yVal>
</c:ser>`;
}
function chartXml(type, title, series, isHorizontal = false) {
const ns = `xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"`;
const axes = `<c:catAx><c:axId val="1"/><c:scaling><c:orientation val="minMax"/></c:scaling><c:delete val="0"/><c:axPos val="${isHorizontal ? "l" : "b"}"/><c:crossAx val="2"/></c:catAx>
<c:valAx><c:axId val="2"/><c:scaling><c:orientation val="minMax"/></c:scaling><c:delete val="0"/><c:axPos val="${isHorizontal ? "r" : "l"}"/><c:crossAx val="1"/></c:valAx>`;
const scatterAxes = `<c:valAx><c:axId val="1"/><c:scaling><c:orientation val="minMax"/></c:scaling><c:delete val="0"/><c:axPos val="b"/><c:crossAx val="2"/></c:valAx>
<c:valAx><c:axId val="2"/><c:scaling><c:orientation val="minMax"/></c:scaling><c:delete val="0"/><c:axPos val="l"/><c:crossAx val="1"/></c:valAx>`;
let plotArea = "";
switch (type) {
case "bar":
plotArea = `<c:barChart><c:barDir val="col"/><c:grouping val="clustered"/><c:varyColors val="0"/>${series}<c:axId val="1"/><c:axId val="2"/></c:barChart>${axes}`;
break;
case "barh":
plotArea = `<c:barChart><c:barDir val="bar"/><c:grouping val="clustered"/><c:varyColors val="0"/>${series}<c:axId val="1"/><c:axId val="2"/></c:barChart>${axes}`;
break;
case "line":
plotArea = `<c:lineChart><c:grouping val="standard"/><c:varyColors val="0"/>${series}<c:axId val="1"/><c:axId val="2"/></c:lineChart>${axes}`;
break;
case "area":
plotArea = `<c:areaChart><c:grouping val="standard"/><c:varyColors val="0"/>${series}<c:axId val="1"/><c:axId val="2"/></c:areaChart>${axes}`;
break;
case "pie":
plotArea = `<c:pieChart><c:varyColors val="1"/>${series}</c:pieChart>`;
break;
case "doughnut":
plotArea = `<c:doughnutChart><c:varyColors val="1"/><c:holeSize val="50"/>${series}</c:doughnutChart>`;
break;
case "scatter":
plotArea = `<c:scatterChart><c:scatterStyle val="marker"/><c:varyColors val="0"/>${series}<c:axId val="1"/><c:axId val="2"/></c:scatterChart>${scatterAxes}`;
break;
}
return `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<c:chartSpace ${ns}>
<c:chart>
<c:title><c:tx><c:rich><a:bodyPr/><a:lstStyle/><a:p><a:r><a:t>${escXml(title)}</a:t></a:r></a:p></c:rich></c:tx><c:overlay val="0"/></c:title>
<c:autoTitleDeleted val="0"/>
<c:plotArea>${plotArea}</c:plotArea>
<c:legend><c:legendPos val="r"/></c:legend>
<c:plotVisOnly val="1"/>
</c:chart>
</c:chartSpace>`;
}
function drawingXml(chartRId, fromCol, fromRow, toCol, toRow) {
return `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xdr:wsDr xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart">
<xdr:twoCellAnchor>
<xdr:from><xdr:col>${fromCol}</xdr:col><xdr:colOff>0</xdr:colOff><xdr:row>${fromRow}</xdr:row><xdr:rowOff>0</xdr:rowOff></xdr:from>
<xdr:to><xdr:col>${toCol}</xdr:col><xdr:colOff>0</xdr:colOff><xdr:row>${toRow}</xdr:row><xdr:rowOff>0</xdr:rowOff></xdr:to>
<xdr:graphicFrame macro=""><xdr:nvGraphicFramePr><xdr:cNvPr id="2" name="Chart 1"/><xdr:cNvGraphicFramePr/></xdr:nvGraphicFramePr>
<xdr:xfrm><a:off x="0" y="0"/><a:ext cx="0" cy="0"/></xdr:xfrm>
<a:graphic><a:graphicData uri="http://schemas.openxmlformats.org/drawingml/2006/chart">
<c:chart r:id="${chartRId}"/>
</a:graphicData></a:graphic>
</xdr:graphicFrame><xdr:clientData/>
</xdr:twoCellAnchor>
</xdr:wsDr>`;
}
function relsXml(id, type, target) {
return `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="${id}" Type="${type}" Target="${target}"/>
</Relationships>`;
}
// ─── Tool ─────────────────────────────────────────────────────────────────────
function getChartTools() {
return [
// ── 27. excel_create_chart ───────────────────────────────────────────────
(0, sdk_1.tool)({
name: "excel_create_chart",
description: "Create a real Excel chart (bar, barh, line, area, pie, doughnut, scatter) embedded in a sheet. The chart is a native Excel chart — not an image — and remains interactive in Excel.",
parameters: {
filePath: zod_1.z.string(),
sheet: zod_1.z.string().optional().describe("Sheet containing the data (default: first sheet)"),
type: zod_1.z.enum(["bar", "barh", "line", "area", "pie", "doughnut", "scatter"]).describe("Chart type"),
title: zod_1.z.string().describe("Chart title"),
categoryColumn: zod_1.z.string().describe("Column name for X axis / categories (or X values for scatter)"),
valueColumns: zod_1.z.array(zod_1.z.string()).describe("Column names for data series (or Y values for scatter)"),
position: zod_1.z.string().optional().describe("Cell range for chart placement, e.g. 'E2:N20' (default: E2:N20)"),
},
implementation: async ({ filePath, sheet, type, title, categoryColumn, valueColumns, position }) => {
const err = (0, config_1.checkReadable)(filePath);
if (err)
return `Error: ${err}`;
try {
// 1. Read workbook to find column positions and row count
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);
if (!rows.length)
return "Error: Sheet has no data";
const headers = Object.keys(rows[0]);
const catIdx = headers.indexOf(categoryColumn) + 1; // 1-based
if (!catIdx)
return `Error: Column "${categoryColumn}" not found. Available: ${headers.join(", ")}`;
const valIdxs = valueColumns.map(v => ({ name: v, idx: headers.indexOf(v) + 1 }));
const missingVal = valIdxs.find(v => !v.idx);
if (missingVal)
return `Error: Column "${missingVal.name}" not found. Available: ${headers.join(", ")}`;
const sheetName = ws.name;
const dataRows = rows.length;
const catRef = `'${sheetName}'!$${(0, workbook_1.colIndexToLetter)(catIdx)}$2:$${(0, workbook_1.colIndexToLetter)(catIdx)}$${dataRows + 1}`;
const nameRowRef = (idx) => `'${sheetName}'!$${(0, workbook_1.colIndexToLetter)(idx)}$1`;
// 2. Save current workbook (ensures clean xlsx structure)
await (0, workbook_1.saveWorkbook)(wb, filePath);
// 3. Open as ZIP and inject chart XML
const buf = fs.readFileSync(filePath);
const zip = await jszip_1.default.loadAsync(buf);
// Determine chart/drawing numbers
const chartNums = Object.keys(zip.files).filter(k => k.match(/xl\/charts\/chart\d+\.xml/)).length;
const drawNums = Object.keys(zip.files).filter(k => k.match(/xl\/drawings\/drawing\d+\.xml/)).length;
const chartN = chartNums + 1;
const drawN = drawNums + 1;
// Find sheet XML path
const wbXml = await zip.file("xl/workbook.xml").async("text");
const wbRels = await zip.file("xl/_rels/workbook.xml.rels").async("text");
const ridMatch = wbXml.match(new RegExp(`name="${sheetName.replace(/[.*+?^${}()|[\]\\]/g, "\\$&")}"[^>]*r:id="([^"]+)"`));
if (!ridMatch)
return `Error: Could not locate sheet XML for "${sheetName}"`;
const sheetRid = ridMatch[1];
const sheetPathMatch = wbRels.match(new RegExp(`Id="${sheetRid}"[^>]*Target="([^"]+)"`));
if (!sheetPathMatch)
return "Error: Could not resolve sheet file path";
const sheetXmlPath = `xl/${sheetPathMatch[1].replace(/^\.\.\//, "")}`;
const sheetXmlPathRel = sheetXmlPath.replace(/^xl\/worksheets\//, "");
// Build series XML
const colors = ["4472C4", "ED7D31", "A9D18E", "FF0000", "FFFF00", "9900FF"];
let seriesXml = "";
if (type === "scatter") {
valIdxs.forEach(({ name, idx }, i) => {
const xRef = `'${sheetName}'!$${(0, workbook_1.colIndexToLetter)(catIdx)}$2:$${(0, workbook_1.colIndexToLetter)(catIdx)}$${dataRows + 1}`;
const yRef = `'${sheetName}'!$${(0, workbook_1.colIndexToLetter)(idx)}$2:$${(0, workbook_1.colIndexToLetter)(idx)}$${dataRows + 1}`;
seriesXml += scatterSerXml(i, xRef, yRef, nameRowRef(idx));
});
}
else {
valIdxs.forEach(({ name, idx }, i) => {
const valRef = `'${sheetName}'!$${(0, workbook_1.colIndexToLetter)(idx)}$2:$${(0, workbook_1.colIndexToLetter)(idx)}$${dataRows + 1}`;
seriesXml += serXml(i, catRef, valRef, nameRowRef(idx), colors[i % colors.length]);
});
}
// Add chart XML
const chartPath = `xl/charts/chart${chartN}.xml`;
zip.file(chartPath, chartXml(type, title, seriesXml, type === "barh"));
// Parse position
const pos = (position ?? "E2:N20").match(/^([A-Z]+)(\d+):([A-Z]+)(\d+)$/i);
const colLetter = (s) => s.toUpperCase().split("").reduce((a, c) => a * 26 + c.charCodeAt(0) - 64, 0) - 1;
const fc = pos ? colLetter(pos[1]) : 4;
const fr = pos ? parseInt(pos[2]) - 1 : 1;
const tc = pos ? colLetter(pos[3]) : 13;
const tr = pos ? parseInt(pos[4]) - 1 : 19;
// Add drawing XML
const drawPath = `xl/drawings/drawing${drawN}.xml`;
const drawRelsPath = `xl/drawings/_rels/drawing${drawN}.xml.rels`;
zip.file(drawPath, drawingXml("rId1", fc, fr, tc, tr));
zip.file(drawRelsPath, relsXml("rId1", "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart", `../charts/chart${chartN}.xml`));
// Link drawing to sheet
let sheetXml = await zip.file(sheetXmlPath).async("text");
const sheetRelsPath = `xl/worksheets/_rels/${sheetXmlPathRel}.rels`;
let sheetRelsXml = "";
const existingRels = zip.file(sheetRelsPath);
if (existingRels) {
sheetRelsXml = await existingRels.async("text");
const existingIds = [...sheetRelsXml.matchAll(/Id="(rId\d+)"/g)].map(m => parseInt(m[1].replace("rId", "")));
const nextId = Math.max(0, ...existingIds) + 1;
sheetRelsXml = sheetRelsXml.replace("</Relationships>", `<Relationship Id="rId${nextId}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing" Target="../drawings/drawing${drawN}.xml"/></Relationships>`);
sheetXml = sheetXml.replace("</worksheet>", `<drawing r:id="rId${nextId}"/></worksheet>`);
}
else {
sheetRelsXml = relsXml("rId1", "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing", `../drawings/drawing${drawN}.xml`);
sheetXml = sheetXml.replace("</worksheet>", `<drawing r:id="rId1"/></worksheet>`);
}
zip.file(sheetXmlPath, sheetXml);
zip.file(sheetRelsPath, sheetRelsXml);
// Update [Content_Types].xml
let ct = await zip.file("[Content_Types].xml").async("text");
ct = ct.replace("</Types>", `<Override PartName="/xl/charts/chart${chartN}.xml" ContentType="application/vnd.openxmlformats-officedocument.drawingml.chart+xml"/>` +
`<Override PartName="/xl/drawings/drawing${drawN}.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml"/></Types>`);
zip.file("[Content_Types].xml", ct);
// Save
const outBuf = await zip.generateAsync({ type: "nodebuffer", compression: "DEFLATE" });
fs.writeFileSync(filePath, outBuf);
return JSON.stringify({ success: true, file: filePath, sheet: sheetName, chart: {
type, title, category_column: categoryColumn, value_columns: valueColumns,
data_rows: dataRows, position: position ?? "E2:N20",
note: "Real Excel chart — opens interactively in Excel/LibreOffice"
} });
}
catch (e) {
return `Error: ${e}`;
}
},
}),
];
}
//# sourceMappingURL=charts.js.map