xls_col_to_num.py

#-*- coding: UTF-8 -*- 

# author : recall404
# email  : tk657309822@gmail.com

"""
将 xls 文件对应的列由字母转换为数字，方便 xlrd 等读取数据
源代码是从 XlsxWriter 中 copy 的
并做了两个小修改：
    1. 忽略大小写
    2. 移除内容中的数字

>>> col_to_num('A')
0
>>> col_to_num('AB')
27
>>> col_to_num('ABA')
728
>>> col_to_num('AAB')
703
"""

import re

def col_to_num(col_str):
    """ 
        Convert base26 column string to number. 
    """
    fix_string = col_str.upper()
    fix_string = re.sub('\d+','',fix_string)
    expn = 0
    col_num = 0
    for char in reversed(fix_string):
        col_num += (ord(char) - ord('A') + 1) * (26 ** expn)
        expn += 1
    return (col_num - 1)


if __name__ == "__main__":
    import doctest
    doctest.testmod()