Sunday, October 29, 2006

Working with excel column labels

#excelize.py
#
#A module containing some helper methods to work with
#excel column label formated numbers.
#Excel and other spreadsheets use a wierd format for their column
#numbering. It is of the form A, B, ..., Y, Z, AA, AB, ..., AZ, BA, ...
#
#See Excel Numbering/Counting
#
#Changes:
#30/10/2006 - Got rid of the inner methods in excelize and deExcelize

def chars():
    """
    Returns an iterator object that yields each charector of the 
    english alphabet in capitals.
    """

    for i in range(26):
        yield chr(65 + i)
        
def excelIter():
    """
    Returns an iterator that yields each excel formated column
    number in ascending order.
    """

    for ch in chars():
        yield ch
    for exCh in excelIter():
        for ch in chars():
            yield exCh+ch


def excelize(n):
    """
    Returns excel formated column number for n
    
    Expects an int value greater than 0.
    """

    n-=1
    div = n/26
    if div==0:
        return chr(65+n)
    else:
        return excelize(div)+chr(65+n%26)


def deExcelize(s):
    """
    Returns an integer value for an excel formated column value
    
    Expects a string containing only capital letters from the
    english alphabet.
    """

    rem = s[:-1]
    if rem == "":
        return ord(s) - 64
    else:
        return 26*deExcelize(s[:-1]) + ord(s[-1]) - 64

3 Comments:

At 11:00 AM, Anonymous John Metta said...

This comment has been removed by a blog administrator.

 
At 11:14 AM, Anonymous John Metta said...

(Sorry, not a good blogger) That was supposed to be:

def excelize(n):
div = n/26
if div==0:
return chr(65+n)
else:
return excelize(div-1)+chr(65+n%26)

I copy/pasted a test version by mistake. BTW, the DeExcelizer would similiarly be modified to subtract 65, rather than 64. Just thought all this might be nice for a beginner to find. Cheers.

 
At 11:54 AM, Blogger monkeeboi said...

That is a good idea, never really occurred to me.

The reason I'd stick with the way I've currently implemented it is that the excel column numbers are an interesting numbering system that actually lacks the concept of zero. Not of practical value I agree, but something I'm hoping others will see.

[rant]0-indexes are actually a pain. You get mixed up with that subtract by one indexing arithmetic to get the actual index. This is a common source for newbie errors, something we tend to forget with experience.[/rant]

(ps I deleted the earlier comment.)

 

Post a Comment

<< Home