Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get Excel-Style Column Names from Column Number

Tags:

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.)

like image 590
saun jean Avatar asked Oct 03 '13 07:10

saun jean


People also ask

How do I get column names from column numbers in Excel?

In a row of Excel, e.g. cell A1, enter the column number =column() In the row below, enter =Address(1,A1) This will provide the result $A$1.

How do I get a list of column names in Excel?

Just click the Navigation Pane button under Kutools Tab, and it displays the Navigation pane at the left. Under the Column Tab, it lists all column header names.

How do I extract column headings in Excel?

Click anywhere in the table. On the Home tab on the ribbon, click the down arrow next to Table and select Toggle Header Row. Click the Table Design tab > Style Options > Header Row.


1 Answers

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' 
like image 141
martineau Avatar answered Sep 28 '22 04:09

martineau