1 files changed, 128 insertions, 0 deletions

diff --git a/lib/tech-vendor-candidates/table/excel-template-download.tsx b/lib/tech-vendor-candidates/table/excel-template-download.tsx
new file mode 100644
index 00000000..673680db
--- /dev/null
+++ b/lib/tech-vendor-candidates/table/excel-template-download.tsx
@@ -0,0 +1,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)
+}
\ No newline at end of file