Python 将电子表格编号转换为列字母
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23861680/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert spreadsheet number to column letter
提问by jason
I'm looking for the opposite to this Q&A: Convert an excel or spreadsheet column letter to its number in Pythonic fashion.
我正在寻找与此问答相反的内容：以 Pythonic 方式将 excel 或电子表格列字母转换为其编号。
or this one but in python How to convert a column number (eg. 127) into an excel column (eg. AA)
或者这个但是在python中如何将列号（例如127）转换为excel列（例如AA）
采纳答案by sundar nataraj
def colnum_string(n):
string = ""
while n > 0:
n, remainder = divmod(n - 1, 26)
string = chr(65 + remainder) + string
return string
print(colnum_string(28))
#output:AB
回答by Marius
Edited after some tough love from Meta
在 Meta 的一些艰难的爱之后编辑
The procedure for this involves dividing the number by 26 until you've reached a number less than 26, taking the remainder each time and adding 65, since 65 is where 'A' is in the ASCII table. Read up on ASCII if that doesn't make sense to you.
此过程涉及将数字除以 26，直到达到小于 26 的数字，每次取余数并添加 65，因为 65 是 'A' 在ASCII 表中的位置。如果这对您没有意义，请阅读 ASCII。
Note that like the originally linked question, this is 1-based rather than zero-based, so A -> 1, B -> 2.
请注意，与最初链接的问题一样，这是从 1 开始的而不是从 0 开始的，因此A -> 1, B -> 2。
def num_to_col_letters(num):
letters = ''
while num:
mod = (num - 1) % 26
letters += chr(mod + 65)
num = (num - 1) // 26
return ''.join(reversed(letters))
Example output:
示例输出：
for i in range(1, 53):
print i, num_to_col_letters(i)
1 A
2 B
3 C
4 D
...
25 Y
26 Z
27 AA
28 AB
29 AC
...
47 AU
48 AV
49 AW
50 AX
51 AY
52 AZ
回答by travisa
The xlsxwriterlibrary includes a conversion function, xlsxwriter.utility.xl_col_to_name(index)and is on github
所述xlsxwriter库包括一个转换功能，xlsxwriter.utility.xl_col_to_name(index)并且是github上
here is a working example:
这是一个工作示例：
>>> import xlsxwriter
>>> xlsxwriter.utility.xl_col_to_name(10)
'K'
>>> xlsxwriter.utility.xl_col_to_name(1)
'B'
>>> xlsxwriter.utility.xl_col_to_name(0)
'A'
Notice that it's using zero-indexing.
请注意，它使用零索引。
回答by Luke
Just for people still interest in this. The chosen answer by @Marius gives wrong outputs in some cases, as commented by @jspurim. Here is the my answer.
只是为了人们仍然对此感兴趣。正如@jspurim 所评论的那样，@Marius 选择的答案在某些情况下会给出错误的输出。这是我的答案。
import string
def convertToTitle(num):
title = ''
alist = string.uppercase
while num:
mod = (num-1) % 26
num = int((num - mod) / 26)
title += alist[mod]
return title[::-1]
回答by Alex Benfica
This simple Python function works for columns with 1 or 2 letters.
这个简单的 Python 函数适用于具有 1 或 2 个字母的列。
def let(num):
alphabeth = string.uppercase
na = len(alphabeth)
if num <= len(alphabeth):
letters = alphabeth[num-1]
else:
letters = alphabeth[ ((num-1) / na) - 1 ] + alphabeth[((num-1) % na)]
return letters
回答by Giancarlo Sportelli
My recipe for this was inspired by another answer on arbitrary base conversion (https://stackoverflow.com/a/24763277/3163607)
我的配方灵感来自另一个关于任意基础转换的答案（https://stackoverflow.com/a/24763277/3163607）
import string
def n2a(n,b=string.ascii_uppercase):
d, m = divmod(n,len(b))
return n2a(d-1,b)+b[m] if d else b[m]
Example:
例子：
for i in range(23,30):
print (i,n2a(i))
outputs
产出
23 X
24 Y
25 Z
26 AA
27 AB
28 AC
29 AD
回答by Andrei
Just to complicate everything a little bit I added caching, so the name of the same column will be calculated only once. The solution is based on a recipe by @Alex Benfica
只是为了使一切复杂化，我添加了缓存，因此同一列的名称将只计算一次。该解决方案基于@Alex Benfica 的食谱
import string
class ColumnName(dict):
def __init__(self):
super(ColumnName, self).__init__()
self.alphabet = string.uppercase
self.alphabet_size = len(self.alphabet)
def __missing__(self, column_number):
ret = self[column_number] = self.get_column_name(column_number)
return ret
def get_column_name(self, column_number):
if column_number <= self.alphabet_size:
return self.alphabet[column_number - 1]
else:
return self.alphabet[((column_number - 1) / self.alphabet_size) - 1] + self.alphabet[((column_number - 1) % self.alphabet_size)]
Usage example:
用法示例：
column = ColumnName()
for cn in range(1, 40):
print column[cn]
for cn in range(1, 50):
print column[cn]
回答by Axalix
Recursive one line solution w/o libraries
没有库的递归单行解决方案
def column(num, res = ''):
return column((num - 1) // 26, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'[(num - 1) % 26] + res) if num > 0 else res
回答by Roman
The openpyxl libraryincludes the conversion function(amongst others) which you are looking for, get_column_letter:
该openpyxl库包括的转换功能，你正在寻找的，（其中包括）get_column_letter：
>>> from openpyxl.utils.cell import get_column_letter
>>> get_column_letter(1)
'A'
>>> get_column_letter(10)
'J'
>>> get_column_letter(3423)
'EAQ'
回答by Ricky
import math
num = 3500
row_number = str(math.ceil(num / 702))
letters = ''
num = num - 702 * math.floor(num / 702)
while num:
mod = (num - 1) % 26
letters += chr(mod + 65)
num = (num - 1) // 26
result = row_number + ("".join(reversed(letters)))
print(result)
