Monday, May 20, 2024
Homepage · excel
 Popular · Latest · Hot · Upcoming
185
rated 0 times [  188] [ 3]  / answers: 1 / hits: 31076  / 9 Years ago, fri, january 15, 2016, 12:00:00

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/


More From » excel
 Answers
8

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>




[#63715] Wednesday, January 13, 2016, 9 Years  [reply] [flag answer]
Only authorized users can answer the question. Please sign in first, or register a free account.
ravenl
Add To Favorites
Follow
Total Points: 338
Total Questions: 107
Total Answers: 112
Location: Belize
Member since Mon, Jun 20, 2022
2 Years ago
ravenl questions
Wed, Sep 1, 21, 00:00, 3 Years ago
Thu, Feb 18, 21, 00:00, 3 Years ago
Tue, Jan 12, 21, 00:00, 3 Years ago
Tue, Mar 17, 20, 00:00, 4 Years ago
Sun, Feb 16, 20, 00:00, 4 Years ago
View All
;