CODEWITHSUNDEEP
pythonexcel
Daniel Standage
Daniel Standage

Reputation: 8314

Convert column offset to column name

Excel files can contain up to 16,384 (214) columns. The first 26 columns are labeled "A", "B", "C", and so on. After that it is "AA", "AB", "AC", ..., "BA", "BB", "BC", ..., and so on, but two-letter labels only support an additional 262=676 columns. So eventually we see "AAA", "AAB", "AAC", and so on, to a max of "XFD".

I'd like to write a Python function that converts a column offset/index to a column label.

Offset Label
0 A
1 B
... ...
25 Z
26 AA
27 AB
... ...
700 ZY
701 ZZ
702 AAA
703 AAB
... ...
16383 XFD

For the first 26 columns this is trivial.

>>> offset = 0
>>> chr(offset + 65)
'A'

But how do I generalize this to support all valid inputs and outputs?

I tried adapting this code and converting the offsets to numbers in base 26, and then using chr to convert each place value to a letter. But I couldn't get it to work as intended.

def numberToBase(n, b):
    if n == 0:
        return [0]
    digits = []
    while n:
        digits.append(int(n % b))
        n //= b
    return digits[::-1]

Upvotes: 0

Views: 194

Answers (1)

Cubix48
Cubix48

Reputation: 2681

Here is my version using recursion:

def column_offset_to_column_name(index):
    if index < 0:
        return ""
    quotient, remainder = divmod(index, 26)
    return column_offset_to_column_name(quotient - 1) + chr(remainder + 65)

Upvotes: 1

Related Questions