Getting an Array of Column Names At Sheetjs

function alphaToNum(alpha) {  var i = 0,      num = 0,      len = alpha.length;
  for (; i < len; i++) {    num = num * 26 + alpha.charCodeAt(i) - 0x40;  }
  return num - 1;}
function numToAlpha(num) {  var alpha = '';
  for (; num >= 0; num = parseInt(num / 26, 10) - 1) {    alpha = String.fromCharCode(num % 26 + 0x41) + alpha;  }
  return alpha;}
function _buildColumnsArray(range) {    var i,      res = [],      rangeNum = range.split(':').map(function(val) {        return alphaToNum(val.replace(/[0-9]/g, ''));      }),      start = rangeNum[0],      end = rangeNum[1] + 1;
  for (i = start; i < end ; i++) {    res.push(numToAlpha(i));  }
  return res;}
mocha.setup('bdd');chai.should();
describe('Alphabet Numbers with base 26', function() {
  describe('alphaToNum', function() {    it('alphaToNum(\'A\') should be equal 0', function() {      alphaToNum('A').should.equal(0);    });    it('alphaToNum(\'HELLO\') should be equal 3752126', function() {      alphaToNum('HELLO').should.equal(3752126);    });  });
  describe('numToAlpha', function() {    it('numToAlpha(3) should be equal \'D\'', function() {      numToAlpha(3).should.equal('D');    });    it('numToAlpha(1337) should be equal 3752126', function() {      numToAlpha(1337).should.equal('AYL');    });  });
  describe('Alphabet Numbers range', function() {    it('_buildColumnsArray(\'B10:K10\') should be deep equal [ \'B\', \'C\', \'D\', \'E\', \'F\', \'G\' , \'H\']', function() {      _buildColumnsArray('B10:H10').should.deep.equal(['B', 'C', 'D', 'E', 'F', 'G', 'H']);    });        it('_buildColumnsArray(\'A1: CA38\') should be equal [\'A\', \'B\', \'C\', \'D\', \'E\', \'F\', \'G\', \'H\', \'I\', \'J\', \'K\', \'L\', \'M\', \'N\', \'O\', \'P\', \'Q\', \'R\', \'S\', \'T\', \'U\', \'V\', \'W\', \'X\', \'Y\', \'Z\', \'AA\', \'AB\', \'AC\', \'AD\', \'AE\', \'AF\', \'AG\', \'AH\', \'AI\', \'AJ\', \'AK\', \'AL\', \'AM\', \'AN\', \'AO\', \'AP\', \'AQ\', \'AR\', \'AS\', \'AT\', \'AU\', \'AV\', \'AW\', \'AX\', \'AY\', \'AZ\', \'BA\', \'BB\', \'BC\', \'BD\', \'BE\', \'BF\', \'BG\', \'BH\', \'BI\', \'BJ\', \'BK\', \'BL\', \'BM\', \'BN\', \'BO\', \'BP\', \'BQ\', \'BR\', \'BS\', \'BT\', \'BU\', \'BV\', \'BW\', \'BX\', \'BY\', \'BZ\', \'CA\']', function() {        _buildColumnsArray('A1:CA38').should.deep.equal(['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ', 'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ', 'CA']);      });  });
});
mocha.run();

<script src="https://cdnjs.cloudflare.com/ajax/libs/chai/3.4.1/chai.min.js"></script><link href="https://cdnjs.cloudflare.com/ajax/libs/mocha/2.3.4/mocha.min.css" rel="stylesheet" /><script src="https://cdnjs.cloudflare.com/ajax/libs/mocha/2.3.4/mocha.min.js"></script><div id="mocha"></div>

Add dynamic columns with xlsx/ sheetjs

The process being followed in the code below is:

1. Transform the data by arranging the id and data properties from each object into a long list
2. Add an order property which is the number at the end of the id e.g. 1 for tagID1
3. Sort that new array by Timestamp then order - this may be unnecessary if your data is already in that order
4. Parse out the headers and create pairs of tagIDN quality and tagIDN value
5. Cast the data into a wide format by taking unique timestamps and creating 1 row per timestamp with as many column pairs as there are tags
6. Steps 4 and 5 are creating an array of arrays which can be passed to the XLSX method XLSX.utils.aoa_to_sheet
7. Because those long timestamps will be converted to scientific notation by Excel, set them to a number format of 0
8. Create a workbook, insert a sheet with the method from step 6 and save

Working code:

const XLSX = require("xlsx");

// input data
const input_data = [
  {
    "id": "tagID1", 
    "error": { "code": 0, "success": true }, 
    "data": [
      [1604395417575, 108, 3], 
      [1604395421453, 879, 3]
    ]
  },
  {
    "id": "tagID2", 
    "error": {"code": 0, "success": true}, 
    "data": [
      [1604395417575, 508, 3], 
      [1604395421453, 179, 3]
    ]
  }
];

// data transforms
// 1st transform - get long array of objects
const prep = input_data.map(obj => {
  return obj.data.map(arr => {
    return {
      "TimeStamp": arr[0],
      "id": obj.id,
      "order": +obj.id.substr(5, obj.id.length - 5),
      "quality": arr[1],
      "value": arr[2]
    }
  });
}).flat();

// sort by timestamp asc, order asc
prep.sort((a, b) => a.TimeStamp - b.TimeStamp || a.order - b.order);

// headers
const headers = ["Timestamp"].concat(
  [...new Set(prep.map(obj => obj.id))]
    .map(id => [`${id} quality`, `${id} value`])
    .flat()
);

// organise the data - in wide format
const ws_data = [...new Set(prep.map(obj => obj.TimeStamp))]
  .map(ts => {
    const objByTimestamp = prep.filter(obj => obj.TimeStamp === ts);
    let arr = [ts];
    objByTimestamp.forEach(obj => arr = arr.concat([obj.quality, obj.value]));
    return arr;
  });

// prepend the headers
ws_data.unshift(headers);

// to Excel
// new workbook
const wb = XLSX.utils.book_new();

// create sheet with array-of-arrays to sheet method
const ws = XLSX.utils.aoa_to_sheet(ws_data);

// assign sheet to workbook
XLSX.utils.book_append_sheet(wb, ws, "Sheet1");

// set column A as text
const range = XLSX.utils.decode_range(ws['!ref']);
console.log(range);
for (let i = range.s.r; i <= range.e.r; i++) {
  const ref = XLSX.utils.encode_cell({r: i , c: 0});
  console.log(ref);
  ws[ref].z = "0";
}

// save workbook
XLSX.writeFile(wb, "C:\\Users\\Robin\\Desktop\\so.xlsx", {});

Excel output:

Edit

To have double headers with merged cells on first row (for tag ids) - see the update:

const XLSX = require("xlsx");

// input data
const input_data = [
  {
    "id": "tagID1", 
    "error": { "code": 0, "success": true }, 
    "data": [
      [1604395417575, 108, 3], 
      [1604395421453, 879, 3]
    ]
  },
  {
    "id": "tagID2", 
    "error": {"code": 0, "success": true}, 
    "data": [
      [1604395417575, 508, 3], 
      [1604395421453, 179, 3]
    ]
  }
];

// data transforms
// 1st transform - get long array of objects
const prep = input_data.map(obj => {
  return obj.data.map(arr => {
    return {
      "TimeStamp": arr[0],
      "id": obj.id,
      "order": +obj.id.substr(5, obj.id.length - 5),
      "quality": arr[1],
      "value": arr[2]
    }
  });
}).flat();

// sort by timestamp asc, order asc
prep.sort((a, b) => a.TimeStamp - b.TimeStamp || a.order - b.order);

// headers
// const headers = ["Timestamp"].concat(
//   [...new Set(prep.map(obj => obj.id))]
//     .map(id => [`${id} quality`, `${id} value`])
//     .flat()
// );
const ids = [...new Set(prep.map(obj => obj.id))];
const headers1 = [""].concat(ids.map(id => Array(2).fill(id)).flat());
const headers2 = ["Timestamp"].concat(ids.map(id => Array(["quality", "value"])).flat()).flat();

// organise the data - in wide format
const ws_data = [...new Set(prep.map(obj => obj.TimeStamp))]
  .map(ts => {
    const objByTimestamp = prep.filter(obj => obj.TimeStamp === ts);
    let arr = [ts];
    objByTimestamp.forEach(obj => arr = arr.concat([obj.quality, obj.value]));
    return arr;
  });

// prepend the headers
ws_data.unshift(headers2);
ws_data.unshift(headers1);

// to Excel
// new workbook
const wb = XLSX.utils.book_new();

// create sheet with array-of-arrays to sheet method
const ws = XLSX.utils.aoa_to_sheet(ws_data);

// assign sheet to workbook
XLSX.utils.book_append_sheet(wb, ws, "Sheet1");

// set column A as text
const range = XLSX.utils.decode_range(ws['!ref']);
for (let i = range.s.r; i <= range.e.r; i++) {
  const ref = XLSX.utils.encode_cell({r: i , c: 0});
  ws[ref].z = "0";
}

// assign merges to sheet
// https://stackoverflow.com/questions/53516403/sheetjs-xlsx-how-to-write-merged-cells
const merges = ids.reduce((acc, curr, idx) => {
  acc.push({
    s: {r: 0, c: 1 + (2 *idx)},
    e: {r: 0, c: 1 + (2 *idx) + 1}
  });
  return acc;
}, []);
ws["!merges"] = merges;

// save workbook
XLSX.writeFile(wb, "C:\\Users\\Robin\\Desktop\\so.xlsx", {});

Excel output:

The method is per this post.

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

sheet_to_json producing bad array of array

After a long while of searching, I realized that I misread the documentation. Per https://docs.sheetjs.com/#json I was specifying the wrong header property in the Sheet2JSONOpts. Instead, it should have been:

this.workSheet = XLSX.utils.sheet_to_json<IRequestItem>(worksheet, {
  blankrows: false,
  header: 'A',
  raw: true,
  rawNumbers: true
});

---

Related Topics