Something went wrong. Try again.
A functional toolkit for authoring resource-leveling algorithms — compose constraint and scoring blocks, enumerate feasible schedules
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624import ExcelJS from "exceljs";import JSZip from "jszip";import type { ProjectFile } from "../model/Project.ts";import type { Duration } from "../model/Duration.ts";import { TimeUnit } from "../model/types.ts";
export interface XlsxWriterOptions { /** Worksheet name. Default: "Schedule". */ sheetName?: string;}
export interface GanttBar { name: string; /** ISO date string YYYY-MM-DD. */ start: string; /** ISO date string YYYY-MM-DD. */ finish: string;}
export interface GanttPhase { label: string; items: GanttBar[];}
export interface GanttSpec { phases: GanttPhase[]; /** Chart title. Default: "Gantt". */ title?: string; /** Hex colors (no #) cycled per phase. Default: ["4472C4", "ED7D31"] (blue, orange). */ phaseColors?: string[];}
const DEFAULT_PHASE_COLORS = ["4472C4", "ED7D31"];
const HEADER_FILL: ExcelJS.Fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FF4472C4" },};const HEADER_FONT: Partial<ExcelJS.Font> = { bold: true, color: { argb: "FFFFFFFF" },};
/** Visual style hooks for spec-driven sheets. The semantics (e.g. muted rows * placed outside SUM ranges, INTERNAL distribution warnings) are the * consuming app's layout concern; the writer only renders the style. */export type CellStyle = "header" | "input" | "derived" | "muted" | "internal";
export interface CellSpec { value?: unknown; /** Formula text without leading "=". Written without a cached result so * Excel/LibreOffice recalculate on open. */ formula?: string; numFmt?: string; /** Cell comment (parameter notes). */ note?: string; style?: CellStyle;}
export interface SheetSpec { name: string; rows: CellSpec[][]; /** Workbook-level defined names, e.g. { name: "Rate_Install_Blended", * range: "Pricing!$B$4" }. Emitters that write formulas against named * ranges only survive row insertion. */ namedRanges?: { name: string; range: string }[]; /** 1-based inclusive row spans to group under an outline level. */ outlineRows?: { from: number; to: number; level: number }[];}
/** * Converts a Date to an Excel serial number (days since 1899-12-30), * avoiding timezone issues by working directly with date components. */function dateToSerial(date: Date): number { const y = date.getFullYear(); const m = date.getMonth(); // 0-based const d = date.getDate(); const h = date.getHours(); const min = date.getMinutes();
// Excel epoch: day 0 = 1899-12-30 (includes the intentional 1900 leap year bug) const epoch = Date.UTC(1899, 11, 30); const dateUtc = Date.UTC(y, m, d); const days = (dateUtc - epoch) / 86400000; const timeFraction = (h * 60 + min) / 1440; return days + timeFraction;}
function ymdToSerial(ymd: string): number { const match = /^(\d{4})-(\d{2})-(\d{2})$/.exec(ymd); if (!match) throw new Error(`Invalid YYYY-MM-DD date: ${ymd}`); const y = Number(match[1]); const mo = Number(match[2]); const d = Number(match[3]); const epoch = Date.UTC(1899, 11, 30); const utc = Date.UTC(y, mo - 1, d); return (utc - epoch) / 86400000;}
function xmlEscape(text: string): string { return text .replace(/&/g, "&") .replace(/</g, "<") .replace(/>/g, ">") .replace(/"/g, """) .replace(/'/g, "'");}
function durationUnitLabel(dur: Duration): string { switch (dur.unit) { case TimeUnit.Minutes: return '" min"'; case TimeUnit.Hours: return '" hrs"'; case TimeUnit.Days: return '" days"'; case TimeUnit.Weeks: return '" wks"'; case TimeUnit.Months: return '" mos"'; case TimeUnit.Percent: return '"%"'; default: return ""; }}
export class XlsxWriter { async write( project: ProjectFile, options?: XlsxWriterOptions, ): Promise<Uint8Array> { return toBuffer(this.buildWorkbook(project, options)); }
/** * Like {@link write}, but also adds a hidden `_Gantt Data` sheet and a * `Gantt` sheet hosting a stacked horizontal bar chart. Series 1 is the * transparent offset; series 2 is the visible duration, colored per phase. */ async writeWithGantt( project: ProjectFile, gantt: GanttSpec, options?: XlsxWriterOptions, ): Promise<Uint8Array> { return this.composeGanttWorkbook(project, gantt, options); }
private async composeGanttWorkbook( project: ProjectFile, gantt: GanttSpec, options?: XlsxWriterOptions, specSheets?: SheetSpec[], ): Promise<Uint8Array> { const flat: { phaseIdx: number; bar: GanttBar }[] = []; for (let pi = 0; pi < gantt.phases.length; pi += 1) { for (const bar of gantt.phases[pi]!.items) { flat.push({ phaseIdx: pi, bar }); } }
if (flat.length === 0) { const workbook = this.buildWorkbook(project, options); if (specSheets) addSpecSheets(workbook, specSheets); return toBuffer(workbook); }
flat.sort((a, b) => { const sa = ymdToSerial(a.bar.start); const sb = ymdToSerial(b.bar.start); if (sa !== sb) return sa - sb; return a.bar.name.localeCompare(b.bar.name); });
const colors = gantt.phaseColors ?? DEFAULT_PHASE_COLORS; const items = flat.map(({ phaseIdx, bar }) => { const offset = ymdToSerial(bar.start); const finish = ymdToSerial(bar.finish); return { label: `${gantt.phases[phaseIdx]!.label}: ${bar.name}`, offset, duration: Math.max(1, finish - offset), color: colors[phaseIdx % colors.length]!, }; });
const origin = Math.min(...items.map((i) => i.offset)); const end = Math.max(...items.map((i) => i.offset + i.duration));
const workbook = this.buildWorkbook(project, options);
const dataSheetName = "_Gantt Data"; const dataSheet = workbook.addWorksheet(dataSheetName, { state: "hidden" }); dataSheet.addRow(["Item", "Offset", "Duration"]); for (const item of items) { dataSheet.addRow([item.label, item.offset, item.duration]); }
workbook.addWorksheet("Gantt");
// Spec sheets come after the Gantt sheet: injectGanttChart patches // sheet3.xml by position (Schedule, _Gantt Data, Gantt). if (specSheets) addSpecSheets(workbook, specSheets);
const buffer = await workbook.xlsx.writeBuffer(); const patched = await injectGanttChart(buffer, { items, origin, end, title: gantt.title ?? "Gantt", dataSheetName, }); return new Uint8Array(patched); }
/** * Writes a workbook from declarative sheet specs: literal values, formula * cells (no cached result), named ranges, cell notes, row outlines, and * the {@link CellStyle} hooks. Formula strings are opaque to the writer — * it writes what it is given. */ async writeSheets(sheets: SheetSpec[]): Promise<Uint8Array> { const workbook = new ExcelJS.Workbook(); addSpecSheets(workbook, sheets); return toBuffer(workbook); }
/** * Escape hatch: composes spec sheets into a workbook that also carries the * existing schedule/Gantt sheets. Spec sheets are appended after the Gantt * sheet so the chart-injection sheet indices stay stable. */ async writeWithGanttAndSheets( project: ProjectFile, gantt: GanttSpec, sheets: SheetSpec[], options?: XlsxWriterOptions, ): Promise<Uint8Array> { return this.composeGanttWorkbook(project, gantt, options, sheets); }
private buildWorkbook( project: ProjectFile, options?: XlsxWriterOptions, ): ExcelJS.Workbook { const sheetName = options?.sheetName ?? "Schedule";
const workbook = new ExcelJS.Workbook(); const sheet = workbook.addWorksheet(sheetName, { properties: { outlineLevelRow: 4 }, });
sheet.columns = [ { header: "ID", key: "id", width: 6 }, { header: "Task Name", key: "name", width: 55 }, { header: "WBS", key: "wbs", width: 10 }, { header: "Start", key: "start", width: 18 }, { header: "Finish", key: "finish", width: 18 }, { header: "Duration", key: "duration", width: 14 }, ];
// Style header row const headerRow = sheet.getRow(1); headerRow.fill = HEADER_FILL; headerRow.font = HEADER_FONT;
const dateFormat = "M/D/YYYY H:MM AM/PM";
for (const task of project.tasks) { if (task.id === null) continue;
const startSerial = task.start ? dateToSerial(task.start) : null; const finishSerial = task.finish ? dateToSerial(task.finish) : null;
const row = sheet.addRow({ id: task.id, name: task.name, wbs: task.wbs, start: startSerial, finish: finishSerial, duration: task.duration?.value ?? null, });
if (startSerial) { row.getCell("start").numFmt = dateFormat; } if (finishSerial) { row.getCell("finish").numFmt = dateFormat; }
if (task.duration) { const label = durationUnitLabel(task.duration); row.getCell("duration").numFmt = `#,##0.##${label}`; }
const outlineLevel = task.outlineLevel ?? 0; if (outlineLevel > 0) { row.getCell("name").alignment = { indent: (outlineLevel - 1) * 2 }; }
if (task.summary) { row.font = { bold: true }; }
// Excel caps the outline level at 7. if (outlineLevel > 0) { row.outlineLevel = Math.min(outlineLevel, 7); } }
sheet.properties.outlineLevelRow = 4;
return workbook; }}
async function toBuffer(workbook: ExcelJS.Workbook): Promise<Uint8Array> { return new Uint8Array(await workbook.xlsx.writeBuffer());}
function applyCellStyle(cell: ExcelJS.Cell, style: CellStyle): void { switch (style) { case "header": cell.fill = HEADER_FILL; cell.font = HEADER_FONT; break; case "input": cell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFFFF2CC" }, }; break; case "derived": cell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFF2F2F2" }, }; break; case "muted": cell.font = { color: { argb: "FF808080" }, strike: true }; break; case "internal": cell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFFCE4D6" }, }; break; }}
function addSpecSheets(workbook: ExcelJS.Workbook, sheets: SheetSpec[]): void { for (const spec of sheets) { const sheet = workbook.addWorksheet(spec.name);
for (let r = 0; r < spec.rows.length; r += 1) { const rowSpec = spec.rows[r]!; for (let c = 0; c < rowSpec.length; c += 1) { const cellSpec = rowSpec[c]!; const cell = sheet.getCell(r + 1, c + 1); if (cellSpec.formula !== undefined) { // No cached result — Excel/LibreOffice recalculate on open. cell.value = { formula: cellSpec.formula }; } else if (cellSpec.value !== undefined) { cell.value = cellSpec.value as ExcelJS.CellValue; } if (cellSpec.numFmt) cell.numFmt = cellSpec.numFmt; if (cellSpec.note) cell.note = cellSpec.note; if (cellSpec.style) applyCellStyle(cell, cellSpec.style); } }
for (const outline of spec.outlineRows ?? []) { for (let r = outline.from; r <= outline.to; r += 1) { // Excel caps the outline level at 7. sheet.getRow(r).outlineLevel = Math.min(outline.level, 7); } }
for (const named of spec.namedRanges ?? []) { workbook.definedNames.add(named.range, named.name); } }}
interface GanttItem { label: string; offset: number; duration: number; color: string;}
interface InjectArgs { items: GanttItem[]; origin: number; end: number; title: string; dataSheetName: string;}
async function injectGanttChart( buffer: ArrayBuffer | Uint8Array, { items, origin, end, title, dataSheetName }: InjectArgs,): Promise<ArrayBuffer> { const zip = await JSZip.loadAsync(buffer);
// The Gantt sheet is the 3rd sheet (Schedule, _Gantt Data, Gantt) and gets // written as sheet3.xml by ExcelJS in insertion order. const ganttSheetPath = "xl/worksheets/sheet3.xml"; const ganttRelsPath = "xl/worksheets/_rels/sheet3.xml.rels"; const drawingPath = "xl/drawings/drawing1.xml"; const drawingRelsPath = "xl/drawings/_rels/drawing1.xml.rels"; const chartPath = "xl/charts/chart1.xml"; const contentTypesPath = "[Content_Types].xml";
zip.file( chartPath, buildChartXml({ items, origin, end, title, dataSheetName }), ); zip.file(drawingPath, buildDrawingXml(items.length)); zip.file(drawingRelsPath, buildDrawingRelsXml()); zip.file(ganttRelsPath, buildSheetRelsXml());
const sheetXml = await zip.file(ganttSheetPath)!.async("string"); zip.file( ganttSheetPath, sheetXml.replace("</worksheet>", '<drawing r:id="rId1"/></worksheet>'), );
const ctXml = await zip.file(contentTypesPath)!.async("string"); const ctPatched = ctXml.replace( "</Types>", '<Override PartName="/xl/charts/chart1.xml" ContentType="application/vnd.openxmlformats-officedocument.drawingml.chart+xml"/>' + '<Override PartName="/xl/drawings/drawing1.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml"/>' + "</Types>", ); zip.file(contentTypesPath, ctPatched);
return zip.generateAsync({ type: "arraybuffer", compression: "DEFLATE" });}
/** * Builds a gradient-fill `<c:spPr>` for the plot area that produces * alternating horizontal row bands (white / light gray), one band per * category. Each adjacent stop pair uses identical positions so the * gradient becomes a hard step transition. */function buildPlotShadingXml(n: number): string { if (n <= 0) return ""; const EVEN = "FFFFFF"; const ODD = "F4F4F4"; const stops: string[] = []; for (let i = 0; i < n; i += 1) { const startPos = Math.round((i / n) * 100000); const endPos = Math.round(((i + 1) / n) * 100000); const color = i % 2 === 0 ? EVEN : ODD; stops.push(`<a:gs pos="${startPos}"><a:srgbClr val="${color}"/></a:gs>`); stops.push(`<a:gs pos="${endPos}"><a:srgbClr val="${color}"/></a:gs>`); } return ( "<c:spPr>" + '<a:gradFill rotWithShape="1">' + `<a:gsLst>${stops.join("")}</a:gsLst>` + // 5400000 = 90° in 60000ths of a degree = top-to-bottom gradient. // catAx uses orientation="maxMin", so band i=0 lands on the top row. '<a:lin ang="5400000" scaled="0"/>' + "</a:gradFill>" + "<a:ln><a:noFill/></a:ln>" + "</c:spPr>" );}
function buildChartXml({ items, origin, end, title, dataSheetName,}: InjectArgs): string { const n = items.length; const lastRow = n + 1; const sheetRef = `'${dataSheetName.replace(/'/g, "''")}'`;
const catCache = items .map((it, i) => `<c:pt idx="${i}"><c:v>${xmlEscape(it.label)}</c:v></c:pt>`) .join(""); const offsetCache = items .map((it, i) => `<c:pt idx="${i}"><c:v>${it.offset}</c:v></c:pt>`) .join(""); const durCache = items .map((it, i) => `<c:pt idx="${i}"><c:v>${it.duration}</c:v></c:pt>`) .join(""); const dPts = items .map( (it, i) => `<c:dPt><c:idx val="${i}"/><c:invertIfNegative val="0"/><c:bubble3D val="0"/>` + `<c:spPr><a:solidFill><a:srgbClr val="${it.color}"/></a:solidFill>` + `<a:ln><a:solidFill><a:srgbClr val="${it.color}"/></a:solidFill></a:ln></c:spPr></c:dPt>`, ) .join("");
return ( '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' + '<c:chartSpace 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">' + "<c:chart>" + "<c:title><c:tx><c:rich><a:bodyPr/><a:lstStyle/>" + `<a:p><a:r><a:t>${xmlEscape(title)}</a:t></a:r></a:p>` + '</c:rich></c:tx><c:overlay val="0"/></c:title>' + '<c:autoTitleDeleted val="0"/>' + "<c:plotArea><c:layout/>" + "<c:barChart>" + '<c:barDir val="bar"/><c:grouping val="stacked"/><c:varyColors val="0"/>' + // Series 0: invisible offset "<c:ser>" + '<c:idx val="0"/><c:order val="0"/>' + `<c:tx><c:strRef><c:f>${sheetRef}!$B$1</c:f>` + '<c:strCache><c:ptCount val="1"/><c:pt idx="0"><c:v>Offset</c:v></c:pt></c:strCache>' + "</c:strRef></c:tx>" + "<c:spPr><a:noFill/><a:ln><a:noFill/></a:ln></c:spPr>" + '<c:invertIfNegative val="0"/>' + `<c:cat><c:strRef><c:f>${sheetRef}!$A$2:$A$${lastRow}</c:f>` + `<c:strCache><c:ptCount val="${n}"/>${catCache}</c:strCache></c:strRef></c:cat>` + `<c:val><c:numRef><c:f>${sheetRef}!$B$2:$B$${lastRow}</c:f>` + `<c:numCache><c:formatCode>General</c:formatCode><c:ptCount val="${n}"/>${offsetCache}</c:numCache>` + "</c:numRef></c:val>" + "</c:ser>" + // Series 1: duration, per-point colors "<c:ser>" + '<c:idx val="1"/><c:order val="1"/>' + `<c:tx><c:strRef><c:f>${sheetRef}!$C$1</c:f>` + '<c:strCache><c:ptCount val="1"/><c:pt idx="0"><c:v>Duration</c:v></c:pt></c:strCache>' + "</c:strRef></c:tx>" + '<c:invertIfNegative val="0"/>' + dPts + `<c:cat><c:strRef><c:f>${sheetRef}!$A$2:$A$${lastRow}</c:f>` + `<c:strCache><c:ptCount val="${n}"/>${catCache}</c:strCache></c:strRef></c:cat>` + `<c:val><c:numRef><c:f>${sheetRef}!$C$2:$C$${lastRow}</c:f>` + `<c:numCache><c:formatCode>General</c:formatCode><c:ptCount val="${n}"/>${durCache}</c:numCache>` + "</c:numRef></c:val>" + "</c:ser>" + '<c:overlap val="100"/>' + '<c:axId val="111111111"/><c:axId val="222222222"/>' + "</c:barChart>" + // Category axis (visually the vertical axis on a horizontal bar chart) "<c:catAx>" + '<c:axId val="111111111"/>' + '<c:scaling><c:orientation val="maxMin"/></c:scaling>' + '<c:delete val="0"/><c:axPos val="l"/>' + '<c:crossAx val="222222222"/>' + "</c:catAx>" + // Value axis (the time axis along the bottom) "<c:valAx>" + '<c:axId val="222222222"/>' + `<c:scaling><c:orientation val="minMax"/><c:max val="${end}"/><c:min val="${origin}"/></c:scaling>` + '<c:delete val="0"/><c:axPos val="b"/>' + '<c:numFmt formatCode="mmm-yy" sourceLinked="0"/>' + '<c:majorTickMark val="out"/><c:minorTickMark val="none"/>' + '<c:crossAx val="111111111"/>' + '<c:majorUnit val="30"/>' + "</c:valAx>" + buildPlotShadingXml(n) + "</c:plotArea>" + '<c:plotVisOnly val="1"/><c:dispBlanksAs val="gap"/>' + "</c:chart></c:chartSpace>" );}
function buildDrawingXml(n: number): string { // Anchor B2 to col 16, row ~ scaled by bar count so the chart is roughly proportional. const toRow = Math.max(20, n * 2 + 5); 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>1</xdr:col><xdr:colOff>0</xdr:colOff>" + "<xdr:row>1</xdr:row><xdr:rowOff>0</xdr:rowOff></xdr:from>" + `<xdr:to><xdr:col>16</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="rId1"/>' + "</a:graphicData></a:graphic>" + "</xdr:graphicFrame>" + "<xdr:clientData/>" + "</xdr:twoCellAnchor>" + "</xdr:wsDr>" );}
function buildDrawingRelsXml(): string { return ( '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' + '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">' + '<Relationship Id="rId1"' + ' Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart"' + ' Target="../charts/chart1.xml"/>' + "</Relationships>" );}
function buildSheetRelsXml(): string { return ( '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' + '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">' + '<Relationship Id="rId1"' + ' Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing"' + ' Target="../drawings/drawing1.xml"/>' + "</Relationships>" );}