Converting numbers to Excel Column indices

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);

}
[download]

Comment on Converting numbers to Excel Column indices Download Code

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 );` [download] 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.	[reply] [d/l]
Re^2: Converting numbers to Excel Column indices by Willworker (Acolyte) on Dec 29, 2004 at 22:46 UTC
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.)	[reply]
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	[reply]


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