Python Excel表格列数的字母表达与数字的相互转换

Excel表格的列数用字母：A,B,C,...,Y,Z,AA,AB,AC,....来表示的，就称它为“列数”。

它的本质就是整数的二十六进制记数，但又有些不一样：若把A看作0，Z之后是AA相当于是“00”，这就是和普通进位制不同的地方。

与普通数制转换的差异

使用内置函数int(x, base=10)转换，把基权设置为26和36进行测试：

>>> int('A',base=26)-int('9',base=26)
1
>>> int('AA',base=26)-int('99',base=26)
27
>>> int('AAA',base=26)-int('999',base=26)
703
>>> int('P',base=26)-int('9',base=26)
16
>>> int('Q',base=26)-int('9',base=26)
Traceback (most recent call last):
  File "<pyshell#194>", line 1, in <module>
    int('Q',base=26)-int('9',base=26)
ValueError: invalid literal for int() with base 26: 'Q'
>>> int('Q',base=36)-9
17
>>> int('Z',base=36)-9
26

普通数制转换从0开始的，10进制以上的数字9以后才用到字母ABC...来代表各位上的数字；而“列数”的表达是从A开始的没有0~9。通过上面的测试规律可知，对“列数”中出现的字母，分段A~P和Q~Z各用26进制和36进制转换，然后乘以各自的位权后累加即可得到结果：

>>> def A2N(s:str)->int:
	ret = 0
	if s[0]>'P': t = int(s[0],base=36)-9
	else: t = int(s[0],base=26)-int('9',base=26)
	ret += t*26**(len(s)-1)
	s = s[1:]
	if s: ret += A2N(s)
	return ret

>>> A2N('A')
1
>>> A2N('AA')
27
>>> A2N('ZY')
701
>>> A2N('ZZ')
702
>>> A2N('AAA')
703
>>> A2N('ZZZ')
18278
>>> 

上面这个代码有个好处， 不区分字母大小写，这是因为int()自带的就这样的。以下内容中所有字符串转整数类的函数，如果也要支持小字字母，只需在函数首行位置添加 s=s.upper() 即可实现。 
 

Excel "列数"转成整数

如果不使用现成的int()函数，就按权展开求和，这也是非十进制转化十进制的普遍方法。以下代码的主体就一个return语句，我把按位展开用了一个推导式来完成：

>>> def Col2Int(s:str)->int:
    assert(isinstance(s, str))
    for i in s:
        if not 64<ord(i)<91:
            raise ValueError('Excel Column ValueError')
    return sum([(ord(n)-64)*26**i for i,n in enumerate(list(s)[::-1])])

>>> Col2Int('A')
1
>>> Col2Int('Z')
26
>>> Col2Int('AA')
27
>>> Col2Int('ZZ')
702
>>> Col2Int('ZJX')
17860
>>> Col2Int('ZZZ')
18278
>>> 26*26**2+26*26+26
18278
>>> Col2Int('ABCD')
19010
>>> 1*26**3+2*26**2+3*26**1+4*26**0
19010
>>> 

整数转换成“列数”

反过来整数转非十进制数的普遍方法是反复“除权求余”获取各位上的数字，先建一个字母列表 ['A', 'B', 'C', ...., 'Z'] 用于映射各位上的“数字”：

>>> def ExcelColumn(n:int)->str:
    assert(isinstance(n, int) and n>0)
    num = [chr(i) for i in range(65,91)]
    ret = []
    while n>0:
        n,m = divmod(n-1, len(num))
        ret.append(num[m])
    return ''.join(ret[::-1])

>>> ExcelColumn(703)
'AAA'
>>> ExcelColumn(26**2+26+1)
'AAA'
>>> ExcelColumn(5*26**4+4*26**3+3*26**2+2*26**1+1*26**0)
'EDCBA'
>>> 5*26**4+4*26**3+3*26**2+2*26**1+1*26**0
2357265
>>> ExcelColumn(2357265)
'EDCBA'
>>> 

或者不建列表，各位分别转换；和上段代码一样其中的 (n-1) 非常关键，原因是“列数”中没有“零”而是从“一”开始的，同样没有“10”直接从“Z”跳到“AA”：

>>> def num2chr(n:int)->str:
	ret = ''
	while n//26:
		n,m = divmod(n-1,26)
		ret += chr(65+m)
	if n: ret += chr(65+n-1)
	return ret[::-1]

递归法实现转换

>>> def ExcelColumn(n:int)->str:
	num = [chr(i) for i in range(65,91)]
	ret,(n,m) = '',divmod(n-1,26)
	if n: ret += ExcelColumn(n)
	ret += num[m]
	return ret

>>> ExcelColumn(1)
'A'
>>> ExcelColumn(26)
'Z'
>>> ExcelColumn(27)
'AA'
>>> ExcelColumn(702)
'ZZ'
>>> ExcelColumn(703)
'AAA'
>>> 
>>> 
>>> def Col2Int(s:str)->int:
	ret=0
	ret += (ord(s[0])-64)*26**(len(s)-1)
	s = s[1:]
	if s: ret += Col2Int(s)
	return ret

>>> Col2Int('Z')
26
>>> Col2Int('AA')
27
>>> Col2Int('XFD')
16384
>>> Col2Int('ILOVEYOU')
76181660199
>>> Col2Int('HANNYANG')
64736574559
>>> 

用第三方库函数转换

需要安装openpyxl库，另外它限制了“列数”最大值为三位字母，实际上 Excel2007+ 的列数最大值是16384 （是XFD并非ZZZ），而EXCEL2003等老版本的最大列数只有256。

>>> from openpyxl.utils import get_column_letter as ExcelColumn
>>> from openpyxl.utils import column_index_from_string as Col2Int
>>> 
>>> ExcelColumn(18278)
'ZZZ'
>>> ExcelColumn(17376)
'YRH'
>>> 
>>> Col2Int('XFD')
16384
>>> Col2Int('ZZZ')
18278
>>> Col2Int('AAAA')
Traceback (most recent call last):
  File "D:\Python38-32\lib\site-packages\openpyxl\utils\cell.py", line 121, in column_index_from_string
    return _COL_STRING_CACHE[str_col.upper()]
KeyError: 'AAAA'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<pyshell#127>", line 1, in <module>
    Col2Int('AAAA')
  File "D:\Python38-32\lib\site-packages\openpyxl\utils\cell.py", line 123, in column_index_from_string
    raise ValueError("{0} is not a valid column name".format(str_col))
ValueError: AAAA is not a valid column name
>>> 

leetcode题号168和171

Excel Sheet Column Title
Given a positive integer, return its corresponding column title as appear in an Excel sheet.    [#168]

Excel Sheet Column Number
Given a column title as appear in an Excel sheet, return its corresponding column number.     [#171]

本篇完，方便的话请点个赞再走^_^

---

学习交流 Python 的群和公众号：

 http://qr01.cn/FHYKEa