john
868 lines
38 KiB
JavaScript
868 lines
38 KiB
JavaScript
import fs from 'node:fs/promises';
|
|
import { constants as fsConstants } from 'node:fs';
|
|
import path from 'node:path';
|
|
import ExcelJS from 'exceljs';
|
|
import JSZip from 'jszip';
|
|
import { reportHtml } from './excel-report-html.mjs';
|
|
|
|
const DEFAULT_MAX_FILE_BYTES = 30 * 1024 * 1024;
|
|
const DEFAULT_MAX_SCAN_CELLS = 200_000;
|
|
const DEFAULT_MAX_DATA_ROWS = 20_000;
|
|
const DEFAULT_SAMPLE_ROWS = 8;
|
|
const DEFAULT_MAX_UNCOMPRESSED_BYTES = 200 * 1024 * 1024;
|
|
const DEFAULT_MAX_ZIP_ENTRIES = 5_000;
|
|
|
|
function asPositiveInteger(value, fallback, max = Number.MAX_SAFE_INTEGER) {
|
|
const number = Number(value);
|
|
if (!Number.isFinite(number) || number <= 0) return fallback;
|
|
return Math.min(Math.floor(number), max);
|
|
}
|
|
|
|
function normalizeText(value) {
|
|
return String(value ?? '').normalize('NFKC').trim();
|
|
}
|
|
|
|
function cellValue(cell) {
|
|
const value = cell?.value;
|
|
if (value == null) return null;
|
|
if (value instanceof Date) return value.toISOString();
|
|
if (typeof value !== 'object') return value;
|
|
if (Array.isArray(value.richText)) return value.richText.map((item) => item.text ?? '').join('');
|
|
if ('formula' in value || 'sharedFormula' in value) return value.result ?? null;
|
|
if ('text' in value) return value.text;
|
|
if ('hyperlink' in value) return value.text ?? value.hyperlink;
|
|
if ('error' in value) return value.error;
|
|
return String(value);
|
|
}
|
|
|
|
function cellFormula(cell) {
|
|
const value = cell?.value;
|
|
if (!value || typeof value !== 'object') return null;
|
|
return value.formula ?? value.sharedFormula ?? null;
|
|
}
|
|
|
|
function isBlank(value) {
|
|
return value == null || (typeof value === 'string' && !value.trim());
|
|
}
|
|
|
|
function asNumber(value) {
|
|
if (typeof value === 'number' && Number.isFinite(value)) return value;
|
|
if (typeof value !== 'string') return null;
|
|
const normalized = value.replace(/[,%￥¥$,\s]/g, '');
|
|
if (!normalized || !/^-?\d+(?:\.\d+)?$/.test(normalized)) return null;
|
|
const number = Number(normalized);
|
|
if (!Number.isFinite(number)) return null;
|
|
return value.includes('%') ? number / 100 : number;
|
|
}
|
|
|
|
function safeRelativeWorkbookPath(workspaceRoot, requestedPath) {
|
|
const relative = normalizeText(requestedPath).replace(/\\/g, '/');
|
|
if (!relative || path.posix.isAbsolute(relative) || relative.split('/').includes('..')) {
|
|
throw Object.assign(new Error('Excel 文件路径必须是当前用户工作区内的相对路径'), { code: 'INVALID_PATH' });
|
|
}
|
|
if (path.extname(relative).toLowerCase() !== '.xlsx') {
|
|
throw Object.assign(new Error('Phase 1 只支持 .xlsx 文件'), { code: 'UNSUPPORTED_FORMAT' });
|
|
}
|
|
const root = path.resolve(workspaceRoot);
|
|
const absolute = path.resolve(root, relative);
|
|
if (absolute !== root && !absolute.startsWith(`${root}${path.sep}`)) {
|
|
throw Object.assign(new Error('Excel 文件路径越出当前用户工作区'), { code: 'INVALID_PATH' });
|
|
}
|
|
return { absolute, relative };
|
|
}
|
|
|
|
function isWithinRoot(root, candidate) {
|
|
return candidate === root || candidate.startsWith(`${root}${path.sep}`);
|
|
}
|
|
|
|
function safeSourceDownloadUrl(value) {
|
|
const raw = normalizeText(value);
|
|
if (!raw) return null;
|
|
let parsed;
|
|
try {
|
|
parsed = new URL(raw, 'http://local.invalid');
|
|
} catch {
|
|
throw Object.assign(new Error('Excel 原始文件下载地址无效'), { code: 'INVALID_SOURCE_DOWNLOAD_URL' });
|
|
}
|
|
if (parsed.origin !== 'http://local.invalid'
|
|
|| !parsed.pathname.startsWith('/api/mindspace/v1/assets/')
|
|
|| !parsed.pathname.endsWith('/download')) {
|
|
throw Object.assign(new Error('Excel 原始文件下载地址必须是受保护的站内资产下载路径'), {
|
|
code: 'INVALID_SOURCE_DOWNLOAD_URL',
|
|
});
|
|
}
|
|
return `${parsed.pathname}${parsed.search}`;
|
|
}
|
|
|
|
function validateZipBudget(buffer, {
|
|
maxUncompressedBytes = DEFAULT_MAX_UNCOMPRESSED_BYTES,
|
|
maxEntries = DEFAULT_MAX_ZIP_ENTRIES,
|
|
} = {}) {
|
|
const eocdSignature = Buffer.from([0x50, 0x4b, 0x05, 0x06]);
|
|
const minimumEocdOffset = Math.max(0, buffer.length - 65_557);
|
|
const eocdOffset = buffer.lastIndexOf(eocdSignature);
|
|
if (eocdOffset < minimumEocdOffset || eocdOffset + 22 > buffer.length) {
|
|
throw Object.assign(new Error('文件不是有效的 .xlsx ZIP 工作簿'), { code: 'INVALID_WORKBOOK' });
|
|
}
|
|
const declaredEntries = buffer.readUInt16LE(eocdOffset + 10);
|
|
const centralDirectoryOffset = buffer.readUInt32LE(eocdOffset + 16);
|
|
if (declaredEntries === 0xffff || centralDirectoryOffset === 0xffffffff) {
|
|
throw Object.assign(new Error('Phase 1 不支持 ZIP64 Excel 文件'), { code: 'UNSUPPORTED_FORMAT' });
|
|
}
|
|
let entries = 0;
|
|
let uncompressedBytes = 0;
|
|
let offset = centralDirectoryOffset;
|
|
while (entries < declaredEntries) {
|
|
if (offset + 46 > eocdOffset || buffer.readUInt32LE(offset) !== 0x02014b50) {
|
|
throw Object.assign(new Error('Excel ZIP 中央目录损坏'), { code: 'INVALID_WORKBOOK' });
|
|
}
|
|
const size = buffer.readUInt32LE(offset + 24);
|
|
const nameLength = buffer.readUInt16LE(offset + 28);
|
|
const extraLength = buffer.readUInt16LE(offset + 30);
|
|
const commentLength = buffer.readUInt16LE(offset + 32);
|
|
if (size === 0xffffffff) {
|
|
throw Object.assign(new Error('Phase 1 不支持 ZIP64 Excel 文件'), { code: 'UNSUPPORTED_FORMAT' });
|
|
}
|
|
entries += 1;
|
|
uncompressedBytes += size;
|
|
if (entries > maxEntries || uncompressedBytes > maxUncompressedBytes) {
|
|
throw Object.assign(
|
|
new Error(`Excel 解压规模超过安全上限：${entries} entries, ${uncompressedBytes} bytes`),
|
|
{ code: 'WORKBOOK_TOO_LARGE', detail: { entries, uncompressedBytes, maxEntries, maxUncompressedBytes } },
|
|
);
|
|
}
|
|
offset += 46 + nameLength + extraLength + commentLength;
|
|
}
|
|
if (!entries || offset > eocdOffset) {
|
|
throw Object.assign(new Error('文件不是有效的 .xlsx ZIP 工作簿'), { code: 'INVALID_WORKBOOK' });
|
|
}
|
|
return { entries, uncompressedBytes };
|
|
}
|
|
|
|
async function normalizeMainNamespacePrefixes(buffer) {
|
|
const zip = await JSZip.loadAsync(buffer);
|
|
let changed = false;
|
|
const mainNamespace = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';
|
|
for (const entry of Object.values(zip.files)) {
|
|
if (entry.dir) continue;
|
|
if (/^xl\/worksheets\/_rels\/.*\.rels$/u.test(entry.name)) {
|
|
const relationships = await entry.async('string');
|
|
const normalizedRelationships = relationships
|
|
.replace(/<Relationship\b[^>]*Type=["'][^"']*(?:comments|vmlDrawing|threadedComment)[^"']*["'][^>]*\/>/giu, '')
|
|
.replace(/Target=(["'])\/xl\//g, 'Target=$1../');
|
|
if (normalizedRelationships !== relationships) {
|
|
zip.file(entry.name, normalizedRelationships);
|
|
changed = true;
|
|
}
|
|
continue;
|
|
}
|
|
if (!entry.name.endsWith('.xml')) continue;
|
|
const xml = await entry.async('string');
|
|
const match = xml.match(new RegExp(`xmlns:([A-Za-z_][\\w.-]*)=["']${mainNamespace}["']`));
|
|
if (!match) continue;
|
|
const prefix = match[1];
|
|
const normalized = xml
|
|
.replace(new RegExp(`<(/?)${prefix}:`, 'g'), '<$1')
|
|
.replace(new RegExp(`xmlns:${prefix}=`, 'g'), 'xmlns=');
|
|
if (normalized !== xml) {
|
|
zip.file(entry.name, normalized);
|
|
changed = true;
|
|
}
|
|
}
|
|
return changed ? zip.generateAsync({ type: 'nodebuffer', compression: 'DEFLATE' }) : buffer;
|
|
}
|
|
|
|
async function assertRealPathWithinWorkspace(workspaceRoot, candidate) {
|
|
const [realRoot, realCandidate] = await Promise.all([
|
|
fs.realpath(path.resolve(workspaceRoot)),
|
|
fs.realpath(candidate),
|
|
]);
|
|
if (!isWithinRoot(realRoot, realCandidate)) {
|
|
throw Object.assign(new Error('Excel 文件路径通过符号链接越出当前用户工作区'), { code: 'INVALID_PATH' });
|
|
}
|
|
}
|
|
|
|
async function prepareSafeOutput(workspaceRoot, outputPath) {
|
|
const root = path.resolve(workspaceRoot);
|
|
const realRoot = await fs.realpath(root);
|
|
let existingAncestor = path.dirname(outputPath);
|
|
while (existingAncestor !== root) {
|
|
try {
|
|
await fs.lstat(existingAncestor);
|
|
break;
|
|
} catch (error) {
|
|
if (error?.code !== 'ENOENT') throw error;
|
|
existingAncestor = path.dirname(existingAncestor);
|
|
}
|
|
}
|
|
const realAncestor = await fs.realpath(existingAncestor);
|
|
if (!isWithinRoot(realRoot, realAncestor)) {
|
|
throw Object.assign(new Error('图表输出目录通过符号链接越出当前用户工作区'), { code: 'INVALID_OUTPUT_PATH' });
|
|
}
|
|
await fs.mkdir(path.dirname(outputPath), { recursive: true });
|
|
const realParent = await fs.realpath(path.dirname(outputPath));
|
|
if (!isWithinRoot(realRoot, realParent)) {
|
|
throw Object.assign(new Error('图表输出目录越出当前用户工作区'), { code: 'INVALID_OUTPUT_PATH' });
|
|
}
|
|
}
|
|
|
|
function safeChartOutputPath(workspaceRoot, requestedPath, sourcePath) {
|
|
const sourceStem = path.basename(sourcePath, path.extname(sourcePath)).replace(/[^\p{L}\p{N}._-]+/gu, '-');
|
|
const fallback = `oa/excel-analysis/${sourceStem || 'workbook'}-chart.svg`;
|
|
const relative = normalizeText(requestedPath || fallback).replace(/\\/g, '/');
|
|
if (
|
|
!relative ||
|
|
path.posix.isAbsolute(relative) ||
|
|
relative.split('/').includes('..') ||
|
|
!/^(?:oa|public)\//.test(relative) ||
|
|
path.extname(relative).toLowerCase() !== '.svg'
|
|
) {
|
|
throw Object.assign(new Error('图表输出必须是当前工作区 oa/ 或 public/ 下的 .svg 相对路径'), { code: 'INVALID_OUTPUT_PATH' });
|
|
}
|
|
const root = path.resolve(workspaceRoot);
|
|
const absolute = path.resolve(root, relative);
|
|
if (!absolute.startsWith(`${root}${path.sep}`)) {
|
|
throw Object.assign(new Error('图表输出路径越出当前用户工作区'), { code: 'INVALID_OUTPUT_PATH' });
|
|
}
|
|
return { absolute, relative };
|
|
}
|
|
|
|
function safeReportOutputPath(workspaceRoot, requestedPath, sourcePath) {
|
|
const sourceStem = path.basename(sourcePath, path.extname(sourcePath)).replace(/[^\p{L}\p{N}._-]+/gu, '-');
|
|
const fallback = `public/${sourceStem || 'workbook'}-analysis.html`;
|
|
const relative = normalizeText(requestedPath || fallback).replace(/\\/g, '/');
|
|
if (
|
|
!relative ||
|
|
path.posix.isAbsolute(relative) ||
|
|
relative.split('/').includes('..') ||
|
|
!/^(?:oa|public)\//.test(relative) ||
|
|
path.extname(relative).toLowerCase() !== '.html'
|
|
) {
|
|
throw Object.assign(new Error('报告输出必须是当前工作区 oa/ 或 public/ 下的 .html 相对路径'), { code: 'INVALID_OUTPUT_PATH' });
|
|
}
|
|
const root = path.resolve(workspaceRoot);
|
|
const absolute = path.resolve(root, relative);
|
|
if (!absolute.startsWith(`${root}${path.sep}`)) {
|
|
throw Object.assign(new Error('报告输出路径越出当前用户工作区'), { code: 'INVALID_OUTPUT_PATH' });
|
|
}
|
|
return { absolute, relative };
|
|
}
|
|
|
|
function detectHeaderRow(worksheet, explicitHeaderRow = null) {
|
|
if (explicitHeaderRow != null) {
|
|
const row = asPositiveInteger(explicitHeaderRow, 1, Math.max(1, worksheet.rowCount));
|
|
return row;
|
|
}
|
|
const upper = Math.min(Math.max(worksheet.rowCount, 1), 20);
|
|
for (let rowNumber = 1; rowNumber <= upper; rowNumber += 1) {
|
|
const row = worksheet.getRow(rowNumber);
|
|
let nonEmpty = 0;
|
|
const uniqueValues = new Set();
|
|
for (let column = 1; column <= Math.max(worksheet.columnCount, 1); column += 1) {
|
|
const value = cellValue(row.getCell(column));
|
|
if (!isBlank(value)) {
|
|
nonEmpty += 1;
|
|
uniqueValues.add(normalizeText(value));
|
|
}
|
|
}
|
|
// ExcelJS exposes a merged title value through every cell in the merge.
|
|
// Requiring more than one distinct value prevents title bands from being
|
|
// mistaken for tabular headers.
|
|
if (nonEmpty >= 2 && uniqueValues.size >= 2) return rowNumber;
|
|
}
|
|
return 1;
|
|
}
|
|
|
|
function buildHeaders(worksheet, headerRow) {
|
|
const used = new Map();
|
|
const headers = [];
|
|
const width = Math.max(worksheet.columnCount, 1);
|
|
for (let column = 1; column <= width; column += 1) {
|
|
const raw = normalizeText(cellValue(worksheet.getRow(headerRow).getCell(column)));
|
|
const base = raw || `column_${column}`;
|
|
const seen = (used.get(base) ?? 0) + 1;
|
|
used.set(base, seen);
|
|
headers.push(seen === 1 ? base : `${base}_${seen}`);
|
|
}
|
|
return headers;
|
|
}
|
|
|
|
function worksheetByName(workbook, sheetName) {
|
|
if (sheetName) {
|
|
const worksheet = workbook.getWorksheet(sheetName);
|
|
if (!worksheet) throw Object.assign(new Error(`Sheet 不存在：${sheetName}`), { code: 'SHEET_NOT_FOUND' });
|
|
return worksheet;
|
|
}
|
|
const worksheet = workbook.worksheets[0];
|
|
if (!worksheet) throw Object.assign(new Error('工作簿没有可分析的 Sheet'), { code: 'EMPTY_WORKBOOK' });
|
|
return worksheet;
|
|
}
|
|
|
|
function loadRows(worksheet, { headerRow: explicitHeaderRow, maxRows, maxCells } = {}) {
|
|
const headerRow = detectHeaderRow(worksheet, explicitHeaderRow);
|
|
const headers = buildHeaders(worksheet, headerRow);
|
|
const rowLimit = asPositiveInteger(maxRows, DEFAULT_MAX_DATA_ROWS, DEFAULT_MAX_DATA_ROWS);
|
|
const cellLimit = asPositiveInteger(maxCells, DEFAULT_MAX_SCAN_CELLS, DEFAULT_MAX_SCAN_CELLS);
|
|
const possibleCells = Math.max(0, worksheet.rowCount - headerRow) * headers.length;
|
|
if (possibleCells > cellLimit) {
|
|
throw Object.assign(
|
|
new Error(`Sheet 数据区域超过 Phase 1 扫描上限：${possibleCells}/${cellLimit} 单元格`),
|
|
{ code: 'WORKBOOK_TOO_LARGE', detail: { possibleCells, maxCells: cellLimit } },
|
|
);
|
|
}
|
|
const rows = [];
|
|
let formulaCount = 0;
|
|
for (let rowNumber = headerRow + 1; rowNumber <= worksheet.rowCount && rows.length < rowLimit; rowNumber += 1) {
|
|
const row = worksheet.getRow(rowNumber);
|
|
const record = {};
|
|
let nonEmpty = false;
|
|
for (let column = 1; column <= headers.length; column += 1) {
|
|
const cell = row.getCell(column);
|
|
const value = cellValue(cell);
|
|
if (!isBlank(value)) nonEmpty = true;
|
|
if (cellFormula(cell)) formulaCount += 1;
|
|
record[headers[column - 1]] = value;
|
|
}
|
|
if (nonEmpty) rows.push(record);
|
|
}
|
|
return { headerRow, headers, rows, formulaCount, truncated: worksheet.rowCount - headerRow > rowLimit };
|
|
}
|
|
|
|
function profileColumn(rows, name) {
|
|
const values = rows.map((row) => row[name]);
|
|
const present = values.filter((value) => !isBlank(value));
|
|
const numeric = present.map(asNumber).filter((value) => value != null);
|
|
const dates = present.filter((value) => typeof value === 'string' && /^\d{4}-\d{2}-\d{2}T/.test(value));
|
|
const unique = new Set(present.map((value) => JSON.stringify(value)));
|
|
let inferredType = 'string';
|
|
if (present.length && numeric.length / present.length >= 0.8) inferredType = 'number';
|
|
else if (present.length && dates.length / present.length >= 0.8) inferredType = 'date';
|
|
else if (present.length && present.every((value) => typeof value === 'boolean')) inferredType = 'boolean';
|
|
const sorted = [...numeric].sort((a, b) => a - b);
|
|
const sum = numeric.reduce((total, value) => total + value, 0);
|
|
return {
|
|
name,
|
|
inferredType,
|
|
nonEmpty: present.length,
|
|
missing: values.length - present.length,
|
|
missingRate: values.length ? Number(((values.length - present.length) / values.length).toFixed(4)) : 0,
|
|
unique: unique.size,
|
|
...(numeric.length
|
|
? {
|
|
numericCount: numeric.length,
|
|
min: sorted[0],
|
|
max: sorted.at(-1),
|
|
mean: Number((sum / numeric.length).toFixed(6)),
|
|
sum: Number(sum.toFixed(6)),
|
|
}
|
|
: {}),
|
|
samples: [...unique].slice(0, 5).map((value) => JSON.parse(value)),
|
|
};
|
|
}
|
|
|
|
function aggregateRows(rows, {
|
|
groupBy,
|
|
metric,
|
|
aggregation = 'sum',
|
|
limit = 20,
|
|
sort = 'value_desc',
|
|
} = {}) {
|
|
if (!['sum', 'avg', 'min', 'max', 'count'].includes(aggregation)) {
|
|
throw Object.assign(new Error(`不支持的聚合方式：${aggregation}`), { code: 'INVALID_AGGREGATION' });
|
|
}
|
|
if (!groupBy || !rows.length || !(groupBy in rows[0])) {
|
|
throw Object.assign(new Error(`分组字段不存在：${groupBy ?? ''}`), { code: 'COLUMN_NOT_FOUND' });
|
|
}
|
|
if (aggregation !== 'count' && (!metric || !(metric in rows[0]))) {
|
|
throw Object.assign(new Error(`指标字段不存在：${metric ?? ''}`), { code: 'COLUMN_NOT_FOUND' });
|
|
}
|
|
const groups = new Map();
|
|
for (const row of rows) {
|
|
const key = normalizeText(row[groupBy]) || '(空)';
|
|
const current = groups.get(key) ?? { key, values: [], count: 0 };
|
|
current.count += 1;
|
|
const number = asNumber(row[metric]);
|
|
if (number != null) current.values.push(number);
|
|
groups.set(key, current);
|
|
}
|
|
const items = [...groups.values()].map((group) => {
|
|
let value = group.count;
|
|
if (aggregation !== 'count') {
|
|
if (!group.values.length) value = null;
|
|
else if (aggregation === 'avg') value = group.values.reduce((a, b) => a + b, 0) / group.values.length;
|
|
else if (aggregation === 'min') value = Math.min(...group.values);
|
|
else if (aggregation === 'max') value = Math.max(...group.values);
|
|
else value = group.values.reduce((a, b) => a + b, 0);
|
|
}
|
|
return { group: group.key, value: value == null ? null : Number(value.toFixed(6)), rows: group.count };
|
|
});
|
|
if (sort === 'group_asc') {
|
|
items.sort((a, b) => a.group.localeCompare(b.group, 'zh-CN', { numeric: true }));
|
|
} else {
|
|
items.sort((a, b) => (b.value ?? Number.NEGATIVE_INFINITY) - (a.value ?? Number.NEGATIVE_INFINITY));
|
|
}
|
|
return items.slice(0, asPositiveInteger(limit, 20, 100));
|
|
}
|
|
|
|
function correlation(rows, xName, yName) {
|
|
if (!xName || !yName || !rows.length || !(xName in rows[0]) || !(yName in rows[0])) {
|
|
throw Object.assign(new Error('相关性分析要求两个存在的数值字段'), { code: 'COLUMN_NOT_FOUND' });
|
|
}
|
|
const pairs = rows
|
|
.map((row) => [asNumber(row[xName]), asNumber(row[yName])])
|
|
.filter(([x, y]) => x != null && y != null);
|
|
if (pairs.length < 2) return { x: xName, y: yName, pairs: pairs.length, correlation: null };
|
|
const xMean = pairs.reduce((sum, [x]) => sum + x, 0) / pairs.length;
|
|
const yMean = pairs.reduce((sum, [, y]) => sum + y, 0) / pairs.length;
|
|
let numerator = 0;
|
|
let xSquared = 0;
|
|
let ySquared = 0;
|
|
for (const [x, y] of pairs) {
|
|
numerator += (x - xMean) * (y - yMean);
|
|
xSquared += (x - xMean) ** 2;
|
|
ySquared += (y - yMean) ** 2;
|
|
}
|
|
const denominator = Math.sqrt(xSquared * ySquared);
|
|
return {
|
|
x: xName,
|
|
y: yName,
|
|
pairs: pairs.length,
|
|
correlation: denominator ? Number((numerator / denominator).toFixed(6)) : null,
|
|
};
|
|
}
|
|
|
|
function escapeXml(value) {
|
|
return String(value ?? '')
|
|
.replace(/&/g, '&')
|
|
.replace(/</g, '<')
|
|
.replace(/>/g, '>')
|
|
.replace(/"/g, '"');
|
|
}
|
|
|
|
function chartSvg(items, { type = 'bar', title = 'Excel 分析图表' } = {}) {
|
|
const width = 960;
|
|
const height = 540;
|
|
const margin = { top: 70, right: 40, bottom: 110, left: 90 };
|
|
const plotWidth = width - margin.left - margin.right;
|
|
const plotHeight = height - margin.top - margin.bottom;
|
|
const values = items.map((item) => Number(item.value ?? 0));
|
|
const max = Math.max(...values.map((value) => Math.abs(value)), 1);
|
|
const points = items.map((item, index) => {
|
|
const x = margin.left + ((index + 0.5) * plotWidth) / Math.max(items.length, 1);
|
|
const y = margin.top + plotHeight - (Math.max(0, Number(item.value ?? 0)) / max) * plotHeight;
|
|
return { ...item, x, y };
|
|
});
|
|
const marks = type === 'line'
|
|
? `<polyline fill="none" stroke="#2563eb" stroke-width="4" points="${points.map((point) => `${point.x},${point.y}`).join(' ')}"/>${points.map((point) => `<circle cx="${point.x}" cy="${point.y}" r="5" fill="#2563eb"/>`).join('')}`
|
|
: points.map((point, index) => {
|
|
const slot = plotWidth / Math.max(items.length, 1);
|
|
const barWidth = Math.max(8, slot * 0.68);
|
|
return `<rect x="${point.x - barWidth / 2}" y="${point.y}" width="${barWidth}" height="${margin.top + plotHeight - point.y}" rx="4" fill="${index % 2 ? '#60a5fa' : '#2563eb'}"/>`;
|
|
}).join('');
|
|
const labels = points.map((point) => `<text x="${point.x}" y="${margin.top + plotHeight + 22}" text-anchor="end" transform="rotate(-35 ${point.x} ${margin.top + plotHeight + 22})" font-size="13" fill="#334155">${escapeXml(point.group).slice(0, 24)}</text><text x="${point.x}" y="${Math.max(margin.top + 14, point.y - 8)}" text-anchor="middle" font-size="12" fill="#0f172a">${escapeXml(point.value)}</text>`).join('');
|
|
return `<?xml version="1.0" encoding="UTF-8"?>\n<svg xmlns="http://www.w3.org/2000/svg" width="${width}" height="${height}" viewBox="0 0 ${width} ${height}"><rect width="100%" height="100%" fill="#fff"/><text x="${width / 2}" y="38" text-anchor="middle" font-size="24" font-family="sans-serif" fill="#0f172a">${escapeXml(title)}</text><line x1="${margin.left}" y1="${margin.top + plotHeight}" x2="${margin.left + plotWidth}" y2="${margin.top + plotHeight}" stroke="#94a3b8"/><line x1="${margin.left}" y1="${margin.top}" x2="${margin.left}" y2="${margin.top + plotHeight}" stroke="#94a3b8"/>${marks}${labels}</svg>\n`;
|
|
}
|
|
|
|
function roundNumber(value, digits = 6) {
|
|
return Number(Number(value || 0).toFixed(digits));
|
|
}
|
|
|
|
function requireColumn(loaded, name, label = '字段') {
|
|
const normalized = normalizeText(name);
|
|
if (!normalized || !loaded.headers.includes(normalized)) {
|
|
throw Object.assign(new Error(`${label}不存在：${normalized}`), { code: 'COLUMN_NOT_FOUND' });
|
|
}
|
|
return normalized;
|
|
}
|
|
|
|
function sumColumn(rows, name) {
|
|
return rows.reduce((sum, row) => sum + (asNumber(row[name]) ?? 0), 0);
|
|
}
|
|
|
|
function buildReportModel(workbook, worksheet, loaded, args = {}) {
|
|
if (loaded.truncated) {
|
|
throw Object.assign(new Error('报告禁止基于截断数据生成'), { code: 'REPORT_SOURCE_TRUNCATED' });
|
|
}
|
|
const metric = requireColumn(loaded, args.metric, '主指标');
|
|
const profitMetric = args.profit_metric ? requireColumn(loaded, args.profit_metric, '利润指标') : null;
|
|
const quantityMetric = args.quantity_metric ? requireColumn(loaded, args.quantity_metric, '数量指标') : null;
|
|
const orderId = args.order_id ? requireColumn(loaded, args.order_id, '订单 ID 字段') : null;
|
|
const dimensions = [...new Set((Array.isArray(args.dimensions) ? args.dimensions : []).map(normalizeText).filter(Boolean))];
|
|
if (!dimensions.length || dimensions.length > 6) {
|
|
throw Object.assign(new Error('报告要求 1-6 个分析维度'), { code: 'INVALID_REPORT_DIMENSIONS' });
|
|
}
|
|
dimensions.forEach((name) => requireColumn(loaded, name, '分析维度'));
|
|
|
|
const usedColumns = new Set([metric, profitMetric, quantityMetric, orderId, ...dimensions].filter(Boolean));
|
|
const contextFields = loaded.headers.filter((header) => !usedColumns.has(header));
|
|
const summaryMetrics = contextFields
|
|
.filter((header) => profileColumn(loaded.rows, header).inferredType === 'number')
|
|
.map((name) => ({ name, total: roundNumber(sumColumn(loaded.rows, name)) }));
|
|
const monthField = contextFields.find((header) => /月/.test(header));
|
|
let periodLabel = null;
|
|
if (monthField) {
|
|
const raw = normalizeText(loaded.rows.find((row) => !isBlank(row[monthField]))?.[monthField]);
|
|
const match = raw?.match(/^(\d{4})-(\d{1,2})/);
|
|
if (match) periodLabel = `${match[1]}年${Number(match[2])}月`;
|
|
}
|
|
|
|
const errors = [];
|
|
const warnings = [];
|
|
const joins = new Map();
|
|
for (const join of Array.isArray(args.joins) ? args.joins : []) {
|
|
const dimension = normalizeText(join?.dimension);
|
|
if (!dimensions.includes(dimension)) {
|
|
throw Object.assign(new Error(`主数据关联维度未被选中：${dimension}`), { code: 'INVALID_REPORT_JOIN' });
|
|
}
|
|
const joinSheet = normalizeText(join?.sheet);
|
|
if (!joinSheet) {
|
|
throw Object.assign(new Error(`主数据关联 ${dimension} 缺少 Sheet`), { code: 'INVALID_REPORT_JOIN' });
|
|
}
|
|
const masterSheet = worksheetByName(workbook, joinSheet);
|
|
const master = loadRows(masterSheet, { headerRow: join?.header_row });
|
|
const key = requireColumn(master, join?.key, '主数据键');
|
|
const fields = [...new Set((Array.isArray(join?.fields) ? join.fields : []).map(normalizeText).filter(Boolean))];
|
|
if (!fields.length || fields.length > 6) {
|
|
throw Object.assign(new Error(`主数据关联 ${dimension} 要求 1-6 个返回字段`), { code: 'INVALID_REPORT_JOIN' });
|
|
}
|
|
fields.forEach((field) => requireColumn(master, field, '主数据字段'));
|
|
const records = new Map();
|
|
for (const row of master.rows) {
|
|
const id = normalizeText(row[key]);
|
|
if (!id) continue;
|
|
if (records.has(id)) {
|
|
errors.push({ code: 'DUPLICATE_MASTER_KEY', dimension, key: id, sheet: masterSheet.name });
|
|
continue;
|
|
}
|
|
records.set(id, Object.fromEntries(fields.map((field) => [field, row[field]])));
|
|
}
|
|
joins.set(dimension, { sheet: masterSheet.name, key, fields, records });
|
|
}
|
|
|
|
const totalMetric = sumColumn(loaded.rows, metric);
|
|
const totalProfit = profitMetric ? sumColumn(loaded.rows, profitMetric) : null;
|
|
const totalQuantity = quantityMetric ? sumColumn(loaded.rows, quantityMetric) : null;
|
|
const uniqueOrders = orderId
|
|
? new Set(loaded.rows.map((row) => normalizeText(row[orderId])).filter(Boolean)).size
|
|
: loaded.rows.length;
|
|
const sections = [];
|
|
const topAverage = {};
|
|
|
|
for (const dimension of dimensions) {
|
|
const grouped = new Map();
|
|
const rowContextFields = loaded.headers.filter((header) => (
|
|
![metric, profitMetric, quantityMetric, orderId, dimension].filter(Boolean).includes(header)
|
|
));
|
|
for (const row of loaded.rows) {
|
|
const key = normalizeText(row[dimension]) || '(空)';
|
|
let current = grouped.get(key);
|
|
if (!current) {
|
|
current = {
|
|
group: key,
|
|
value: 0,
|
|
profit: 0,
|
|
quantity: 0,
|
|
rows: 0,
|
|
orderIds: new Set(),
|
|
context: {},
|
|
};
|
|
for (const field of rowContextFields) {
|
|
const profile = profileColumn(loaded.rows, field);
|
|
const raw = row[field];
|
|
current.context[field] = profile.inferredType === 'number' ? asNumber(raw) : normalizeText(raw) || '';
|
|
}
|
|
}
|
|
current.value += asNumber(row[metric]) ?? 0;
|
|
if (profitMetric) current.profit += asNumber(row[profitMetric]) ?? 0;
|
|
if (quantityMetric) current.quantity += asNumber(row[quantityMetric]) ?? 0;
|
|
current.rows += 1;
|
|
if (orderId && !isBlank(row[orderId])) current.orderIds.add(normalizeText(row[orderId]));
|
|
grouped.set(key, current);
|
|
}
|
|
if (grouped.size > 200) {
|
|
throw Object.assign(new Error(`维度 ${dimension} 的分组数 ${grouped.size} 超过报告上限 200`), { code: 'REPORT_DIMENSION_TOO_LARGE' });
|
|
}
|
|
const join = joins.get(dimension);
|
|
const items = [...grouped.values()].map((item) => {
|
|
const orderCount = orderId ? item.orderIds.size : item.rows;
|
|
const masterData = join?.records.get(item.group) ?? null;
|
|
if (join && item.group !== '(空)' && !masterData) {
|
|
errors.push({ code: 'MISSING_MASTER_DATA', dimension, key: item.group, sheet: join.sheet });
|
|
}
|
|
return {
|
|
group: item.group,
|
|
value: roundNumber(item.value),
|
|
...(profitMetric ? { profit: roundNumber(item.profit), margin: item.value ? roundNumber(item.profit / item.value) : null } : {}),
|
|
...(quantityMetric ? { quantity: roundNumber(item.quantity) } : {}),
|
|
rows: item.rows,
|
|
orders: orderCount,
|
|
averagePerOrder: orderCount ? roundNumber(item.value / orderCount) : null,
|
|
...(masterData ? { masterData } : {}),
|
|
context: item.context,
|
|
};
|
|
}).sort((a, b) => b.value - a.value || a.group.localeCompare(b.group, 'zh-CN', { numeric: true }));
|
|
const reconciled = items.reduce((sum, item) => sum + item.value, 0);
|
|
if (Math.abs(reconciled - totalMetric) > Math.max(0.01, Math.abs(totalMetric) * 1e-9)) {
|
|
errors.push({ code: 'DIMENSION_TOTAL_MISMATCH', dimension, expected: roundNumber(totalMetric), actual: roundNumber(reconciled) });
|
|
}
|
|
const averageWinner = [...items].sort((a, b) => (b.averagePerOrder ?? -Infinity) - (a.averagePerOrder ?? -Infinity))[0] ?? null;
|
|
if (averageWinner) topAverage[dimension] = { group: averageWinner.group, value: averageWinner.averagePerOrder };
|
|
sections.push({ dimension, items, total: roundNumber(reconciled) });
|
|
}
|
|
|
|
const regionField = dimensions.includes('区域') ? '区域' : null;
|
|
const cityField = dimensions.includes('城市') ? '城市' : null;
|
|
const productField = dimensions.includes('产品') ? '产品' : null;
|
|
const regionCityCounts = {};
|
|
const productRegionCounts = {};
|
|
const productCityCounts = {};
|
|
const productOnlyCity = {};
|
|
if (regionField && cityField) {
|
|
const regionCitySets = new Map();
|
|
for (const row of loaded.rows) {
|
|
const region = normalizeText(row[regionField]);
|
|
const city = normalizeText(row[cityField]);
|
|
if (!region || !city) continue;
|
|
const set = regionCitySets.get(region) ?? new Set();
|
|
set.add(city);
|
|
regionCitySets.set(region, set);
|
|
}
|
|
for (const [region, cities] of regionCitySets.entries()) {
|
|
regionCityCounts[region] = cities.size;
|
|
}
|
|
}
|
|
if (productField) {
|
|
const productRegions = new Map();
|
|
const productCities = new Map();
|
|
for (const row of loaded.rows) {
|
|
const product = normalizeText(row[productField]);
|
|
if (!product) continue;
|
|
if (regionField) {
|
|
const region = normalizeText(row[regionField]);
|
|
if (region) {
|
|
const set = productRegions.get(product) ?? new Set();
|
|
set.add(region);
|
|
productRegions.set(product, set);
|
|
}
|
|
}
|
|
if (cityField) {
|
|
const city = normalizeText(row[cityField]);
|
|
if (city) {
|
|
const set = productCities.get(product) ?? new Set();
|
|
set.add(city);
|
|
productCities.set(product, set);
|
|
}
|
|
}
|
|
}
|
|
for (const [product, regions] of productRegions.entries()) {
|
|
productRegionCounts[product] = regions.size;
|
|
}
|
|
for (const [product, cities] of productCities.entries()) {
|
|
productCityCounts[product] = cities.size;
|
|
if (cities.size === 1) productOnlyCity[product] = [...cities][0];
|
|
}
|
|
}
|
|
|
|
const missingMetric = loaded.rows.filter((row) => asNumber(row[metric]) == null).length;
|
|
if (missingMetric) warnings.push({ code: 'MISSING_METRIC_VALUES', metric, rows: missingMetric });
|
|
if (orderId && uniqueOrders < loaded.rows.length) {
|
|
warnings.push({ code: 'DUPLICATE_ORDER_IDS', field: orderId, rows: loaded.rows.length, unique: uniqueOrders });
|
|
}
|
|
const validation = { valid: errors.length === 0, errors, warnings };
|
|
return {
|
|
schemaVersion: 1,
|
|
source: {
|
|
path: args.path,
|
|
sheet: worksheet.name,
|
|
headerRow: loaded.headerRow,
|
|
rows: loaded.rows.length,
|
|
...(args.source_download_url
|
|
? { downloadUrl: safeSourceDownloadUrl(args.source_download_url) }
|
|
: {}),
|
|
},
|
|
fields: { metric, profitMetric, quantityMetric, orderId, dimensions },
|
|
totals: {
|
|
metric: roundNumber(totalMetric),
|
|
...(profitMetric ? { profit: roundNumber(totalProfit), margin: totalMetric ? roundNumber(totalProfit / totalMetric) : null } : {}),
|
|
...(quantityMetric ? { quantity: roundNumber(totalQuantity) } : {}),
|
|
rows: loaded.rows.length,
|
|
orders: uniqueOrders,
|
|
},
|
|
sections,
|
|
facts: { topAverage },
|
|
meta: {
|
|
periodLabel,
|
|
summaryMetrics,
|
|
contextFields,
|
|
regionCityCounts,
|
|
productRegionCounts,
|
|
productCityCounts,
|
|
productOnlyCity,
|
|
},
|
|
validation,
|
|
};
|
|
}
|
|
|
|
export function createExcelAnalysisEngine({ workspaceRoot, maxFileBytes = DEFAULT_MAX_FILE_BYTES } = {}) {
|
|
if (!workspaceRoot) throw new Error('Excel engine requires workspaceRoot');
|
|
|
|
async function loadWorkbook(requestedPath) {
|
|
const resolved = safeRelativeWorkbookPath(workspaceRoot, requestedPath);
|
|
const stat = await fs.stat(resolved.absolute).catch(() => null);
|
|
if (!stat?.isFile()) throw Object.assign(new Error(`Excel 文件不存在：${resolved.relative}`), { code: 'FILE_NOT_FOUND' });
|
|
await assertRealPathWithinWorkspace(workspaceRoot, resolved.absolute);
|
|
if (stat.size > maxFileBytes) {
|
|
throw Object.assign(new Error(`Excel 文件超过大小上限：${stat.size}/${maxFileBytes}`), { code: 'FILE_TOO_LARGE' });
|
|
}
|
|
const buffer = await fs.readFile(resolved.absolute);
|
|
validateZipBudget(buffer);
|
|
const workbook = new ExcelJS.Workbook();
|
|
await workbook.xlsx.load(await normalizeMainNamespacePrefixes(buffer));
|
|
return { workbook, source: resolved, sizeBytes: stat.size };
|
|
}
|
|
|
|
return {
|
|
async inspect(args = {}) {
|
|
const { workbook, source, sizeBytes } = await loadWorkbook(args.path);
|
|
const sampleRows = asPositiveInteger(args.sample_rows, DEFAULT_SAMPLE_ROWS, 20);
|
|
const sheets = workbook.worksheets.map((worksheet) => {
|
|
const loaded = loadRows(worksheet, {
|
|
headerRow: args.header_row,
|
|
maxRows: args.max_rows,
|
|
maxCells: args.max_cells,
|
|
});
|
|
const profiles = loaded.headers.map((name) => profileColumn(loaded.rows, name));
|
|
return {
|
|
name: worksheet.name,
|
|
state: worksheet.state ?? 'visible',
|
|
rowCount: worksheet.rowCount,
|
|
columnCount: worksheet.columnCount,
|
|
headerRow: loaded.headerRow,
|
|
headers: loaded.headers,
|
|
formulaCount: loaded.formulaCount,
|
|
mergedRanges: worksheet.model?.merges?.length ?? 0,
|
|
hiddenRows: Array.from({ length: worksheet.rowCount }, (_, index) => worksheet.getRow(index + 1)).filter((row) => row.hidden).length,
|
|
hiddenColumns: Array.from({ length: worksheet.columnCount }, (_, index) => worksheet.getColumn(index + 1)).filter((column) => column.hidden).length,
|
|
dimensions: profiles.filter((item) => item.inferredType !== 'number' && item.unique <= Math.max(50, Math.ceil(loaded.rows.length * 0.8))).map((item) => item.name),
|
|
metrics: profiles.filter((item) => item.inferredType === 'number').map((item) => item.name),
|
|
profiles,
|
|
samples: loaded.rows.slice(0, sampleRows),
|
|
truncated: loaded.truncated,
|
|
};
|
|
});
|
|
return { format: 'xlsx', path: source.relative, sizeBytes, sheets };
|
|
},
|
|
|
|
async analyze(args = {}) {
|
|
const { workbook, source } = await loadWorkbook(args.path);
|
|
const worksheet = worksheetByName(workbook, args.sheet);
|
|
const loaded = loadRows(worksheet, {
|
|
headerRow: args.header_row,
|
|
maxRows: args.max_rows,
|
|
maxCells: args.max_cells,
|
|
});
|
|
const operation = normalizeText(args.operation || 'summary').toLowerCase();
|
|
let result;
|
|
if (operation === 'summary') {
|
|
result = { columns: loaded.headers.map((name) => profileColumn(loaded.rows, name)) };
|
|
} else if (operation === 'correlation') {
|
|
result = correlation(loaded.rows, args.x, args.y);
|
|
} else if (['group_by', 'top_n', 'trend'].includes(operation)) {
|
|
result = {
|
|
groupBy: args.group_by,
|
|
metric: args.metric ?? null,
|
|
aggregation: args.aggregation ?? 'sum',
|
|
items: aggregateRows(loaded.rows, {
|
|
groupBy: args.group_by,
|
|
metric: args.metric,
|
|
aggregation: args.aggregation,
|
|
limit: args.limit,
|
|
sort: operation === 'trend' ? 'group_asc' : 'value_desc',
|
|
}),
|
|
};
|
|
} else {
|
|
throw Object.assign(new Error(`不支持的分析操作：${operation}`), { code: 'INVALID_OPERATION' });
|
|
}
|
|
return { path: source.relative, sheet: worksheet.name, operation, rowCount: loaded.rows.length, truncated: loaded.truncated, result };
|
|
},
|
|
|
|
async chart(args = {}) {
|
|
const { workbook, source } = await loadWorkbook(args.path);
|
|
const worksheet = worksheetByName(workbook, args.sheet);
|
|
const loaded = loadRows(worksheet, {
|
|
headerRow: args.header_row,
|
|
maxRows: args.max_rows,
|
|
maxCells: args.max_cells,
|
|
});
|
|
const type = normalizeText(args.type || 'bar').toLowerCase();
|
|
if (!['bar', 'line'].includes(type)) {
|
|
throw Object.assign(new Error('Phase 1 图表仅支持 bar 或 line'), { code: 'INVALID_CHART_TYPE' });
|
|
}
|
|
const items = aggregateRows(loaded.rows, {
|
|
groupBy: args.group_by,
|
|
metric: args.metric,
|
|
aggregation: args.aggregation,
|
|
limit: args.limit,
|
|
sort: type === 'line' ? 'group_asc' : 'value_desc',
|
|
});
|
|
const output = safeChartOutputPath(workspaceRoot, args.output_path, source.relative);
|
|
await prepareSafeOutput(workspaceRoot, output.absolute);
|
|
const handle = await fs.open(
|
|
output.absolute,
|
|
fsConstants.O_WRONLY | fsConstants.O_CREAT | fsConstants.O_TRUNC | fsConstants.O_NOFOLLOW,
|
|
0o600,
|
|
);
|
|
try {
|
|
await handle.writeFile(chartSvg(items, { type, title: args.title || `${worksheet.name} 分析` }), 'utf8');
|
|
} finally {
|
|
await handle.close();
|
|
}
|
|
return { path: source.relative, sheet: worksheet.name, type, outputPath: output.relative, items };
|
|
},
|
|
|
|
async report(args = {}) {
|
|
const { workbook, source } = await loadWorkbook(args.path);
|
|
const worksheet = worksheetByName(workbook, args.sheet);
|
|
const loaded = loadRows(worksheet, {
|
|
headerRow: args.header_row,
|
|
maxRows: args.max_rows,
|
|
maxCells: args.max_cells,
|
|
});
|
|
const model = buildReportModel(workbook, worksheet, loaded, { ...args, path: source.relative });
|
|
if (!model.validation.valid) {
|
|
throw Object.assign(new Error('Excel 报告事实校验失败，未生成报告'), {
|
|
code: 'REPORT_VALIDATION_FAILED',
|
|
detail: model.validation,
|
|
});
|
|
}
|
|
const output = safeReportOutputPath(workspaceRoot, args.output_path, source.relative);
|
|
await prepareSafeOutput(workspaceRoot, output.absolute);
|
|
const handle = await fs.open(
|
|
output.absolute,
|
|
fsConstants.O_WRONLY | fsConstants.O_CREAT | fsConstants.O_TRUNC | fsConstants.O_NOFOLLOW,
|
|
0o600,
|
|
);
|
|
try {
|
|
await handle.writeFile(reportHtml(model, { title: args.title }), 'utf8');
|
|
} finally {
|
|
await handle.close();
|
|
}
|
|
return { path: source.relative, sheet: worksheet.name, outputPath: output.relative, model };
|
|
},
|
|
};
|
|
}
|
|
|
|
export const excelAnalysisInternals = {
|
|
asNumber,
|
|
cellValue,
|
|
detectHeaderRow,
|
|
profileColumn,
|
|
buildReportModel,
|
|
safeRelativeWorkbookPath,
|
|
safeChartOutputPath,
|
|
safeReportOutputPath,
|
|
safeSourceDownloadUrl,
|
|
normalizeMainNamespacePrefixes,
|
|
validateZipBudget,
|
|
};
|