Notes

import { MONTHLY_INPUT_HEADERS } from "@/config/constants";
import { BusinessValidationException } from "@/exceptions";
import { Injectable } from "@tsed/di";
import * as XLSX from "xlsx";
import * as fs from "fs/promises";

@Injectable()
export class XLSXReaderServices {
constructor() {}

async readXLSX(contractorId: number, month: number, year: number) {
// Read XLSX file

const workbook = XLSX.readFile("src/services/test.xlsx");
const fifthSheet = workbook.Sheets["monthlyfill"];
const jsonFormat = XLSX.utils.sheet_to_json(fifthSheet, {
header: 1,
}) as string[][];

const headers = jsonFormat[0];
const rows = jsonFormat.slice(1);

const date = new Date(year, month, 0).getDate();

const requiredHeaders = [...MONTHLY_INPUT_HEADERS];

const periodEndDateIndex = requiredHeaders.indexOf("Period End Date");
requiredHeaders.splice(
periodEndDateIndex + 1,
0,
...Array.from({ length: date }, (_, i) => `Day ${i + 1}`)
);

if (
headers.length !== requiredHeaders.length ||
!headers.every((header, index) => header === requiredHeaders[index])
) {
throw new BusinessValidationException("Invalid Headers");
}

const newRows: any[] = [];

rows.forEach((row) => {
const totalPresent = row.slice(4, 4 + date).filter((x) => x.toLowerCase() === "p").length;
const totalAbsent = row.slice(4, 4 + date).filter((x) => x.toLowerCase() === "a").length;
const totalLeave = row.slice(4, 4 + date).filter((x) => x.toLowerCase() === "l").length;
const totalRest = row.slice(4, 4 + date).filter((x) => x.toLowerCase() === "r").length;
const totalWeeklyOff = row.slice(4, 4 + date).filter((x) => x.toLowerCase() === "w").length;
const totalEstablishmentClosed = row.slice(4, 4 + date).filter((x) => x.toLowerCase() === "e").length;

newRows.push([
...row,
totalPresent,
totalAbsent,
totalLeave,
totalRest,
totalWeeklyOff,
totalEstablishmentClosed,
]);
});

const wb = XLSX.utils.book_new();

// Information above the table
const info = [
["Name of Establishment :", "XYZ Corporation"],
["Name of Owner :", "John Doe"],
["Labour Identification No. :", "12345"],
["***Name and Address of Principal Employer :", "1234 Main St, City, Country"],
["***Labour Identification No. of Principal Employer :", "67890"],
];

let startingRow = 1;

// Create a worksheet
const ws = XLSX.utils.aoa_to_sheet([]);

// Write non-tabular information
info.forEach((item, index) => {
const row = startingRow + index;
item.forEach((value, colIndex) => {
const address = XLSX.utils.encode_cell({ r: row - 1, c: colIndex });
ws[address] = { t: "s", v: value };
});
});

// Update starting row for headers
startingRow += info.length + 1;

// Write headers
requiredHeaders.forEach((header, index) => {
const address = XLSX.utils.encode_cell({ r: startingRow - 1, c: index });
ws[address] = { t: "s", v: header };
});

// Update starting row for data
startingRow += 1;

// Write data
newRows.forEach((row, rowIndex) => {
row.forEach((value, colIndex) => {
const address = XLSX.utils.encode_cell({ r: startingRow + rowIndex, c: colIndex });
ws[address] = { t: "s", v: value };
});
});

// Add the worksheet to the workbook
XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');

// Convert the workbook to a binary string
const wbout = XLSX.write(wb, { bookType: "xlsx", type: "binary" });

// Function to convert a binary string to a buffer
function s2ab(s: string) {
const buf = new ArrayBuffer(s.length);
const view = new Uint8Array(buf);
for (let i = 0; i < s.length; i++) view[i] = s.charCodeAt(i) & 0xff;
return Buffer.from(buf); // Convert ArrayBuffer to Buffer
}

// Write the file asynchronously
await fs.writeFile('EmployeeDetails1.xlsx', s2ab(wbout));

console.log(wb.SheetNames);
}
}