XLSX SheetJS flattener

1import * as XLSX from "@e965/xlsx";
2 
3import { createCompactSheet, type CompactSheet } from "@/lib/xlsx-workbook";
4import {
5  XLSX_PARSE_LIMITS,
6  XlsxWorkerError,
7  type XlsxParseLimits,
8} from "@/lib/xlsx-worker-protocol";
9 
10type SheetJsCell = {
11  t?: string;
12  v?: unknown;
13  w?: unknown;
14};
15 
16type SheetJsWorksheet = Record<string, SheetJsCell | string | undefined>;
17 
18type SheetJsWorkbook = {
19  SheetNames: string[];
20  Sheets: Record<string, SheetJsWorksheet | undefined>;
21};
22 
23export function flattenSheetJsWorkbook(
24  workbook: SheetJsWorkbook,
25  limits: XlsxParseLimits = XLSX_PARSE_LIMITS,
26): CompactSheet[] {
27  return workbook.SheetNames.map((name) =>
28    flattenSheetJsWorksheet(name, workbook.Sheets[name], limits),
29  );
30}
31 
32export function flattenSheetJsWorksheet(
33  name: string,
34  worksheet: SheetJsWorksheet | undefined,
35  limits: XlsxParseLimits = XLSX_PARSE_LIMITS,
36): CompactSheet {
37  const ref = worksheet?.["!ref"];
38  if (typeof ref !== "string") {
39    return createEmptyCompactSheet(name);
40  }
41  const source = worksheet as SheetJsWorksheet;
42 
43  const range = XLSX.utils.decode_range(ref);
44  if (!isValidDecodedRange(range)) return createEmptyCompactSheet(name);
45  const rowCount = range.e.r + 1;
46  const columnCount = range.e.c + 1;
47  const maxIndex = rowCount * columnCount - 1;
48  if (maxIndex > limits.maxRowMajorIndex) {
49    throw new XlsxWorkerError(
50      "range_too_large",
51      `Spreadsheet range is too large to preview (${rowCount} rows x ${columnCount} columns)`,
52    );
53  }
54 
55  const entries: Array<{
56    cellIndex: number;
57    text: string;
58    numeric?: boolean;
59  }> = [];
60  let textChars = 0;
61 
62  for (const key of Object.keys(source)) {
63    if (key.charCodeAt(0) === 33) continue;
64    const cell = source[key];
65    if (!isSheetJsCell(cell)) continue;
66    const { c: columnIndex, r: rowIndex } = XLSX.utils.decode_cell(key);
67    if (
68      rowIndex < range.s.r ||
69      columnIndex < range.s.c ||
70      rowIndex >= rowCount ||
71      columnIndex >= columnCount
72    ) {
73      continue;
74    }
75 
76    const text =
77      cell.w != null ? String(cell.w) : cell.v != null ? String(cell.v) : "";
78    if (text === "") continue;
79    if (entries.length >= limits.maxNonEmptyCells) {
80      throw new XlsxWorkerError(
81        "too_many_cells",
82        `Spreadsheet has too many non-empty cells to preview (>${limits.maxNonEmptyCells.toLocaleString()})`,
83      );
84    }
85    textChars += text.length;
86    if (textChars > limits.maxTextChars) {
87      throw new XlsxWorkerError(
88// … truncated