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