Jaspers Hub

rym / excel-export

pluginReviewed★ 0↓ 0

Excel Export: Builds .xlsx workbooks from supplied tables or grid panel data and writes/opens them in the plugin's exports folder.

7.2 KB
Raw
1import { strToU8, zipSync } from 'fflate'
2
3/** One sheet ready to write: its sanitized name, headers, and cells. */
4export interface Sheet {
5 name: string
6 columns: string[]
7 rows: unknown[][]
8}
9
10const XML = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n'
11const NS = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'
12const REL = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'
13
14function esc(s: string): string {
15 return s
16 .replace(/[\x00-\x08\x0b\x0c\x0e-\x1f]/g, '')
17 .replace(/&/g, '&amp;')
18 .replace(/</g, '&lt;')
19 .replace(/>/g, '&gt;')
20 .replace(/"/g, '&quot;')
21}
22
23function colName(i: number): string {
24 let s = ''
25 for (let n = i + 1; n > 0; n = Math.floor((n - 1) / 26)) s = String.fromCharCode(65 + ((n - 1) % 26)) + s
26 return s
27}
28
29/** A plain decimal written as text, like "4.77" or "-12", but not "007" or "1,000". */
30const NUMERIC = /^-?(0|[1-9]\d*)(\.\d+)?([eE][-+]?\d+)?$/
31
32type Cell = { kind: 'n'; value: number } | { kind: 'b'; value: boolean } | { kind: 's'; value: string } | null
33
34function toCell(v: unknown): Cell {
35 if (v === null || v === undefined || v === '') return null
36 if (typeof v === 'number') return Number.isFinite(v) ? { kind: 'n', value: v } : { kind: 's', value: String(v) }
37 if (typeof v === 'boolean') return { kind: 'b', value: v }
38 if (typeof v === 'string') {
39 const t = v.trim()
40 if (NUMERIC.test(t) && t.length < 16) return { kind: 'n', value: Number(t) }
41 return { kind: 's', value: v.slice(0, 32767) }
42 }
43 return { kind: 's', value: JSON.stringify(v).slice(0, 32767) }
44}
45
46function cellXml(ref: string, cell: Cell, style: number): string {
47 const s = style ? ` s="${style}"` : ''
48 if (!cell) return style ? `<c r="${ref}"${s}/>` : ''
49 if (cell.kind === 'n') return `<c r="${ref}"${s}><v>${cell.value}</v></c>`
50 if (cell.kind === 'b') return `<c r="${ref}"${s} t="b"><v>${cell.value ? 1 : 0}</v></c>`
51 return `<c r="${ref}"${s} t="inlineStr"><is><t xml:space="preserve">${esc(cell.value)}</t></is></c>`
52}
53
54function displayLength(cell: Cell): number {
55 if (!cell) return 0
56 if (cell.kind === 'n') return String(cell.value).length
57 if (cell.kind === 'b') return 5
58 return Math.max(...cell.value.split('\n').map((l) => l.length))
59}
60
61function sheetXml(sheet: Sheet): string {
62 const width = Math.max(sheet.columns.length, ...sheet.rows.map((r) => r.length), 1)
63 const widths = Array.from({ length: width }, (_, i) => (sheet.columns[i] ?? '').length + 2)
64 const rows: string[] = []
65 const header = Array.from({ length: width }, (_, i) =>
66 cellXml(`${colName(i)}1`, { kind: 's', value: sheet.columns[i] ?? `Column ${i + 1}` }, 1),
67 )
68 rows.push(`<row r="1">${header.join('')}</row>`)
69 sheet.rows.forEach((row, r) => {
70 const cells = row.map((v, c) => {
71 const cell = toCell(v)
72 widths[c] = Math.max(widths[c] ?? 0, displayLength(cell) + 2)
73 return cellXml(`${colName(c)}${r + 2}`, cell, 0)
74 })
75 rows.push(`<row r="${r + 2}">${cells.join('')}</row>`)
76 })
77 const cols = widths
78 .map((w, i) => `<col min="${i + 1}" max="${i + 1}" width="${Math.min(Math.max(w, 8), 60)}" customWidth="1"/>`)
79 .join('')
80 const last = `${colName(width - 1)}${sheet.rows.length + 1}`
81 return (
82 XML +
83 `<worksheet xmlns="${NS}" xmlns:r="${REL}">` +
84 `<dimension ref="A1:${last}"/>` +
85 `<sheetViews><sheetView workbookViewId="0"><pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/>` +
86 `<selection pane="bottomLeft" activeCell="A2" sqref="A2"/></sheetView></sheetViews>` +
87 `<sheetFormatPr defaultRowHeight="15"/><cols>${cols}</cols>` +
88 `<sheetData>${rows.join('')}</sheetData>` +
89 `<autoFilter ref="A1:${last}"/>` +
90 `</worksheet>`
91 )
92}
93
94const STYLES =
95 XML +
96 `<styleSheet xmlns="${NS}">` +
97 `<fonts count="2"><font><sz val="11"/><name val="Calibri"/><family val="2"/></font>` +
98 `<font><b/><sz val="11"/><name val="Calibri"/><family val="2"/></font></fonts>` +
99 `<fills count="3"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill>` +
100 `<fill><patternFill patternType="solid"><fgColor rgb="FFF2F2F2"/><bgColor indexed="64"/></patternFill></fill></fills>` +
101 `<borders count="2"><border><left/><right/><top/><bottom/><diagonal/></border>` +
102 `<border><left/><right/><top/><bottom style="thin"><color auto="1"/></bottom><diagonal/></border></borders>` +
103 `<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>` +
104 `<cellXfs count="2"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>` +
105 `<xf numFmtId="0" fontId="1" fillId="2" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1"/></cellXfs>` +
106 `<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>` +
107 `</styleSheet>`
108
109/** The .xlsx file's bytes for these sheets. */
110export function buildXlsx(sheets: Sheet[]): Uint8Array {
111 const files: Record<string, Uint8Array> = {}
112 const overrides = sheets
113 .map(
114 (_, i) =>
115 `<Override PartName="/xl/worksheets/sheet${i + 1}.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>`,
116 )
117 .join('')
118 files['[Content_Types].xml'] = strToU8(
119 XML +
120 `<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">` +
121 `<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>` +
122 `<Default Extension="xml" ContentType="application/xml"/>` +
123 `<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>` +
124 `<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>` +
125 overrides +
126 `</Types>`,
127 )
128 files['_rels/.rels'] = strToU8(
129 XML +
130 `<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">` +
131 `<Relationship Id="rId1" Type="${REL}/officeDocument" Target="xl/workbook.xml"/></Relationships>`,
132 )
133 files['xl/workbook.xml'] = strToU8(
134 XML +
135 `<workbook xmlns="${NS}" xmlns:r="${REL}"><bookViews><workbookView/></bookViews><sheets>` +
136 sheets.map((s, i) => `<sheet name="${esc(s.name)}" sheetId="${i + 1}" r:id="rId${i + 1}"/>`).join('') +
137 `</sheets><definedNames>` +
138 sheets
139 .map((s, i) => {
140 const width = Math.max(s.columns.length, ...s.rows.map((r) => r.length), 1)
141 const quoted = `'${s.name.replace(/'/g, "''")}'`
142 return `<definedName name="_xlnm._FilterDatabase" localSheetId="${i}" hidden="1">${esc(quoted)}!$A$1:$${colName(width - 1)}$${s.rows.length + 1}</definedName>`
143 })
144 .join('') +
145 `</definedNames></workbook>`,
146 )
147 files['xl/_rels/workbook.xml.rels'] = strToU8(
148 XML +
149 `<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">` +
150 sheets
151 .map((_, i) => `<Relationship Id="rId${i + 1}" Type="${REL}/worksheet" Target="worksheets/sheet${i + 1}.xml"/>`)
152 .join('') +
153 `<Relationship Id="rId${sheets.length + 1}" Type="${REL}/styles" Target="styles.xml"/></Relationships>`,
154 )
155 files['xl/styles.xml'] = strToU8(STYLES)
156 sheets.forEach((s, i) => (files[`xl/worksheets/sheet${i + 1}.xml`] = strToU8(sheetXml(s))))
157 return zipSync(files, { level: 6 })
158}