Inverse of xlrd.cellname() or xlrd.colname()
cappy2112
Is there a function in xlrd to convert the Alpha column name to its
numeric (zero-based) name?
thanks
eorg
be a better way than using say cell ax312 which is un-human-readable :-)
these are the functions in question:
def cellname(rowx, colx):
""" (5, 7) => 'H6' """
return "%s%d" % (colname(colx), rowx+1)
def colname(colx):
""" 7 => 'H', 27 => 'AB' """
alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
if colx <= 25:
return alphabet[colx]
else:
xdiv26, xmod26 = divmod(colx, 26)
return alphabet[xdiv26 - 1] + alphabet[xmod26]
i am too lazy to reverse them for you but if you have problems, please,
send me a message and i'll help you :-)
cappy2112
On Apr 14, 8:34 am, eorg <eorg.ch...@gmail.com> wrote:
> i believe no - why would you need one?
Because the xlrd library uses numeric coordinates, instead of Excel
coordinates (H:16). Columns A-Z aren't a problem, it's the AA-ZZ ones
that are a problem.
> these are the functions in question:
>
> def cellname(rowx, colx):
> """ (5, 7) => 'H6' """
> return "%s%d" % (colname(colx), rowx+1)
>
> def colname(colx):
> """ 7 => 'H', 27 => 'AB' """
> alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
> if colx <= 25:
> return alphabet[colx]
> else:
> xdiv26, xmod26 = divmod(colx, 26)
> return alphabet[xdiv26 - 1] + alphabet[xmod26]
>
> i am too lazy to reverse them for you but if you have problems, please,
> send me a message and i'll help you :-)
I ended up using something similar which has worked fine so far. But
this wasn't obvious to me when I posted the message.
Column = (ord(leftDigit)-ord('A'))**2+26+
(ord(rightDigit)-ord('A'))
John Machin
On Apr 17, 4:06 am, "cappy2112" <cappy2...@gmail.com> wrote:
> Thanks for the reply.
>
> On Apr 14, 8:34 am, eorg <eorg.ch...@gmail.com> wrote:> i believe no - why would you need one?
>
> Because the xlrd library uses numeric coordinates, instead of Excel
> coordinates (H:16). Columns A-Z aren't a problem, it's the AA-ZZ ones
> that are a problem.
To rephrase eorg's question: why do you want to specify CONSTANT
coordinates, whether alphabetical or numerical? Consider what happens
to your code when you find you need to insert a few columns at
different places in the layout.
> I ended up using something similar which has worked fine so far. But
> this wasn't obvious to me when I posted the message.
> Column = (ord(leftDigit)-ord('A'))**2+26+
> (ord(rightDigit)-ord('A'))
"so far" tends to indicate that you have not tested this very much.
Inspection of the code shows a **2 which can't be correct -- the index
must be of the form K1 * ord(left) + K2 * ord(right) + K3 where the Ki
are constant integers; there is no way a quadratic function of a
"digit" has any place in the solution.
Is 69 == (6-0)**2 + 9, or is it 10*(6-0) + 9?
E.g. test values: C -> 2, so AC -> 28, BC -> 54, CC -> 80, CD -> 81
Looks like K1 should be 26, K2 should be 1, K3 should be whatever it
takes to give the correct answer.
For AC, your code produces 0 ** 2 + 26 + 2 i.e. 28 (so far, so good)
For BC, your code produces 1 ** 2 + 26 + 2 i.e. 29 (not good)
For CC, your code produces 2 ** 2 + 26 + 2 i.e. 32 (not good)
Try using xlrd.colname to help you check your answers, like I did:
| >>> [xlrd.colname(x) for x in [2, 28, 54, 80, 81]]
| ['C', 'AC', 'BC', 'CC', 'CD']
| >>>
I look forward to your next iteration.
Cheers,
John
cappy2112
>>i believe no - why would you need one? i am asking because there should
>>be a better way than using say cell ax312 which is un-human-readable :-)
ago- or whenever spreadsheets first appeared.
Why is it that we cannot use this notation now?
> To rephrase eorg's question: why do you want to specify CONSTANT
> coordinates, whether alphabetical or numerical? Consider what happens
> to your code when you find you need to insert a few columns at
> different places in the layout.
Because the file I am reading has values I need to retrieve from
specific columns.
This file was produced by a benchmark program which outputs a lot of
extra information we don't care about.
If the coordinates change, the user just needs to edit a text file
that my program reads in. My program does not need to change since its
input is based
on the contents of a user-defined text file.
cappy2112
On Apr 14, 8:34 am, eorg <eorg.ch...@gmail.com> wrote:
> i believe no - why would you need one? i am asking because there should
> be a better way than using say cell ax312 which is un-human-readable :-)
Excel and other spreadsheets started using this notation 20 some years
ago- or whenever spreadsheets first appeared.
Why is it that we cannot use this notation now? It makes perfect sense
tome: to refer the to the cell using the name that is in the screen in
front of your eyes.
Chris Withers
I'm sure John would accept a fully intergrated and unit tested patch
which didn't have any backwards compatability issues ;-)
cheers,
Chris
--
Simplistix - Content Management, Zope & Python Consulting
- http://www.simplistix.co.uk
Martin Burch
def excel2num(x):
return reduce(lambda s,a:s*26+ord(a)-ord('A')+1, x, 0)
Charlie Clark
> There isn't a function in xlrd, but there is a one-liner, and some
> discussion of various solutions, over at Stack Overflow.
> http://stackoverflow.com/questions/763691/programming-riddle-how-might-you-translate-an-excel-column-name-to-a-number
FWIW openpyxl does provide functions for converting between Excel column
names and ordinals together with other functions for dealing with the A1
style:
from openpyxl.utils import (
column_index_from_string,
get_column_letter,
)
These use a 1-based index, so adjust as necessary.
When profiling the code it turned out that going from 1 -> 'A' was
significantly faster. So, this, plus a cache (pre-generated as openpyxl
does, or lazy as xlsxwriter does) is the way to go within the Excel world
where there is a maximum of 16384 columns.
Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226