Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

Get Excel column name for given decimal number

by Akshit (Novice)
on Mar 26, 2020 at 13:00 UTC ( [id://11114672]=perlquestion: print w/replies, xml ) Need Help??

Akshit has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks..!!

Hope all doing good :)

I am trying to Get Excel column name for given decimal number.

my code able to obtion from A..ZZ. Once it reaches column AAA its not working. I have to update logic but I am running out of ideas.

Also I felt my code is very long. So any ideas to improve my code is appreciated.

Here is my code:

.
$colname = GetExcelColumnName(703); print "colname is : $colname \n"; sub GetExcelColumnName { $columnNumber = $_[0]; $columnnamestr = ""; print "column number is : $columnNumber \n"; $columnNumber = $columnNumber; $div = $columnNumber/26; my $divstart = 0; if($columnNumber%26 == 0) { $div = $div - 1; $divstart = 1; } $divcnt = 0; if($div >= 1) { $divcnt = $columnNumber/26; } if(($divcnt == 0)) { $columnname = 65-1+$columnNumber; $return_str = chr($columnname); } else { if($columnNumber == 26) { $columnname = 65+$div; } else{ $columnname = 65-1+$div; } $return_str = chr($columnname); $mod = $columnNumber%26; if($columnname <= 90) { if(($return_str != 'Z') && ($mod == 0)) { $columnname = 65; } elsif(($return_str == 'Z') && ($mod == 0)) { $columnname = 90; } else{ $columnname = 65-1+$mod; } $return_str = $return_str.chr($columnname); } else { $return_str = "update logic \n"; } } return $return_str; }

Replies are listed 'Best First'.
Re: Get Excel column name for given decimal number
by Tux (Canon) on Mar 26, 2020 at 13:04 UTC

    Hmm, that is a bit over-complicated.

    See Spreadsheet::Read::cell2cr:

    # cell2cr ("D18") => (4, 18) sub cell2cr { ref $_[0] eq __PACKAGE__ and shift; my ($cc, $r) = (uc ($_[0]||"") =~ m/^([A-Z]+)([0-9]+)$/) or return + (0, 0); my $c = 0; while ($cc =~ s/^([A-Z])//) { $c = 26 * $c + 1 + ord ($1) - ord ("A"); } ($c, $r); } # cell2cr

    update:

    haukex is correct, as always. Sorry for not reading more closely.

    Look at col2label instead:

    # col2label (4) => "D" sub col2label { ref $_[0] eq __PACKAGE__ and shift; my $c = shift; defined $c && $c > 0 or return ""; my $cell = ""; while ($c) { use integer; substr $cell, 0, 0, chr (--$c % 26 + ord "A"); $c /= 26; } $cell; } # col2label

    Enjoy, Have FUN! H.Merijn

      Nice, I started looking in the various Spreadsheet::* modules but of course it's in Spreadsheet::Read ;-) Though I do think OP wants col2label instead.

      Thanks, Tux!

      But Whatever the code I added is part of my other big code.

      I am using WIN32::OLE library for excel. Do you think still it works ?

Re: Get Excel column name for given decimal number
by johngg (Canon) on Mar 27, 2020 at 11:35 UTC

    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

      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
Re: Get Excel column name for given decimal number
by dasgar (Priest) on Mar 26, 2020 at 21:19 UTC

    You might be interested in the int2LATIN function from the Number::Latin module.

    Alternatively, since you are using Win32::OLE to control Excel directly, there are methods for specifying a cell by using integers to specify a row and column. I don't remember the details and there are some situations where that method isn't available. That's why I consider Number::Latin to be indispensable when using Win32::OLE to control Excel.

    Using your example of column number 703 (aka column AAA):

    C:\>perl -E"use Number::Latin; say int2LATIN(703);" AAA C:\>perl -E"use Number::Latin; say latin2int('AAA');" 703

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11114672]
Approved by Athanasius
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others examining the Monastery: (4)
As of 2024-03-29 13:52 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found