| 1 | import JSZip from "jszip"; |
| 2 | |
| 3 | import { |
| 4 | getSchemaForMode, |
| 5 | type ChartDataField, |
| 6 | type ChartDataRow, |
| 7 | } from "./schemas"; |
| 8 | import { type ChartDataMode, type ChartDataType } from "./types"; |
| 9 | |
| 10 | type SpreadsheetCell = string | number; |
| 11 | |
| 12 | const XLSX_NAMESPACE = |
| 13 | "http://schemas.openxmlformats.org/spreadsheetml/2006/main"; |
| 14 | |
| 15 | function normalizeHeader(value: string): string { |
| 16 | return value |
| 17 | .trim() |
| 18 | .toLowerCase() |
| 19 | .replace(/[^a-z0-9]+/g, ""); |
| 20 | } |
| 21 | |
| 22 | function parseCsv(text: string): SpreadsheetCell[][] { |
| 23 | const rows: SpreadsheetCell[][] = []; |
| 24 | let currentRow: SpreadsheetCell[] = []; |
| 25 | let currentValue = ""; |
| 26 | let inQuotes = false; |
| 27 | |
| 28 | for (let index = 0; index < text.length; index += 1) { |
| 29 | const char = text[index]; |
| 30 | const nextChar = text[index + 1]; |
| 31 | |
| 32 | if (char === '"' && inQuotes && nextChar === '"') { |
| 33 | currentValue += '"'; |
| 34 | index += 1; |
| 35 | } else if (char === '"') { |
| 36 | inQuotes = !inQuotes; |
| 37 | } else if (char === "," && !inQuotes) { |
| 38 | currentRow.push(coerceCellValue(currentValue)); |
| 39 | currentValue = ""; |
| 40 | } else if ((char === "\n" || char === "\r") && !inQuotes) { |
| 41 | if (char === "\r" && nextChar === "\n") { |
| 42 | index += 1; |
| 43 | } |
| 44 | currentRow.push(coerceCellValue(currentValue)); |
| 45 | rows.push(currentRow); |
| 46 | currentRow = []; |
| 47 | currentValue = ""; |
| 48 | } else { |
| 49 | currentValue += char; |
| 50 | } |
| 51 | } |
| 52 | |
| 53 | currentRow.push(coerceCellValue(currentValue)); |
| 54 | rows.push(currentRow); |
| 55 | |
| 56 | return rows.filter((row) => |
| 57 | row.some((cell) => String(cell).trim().length > 0), |
| 58 | ); |
| 59 | } |
| 60 | |
| 61 | function coerceCellValue(value: string): SpreadsheetCell { |
| 62 | const trimmed = value.trim(); |
| 63 | if (trimmed.length === 0) return ""; |
| 64 | const numberValue = Number(trimmed); |
| 65 | return Number.isFinite(numberValue) ? numberValue : trimmed; |
| 66 | } |
| 67 | |
| 68 | function getTextContent(parent: Element, tagName: string): string { |
| 69 | return ( |
| 70 | parent.getElementsByTagNameNS(XLSX_NAMESPACE, tagName)[0]?.textContent ?? "" |
| 71 | ); |
| 72 | } |
| 73 | |
| 74 | function parseSharedStrings(xml: string): string[] { |
| 75 | const document = new DOMParser().parseFromString(xml, "application/xml"); |
| 76 | return Array.from(document.getElementsByTagNameNS(XLSX_NAMESPACE, "si")).map( |
| 77 | (item) => |
| 78 | Array.from(item.getElementsByTagNameNS(XLSX_NAMESPACE, "t")) |
| 79 | .map((node) => node.textContent ?? "") |
| 80 | .join(""), |
| 81 | ); |
| 82 | } |
| 83 | |
| 84 | function getColumnIndex(cellReference: string): number { |
| 85 | const letters = cellReference.match(/[A-Z]+/i)?.[0]?.toUpperCase() ?? "A"; |
| 86 | return ( |
| 87 | [...letters].reduce( |
| 88 | (total, letter) => total * 26 + letter.charCodeAt(0) - 64, |
| 89 | 0, |
| 90 | ) - 1 |
| 91 | ); |
| 92 | } |
| 93 | |
| 94 | function parseWorksheet( |
| 95 | xml: string, |
| 96 | sharedStrings: readonly string[], |
| 97 | ): SpreadsheetCell[][] { |
| 98 | const document = new DOMParser().parseFromString(xml, "application/xml"); |
| 99 | |
| 100 | return Array.from(document.getElementsByTagNameNS(XLSX_NAMESPACE, "row")).map( |
| 101 | (row) => { |
| 102 | const cells: SpreadsheetCell[] = []; |
| 103 | |
| 104 | Array.from(row.getElementsByTagNameNS(XLSX_NAMESPACE, "c")).forEach( |
| 105 | (cell) => { |
| 106 | const reference = cell.getAttribute("r") ?? "A1"; |
| 107 | const type = cell.getAttribute("t"); |
| 108 | const rawValue = getTextContent(cell, "v"); |
| 109 | const columnIndex = getColumnIndex(reference); |
| 110 | |
| 111 | if (type === "s") { |
| 112 | cells[columnIndex] = sharedStrings[Number(rawValue)] ?? ""; |
| 113 | } else if (type === "inlineStr") { |
| 114 | cells[columnIndex] = getTextContent(cell, "t"); |
| 115 | } else { |
| 116 | cells[columnIndex] = coerceCellValue(rawValue); |
| 117 | } |
| 118 | }, |
| 119 | ); |
| 120 | |
| 121 | return cells; |
| 122 | }, |
| 123 | ); |
| 124 | } |
| 125 | |
| 126 | async function parseXlsx(file: File): Promise<SpreadsheetCell[][]> { |
| 127 | const zip = await JSZip.loadAsync(await file.arrayBuffer()); |
| 128 | const sharedStringsFile = zip.file("xl/sharedStrings.xml"); |
| 129 | const sharedStrings = sharedStringsFile |
| 130 | ? parseSharedStrings(await sharedStringsFile.async("text")) |
| 131 | : []; |
| 132 | const firstWorksheet = |
| 133 | zip.file("xl/worksheets/sheet1.xml") ?? |
| 134 | zip |
| 135 | .file(/^xl\/worksheets\/sheet\d+\.xml$/) |
| 136 | .sort((left, right) => left.name.localeCompare(right.name))[0]; |
| 137 | |
| 138 | if (!firstWorksheet) { |
| 139 | throw new Error("No worksheet was found in the Excel file."); |
| 140 | } |
| 141 | |
| 142 | return parseWorksheet(await firstWorksheet.async("text"), sharedStrings); |
| 143 | } |
| 144 | |
| 145 | function fieldMatchesHeader(field: ChartDataField, header: string): boolean { |
| 146 | const normalizedHeader = normalizeHeader(header); |
| 147 | return ( |
| 148 | normalizedHeader === normalizeHeader(field.key) || |
| 149 | normalizedHeader === normalizeHeader(field.label) |
| 150 | ); |
| 151 | } |
| 152 | |
| 153 | function rowLooksLikeHeader( |
| 154 | row: readonly SpreadsheetCell[] | undefined, |
| 155 | fields: readonly ChartDataField[], |
| 156 | ): boolean { |
| 157 | if (!row || row.length === 0) return false; |
| 158 | |
| 159 | const normalizedHeaders = row.map((cell) => normalizeHeader(String(cell))); |
| 160 | const matchesKnownField = fields.some((field) => |
| 161 | normalizedHeaders.some( |
| 162 | (header) => |
| 163 | header === normalizeHeader(field.key) || |
| 164 | header === normalizeHeader(field.label), |
| 165 | ), |
| 166 | ); |
| 167 | const allText = row.every((cell) => typeof cell === "string"); |
| 168 | |
| 169 | return matchesKnownField || allText; |
| 170 | } |
| 171 | |
| 172 | function getCellForField( |
| 173 | row: readonly SpreadsheetCell[], |
| 174 | headers: readonly string[], |
| 175 | field: ChartDataField, |
| 176 | fieldIndex: number, |
| 177 | ): SpreadsheetCell { |
| 178 | const headerIndex = headers.findIndex((header) => |
| 179 | fieldMatchesHeader(field, header), |
| 180 | ); |
| 181 | return row[headerIndex >= 0 ? headerIndex : fieldIndex] ?? ""; |
| 182 | } |
| 183 | |
| 184 | function normalizeImportedValue( |
| 185 | value: SpreadsheetCell, |
| 186 | field: ChartDataField, |
| 187 | ): string | number { |
| 188 | if (field.type === "number") { |
| 189 | const numericValue = typeof value === "number" ? value : Number(value); |
| 190 | return Number.isFinite(numericValue) ? numericValue : 0; |
| 191 | } |
| 192 | |
| 193 | return String(value ?? ""); |
| 194 | } |
| 195 | |
| 196 | function rowsFromSpreadsheet( |
| 197 | spreadsheetRows: SpreadsheetCell[][], |
| 198 | chartType: ChartDataMode, |
| 199 | ): ChartDataRow[] { |
| 200 | const schema = getSchemaForMode(chartType); |
| 201 | const hasHeader = rowLooksLikeHeader( |
| 202 | spreadsheetRows[0], |
| 203 | schema.supportsSeries |
| 204 | ? [ |
| 205 | ...schema.fixedFields, |
| 206 | { key: "value", label: "Value", type: "number" }, |
| 207 | ] |
| 208 | : schema.fixedFields, |
| 209 | ); |
| 210 | const headers = hasHeader |
| 211 | ? (spreadsheetRows[0] ?? []).map((cell) => String(cell)) |
| 212 | : []; |
| 213 | const bodyRows = hasHeader ? spreadsheetRows.slice(1) : spreadsheetRows; |
| 214 | |
| 215 | if (schema.supportsSeries) { |
| 216 | const knownLabelIndex = headers.findIndex((header) => |
| 217 | ["label", "name", "category"].includes(normalizeHeader(header)), |
| 218 | ); |
| 219 | const labelHeaderIndex = knownLabelIndex >= 0 ? knownLabelIndex : 0; |
| 220 | const seriesHeaders = |
| 221 | headers.length > 0 |
| 222 | ? headers.filter((_, index) => index !== labelHeaderIndex) |
| 223 | : ["value"]; |
| 224 | |
| 225 | return bodyRows |
| 226 | .filter((row) => row.some((cell) => String(cell).trim().length > 0)) |
| 227 | .map((row, rowIndex) => { |
| 228 | const normalizedRow: ChartDataRow = { |
| 229 | label: String(row[labelHeaderIndex] ?? `Item ${rowIndex + 1}`), |
| 230 | }; |
| 231 | |
| 232 | seriesHeaders.forEach((header, index) => { |
| 233 | const sourceIndex = index >= labelHeaderIndex ? index + 1 : index; |
| 234 | const numberValue = Number(row[sourceIndex]); |
| 235 | normalizedRow[header || `Series ${index + 1}`] = Number.isFinite( |
| 236 | numberValue, |
| 237 | ) |
| 238 | ? numberValue |
| 239 | : 0; |
| 240 | }); |
| 241 | |
| 242 | return normalizedRow; |
| 243 | }); |
| 244 | } |
| 245 | |
| 246 | return bodyRows |
| 247 | .filter((row) => row.some((cell) => String(cell).trim().length > 0)) |
| 248 | .map((row) => |
| 249 | schema.fixedFields.reduce<ChartDataRow>((normalizedRow, field, index) => { |
| 250 | const value = getCellForField(row, headers, field, index); |
| 251 | normalizedRow[field.key] = normalizeImportedValue(value, field); |
| 252 | return normalizedRow; |
| 253 | }, {}), |
| 254 | ); |
| 255 | } |
| 256 | |
| 257 | export async function importChartDataFromFile( |
| 258 | file: File, |
| 259 | chartType: ChartDataMode, |
| 260 | ): Promise<ChartDataType> { |
| 261 | const extension = file.name.split(".").pop()?.toLowerCase(); |
| 262 | const spreadsheetRows = |
| 263 | extension === "xlsx" || extension === "xlsm" |
| 264 | ? await parseXlsx(file) |
| 265 | : parseCsv(await file.text()); |
| 266 | const rows = rowsFromSpreadsheet(spreadsheetRows, chartType); |
| 267 | |
| 268 | if (rows.length === 0) { |
| 269 | throw new Error("The imported file did not contain any chart rows."); |
| 270 | } |
| 271 | |
| 272 | return rows as ChartDataType; |
| 273 | } |
| 274 |