Imagine if your data in the spreadsheet was structured like this - processing each row without considering headers would make it easier to generate the intended results:
To achieve this, you can transform the sheet_to_json output accordingly as shown in this reference. Don't forget that sheet_to_json is utilized with {header: 1}:
const XLSX = require("xlsx");
const filename = "./Book1.xlsx";
const workbook = XLSX.readFile(filename);
const worksheet = workbook.Sheets["Calendar Config"];
const rowMajor = XLSX.utils.sheet_to_json(worksheet, {header: 1});
// Performing transpose from row-major to column-major
// Source: https://github.com/SheetJS/sheetjs/issues/1729
const rawTransposed = [];
for (let i=0; i<rowMajor.length; i++) {
for (let j=0; j<rowMajor[i].length; j++) {
if (!rawTransposed[j]) rawTransposed[j] = [];
rawTransposed[j][i] = rowMajor[i][j];
}
}
// Removing undefined values
const transposed = rawTransposed.map(arr => arr.filter(k => !!k));
console.log(transposed);
The resultant array will look like this:
[
[ 'Calendar Name', 'Standard' ],
[ 'Valid From', 44197 ],
[ 'Valid To', 44561 ],
[ 'Use Holidays', 'yes' ],
[
'Working Day',
'Monday',
'Tuesday',
'Wednesday',
'Thursday',
'Friday',
'Saturday',
'Sunday'
],
[
'Start',
0.3333333333333333,
0.3333333333333333,
0.3333333333333333,
0.3333333333333333,
0.3333333333333333,
'-',
'-'
],
[
'End',
0.8333333333333334,
0.8333333333333334,
0.8333333333333334,
0.8333333333333334,
0.8333333333333334,
'-',
'-'
]
]
With this structure, transforming the array into the desired object becomes more straightforward, such as converting 'yes' to 'true', processing dates and blending working days information.
const XLSX = require("xlsx");
const filename = "./Book1.xlsx";
const workbook = XLSX.readFile(filename);
const worksheet = workbook.Sheets["Calendar Config"];
const rowMajor = XLSX.utils.sheet_to_json(worksheet, {header: 1});
// Transposing from row-major to column-major
// Reference: https://github.com/SheetJS/sheetjs/issues/1729
const rawTransposed = [];
for (let i=0; i<rowMajor.length; i++) {
for (let j=0; j<rowMajor[i].length; j++) {
if (!rawTransposed[j]) rawTransposed[j] = [];
rawTransposed[j][i] = rowMajor[i][j];
}
}
// Removing undefined values
const transposed = rawTransposed.map(arr => arr.filter(k => !!k));
// console.log(transposed);
// Function to convert Excel date serial number to date using JavaScript
function xlDateConvert(xlIndex) {
const d = new Date(Math.round(xlIndex - 25569) * 86400000);
return d.toISOString();
}
// Function to convert Excel time serial number to time format with UTC offset adjustment
function xlTimeConvert(xlIndex, utcOffset) {
const hours = Math.floor((xlIndex % 1) * 24);
const minutes = Math.floor((((xlIndex % 1) * 24) - hours) * 60)
const d = new Date(Date.UTC(0, 0, xlIndex, hours - utcOffset, minutes));
return d.toLocaleTimeString("en-IT", {hour: "2-digit", minute:"2-digit", hour12: false});
}
// Creating custom object
const index = Array.from({length: 5}, (k, i) => i); // Representing 5 keys in the object
const output = index.reduce((acc, curr, idx) => {
switch (curr) {
case 0: // name
acc["name"] = transposed[idx].slice(1)[0];
break;
case 1: // validFrom
acc["validFrom"] = xlDateConvert(transposed[idx][1]);
break;
case 2: // validTo
acc["validTo"] = xlDateConvert(transposed[idx][1]);
break;
case 3: // useHolidays
acc["useHolidays"] = transposed[idx][1] === "yes" ? true : false;
break;
case 4: // workingDays
acc["workingDays"] = transposed[idx].slice(1).map((arr, i) => {
const start = transposed[idx + 1][i + 1];
const end = transposed[idx + 2][i + 1];
const dayStart = start === "-" ? start : xlTimeConvert(start, 10);
const dayEnd = end === "-" ? end : xlTimeConvert(end, 10);
return {
dayIndex: i,
dayStart: dayStart,
dayEnd: dayEnd
}
})
default:
break;
}
return acc;
}, {});
// Adding a custom property
output["uploadedBy"] = "foo";
// Outputting the result
console.log(output);
This operation yields the following result:
{
name: 'Standard',
validFrom: '2021-01-01T00:00:00.000Z',
validTo: '2021-12-31T00:00:00.000Z',
useHolidays: true,
workingDays: [
{ dayIndex: 0, dayStart: '08:00', dayEnd: '20:00' },
{ dayIndex: 1, dayStart: '08:00', dayEnd: '20:00' },
{ dayIndex: 2, dayStart: '08:00', dayEnd: '20:00' },
{ dayIndex: 3, dayStart: '08:00', dayEnd: '20:00' },
{ dayIndex: 4, dayStart: '08:00', dayEnd: '20:00' },
{ dayIndex: 5, dayStart: '-', dayEnd: '-' },
{ dayIndex: 6, dayStart: '-', dayEnd: '-' }
],
uploadedBy: 'foo'
}