Get Excel-Style Column Names from Column Number

by

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'

More Related Contents:

  1. Export a list of stock prices to excel
  2. How to write to an existing excel file without overwriting data (using pandas)?
  3. How to convert a given ordinal number (from Excel) to a date
  4. Running an Excel macro via Python?
  5. Python Pandas – Read csv file containing multiple tables
  6. How to match a string to pattern “Foo-Bar”, where Bar can be any element of a list? [closed]
  7. Check if the element is last in the list
  8. What’s wrong with my Python 2 program?
  9. How do you send a HEAD HTTP request in Python 2?
  10. Dead simple example of using Multiprocessing Queue, Pool and Locking
  11. Removing unicode \u2026 like characters in a string in python2.7 [duplicate]
  12. Getting only element from a single-element list in Python?
  13. What is the difference between len() and sys.getsizeof() methods in python?
  14. Python: Exporting environment variables in subprocess.Popen(..)
  15. Python regex for unicode capitalized words
  16. Merging dataframes based on date range
  17. Merge rows based on value (pandas to excel – xlsxwriter)
  18. Reading XML file and fetching its attributes value in Python
  19. Unexpected IndexError while removing list items [duplicate]
  20. Can’t create pdf using python PDFKIT Error : ” No wkhtmltopdf executable found:”
  21. Python: setting two variable values separated by a comma in python
  22. Switch between python 2.7 and python 3.5 on Mac OS X
  23. How to modify the default font in Tkinter?
  24. creating a spiral array in python?
  25. Get cell color from .xlsx
  26. Python returns length of 2 for single Unicode character string
  27. Catching exception in context manager __enter__()
  28. confused about random_state in decision tree of scikit learn
  29. Is possible to mapping view with class using mapper in SqlAlchemy?
  30. Python 2.7 : LookupError: unknown encoding: cp65001 [duplicate]

Leave a Comment