1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
|
import ExcelJS from "exceljs"
interface ImportExcelOptions {
file: File
/**
* Map Excel header names to data keys.
* Example: { "Name": "name", "Age": "age" }
*/
columnMapping?: Record<string, string>
/**
* Row offset to start reading data (0-based).
* Default: 1 (assuming row 0 is header)
*/
dataStartRow?: number
}
export interface ExcelImportResult<T = any> {
data: T[]
errors: string[]
}
/**
* Generic function to read an Excel file and convert it to an array of objects.
* Does NOT handle database insertion or validation logic.
*/
export async function importFromExcel<T = any>({
file,
columnMapping = {},
dataStartRow = 1,
}: ImportExcelOptions): Promise<ExcelImportResult<T>> {
const workbook = new ExcelJS.Workbook()
const arrayBuffer = await file.arrayBuffer()
try {
await workbook.xlsx.load(arrayBuffer)
} catch (error) {
return { data: [], errors: ["Failed to parse Excel file. Please ensure it is a valid .xlsx file."] }
}
const worksheet = workbook.worksheets[0] // Read the first sheet
const data: T[] = []
const errors: string[] = []
if (!worksheet) {
return { data: [], errors: ["No worksheet found in the Excel file."] }
}
// 1. Read Header Row (assumed to be row 1 for mapping if no explicit mapping provided,
// or we can use it to validate mapping)
const headers: string[] = []
const headerRow = worksheet.getRow(1)
headerRow.eachCell((cell, colNumber) => {
headers[colNumber] = String(cell.value).trim()
})
// 2. Iterate Data Rows
worksheet.eachRow((row, rowNumber) => {
if (rowNumber <= dataStartRow) return // Skip header/pre-header rows
const rowData: any = {}
let hasData = false
row.eachCell((cell, colNumber) => {
const headerText = headers[colNumber]
if (!headerText) return
// Determine the key to use for this column
// Priority: Explicit mapping -> Header text as key
const key = columnMapping[headerText] || headerText
let cellValue = cell.value
// Handle ExcelJS object values (e.g. formula results, hyperlinks)
if (cellValue && typeof cellValue === 'object') {
if ('result' in cellValue) {
// Formula result
cellValue = (cellValue as any).result
} else if ('text' in cellValue) {
// Hyperlink text
cellValue = (cellValue as any).text
} else if ('richText' in cellValue) {
// Rich text
cellValue = (cellValue as any).richText.map((t: any) => t.text).join('')
}
}
if (cellValue !== null && cellValue !== undefined && String(cellValue).trim() !== '') {
hasData = true
rowData[key] = cellValue
}
})
if (hasData) {
data.push(rowData as T)
}
})
return { data, errors }
}
|
