Skip to content

Build your personal knowledge base with Trilium Notes

3.2 KB
Raw Blame
/**
 * Extracts plain text from a UniversJS workbook JSON structure for full-text search indexing.
 * Mirrors the traversal logic of {@link renderSpreadsheetToHtml}: only visible sheets,
 * skipping hidden rows and columns.
 *
 * Handles both original camelCase keys and lowercased keys (the search preprocessor
 * runs `normalize()` on content before passing it here, which lowercases the entire
 * JSON string).
 */

/* eslint-disable @typescript-eslint/no-explicit-any */

import { normalizeDataStream } from "./workbook_model.js";

/** Read a property by trying the camelCase name first, then the lowercased form. */
function prop(obj: any, camel: string): any {
    if (obj == null) return undefined;
    return obj[camel] ?? obj[camel.toLowerCase()];
}

/**
 * Parses the raw JSON content of a spreadsheet note and extracts all visible
 * cell values as a single plain-text string (space-separated).
 *
 * Returns an empty string for invalid or empty spreadsheets.
 */
export function extractSpreadsheetText(jsonContent: string): string {
    let data: any;
    try {
        data = JSON.parse(jsonContent);
    } catch {
        return "";
    }

    const workbook = prop(data, "workbook");
    const sheets = workbook && prop(workbook, "sheets");
    if (!sheets) {
        return "";
    }

    const sheetOrder: string[] | undefined = prop(workbook, "sheetOrder");
    const sheetIds = sheetOrder ?? Object.keys(sheets);
    const visibleSheets = sheetIds
        .map((id: string) => sheets[id])
        .filter((s: any) => s && !prop(s, "hidden"));

    if (visibleSheets.length === 0) {
        return "";
    }

    const parts: string[] = [];
    for (const sheet of visibleSheets) {
        extractSheetText(sheet, parts);
    }

    return parts.join(" ");
}

function extractSheetText(sheet: any, parts: string[]): void {
    const cellData = prop(sheet, "cellData");
    if (!cellData) return;

    const rowData = prop(sheet, "rowData") ?? {};
    const columnData = prop(sheet, "columnData") ?? {};

    for (const rowStr of Object.keys(cellData)) {
        const row = Number(rowStr);
        if (prop(rowData[row], "hd")) continue;

        const cols = cellData[row];
        for (const colStr of Object.keys(cols)) {
            const col = Number(colStr);
            if (prop(columnData[col], "hd")) continue;

            const cell = cols[col];
            if (!cell) continue;

            if (cell.v == null || cell.v === "") {
                // A rich-text cell (a link, mixed formatting) can carry its text only in `p`.
                const text = cellDocumentText(cell).trim();
                if (text) {
                    parts.push(text);
                }
            } else if (typeof cell.v === "boolean") {
                parts.push(cell.v ? "TRUE" : "FALSE");
            } else {
                const text = String(cell.v).trim();
                if (text) {
                    parts.push(text);
                }
            }
        }
    }
}

/** Reads a rich-text cell's document text, tolerating the lowercased keys `prop` handles. */
function cellDocumentText(cell: any): string {
    const body = prop(prop(cell, "p"), "body");
    return normalizeDataStream(prop(body, "dataStream"));
}