Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Converting numbers to Excel Column indices

by Willworker (Acolyte)
on Dec 14, 2004 at 01:38 UTC ( #414585=snippet: print w/ replies, xml ) 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);

}
Comment on Converting numbers to Excel Column indices
Download Code
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

Back to Snippets Section

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: snippet [id://414585]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (12)
As of 2014-12-19 15:07 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (84 votes), past polls