在SheetJS获取列名称数组

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

我正在尝试将Excel电子表格(.xlsx)解析为JSON。

I'm using the SheetJS node package for it.

我正在使用SheetJS节点包。

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/

以下是我目前正在尝试的不完美的工作:https://jsfiddle.net/t8hej9hu/

3 个解决方案

#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.

这里有一些不同的字母数字转换,受PHP解决方案的启发。这是一个简单的最小例子,只是为了完成工作而进行零错误检查。

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.

我们需要两个辅助函数来进行转换。字母字母的字符代码在Unicode表中已经按字母顺序排列,因此我们只需要在转换时添加或删除偏移量。

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:

_buildColumnsArray函数的最终版本:

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:

返回的数组必须与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.

这是一个工作示例,我一直在使用Mocha和Chai来测试(“重放”按钮在这里没有表现出预期的行为)结果,所以代码量要大一些。

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>

#2

This is how I have done using SheetJS.

这就是我使用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
        }
    }
 }

#3

If I get the question right, the task is to expand Excel column names range for the range like ["A1:DD38"].

如果我的问题是正确的,那么任务是扩展像[“A1:DD38”]这样的范围的Excel列名称范围。

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到DD的列块以及从1到38的行。我们只需要获得一个列出的列的列表:

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.

请注意,此行列名实际上用作数字。你从“A”开始,然后转到“Z”。然后“B”成为第一个数字,我们在零数字中继续“A”到“Z”,依此类推。

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

因此,列名实际上代表26-radix系统中的数字,其中0 =“A”,1 =“B”,依此类推。

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

在javascript中我们有Number()。toString(radix)方法将数字转换为具有任何给定基数的另一个数字系统,请参阅。

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

可以使用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.

对于具有基数26的系统,javascript使用0到9之间的数字,然后使用从“a”到“p”的小写字母表示其余符号。

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

我们可以通过简单的字符替换从javascript系统切换到excel系统(“A”到“Z”),因为系统具有相同的基础。

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

将每个数字转换为Excel系统并添加到生成的数组中

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.

更新:不幸的是,情况并不理想,我们有A ... Z然后AA ... AZ,而A代表零数字。所以在数字上它就像0..9然后00..09而且只有10 ... 19,所以我们需要人为地模仿00 ... 09的情况。

Here is jsfiddle with working example.

这是jsfiddle的工作示例。