Isuru Bimsara
117 lines
2.7 KiB
JavaScript
117 lines
2.7 KiB
JavaScript
/**
|
|
* Copyright (c) 2026 Niolla
|
|
* All rights reserved.
|
|
*
|
|
* This source code is proprietary and confidential.
|
|
* Unauthorized copying, modification, distribution, or use
|
|
* of this file, via any medium, is strictly prohibited.
|
|
*/
|
|
|
|
// app/utills/generateExcelTemplate.utill.js
|
|
|
|
const ExcelJS = require("exceljs");
|
|
|
|
exports.generateExcelTemplate = async (columns, vendors) => {
|
|
const workbook = new ExcelJS.Workbook();
|
|
|
|
// =========================
|
|
// Template Sheet
|
|
// =========================
|
|
|
|
const worksheet = workbook.addWorksheet("Template");
|
|
|
|
const headers = columns.map((col) =>
|
|
col.is_required ? `${col.column_name} *` : col.column_name,
|
|
);
|
|
|
|
const headerRow = worksheet.addRow(headers);
|
|
|
|
headerRow.eachCell((cell) => {
|
|
cell.font = { bold: true };
|
|
cell.alignment = { vertical: "middle", horizontal: "center" };
|
|
});
|
|
|
|
headers.forEach((header, i) => {
|
|
worksheet.getColumn(i + 1).width = Math.max(header.length + 5, 20);
|
|
});
|
|
|
|
columns.forEach((col, index) => {
|
|
const headerCell = worksheet.getCell(1, index + 1);
|
|
|
|
if (col.is_required) {
|
|
headerCell.fill = {
|
|
type: "pattern",
|
|
pattern: "solid",
|
|
fgColor: { argb: "FFF4CCCC" },
|
|
};
|
|
|
|
headerCell.font = {
|
|
bold: true,
|
|
color: { argb: "FF000000" },
|
|
};
|
|
} else {
|
|
headerCell.fill = {
|
|
type: "pattern",
|
|
pattern: "solid",
|
|
fgColor: { argb: "FFFFF2CC" },
|
|
};
|
|
|
|
headerCell.font = {
|
|
bold: true,
|
|
color: { argb: "FF000000" },
|
|
};
|
|
}
|
|
|
|
if (col.validation_type === "number") {
|
|
for (let i = 2; i <= 100; i++) {
|
|
worksheet.getCell(i, index + 1).dataValidation = {
|
|
type: "whole",
|
|
operator: "greaterThanOrEqual",
|
|
formulae: [0],
|
|
showErrorMessage: true,
|
|
error: "Please enter a valid number",
|
|
};
|
|
}
|
|
}
|
|
});
|
|
|
|
worksheet.views = [{ state: "frozen", ySplit: 1 }];
|
|
|
|
// =========================
|
|
// Vendors Sheet
|
|
// =========================
|
|
const vendorSheet = workbook.addWorksheet("Vendors");
|
|
|
|
const vendorHeader = vendorSheet.addRow(["Supplier ID", "Supplier Name"]);
|
|
|
|
vendorHeader.eachCell((cell) => {
|
|
cell.font = {
|
|
bold: true,
|
|
color: { argb: "FFFFFFFF" },
|
|
};
|
|
|
|
cell.alignment = {
|
|
vertical: "middle",
|
|
horizontal: "center",
|
|
};
|
|
|
|
cell.fill = {
|
|
type: "pattern",
|
|
pattern: "solid",
|
|
fgColor: { argb: "4472C4" },
|
|
};
|
|
});
|
|
|
|
// Add vendor rows
|
|
vendors.forEach((vendor) => {
|
|
vendorSheet.addRow([vendor.vendor_id, vendor.name]);
|
|
});
|
|
|
|
// Optional styling
|
|
vendorSheet.columns = [{ width: 25 }, { width: 35 }];
|
|
|
|
vendorSheet.views = [{ state: "frozen", ySplit: 1 }];
|
|
|
|
return workbook;
|
|
};
|