asked May 26, 2014 at 1:44
2
start_index = 1 # it can start either at 0 or at 1
letter = ''
while column_int > 25 + start_index:
letter += chr[65 + int[[column_int-start_index]/26] - 1]
column_int = column_int - [int[[column_int-start_index]/26]]*26
letter += chr[65 - start_index + [int[column_int]]]
answered May 26, 2014 at 3:14
sundar natarajsundar nataraj
8,3062 gold badges31 silver badges44 bronze badges
3
The
xlsxwriter library includes a conversion function, xlsxwriter.utility.xl_col_to_name[index]
and is on 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.
answered Jan 14, 2015 at 21:47
travisatravisa
9278 silver badges12 bronze badges
4
The openpyxl library includes the conversion
function [amongst others] which you are looking for, 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'
answered Oct 10, 2018 at 0:45
RomanRoman
6347 silver badges11 bronze badges
0
My recipe for this was inspired by another answer on arbitrary base conversion [//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
answered Jun 3, 2016 at 0:19
2
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.
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]
answered Feb 28, 2015 at 14:07
LukeLuke
6601 gold badge9 silver badges20 bronze badges
Edited after some tough love from 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.
Note that like the originally linked question, this is 1-based rather than zero-based, so 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
answered May 26, 2014 at 3:18
MariusMarius
55.8k15 gold badges103 silver badges100 bronze badges
8
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
answered Aug 19, 2018 at 20:16
AxalixAxalix
2,7711 gold badge19 silver badges35 bronze badges
Recursive Implementation
import string
def spreadsheet_column_encoding_reverse_recursive[x]:
def converter[x]:
return [
""
if x == 0
else converter[[x - 1] // 26] + string.ascii_uppercase[[x - 1] % 26]
]
return converter[x]
Iterative Implementations
Version 1:
uses chr
, ord
def spreadsheet_column_encoding_reverse_iterative[x]:
s = list[]
while x:
x -= 1
s.append[chr[ord["A"] + x % 26]]
x //= 26
return "".join[reversed[s]]
Version 2: Uses string.ascii_uppercase
import string
def spreadsheet_column_encoding_reverse_iterative[x]:
s = list[]
while x:
x -= 1
s.append[string.ascii_uppercase[x % 26]]
x //= 26
return "".join[reversed[s]]
Version 3: Uses divmod
, chr
, ord
def spreadsheet_column_encoding_reverse_iterative[x]:
s = list[]
while x:
x, remainder = divmod[x - 1, 26]
s.append[chr[ord["A"] + remainder]]
return "".join[reversed[s]]
answered May 6, 2020 at 7:38
BrayoniBrayoni
5964 silver badges14 bronze badges
def _column[aInt]:
return chr[[[aInt - 1] / 26]+ 64] + chr[[[aInt - 1] % 26] + 1 + 64] if aInt > 26 else chr[aInt + 64]
print _column[1]
print _column[50]
print _column[100]
print _column[260]
print _column[270]
Output: A AX CV IZ JJ
answered Mar 2, 2021 at 12:59
This simple Python function works for columns with 1 or 2 letters.
def let[num]:
alphabeth = string.uppercase
na = len[alphabeth]
if num str:
return gspread.utils.rowcol_to_a1[1, col_num][:-1]
# letter2num['D'] => returns 4
# num2letter[4] => returns 'D'
answered May 27, 2020 at 23:43
George CGeorge C
1,05813 silver badges29 bronze badges
an easy to understand solution:
def gen_excel_column_name[col_idx, dict_size=26]:
"""generate column name for excel
Args:
col_idx [int]: column index, 1 based.
dict_size [int, optional]: NO. of letters to use. Defaults to 26 [A~Z].
Returns:
str: column name. e.g. A, B, C, AA, AB, AC
"""
if col_idx < 1:
return ''
# determine how many letters in the result
l = 1 # length of result
capcity = dict_size # number of patterns when length is l
while col_idx > capcity:
col_idx -= capcity
l += 1
capcity *= dict_size
res = []
col_idx -= 1 # now col_idx is a dict_size system. when dict_size = 3, l = 2, col_idx=2 means 02, col_idx=3 means 10
while col_idx > 0:
d = col_idx % dict_size
res.append[d]
col_idx = col_idx // dict_size
# padding leading zeros
while len[res] < l:
res.append[0]
# change digits to letters and reverse
res = [chr[65 + d] for d in reversed[res]]
return ''.join[res]
for i in range[1, 42]:
print[i, gen_excel_column_name[i, 3]]
part of the output:
1 A
2 B
3 C
4 AA
5 AB
6 AC
7 BA
8 BB
9 BC
10 CA
11 CB
12 CC
13 AAA
14 AAB
15 AAC
16 ABA
17 ABB
18 ABC
19 ACA
20 ACB
21 ACC
22 BAA
23 BAB
24 BAC
25 BBA
26 BBB
27 BBC
28 BCA
29 BCB
30 BCC
31 CAA
32 CAB
33 CAC
34 CBA
35 CBB
36 CBC
37 CCA
38 CCB
39 CCC
40 AAAA
41 AAAB
answered Jul 19 at 9:49