Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

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) {
    # @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) {

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.



Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: snippet [id://414585]
[jdporter]: Party at thezip's!

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (2)
As of 2017-08-17 03:17 GMT
Find Nodes?
    Voting Booth?
    Who is your favorite scientist and why?

    Results (279 votes). Check out past polls.