Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Re: Get Excel column name for given decimal number

by johngg (Canon)
on Mar 27, 2020 at 11:35 UTC ( #11114715=note: print w/replies, xml ) Need Help??


in reply to Get Excel column name for given decimal number

Incrementing the column name auto-magically rolls 'Z' over to 'AA' and 'ZZ' over to 'AAA' so could be used here if access to non-core modules is difficult.

johngg@shiraz:~/perl/Monks$ perl -Mstrict -Mwarnings -E ' my @tests = ( -3, 0, 1, 26, 27, 701, 702, 703, 10342, 397645 ); foreach my $test ( @tests ) { printf qq{Col. no. %6d : Col name %s\n}, $test, getColName( $test +); } sub getColName { my $colNo = shift; return q{Bad column number} unless $colNo > 0; my $colName = q{A}; return $colName if $colNo == 1; do { $colName ++; $colNo -- } while $colNo > 1; return $colName; }' Col. no. -3 : Col name Bad column number Col. no. 0 : Col name Bad column number Col. no. 1 : Col name A Col. no. 26 : Col name Z Col. no. 27 : Col name AA Col. no. 701 : Col name ZY Col. no. 702 : Col name ZZ Col. no. 703 : Col name AAA Col. no. 10342 : Col name OGT Col. no. 397645 : Col name VPFA

I hope this is of interest.

Update : The do { ... } while ... ; using a statement modifier always executes at least once, thus making the return $colName if $colNo == 1; statement ahead of it necessary. Using while ( ... ) { ... } works better here.

johngg@shiraz:~/perl/Monks$ perl -Mstrict -Mwarnings -E ' my @tests = ( -3, 0, 1, 26, 27, 701, 702, 703, 10342, 397645 ); foreach my $test ( @tests ) { printf qq{Col. no. %6d : Col name %s\n}, $test, getColName( $test +); } sub getColName { my $colNo = shift; return q{Bad column number} unless $colNo > 0; my $colName = q{A}; while ( $colNo > 1 ) { $colName ++; $colNo --; } return $colName; }' Col. no. -3 : Col name Bad column number Col. no. 0 : Col name Bad column number Col. no. 1 : Col name A Col. no. 26 : Col name Z Col. no. 27 : Col name AA Col. no. 701 : Col name ZY Col. no. 702 : Col name ZZ Col. no. 703 : Col name AAA Col. no. 10342 : Col name OGT Col. no. 397645 : Col name VPFA

Cheers,

JohnGG

Replies are listed 'Best First'.
Re^2: Get Excel column name for given decimal number
by Akshit (Novice) on Mar 28, 2020 at 13:52 UTC

    Thanks all, such nice ideas to come up with for my problem. I will take those ideas and try which is the best fit for my code.

      Take into account that there is a real performance difference between the three methods, which especially shows for high column numbers:

      (warning: too few iterations for a reliable count) Rate getcolname int2LATIN col2label getcolname 0.119/s -- -100% -100% int2LATIN 31.4/s 26218% -- -55% col2label 68.9/s 57742% 120% --

      So if this is hot code, don't use johngg's version, however nice his solution is :)


      Enjoy, Have FUN! H.Merijn

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (5)
As of 2020-06-03 14:05 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you really want to know if there is extraterrestrial life?



    Results (24 votes). Check out past polls.

    Notices?