Getting an array of column names at SheetJS

Question

I'm trying to parse an Excel spreadsheet (.xlsx) into JSON.

I'm using the SheetJS node package for it.

However I can't realize such a simple thing as - how do I get a column name in the same format as it is stored internally?

Below is my code:

function _buildColumnsArray(range) {
    // range = "A1:CA38"
    // ...
    // should return ['A', ..., 'Z', 'AA', ..., 'AZ', 'BA', ..., 'BZ', 'CA'...]
}

// --------

var workbook = XLSX.readFile(fileUrl),
    sheets = workbook.Sheets,
    result = {};

result.sheetNames = [];

for (var i = 0; i < workbook.SheetNames.length; i++) {
    var sheet = workbook.Sheets[workbook.SheetNames[i]],
        csvSheet = XLSX.utils.sheet_to_csv(sheet);

    // '!ref' referring to a specific range by docs
    if (sheet["!ref"]) {
        var columns = _buildColumnsArray(sheet["!ref"]),
            rows = _buildRowsArray(columns, csvSheet);

        // build final result
        result[workbook.SheetNames[i]] = {
            columns: columns,
            rows: rows
        };
        result.sheetNames.push(workbook.SheetNames[i]);
    }
}

Here is what I'm currently trying which is not perfectly working: https://jsfiddle.net/t8hej9hu/

Answer 1

Alphabet Number conversion in JavaScript

Here's a bit different take on alphanumeric conversion, inspired by PHP solution. It's a bare-bone minimal example with zero error checking just to do the job.

We will need two helper functions for conversions. Character codes for alphabet letters are already in alphabet order in Unicode table, so all we need is to add or remove the offset when converting.

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

And another one for convertng numbers in to alphabet numbers.

function numToAlpha(num) {

  var alpha = '';

  for (; num >= 0; num = parseInt(num / 26, 10) - 1) {
    alpha = String.fromCharCode(num % 26 + 0x41) + alpha;
  }

  return alpha;
}

The final version of _buildColumnsArray function:

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

The returned array must be exactly as the column names in MS Excel:

['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']

And here is a working example, I've been using Mocha and Chai to test(the "replay" button does not behave as expected here) the results, so the amount of code is a bit bigger.

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>

Answer 2

Did the following in xlsx v0.16.9

const workbookHeaders = xlsx.readFile(filePath, { sheetRows: 1 });
const columnsArray = xlsx.utils.sheet_to_json(workbookHeaders.Sheets[sheetName], { header: 1 })[0];

Answer 3

This code will provide you with the column headers name from all the sheets in a workbook.

var XLSX = require('xlsx');
var workbook = XLSX.readFile('./test.xlsx');
var sheet_name_list = workbook.SheetNames;
let columnHeaders = [];
for (var sheetIndex = 0; sheetIndex < sheet_name_list.length; sheetIndex++) {
    var worksheet = workbook.Sheets[sheet_name_list[sheetIndex]];
    for (let key in worksheet) {
        let regEx = new RegExp("^\(\\w\)\(1\){1}$");
        if (regEx.test(key) == true) {
            columnHeaders.push(worksheet[key].v);
        }
    }
}

Answer 4

This is how I have done using SheetJS.

var colValues =[];

function checkCols(workbook)  //your workbook variable
  {
    var first_sheet_name = workbook.SheetNames[0];
    var worksheet = workbook.Sheets[first_sheet_name];
    var cells = Object.keys(worksheet);
    for (var i = 0; i < Object.keys(cells).length; i++) {
        if( cells[i].indexOf('1') > -1)
        {
            colValues.push(worksheet[cells[i]].v); //Contails all column names
        }
    }
 }