Build your personal knowledge base with Trilium Notes
TypeScript electronelectron-appknowledge-baseknowledge-graphknowledge-managementknowledge-management-graphlocal-firstnote-managmentnote-takernote-takingnotebooknotesnotes-apppersonal-knowledge-basepersonal-wikiscriptableself-hostedself-hostingwiki
/**
* Converts a UniversJS workbook JSON structure into a CSV file (RFC 4180).
*
* CSV is a flat, single-sheet, value-only format: it carries no styling, merges, or
* formulas. So this emitter throws away everything the HTML/XLSX emitters preserve and
* keeps only the cell values, laid out as a dense rectangle over the cells that hold one —
* a cell carrying nothing but formatting does not widen it. Formula cells export their cached
* result (Univer stores it in `v`); most numbers export their raw underlying value rather than
* the formatted display string, which is what downstream CSV consumers expect.
*
* Dates are the exception: Univer stores them as serial numbers (e.g. `46118`), which no
* CSV consumer recognizes as a date. So date-formatted cells are emitted as ISO 8601
* (`yyyy-mm-dd`, plus time when the format carries it) — unambiguous and parsed by Excel,
* LibreOffice, etc. — rather than the locale-specific display string or the raw serial.
*
* Because CSV holds a single sheet, the caller picks which one via `sheetId` (e.g. the
* active sheet in the editor); absent that, the first visible sheet is used. To export a
* multi-sheet workbook without losing data, `renderSpreadsheetToCsvZip` emits one CSV per
* visible sheet bundled into a `.zip`.
*/
import { format as formatNumfmt, getFormatDateInfo, isDateFormat } from "numfmt";
import {
computeBounds,
getCellDocumentText,
getVisibleSheets,
hasContent,
type ICellData,
isFiniteNumber,
type IStyleData,
type IWorkbookData,
type IWorksheetData,
parseWorkbookData,
resolveCellStyle
} from "./workbook_model.js";
// Prepended to each emitted CSV so Excel auto-detects UTF-8 (it only does so with a BOM).
const UTF8_BOM = "\uFEFF";
export interface CsvRenderOptions {
/** Id of the sheet to export. Falls back to the first visible sheet when omitted or not found. */
sheetId?: string;
}
/**
* Parses the raw JSON content of a spreadsheet note and renders a single sheet as CSV.
* Hidden rows and columns are still exported (their data is part of the sheet). Returns
* an empty string for an empty sheet. Throws if the content is not a parseable workbook.
*
* Note: the returned string has no UTF-8 BOM, so callers downloading it for Excel should
* prepend one (the zip variant below does this per entry).
*/
export function renderSpreadsheetToCsv(jsonContent: string, options: CsvRenderOptions = {}): string {
const workbook = readWorkbook(jsonContent);
const visibleSheets = getVisibleSheets(workbook);
if (visibleSheets.length === 0) {
return "";
}
const sheet = pickSheet(visibleSheets, options.sheetId);
return renderSheet(sheet, workbook.styles ?? {});
}
/**
* Renders every visible sheet to its own CSV and bundles them into a `.zip`, returned as a
* binary buffer. Each entry is named after its sheet (sanitized and de-duplicated) with a
* UTF-8 BOM. Use this for multi-sheet workbooks, where the single-sheet renderer would
* silently drop every sheet but one. Throws if the content is not a parseable workbook.
*/
export async function renderSpreadsheetToCsvZip(jsonContent: string): Promise<Uint8Array> {
const workbook = readWorkbook(jsonContent);
const styles = workbook.styles ?? {};
// Dynamic import keeps jszip out of the main bundle (and out of standalone/core).
const { default: JSZip } = await import("jszip");
const zip = new JSZip();
const usedNames = new Set<string>();
for (const sheet of getVisibleSheets(workbook)) {
const fileName = uniqueFileName(sheet.name, usedNames);
zip.file(fileName, UTF8_BOM + renderSheet(sheet, styles));
}
return zip.generateAsync({ type: "uint8array" });
}
/** Parses and validates workbook JSON, throwing on unparseable content or a missing workbook. */
function readWorkbook(jsonContent: string): IWorkbookData {
const { ok, data } = parseWorkbookData(jsonContent);
if (!ok) {
throw new Error("Unable to parse spreadsheet data.");
}
if (!data?.workbook?.sheets) {
throw new Error("Spreadsheet contains no sheets.");
}
return data.workbook;
}
function pickSheet(visibleSheets: IWorksheetData[], sheetId: string | undefined): IWorksheetData {
if (sheetId) {
const match = visibleSheets.find((s) => s.id === sheetId);
if (match) return match;
}
return visibleSheets[0];
}
/**
* Turns a sheet name into a safe, unique `<name>.csv` zip entry: illegal filename characters
* (path separators, `:*?"<>|`, control codes) become `_`, blank names fall back to "Sheet",
* and collisions get a ` (n)` suffix. De-duplication is case-insensitive so the archive
* unzips cleanly on case-insensitive filesystems (Windows, macOS).
*/
function uniqueFileName(sheetName: string, usedNames: Set<string>): string {
const base = (sheetName ?? "").replace(/[\\/:*?"<>|\u0000-\u001f]/g, "_").trim() || "Sheet";
let candidate = base;
for (let n = 2; usedNames.has(candidate.toLowerCase()); n++) {
candidate = `${base} (${n})`;
}
usedNames.add(candidate.toLowerCase());
return `${candidate}.csv`;
}
function renderSheet(sheet: IWorksheetData, styles: Record<string, IStyleData | null>): string {
const { cellData } = sheet;
// A sheet usually carries formatting past its data: an Excel template borders or fills the rows
// someone is meant to fill in, and each of those empty cells is stored. CSV holds no formatting,
// so a rectangle bounded by every stored cell would trail hundreds of comma-only records. Only
// the cells that emit a field bound it, and a merge does not widen it: the merged value sits in
// the cell the rectangle already reaches.
const bounds = computeBounds(cellData, [], hasContent);
if (!bounds) {
return "";
}
const { minRow, maxRow, minCol, maxCol } = bounds;
const lines: string[] = [];
for (let row = minRow; row <= maxRow; row++) {
const cells: string[] = [];
for (let col = minCol; col <= maxCol; col++) {
cells.push(escapeCsvField(cellText(cellData[row]?.[col], styles)));
}
lines.push(cells.join(","));
}
// RFC 4180 uses CRLF as the record separator.
return lines.join("\r\n");
}
/**
* Extracts a cell's plain-text value. Formula cells use their cached result (`v`); date
* serials (a numeric value with a date number format) are rendered as ISO 8601.
*/
function cellText(cell: ICellData | undefined, styles: Record<string, IStyleData | null>): string {
if (!cell) return "";
// A rich-text cell (a link, mixed formatting) can carry its text only in `p`.
if (cell.v == null || cell.v === "") return getCellDocumentText(cell);
if (typeof cell.v === "boolean") return cell.v ? "TRUE" : "FALSE";
const pattern = resolveCellStyle(cell.s, styles)?.n?.pattern;
if (pattern && isFiniteNumber(cell.v) && isDateFormat(pattern)) {
const formatted = formatDate(pattern, cell.v);
if (formatted != null) return formatted;
}
return String(cell.v);
}
/**
* Renders a date serial as ISO 8601 when the format is a full year-month-day date (with
* time appended only if the format carries it). Partial date formats (e.g. month-year only)
* fall back to the cell's own pattern so the output stays a readable date, never a serial.
* Returns `null` if the value can't be formatted, so the caller falls back to the raw value.
*/
export function formatDate(pattern: string, serial: number): string | null {
try {
const info = getFormatDateInfo(pattern);
if (info.year && info.month && info.day) {
let isoFormat = "yyyy-mm-dd";
if (info.seconds) isoFormat += " hh:mm:ss";
else if (info.hours || info.minutes) isoFormat += " hh:mm";
return formatNumfmt(isoFormat, serial);
}
return formatNumfmt(pattern, serial);
} catch {
return null;
}
}
/**
* Quotes a field per RFC 4180 when it contains a comma, double quote, CR, or LF;
* embedded double quotes are doubled. Plain fields pass through unchanged.
*/
function escapeCsvField(value: string): string {
if (/[",\r\n]/.test(value)) {
return `"${value.replace(/"/g, '""')}"`;
}
return value;
}