What is the most idiomatic way of generating A1 reference style column names?

Question

I am trying to create an algorithm that will implicitly handle every cell identifier possible; using A1 reference style. The A1 reference style is typically limited to columns (A - IV), and rows (0 - 65,536). This is the format used to generate Excel, and Numeric spreadsheets. I understand why these limitations are in place, but I am trying to wrap my head around the "correct" way to name the columns; procedurally. More specifically, I am trying to understand how to procedurally generate the correct names for each column. The result would be a dictionary with columns A-Z, then AA-ZZ, then AAA-ZZZ, and so on. The cells of this "spreadsheet" would be something like AHD129, or A1, or XAPRC100204.

It all started with trying to generate the cells of a chessboard, which was relatively easy:

board = {}
for file in 'ABCDEFGH':
    for rank in range(0, 8):
        cell = file + str(rank + 1)
        board[cell] = ''

This handles every combination of a chess board (A1 - H8), and places it all into a dictionary. The problem is that it is highly explicit.

We could implicitly handle every possible combination (A1 - Z26), like so...

def generate(units):
    cells = {}
    alphabet = list('ABCDEFGHIJKLMNOPQRSTUVWXYZ')
    for column in range(0, units):
        char = alphabet[column]
        for row in range(0, units):
            cell = char + str(row + 1)
            cells[cell] = ''
    return cells

This is better, because it allows us to generate a "chessboard", within a given range (A1-Z26). You can pass this function any integer from 1 to 26, but an error will be thrown when (units > 26). If we divided the "chessboard" by 27 units, then we would need to generate (A1-AA27).

unit = 1 -> 26 | result A1 - Z26

unit = 1 -> 52 | result A1 - AZ52

unit = 1 -> 78 | result A1 - BZ78

unit = 1 -> 104| result A1 - CZ104

My math may be off by a few integers, but I am basically just trying to create a representation of a square. Columns and Rows are ALWAYS equal, which is why I was approaching this with nested for loops. I know that I could write a massive conditional block to handle each range of 26, beyond the initial 26 characters, something like:

if column in range(0, len(alphabet)):
    char = alphabet[i]
elif column in range(len(alphabet), len(alphabet) * 2):
    char = 'A' + alphabet[i]
elif column in range(len(alphabet) * 2, len(alphabet) * 3):
    char = 'B' + alphabet[i]

...but that would be counter-intuitive. I am trying to handle an arbitrary integer (such as: 1,000,000, or 63,432). I am assuming (welp) that it has something do with "brute-forcing" the column name by means of a while loop, but I also know that this is Python. i.e. there is an extremely easy way to solve this that I have never come across before.

I don't want anyone to "write my code" for me, but could somebody please point me in the right direction? How does one go about procedurally generating column names in an A1 reference style spreadsheet format? What is the best way to construct a string from a given list of characters in Python?

Please do not suggest a library to solve this, as I am trying to understand the underlying solution. Sorry for any incorrect terminology, and please correct my words if I have used anything incorrectly. Any help, or consideration, would be greatly appreciated. I am more than willing to revise this entire question for future viewers.

Answer 1

How to create a function that converts a Number to a Bijective Hexavigesimal?

The encoding is called bijective-base 26. Or you can use the fancy term for base 26 used in the above stack overflow post. Actually, the decoding is really easy. ABC = 1 * 26 * 26 + 2 * 26 + 3. Treat the number as if it were base 26, but with A=1, Z=26 rather than A=0, Z=25.

== Update ==

Now that I think about it, the reverse direction is pretty easy, too. The standard algorithm for converting to base 26 is that you repeatedly divide by 26, and the remainders give you the digits of the number is reverse order.

For this algorithm, repeatedly divide the previous quotient minus 1 by 26. The remainder, when 0-25 is converted to A-Z, gives you the answer in reverse order.

#convert 65536 
divmod(65535, 26)
(2520, 15)
>>> divmod(2519, 26)
divmod(2519, 26)
(96, 23)
>>> divmod(95, 26)
divmod(95, 26)
(3, 17)
>>> divmod(2, 16)
divmod(2, 16)
(0, 2)
# The remainders in reverse order are 2,17,23,15 -> CRXP

Answer 2

I am back; with a vengeance. I could not find out the most idiomatic way to do this, but I did come up with a naive method for generating an A1 Reference Style Grid. You can enter any integer greater than zero for the width or height parameters.

def generate_cells(width, height):
    """Generates cell-names for an 'A1 Reference Style' grid,
        and places them into a dictionary with empty values.
    width and height parameters are both = integer > zero"""
    
    alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' # explicit to avoid import
    empty_grid = {}
    for column in range(1, width+1):
    
        bijective_hexavigesimal = []
        while column: # repetitiously reduce unit with divmod()
            column, remainder = divmod(column - 1, 26)
            bijective_hexavigesimal[:0] = alphabet[remainder]
            
        column = ''.join(bijective_hexavigesimal)
        
        for row in range(1, height+1):
            cell = column + str(row)
            empty_grid[cell] = ''
                 
    return empty_grid

Returns a dictionary of keys with empty string values... Enjoy.