http://www.perlmonks.org?node_id=414585

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]

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]
      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]

Back to Cool Uses for Perl