Get Excel-Style Column Names from Column Number

Question

This is the code for providing the COLUMN name when the row and col ID is provided but when I give values like row = 1 and col = 104, it should return CZ, but it returns D@

row = 1
col = 104
div = col
column_label = str()
while div:
    (div, mod) = divmod(div, 26)
    column_label = chr(mod + 64) + column_label

print column_label

What is wrong with what I am doing?

(This code is in reference for EXCEL Columns, where I provide the Row,Column ID value and expect the ALPHABETIC value for the same.)

Answer 1

Here's the answer I expected using itertools.

import string
import itertools

def gen_labels(seq=string.ascii_uppercase):
   for n in itertools.count(1):
     for p in itertools.product(seq, repeat=n):
       yield("".join(p))

You can use a short input sequence see the results.

>>> labels = gen_labels("ABC")
>>> [next(labels) for i in range(26)]
['A', 'B', 'C', 'AA', 'AB', 'AC', 'BA', 'BB', 'BC', 'CA', 'CB', 'CC', 
'AAA', 'AAB', 'AAC', 'ABA', 'ABB', 'ABC', 'ACA', 'ACB', 'ACC', 'BAA', 
'BAB', 'BAC', 'BBA', 'BBB']

Answer 2

Another solution to get the Excel-style column is by using an existing library, e.g. xlsxwriter has a utility function for this:

from xlsxwriter.utility import xl_col_to_name

xl_col_to_name(0)  # A
xl_col_to_name(25)  # Z
xl_col_to_name(26)  # AA
xl_col_to_name(18277)  # ZZZ

This function uses zero indexed columns, so for your example one should subtract 1 before calling it.

Answer 3

All the current answers use old Python conventions. Here an answer using Python 3.6+ list manipulation and type annotations.

import string

def excel_style(col: int) -> str:
    """Convert given row and column number to an Excel-style cell name."""
    result = []
    while col:
        col, rem = divmod(col - 1, 26)
        result.append(string.ascii_uppercase[rem])
    result.reverse()

    return "".join(result)

Answer 4

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'

Answer 5

Here's another way to get excel column names without a loop, using a pandas multiindex dataframe. This is a modification of a generalized base-to-base converter, so the code is a little lengthier than some of the other options, but I think it's effective:

def xlcolumn(num):
    base = ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"]
    baseln = len(base)
    idx = [""]+base
    num = num-1

    # create pandas multiindex using idx, base
    # current excel version has 16384 columns (A --> XFD), so multiindex needs to have a minimum of 3 levels:
    #   (26x26x26 = 17576 > 16384 columns) 

    index = pd.MultiIndex.from_product([idx, idx, idx],names=['level 1', 'level 2', 'level 3'])
    df = pd.DataFrame(index = index)
    df = df.drop("",level = 'level 3')
    df = df.iloc[:baseln].append(df.drop("",level = 'level 2'))
    df['val']=1

    if num < baseln:
        xlcol = str(df.iloc[num].name[2])
    elif num >= baseln and num < baseln**2:
        xlcol = str(df.iloc[num].name[1])+str(df.iloc[num].name[2])
    else:
        xlcol = str(df.iloc[num].name[0])+str(df.iloc[num].name[1])+str(df.iloc[num].name[2])

    return xlcol

Answer 6

def ColNum2ColName(n):
   convertString = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
   base = 26
   i = n - 1

   if i < base:
      return convertString[i]
   else:
      return ColNum2ColName(i//base) + convertString[i%base]

---

EDIT: Right, right zondo.

I just approached A, B, .. AA, AB, ... as a numeric base with digits A-Z.

A = 1
B = 2
 .
 .
X = 24
Y = 25
Z = 26
 .
 .
 .

It's an easy way without any while loop etc. and works for any number > 0.

Answer 7

use this code:

def xlscol(colnum):
    a = []
    while colnum:
        colnum, remainder = divmod(colnum - 1, 26)
        a.append(remainder)
    a.reverse()
    return ''.join([chr(n + ord('A')) for n in a])

Answer 8

I love maritineau's answer since its code looks plain and easy to follow. But it can't handle the column number which is greater than 26**2 + 26. So I modify part of it.

def excel_col(col):
    """Covert 1-relative column number to excel-style column label."""
    quot, rem = divmod(col-1,26)
    return excel_col(quot) + chr(rem+ord('A')) if col!=0 else ''



if __name__=='__main__':
    for i in [1, 26, 27, 26**3+26**2+26]:
        print 'excel_col({0}) -> {1}'.format(i, excel_col(i))

Results

excel_col(1) -> A
excel_col(26) -> Z
excel_col(27) -> AA
excel_col(18278) -> ZZZ

Answer 9

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

Answer 10

I think it is something like this :

def get_col(col):
    """Get excel-style column names"""
    (div, mod) = divmod(col, 26)
    if div == 0:
        return str(unichr(mod+64))
    elif mod == 0:
        return str(unichr(div+64-1)+'Z')
    else:
        return str(unichr(div+64)+unichr(mod+64))

Some tests :

>>> def get_col(col):
...     (div, mod) = divmod(col, 26)
...     if div == 0:
...         return str(unichr(mod+64))
...     elif mod == 0:
...         return str(unichr(div+64-1)+'Z')
...     else:
...         return str(unichr(div+64)+unichr(mod+64))
... 
>>> get_col(105)
'DA'
>>> get_col(104)
'CZ'
>>> get_col(1)
'A'
>>> get_col(55)
'BC'

Answer 11

I think i figured it out. divmod(104,26) gives mod=0 which makes chr(0+64) = 64 ie '@'.

if i add this line before column_label "mod=26 if mod==0 else mod" i think it should work fine

column_label=''
div=104
while div:
    (div, mod) = divmod(div, 26)
    mod=26 if mod==0 else mod
    column_label = chr(mod + 64) + column_label

print column_label