Getting an array of column names at SheetJS

February 16, 2021 by admin

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/

5 thoughts on “Getting an array of column names at SheetJS”

Admin

February 16, 2021 at 1:00 am
If I get the question right, the task is to expand Excel column names range for the range like [“A1:DD38”].

The range defines a block of columns from A to DD and rows from 1 to 38.
And we only need to get an expaned list of columns:
```
A, B, ... Z, AA, AB, ..., AZ, BA, ... BZ, ...
```
Note that this row of column names actually works as numbers. You start with “A” and go to “Z”. Then “B” becomes first digit and we continue with “A” to “Z” in the zero digit, and so on.

So column names are actually represent numbers in 26-radix system where 0 = “A”, 1 = “B” and so on.

In javascript we have Number().toString(radix) method to convert the number to another number system with any given base, see .

The reverse conversion can be done with parseInt(radix).

For the system with base 26, javascript uses numbers from 0 to 9 and then lowercase letters from “a” to “p” for the rest of symbols.

We can switch from javascript system to excel system (“A” to “Z”) with simple chars replacement, since systems have same base.

So our task reduces to this:
- Convert start / end columns to decimal values
- Iterate from start to end
- Convert each number to Excel system and add to the resulting array
Update:
Unfortunately, the situation is not ideal, we have A...Z and then AA ... AZ, while A represents the zero digit.
So in numbers it is like having 0..9 and then 00..09 and only then 10...19, so we need to artificially emulate the 00...09 situation.

Here is jsfiddle with working example.
Reply

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>

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

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

Pass the worksheet and the function returns an array of column names:

get_header_row(sheet) {
    let headers = [];
    let range = XLSX.utils.decode_range(sheet['!ref']);
    let C, R = range.s.r; /* start in the first row */
    /* walk every column in the range */
    for(C = range.s.c; C <= range.e.c; ++C) {
      let cell = sheet[XLSX.utils.encode_cell({c:C, r:R})] /* find the cell in the first row */

      let hdr = "";
        if(cell && cell.t) hdr = XLSX.utils.format_cell(cell);

        headers.push(hdr);
    }
    return headers;
}

Reference: Solution adopted from Github Issue

More Info: If you want to know more about the general structure, then please look at the official link

5 thoughts on “Getting an array of column names at SheetJS”

Alphabet Number conversion in JavaScript

Leave a Comment Cancel reply