1 files changed, 39 insertions, 5 deletions

diff --git a/lib/vendor-candidates/table/excel-template-download.tsx b/lib/vendor-candidates/table/excel-template-download.tsx
index b69ab821..673680db 100644
--- a/lib/vendor-candidates/table/excel-template-download.tsx
+++ b/lib/vendor-candidates/table/excel-template-download.tsx
@@ -16,10 +16,14 @@ export async function exportVendorCandidateTemplate() {
   // 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 },
   ]
   
@@ -27,7 +31,7 @@ export async function exportVendorCandidateTemplate() {
   worksheet.columns = columns
   
   // Style the header row
-  const headerRow = worksheet.getRow(1)
+  const headerRow = worksheet.getRow(2)
   headerRow.font = { bold: true }
   headerRow.alignment = { horizontal: "center" }
   headerRow.eachCell((cell) => {
@@ -36,24 +40,39 @@ export async function exportVendorCandidateTemplate() {
       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",
     },
   ]
@@ -65,8 +84,11 @@ export async function exportVendorCandidateTemplate() {
   
   // Add data validation for Status column
   const statusValues = ["COLLECTED", "INVITED", "DISCARDED"]
-  for (let i = 2; i <= 100; i++) { // Apply to rows 2-100
-    worksheet.getCell(`F${i}`).dataValidation = {
+  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(',')}"`]
@@ -74,11 +96,23 @@ export async function exportVendorCandidateTemplate() {
   }
   
   // Add instructions row
-  worksheet.insertRow(1, ["Please fill in the data below. Required fields: Company Name, Contact Email"])
-  worksheet.mergeCells("A1:F1")
+  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()