excel-tools

"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.loadWorkbook = loadWorkbook;
exports.saveWorkbook = saveWorkbook;
exports.getSheet = getSheet;
exports.getCellValue = getCellValue;
exports.worksheetToJson = worksheetToJson;
exports.jsonToWorksheet = jsonToWorksheet;
exports.matchesFilters = matchesFilters;
exports.colIndexToLetter = colIndexToLetter;
exports.preview = preview;
const exceljs_1 = __importDefault(require("exceljs"));
const XLSX = __importStar(require("xlsx"));
const path = __importStar(require("path"));
// ─── Loading ──────────────────────────────────────────────────────────────────
/**
 * Load any Excel/CSV format into an ExcelJS Workbook.
 * ExcelJS handles .xlsx/.xlsm/.csv natively.
 * SheetJS converts .xls/.xlsb/.ods/.tsv → xlsx buffer → ExcelJS.
 */
async function loadWorkbook(filePath) {
    const ext = path.extname(filePath).toLowerCase();
    const wb = new exceljs_1.default.Workbook();
    if (ext === ".xlsx" || ext === ".xlsm") {
        await wb.xlsx.readFile(filePath);
    }
    else if (ext === ".csv") {
        await wb.csv.readFile(filePath);
    }
    else {
        // SheetJS → xlsx buffer → ExcelJS
        const sheetJsWb = XLSX.readFile(filePath, { bookVBA: true });
        const buf = XLSX.write(sheetJsWb, { type: "buffer", bookType: "xlsx" });
        await wb.xlsx.load(buf.buffer);
    }
    return wb;
}
/** Save workbook — always as .xlsx */
async function saveWorkbook(wb, filePath) {
    await wb.xlsx.writeFile(filePath);
}
// ─── Sheet helpers ────────────────────────────────────────────────────────────
function getSheet(wb, name) {
    const ws = name ? wb.getWorksheet(name) : wb.worksheets[0];
    if (!ws) {
        const available = wb.worksheets.map((s) => s.name).join(", ");
        return name
            ? `Sheet "${name}" not found. Available: ${available}`
            : "Workbook has no sheets";
    }
    return ws;
}
// ─── Cell value extraction ────────────────────────────────────────────────────
function getCellValue(cell) {
    const val = cell.value;
    if (val === null || val === undefined)
        return null;
    if (val instanceof Date)
        return val.toISOString().split("T")[0];
    if (typeof val === "object") {
        if ("result" in val)
            return val.result ?? null;
        if ("richText" in val)
            return val.richText.map((r) => r.text).join("");
        if ("text" in val)
            return val.text;
        if ("error" in val)
            return `#${val.error}`;
    }
    return val;
}
// ─── Conversion helpers ───────────────────────────────────────────────────────
function worksheetToJson(ws) {
    const headers = [];
    ws.getRow(1).eachCell({ includeEmpty: false }, (cell, col) => {
        headers[col - 1] = String(getCellValue(cell) ?? "");
    });
    if (!headers.length)
        return [];
    const rows = [];
    ws.eachRow((row, rowNum) => {
        if (rowNum === 1)
            return;
        const obj = {};
        let hasData = false;
        headers.forEach((h, i) => {
            if (!h)
                return;
            const v = getCellValue(row.getCell(i + 1));
            obj[h] = v;
            if (v !== null && v !== undefined)
                hasData = true;
        });
        if (hasData)
            rows.push(obj);
    });
    return rows;
}
function jsonToWorksheet(ws, data, opts = {}) {
    if (!data.length)
        return;
    const headers = Object.keys(data[0]);
    const { boldHeaders = true, autoWidth = true, headerFill = "FFE0E0E0" } = opts;
    const hRow = ws.addRow(headers);
    if (boldHeaders) {
        hRow.font = { bold: true };
        hRow.fill = { type: "pattern", pattern: "solid", fgColor: { argb: headerFill } };
    }
    hRow.commit();
    data.forEach((row) => {
        ws.addRow(headers.map((h) => row[h] ?? null)).commit();
    });
    if (autoWidth) {
        ws.columns.forEach((col, i) => {
            const h = headers[i] ?? "";
            const max = Math.max(h.length, ...data.slice(0, 200).map((r) => String(r[h] ?? "").length));
            col.width = Math.min(max + 2, 60);
        });
    }
}
function matchesFilters(row, filters) {
    return filters.every(({ column, operator, value }) => {
        const cell = row[column];
        const cs = String(cell ?? "").toLowerCase();
        const vs = String(value ?? "").toLowerCase();
        switch (operator) {
            case "=": return cs === vs;
            case "!=": return cs !== vs;
            case ">": return Number(cell) > Number(value);
            case "<": return Number(cell) < Number(value);
            case ">=": return Number(cell) >= Number(value);
            case "<=": return Number(cell) <= Number(value);
            case "contains": return cs.includes(vs);
            case "startsWith": return cs.startsWith(vs);
            case "endsWith": return cs.endsWith(vs);
            case "regex": return new RegExp(String(value ?? ""), "i").test(String(cell ?? ""));
            case "isNull": return cell === null || cell === undefined || cell === "";
            case "isNotNull": return cell !== null && cell !== undefined && cell !== "";
            default: return true;
        }
    });
}
// ─── Misc ─────────────────────────────────────────────────────────────────────
function colIndexToLetter(n) {
    let r = "";
    while (n > 0) {
        r = String.fromCharCode(64 + ((n - 1) % 26 + 1)) + r;
        n = Math.floor((n - 1) / 26);
    }
    return r;
}
function preview(rows, n = 5) {
    return rows.slice(0, n);
}
//# sourceMappingURL=workbook.js.map