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:
This comment has been removed by a blog administrator.
(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.
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