Problems? Is your data what you think it is? PerlMonks

### Converting numbers to Excel Column indices

by Willworker (Acolyte)
 on Dec 14, 2004 at 01:38 UTC Need Help??
 Description: The basic base-26 conversion doesn't work right immediately (you get ...Y,AZ,AA,AB...), but a well placed decrement fixes it
```sub ConvertToAlpha {
my \$self = shift;
my \$inNumber = shift;
my @output = ();
while (\$inNumber > 0) {
unshift(@output, (\$inNumber % 26));
my \$shouldDecrement = (\$inNumber %26 == 0);
use integer;
\$inNumber = \$inNumber / 26;
if (\$shouldDecrement) {
\$inNumber--;
}
}
# @output-1 since we won't ever need to change the last digit
my %toAlpha = ( 1=>'A',        2=>'B',        3=>'C',
4=>'D',        5=>'E',        6=>'F',
7=>'G',        8=>'H',        9=>'I',
10=>'J',    11=>'K',    12=>'L',
13=>'M',    14=>'N',    15=>'O',
16=>'P',    17=>'Q',    18=>'R',
19=>'S',    20=>'T',    21=>'U',
22=>'V',    23=>'W',    24=>'X',
25=>'Y',    0=>'Z');
foreach (@output) {
\$_=\$toAlpha{\$_};
}
join("",@output);

}
```
Replies are listed 'Best First'.
Re: Converting numbers to Excel Column indices
by dragonchild (Archbishop) on Dec 14, 2004 at 13:57 UTC
How is this better than
```use Spreadsheet::WriteExcel::Utility;

my (\$cell) = xl_rowcol_to_cell( 0, \$col );
You now have something that's like "AX0" or something. Strip off the zero and you have the column.

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

Probably isn't. Just hadn't seen any (working) way to do this when I googled/google group'd the topic, and couldn't find a way in the OLE documentation that I'd seen, so I wanted to have something up for others. (And this way I get feedback, to boot.)
Re: Converting numbers to Excel Column indices
by 5mi11er (Deacon) on Sep 02, 2005 at 17:47 UTC
Just wanted to say, I just had a need to do just this, and hadn't been able to figure it out on my own. Works great and since I'm not actually futzing with excel, this fits better than having to load a module.

Thanks!

-Scott

Create A New User
Node Status?
node history
Node Type: snippet [id://414585]
help
Chatterbox?
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (3)
As of 2018-04-22 07:15 GMT
Sections?
Information?
Find Nodes?
Leftovers?
Voting Booth?
My travels bear the most uncanny semblance to ...

Results (82 votes). Check out past polls.

Notices?