Getting an array of column names at SheetJS
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 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);
}
}
}
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
}
}
}