SheetJS: transpose row values from array to object

Consider, if your spreadsheet was like this - parsing each row (and ignoring headers) would allow you to generate the desired output with greater ease:

You can transpose the sheet_to_json output to achieve this. Refer to this issue. Note sheet_to_json is called 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});

// transpose from row-major to column-major
// 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]; 
  }
}

// clean out undefineds
const transposed = rawTransposed.map(arr => arr.filter(k => !!k));

console.log(transposed);

Which will give you 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,
    '-',
    '-'
  ]
]

It's then more straight-forward to transform this array into the desired object e.g. you are converting yes to true and converting dates and blending the work days etc.

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});

// transpose from row-major to column-major
// 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]; 
  }
}

// clean out undefineds
const transposed = rawTransposed.map(arr => arr.filter(k => !!k));

// console.log(transposed);

// https://stackoverflow.com/questions/16229494/converting-excel-date-serial-number-to-date-using-javascript
function xlDateConvert(xlIndex) {
  const d = new Date(Math.round(xlIndex - 25569) * 86400000);
  return d.toISOString();
}

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});
}

// create custom object
const index = Array.from({length: 5}, (k, i) => i); // 5 keys in 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;
}, {});

// some custom property
output["uploadedBy"] = "foo";

// output
console.log(output);

Will output:

{
  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'
}