Get Excel-Style Column Names from Column Number
Note
The code now shown in this answer isn't what it was when you accepted it because I've found and fixed a bug it had that prevented it from properly handling column numbers greater than 702
(corresponding to Excel column 'ZZ'
).
It's quite likely you never used the previous version with large enough column numbers to have encountered the issue. FWIW, the Microsoft Excel specifications and limits say it supports worksheets with up to 1,048,576 rows by 16,384 columns (i.e. column 'XFD'
).
LETTERS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
def excel_style(row, col):
""" Convert given row and column number to an Excel-style cell name. """
result = []
while col:
col, rem = divmod(col-1, 26)
result[:0] = LETTERS[rem]
return ''.join(result) + str(row)
if __name__ == '__main__':
addresses = [(1, 1), (1, 26),
(1, 27), (1, 52),
(1, 53), (1, 78),
(1, 79), (1, 104),
(1, 18253), (1, 18278),
(1, 702), # -> 'ZZ1'
(1, 703), # -> 'AAA1'
(1, 16384), # -> 'XFD1'
(1, 35277039)]
print('({:3}, {:>10}) --> {}'.format('row', 'col', 'Excel'))
print('==========================')
for row, col in addresses:
print('({:3}, {:10,}) --> {!r}'.format(row, col, excel_style(row, col)))
Output:
(row, col) --> Excel
========================
( 1, 1) --> 'A1'
( 1, 26) --> 'Z1'
( 1, 27) --> 'AA1'
( 1, 52) --> 'AZ1'
( 1, 53) --> 'BA1'
( 1, 78) --> 'BZ1'
( 1, 79) --> 'CA1'
( 1, 104) --> 'CZ1'
( 1, 18253) --> 'ZZA1'
( 1, 18278) --> 'ZZZ1'
( 1, 702) --> 'ZZ1'
( 1, 703) --> 'AAA1'
( 1, 16384) --> 'XFD1'
( 1, 35277039) --> 'BYEBYE1'
You have a couple of index issues:
So to fix your problem, you need to make all your indices match:
def colToExcel(col): # col is 1 based
excelCol = str()
div = col
while div:
(div, mod) = divmod(div-1, 26) # will return (x, 0 .. 25)
excelCol = chr(mod + 65) + excelCol
return excelCol
print colToExcel(1) # => A
print colToExcel(26) # => Z
print colToExcel(27) # => AA
print colToExcel(104) # => CZ
print colToExcel(26**3+26**2+26) # => ZZZ