Skip to content

Build your personal knowledge base with Trilium Notes

16.7 KB
Raw Blame
import { describe, expect, it } from "vitest";
import { extractSpreadsheetText } from "./extract_text.js";

describe("extractSpreadsheetText", () => {
    it("extracts cell values from a basic spreadsheet", () => {
        const input = JSON.stringify({
            version: 1,
            workbook: {
                sheetOrder: ["s1"],
                sheets: {
                    s1: {
                        id: "s1",
                        name: "Sheet1",
                        hidden: 0,
                        cellData: {
                            "1": { "1": { v: "The", t: 1 } },
                            "2": { "2": { v: "quick", t: 1 } },
                            "3": { "3": { v: "brown", t: 1 } },
                            "4": { "1": { v: "fox", t: 1 } }
                        },
                        rowData: {},
                        columnData: {}
                    }
                }
            }
        });

        const text = extractSpreadsheetText(input);
        expect(text).toBe("The quick brown fox");
    });

    it("skips hidden sheets", () => {
        const input = JSON.stringify({
            version: 1,
            workbook: {
                sheetOrder: ["s1", "s2"],
                sheets: {
                    s1: {
                        id: "s1",
                        name: "Visible",
                        hidden: 0,
                        cellData: { "0": { "0": { v: "shown" } } },
                        rowData: {},
                        columnData: {}
                    },
                    s2: {
                        id: "s2",
                        name: "Hidden",
                        hidden: 1,
                        cellData: { "0": { "0": { v: "secret" } } },
                        rowData: {},
                        columnData: {}
                    }
                }
            }
        });

        const text = extractSpreadsheetText(input);
        expect(text).toContain("shown");
        expect(text).not.toContain("secret");
    });

    it("skips hidden rows", () => {
        const input = JSON.stringify({
            version: 1,
            workbook: {
                sheetOrder: ["s1"],
                sheets: {
                    s1: {
                        id: "s1",
                        name: "Sheet1",
                        hidden: 0,
                        cellData: {
                            "0": { "0": { v: "visible row" } },
                            "1": { "0": { v: "hidden row" } }
                        },
                        rowData: { "1": { hd: 1 } },
                        columnData: {}
                    }
                }
            }
        });

        const text = extractSpreadsheetText(input);
        expect(text).toContain("visible row");
        expect(text).not.toContain("hidden row");
    });

    it("skips hidden columns", () => {
        const input = JSON.stringify({
            version: 1,
            workbook: {
                sheetOrder: ["s1"],
                sheets: {
                    s1: {
                        id: "s1",
                        name: "Sheet1",
                        hidden: 0,
                        cellData: {
                            "0": {
                                "0": { v: "visible col" },
                                "1": { v: "hidden col" }
                            }
                        },
                        rowData: {},
                        columnData: { "1": { hd: 1 } }
                    }
                }
            }
        });

        const text = extractSpreadsheetText(input);
        expect(text).toContain("visible col");
        expect(text).not.toContain("hidden col");
    });

    it("extracts text from multiple visible sheets", () => {
        const input = JSON.stringify({
            version: 1,
            workbook: {
                sheetOrder: ["s1", "s2"],
                sheets: {
                    s1: {
                        id: "s1",
                        name: "Data",
                        hidden: 0,
                        cellData: { "0": { "0": { v: "alpha" } } },
                        rowData: {},
                        columnData: {}
                    },
                    s2: {
                        id: "s2",
                        name: "Summary",
                        hidden: 0,
                        cellData: { "0": { "0": { v: "beta" } } },
                        rowData: {},
                        columnData: {}
                    }
                }
            }
        });

        const text = extractSpreadsheetText(input);
        expect(text).toContain("alpha");
        expect(text).toContain("beta");
    });

    it("handles boolean values", () => {
        const input = JSON.stringify({
            version: 1,
            workbook: {
                sheetOrder: ["s1"],
                sheets: {
                    s1: {
                        id: "s1",
                        name: "Sheet1",
                        hidden: 0,
                        cellData: {
                            "0": {
                                "0": { v: true, t: 3 },
                                "1": { v: false, t: 3 }
                            }
                        },
                        rowData: {},
                        columnData: {}
                    }
                }
            }
        });

        const text = extractSpreadsheetText(input);
        expect(text).toContain("TRUE");
        expect(text).toContain("FALSE");
    });

    it("handles numeric values", () => {
        const input = JSON.stringify({
            version: 1,
            workbook: {
                sheetOrder: ["s1"],
                sheets: {
                    s1: {
                        id: "s1",
                        name: "Sheet1",
                        hidden: 0,
                        cellData: {
                            "0": { "0": { v: 42 }, "1": { v: 3.14 } }
                        },
                        rowData: {},
                        columnData: {}
                    }
                }
            }
        });

        const text = extractSpreadsheetText(input);
        expect(text).toContain("42");
        expect(text).toContain("3.14");
    });

    it("skips cells with null or undefined values", () => {
        const input = JSON.stringify({
            version: 1,
            workbook: {
                sheetOrder: ["s1"],
                sheets: {
                    s1: {
                        id: "s1",
                        name: "Sheet1",
                        hidden: 0,
                        cellData: {
                            "0": {
                                "0": { v: "data" },
                                "1": { v: null },
                                "2": {},
                                "3": { s: "someStyle" }
                            }
                        },
                        rowData: {},
                        columnData: {}
                    }
                }
            }
        });

        const text = extractSpreadsheetText(input);
        expect(text).toBe("data");
    });

    it("returns empty string for invalid JSON", () => {
        expect(extractSpreadsheetText("not json")).toBe("");
    });

    it("returns empty string for empty workbook", () => {
        const input = JSON.stringify({
            version: 1,
            workbook: {
                sheetOrder: ["s1"],
                sheets: {
                    s1: {
                        id: "s1",
                        name: "Sheet1",
                        hidden: 0,
                        cellData: {},
                        rowData: {},
                        columnData: {}
                    }
                }
            }
        });

        expect(extractSpreadsheetText(input)).toBe("");
    });

    it("returns empty string for missing workbook", () => {
        expect(extractSpreadsheetText(JSON.stringify({ version: 1 }))).toBe("");
    });

    it("handles sheet with missing cellData", () => {
        const input = JSON.stringify({
            version: 1,
            workbook: {
                sheetOrder: ["s1"],
                sheets: {
                    s1: {
                        id: "s1",
                        name: "Sheet1",
                        hidden: 0
                    }
                }
            }
        });

        expect(extractSpreadsheetText(input)).toBe("");
    });

    it("handles lowercased JSON from normalize()", () => {
        const input = JSON.stringify({
            version: 1,
            workbook: {
                sheetOrder: ["s1"],
                sheets: {
                    s1: {
                        id: "s1",
                        name: "sheet1",
                        hidden: 0,
                        cellData: {
                            "0": { "0": { v: "hello" } }
                        },
                        rowData: { "1": { hd: 1 } },
                        columnData: { "2": { hd: 1 } }
                    }
                }
            }
        }).toLowerCase();

        expect(extractSpreadsheetText(input)).toBe("hello");
    });

    it("returns empty string when all referenced sheets are hidden", () => {
        const input = JSON.stringify({
            version: 1,
            workbook: {
                sheetOrder: ["s1"],
                sheets: {
                    s1: {
                        id: "s1",
                        name: "Hidden",
                        hidden: 1,
                        cellData: { "0": { "0": { v: "secret" } } },
                        rowData: {},
                        columnData: {}
                    }
                }
            }
        });

        expect(extractSpreadsheetText(input)).toBe("");
    });

    it("returns empty string when sheetOrder points to missing ids", () => {
        const input = JSON.stringify({
            version: 1,
            workbook: {
                sheetOrder: ["does-not-exist"],
                sheets: {
                    s1: {
                        id: "s1",
                        name: "Sheet1",
                        hidden: 0,
                        cellData: { "0": { "0": { v: "data" } } },
                        rowData: {},
                        columnData: {}
                    }
                }
            }
        });

        expect(extractSpreadsheetText(input)).toBe("");
    });

    it("extracts cells from fully lowercased keys via prop fallback", () => {
        const input = JSON.stringify({
            version: 1,
            workbook: {
                sheetorder: ["s1"],
                sheets: {
                    s1: {
                        id: "s1",
                        hidden: 0,
                        celldata: {
                            "0": {
                                "0": { v: "lower" },
                                "1": { v: "case" }
                            },
                            "1": { "0": { v: "skipped" } }
                        },
                        rowdata: { "1": { hd: 1 } },
                        columndata: { "1": { hd: 1 } }
                    }
                }
            }
        });

        const text = extractSpreadsheetText(input);
        expect(text).toBe("lower");
    });

    it("uses Object.keys(sheets) when sheetOrder is absent", () => {
        const input = JSON.stringify({
            version: 1,
            workbook: {
                sheets: {
                    s1: {
                        id: "s1",
                        hidden: 0,
                        cellData: { "0": { "0": { v: "noOrder" } } },
                        rowData: {},
                        columnData: {}
                    }
                }
            }
        });

        expect(extractSpreadsheetText(input)).toBe("noOrder");
    });

    it("skips whitespace-only string cell values", () => {
        const input = JSON.stringify({
            version: 1,
            workbook: {
                sheetOrder: ["s1"],
                sheets: {
                    s1: {
                        id: "s1",
                        hidden: 0,
                        cellData: {
                            "0": {
                                "0": { v: "   " },
                                "1": { v: "kept" }
                            }
                        },
                        rowData: {},
                        columnData: {}
                    }
                }
            }
        });

        expect(extractSpreadsheetText(input)).toBe("kept");
    });

    it("defaults rowData and columnData to empty objects when absent", () => {
        const input = JSON.stringify({
            version: 1,
            workbook: {
                sheetOrder: ["s1"],
                sheets: {
                    s1: {
                        id: "s1",
                        hidden: 0,
                        cellData: {
                            "0": { "0": { v: "noMeta" } }
                        }
                    }
                }
            }
        });

        expect(extractSpreadsheetText(input)).toBe("noMeta");
    });

    it("handles a null cell entry without throwing", () => {
        const input = JSON.stringify({
            version: 1,
            workbook: {
                sheetOrder: ["s1"],
                sheets: {
                    s1: {
                        id: "s1",
                        hidden: 0,
                        cellData: {
                            "0": {
                                "0": null,
                                "1": { v: "present" }
                            }
                        },
                        rowData: {},
                        columnData: {}
                    }
                }
            }
        });

        expect(extractSpreadsheetText(input)).toBe("present");
    });

    it("extracts text from the sample spreadsheet", () => {
        const input = JSON.stringify({
            version: 1,
            workbook: {
                id: "vlyRSv",
                sheetOrder: ["YdJwCRPZnEn4twLYtcdrE"],
                name: "",
                appVersion: "0.20.1",
                locale: "zhCN",
                styles: {
                    yIwJoC: { bl: 1 },
                    bTNHmx: { it: 1 },
                    "6ZCsr1": { ff: "Arial" }
                },
                sheets: {
                    YdJwCRPZnEn4twLYtcdrE: {
                        id: "YdJwCRPZnEn4twLYtcdrE",
                        name: "Sheet1",
                        hidden: 0,
                        rowCount: 1000,
                        columnCount: 20,
                        defaultColumnWidth: 88,
                        defaultRowHeight: 24,
                        mergeData: [],
                        cellData: {
                            "1": { "1": { v: "The", t: 1 } },
                            "2": { "2": { v: "quick", t: 1 } },
                            "3": { "3": { s: "yIwJoC", v: "brown", t: 1 } },
                            "4": { "1": { s: "bTNHmx", v: "fox", t: 1 } },
                            "8": {
                                "0": { s: "6ZCsr1" },
                                "3": { v: "This text is available only in the spreadsheet", t: 1 }
                            }
                        },
                        rowData: {},
                        columnData: {},
                        showGridlines: 1
                    }
                }
            }
        });

        const text = extractSpreadsheetText(input);
        expect(text).toContain("The");
        expect(text).toContain("quick");
        expect(text).toContain("brown");
        expect(text).toContain("fox");
        expect(text).toContain("This text is available only in the spreadsheet");
    });
});

describe("extractSpreadsheetText rich-text cells", () => {
    it("indexes cells whose text lives only in the rich-text document, lowercased keys included", () => {
        const sheet = (dataStreamKey: string) => JSON.stringify({
            version: 1,
            workbook: {
                sheetOrder: ["s1"],
                sheets: {
                    s1: {
                        id: "s1",
                        name: "Sheet1",
                        hidden: 0,
                        cellData: {
                            "0": { "0": { p: { body: { [dataStreamKey]: "Notebook\r\n" } } } },
                            "1": { "0": { v: "plain" } }
                        },
                        rowData: {},
                        columnData: {}
                    }
                }
            }
        });

        expect(extractSpreadsheetText(sheet("dataStream"))).toBe("Notebook plain");
        expect(extractSpreadsheetText(sheet("datastream"))).toBe("Notebook plain");
    });
});