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
|
"use client"
import { type Table } from "@tanstack/react-table"
import ExcelJS from "exceljs"
import { VendorCandidates } from "@/db/schema/vendors"
/**
* Export an empty template for vendor candidates with column headers
* matching the expected import format
*/
export async function exportVendorCandidateTemplate() {
// Create a new workbook and worksheet
const workbook = new ExcelJS.Workbook()
const worksheet = workbook.addWorksheet("Vendor Candidates")
// Define the columns with expected headers
const columns = [
{ header: "Company Name", key: "companyName", width: 30 },
{ header: "Tax ID", key: "taxId", width: 20 },
{ header: "Contact Email", key: "contactEmail", width: 30 },
{ header: "Contact Phone", key: "contactPhone", width: 20 },
{ header: "Address", key: "address", width: 40 },
{ header: "Country", key: "country", width: 20 },
{ header: "Source", key: "source", width: 20 },
{ header: "Items", key: "items", width: 40 },
{ header: "Remark", key: "remark", width: 40 },
{ header: "Status", key: "status", width: 15 },
]
// Add columns to the worksheet
worksheet.columns = columns
// Style the header row
const headerRow = worksheet.getRow(2)
headerRow.font = { bold: true }
headerRow.alignment = { horizontal: "center" }
headerRow.eachCell((cell) => {
cell.fill = {
type: "pattern",
pattern: "solid",
fgColor: { argb: "FFCCCCCC" },
}
// Mark required fields with a red asterisk
const requiredFields = ["Company Name", "Source", "Items"]
if (requiredFields.includes(cell.value as string)) {
cell.value = `${cell.value} *`
cell.font = { bold: true, color: { argb: "FFFF0000" } }
}
})
// Add example data rows
const exampleData = [
{
companyName: "ABC Corporation",
taxId: "123-45-6789",
contactEmail: "contact@abc.com",
contactPhone: "+1-123-456-7890",
address: "123 Business Ave, Suite 100, New York, NY 10001",
country: "US",
source: "Website",
items: "Electronic components, Circuit boards, Sensors",
remark: "Potential supplier for Project X",
status: "COLLECTED",
},
{
companyName: "XYZ Ltd.",
taxId: "GB987654321",
contactEmail: "info@xyz.com",
contactPhone: "+44-987-654-3210",
address: "45 Industrial Park, London, EC2A 4PX",
country: "GB",
source: "Referral",
items: "Steel components, Metal frames, Industrial hardware",
remark: "Met at trade show in March",
status: "COLLECTED",
},
]
// Add the example rows to the worksheet
exampleData.forEach((data) => {
worksheet.addRow(data)
})
// Add data validation for Status column
const statusValues = ["COLLECTED", "INVITED", "DISCARDED"]
const statusColumn = columns.findIndex(col => col.key === "status") + 1
const statusColLetter = String.fromCharCode(64 + statusColumn)
for (let i = 4; i <= 100; i++) { // Apply to rows 4-100 (after example data)
worksheet.getCell(`${statusColLetter}${i}`).dataValidation = {
type: 'list',
allowBlank: true,
formulae: [`"${statusValues.join(',')}"`]
}
}
// Add instructions row
worksheet.insertRow(1, ["Please fill in the data below. Required fields are marked with an asterisk (*): Company Name, Source, Items"])
worksheet.mergeCells(`A1:${String.fromCharCode(64 + columns.length)}1`)
const instructionRow = worksheet.getRow(1)
instructionRow.font = { bold: true, color: { argb: "FF0000FF" } }
instructionRow.alignment = { horizontal: "center" }
instructionRow.height = 30
// Auto-width columns based on content
worksheet.columns.forEach(column => {
if (column.key) { // Check that column.key is defined
const dataMax = Math.max(...worksheet.getColumn(column.key).values
.filter(value => value !== null && value !== undefined)
.map(value => String(value).length)
)
column.width = Math.max(column.width || 10, dataMax + 2)
}
})
// Download the workbook
const buffer = await workbook.xlsx.writeBuffer()
const blob = new Blob([buffer], {
type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
})
const url = URL.createObjectURL(blob)
const link = document.createElement("a")
link.href = url
link.download = "vendor-candidates-template.xlsx"
link.click()
URL.revokeObjectURL(url)
}
|
