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
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
|
import { ColumnDef } from "@tanstack/react-table"
import ExcelJS from "exceljs"
import { saveAs } from "file-saver"
export async function exportToExcel<TData>(
data: TData[],
columns: ColumnDef<TData, any>[],
filename: string = "export.xlsx"
) {
const workbook = new ExcelJS.Workbook()
const worksheet = workbook.addWorksheet("Data")
// Filter out utility columns and resolve headers
const exportableColumns = columns.filter(
(col) =>
col.id !== "select" &&
col.id !== "actions" &&
// @ts-ignore - simple check for now
(typeof col.header === "string" || typeof col.accessorKey === "string")
)
// Setup columns
worksheet.columns = exportableColumns.map((col) => {
let headerText = ""
if (typeof col.header === "string") {
headerText = col.header
} else if (typeof col.accessorKey === "string") {
headerText = col.accessorKey
}
return {
header: headerText,
key: (col.accessorKey as string) || col.id,
width: 20,
}
})
// Add rows
data.forEach((row) => {
const rowData: any = {}
exportableColumns.forEach((col) => {
const key = (col.accessorKey as string) || col.id
if (key) {
const value = getValueByPath(row, key)
rowData[key] = value
}
})
worksheet.addRow(rowData)
})
worksheet.getRow(1).font = { bold: true }
const buffer = await workbook.xlsx.writeBuffer()
const blob = new Blob([buffer], {
type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
})
saveAs(blob, filename)
}
function getValueByPath(obj: any, path: string) {
return path.split('.').reduce((acc, part) => acc && acc[part], obj)
}
interface CreateTemplateOptions<TData> {
columns: ColumnDef<TData, any>[]
filename?: string
includeColumns?: { key: string; header: string }[]
excludeColumns?: string[] // accessorKey or id to exclude
}
export async function createExcelTemplate<TData>({
columns,
filename = "template.xlsx",
includeColumns = [],
excludeColumns = [],
}: CreateTemplateOptions<TData>) {
const workbook = new ExcelJS.Workbook()
const worksheet = workbook.addWorksheet("Template")
// 1. Filter columns from definition
const baseColumns = columns.filter((col) => {
const key = (col.accessorKey as string) || col.id
// Skip system columns
if (col.id === "select" || col.id === "actions") return false
// Skip excluded columns
if (excludeColumns.includes(key!) || (col.id && excludeColumns.includes(col.id))) return false
return true
})
// 2. Map to ExcelJS columns
const excelColumns = baseColumns.map((col) => {
let headerText = ""
if (typeof col.header === "string") {
headerText = col.header
} else if (typeof col.accessorKey === "string") {
headerText = col.accessorKey
}
return {
header: headerText,
key: (col.accessorKey as string) || col.id,
width: 20
}
})
// 3. Add extra included columns
includeColumns.forEach((col) => {
excelColumns.push({
header: col.header,
key: col.key,
width: 20
})
})
worksheet.columns = excelColumns
// Style Header
const headerRow = worksheet.getRow(1)
headerRow.font = { bold: true }
headerRow.fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: 'FFD3D3D3' } // Light Gray
}
// Add Data Validation or Comments if needed (future expansion)
const buffer = await workbook.xlsx.writeBuffer()
const blob = new Blob([buffer], {
type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
})
saveAs(blob, filename)
}
|
