类Excel表格坐标的转化

类Excel表格坐标的转化

有时候我们需要操作类excel表格的时候需要知道表格的具体坐标，比如我们知道A2的数组坐标是[0,1],怎么把A2转为这个坐标。或者将数组转为坐标，只想知道列

1. 坐标转数组

//getIdFromColumnName调用这个函数，可以将坐标转为数组
//console.log(getIdFromColumnName('A2')) => [0,1]
//console.log(getIdFromColumnName('B4')) => [1,3]
//console.log(getIdFromColumnName('C5')) => [2,4]
      function getIdFromColumnName(id) {
		      // Get the letters
		      var t = /^[a-zA-Z]+/.exec(id)
		
		      if (t) {
		        // Base 26 calculation
		        var code = 0
		        for (var i = 0; i < t[0].length; i++) {
		          code +=
		            parseInt(t[0].charCodeAt(i) - 64) *
		            Math.pow(26, t[0].length - 1 - i)
		        }
		        code--
		        // Make sure  starts on zero
		        if (code < 0) {
		          code = 0
		        }
		
		        // Number
		        var number = parseInt(/[0-9]+$/.exec(id))
		        if (number > 0) {
		          number--
		        }
		
		        id = [code, number]
		      }
		      // console.log(id)
		      return id
		    }

示例：A2， B4， C5

2.坐标转数组

//console.log(getColumnNameFromId([8,9])) =>I10
//console.log(getColumnNameFromId([2,5])) =>C6
//console.log(getColumnNameFromId([6,7])) =>G8
     function getColumnNameFromId(cellId) {
		      if (!Array.isArray(cellId)) {
		        cellId = cellId.split('-')
		      }
		      return this.getColumnName(parseInt(cellId[0])) + (parseInt(cellId[1]) + 1)
		    }

示例：[8,9], [2,5],[6,7]

3.只获取列坐标

/*
示例
console.log(getColumnName(3)) => D
console.log(getColumnName(5)) => F
console.log(getColumnName(9)) => J
*/
      function  getColumnName(i) {
		      var letter = ''
		      if (i > 701) {
		        letter += String.fromCharCode(64 + parseInt(i / 676))
		        letter += String.fromCharCode(64 + parseInt((i % 676) / 26))
		      } else if (i > 25) {
		        letter += String.fromCharCode(64 + parseInt(i / 26))
		      }
		      letter += String.fromCharCode(65 + (i % 26))
		      // console.log(letter)
		      return letter
		    }

示例：3，5，9