Search code examples
javascriptexceltypescriptexceljssheetjs

SheetJS: transpose row values from array to object

I am trying to create an array, where each "Working Day" is an object with an index and start/end date but I have no clue how to manipulate the JSON to have a specific structure.

I am using the following package: https://github.com/SheetJS/sheetjs

Current code:

const workbook = XLSX.read(file.buffer, { type: 'buffer' });
const worksheet = workbook.Sheets['Calendar Config'];
const parsed = XLSX.utils.sheet_to_json(worksheet);

Current output:

[
  {
    'Calendar Name': 'Standard',
    'Valid From': 44197,
    'Valid To': 44561,
    'Use Holidays': 'yes',
    'Working Day': 'Monday',
    Start: 0.3333333333333333,
    End: 0.8333333333333334
  },
  {
    'Working Day': 'Tuesday',
    Start: 0.3333333333333333,
    End: 0.8333333333333334
  },
  {
    'Working Day': 'Wednesday',
    Start: 0.3333333333333333,
    End: 0.8333333333333334
  },
  {
    'Working Day': 'Thursday',
    Start: 0.3333333333333333,
    End: 0.8333333333333334
  },
  {
    'Working Day': 'Friday',
    Start: 0.3333333333333333,
    End: 0.8333333333333334
  },
  { 'Working Day': 'Saturday', Start: '-', End: '-' },
  { 'Working Day': 'Sunday', Start: '-', End: '-' }
]

Desired JSON:

{
 "name": "Standard",
 "validFrom": "2021-01-01T00:00:00.000Z",
 "validTo": "2021-12-31T00:00:00.000Z",
 "useHolidays": true,
 "workingDays": [
  {
    "dayIndex": 0,
    "dayStart": "8:00",
    "dayEnd": "20:00"
  },
  {
    "dayIndex": 1,
    "dayStart": "8:00",
    "dayEnd": "20:00"
  },
  ...
  {
    "dayIndex": 6,
    "dayStart": "-",
    "dayEnd": "-"
  },
 ],
 "uploadedBy": "foo"
}

Parsed Excel Sheet:

excel screenshot

Solution

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

    1

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