Get Excel column name for given decimal number

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;
}
[download]

Comment on Get Excel column name for given decimal number Download Code

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` [download] 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` [download] Enjoy, Have FUN! H.Merijn	[reply] [d/l] [select]
Re^2: Get Excel column name for given decimal number by haukex (Archbishop) on Mar 26, 2020 at 13:13 UTC
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.	[reply] [d/l] [select]
Re^2: Get Excel column name for given decimal number by Akshit (Novice) on Mar 26, 2020 at 13:19 UTC
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 ?	[reply]
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 [download] 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 [download] Cheers, JohnGG	[reply] [d/l] [select]
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.	[reply]
Re^3: Get Excel column name for given decimal number by Tux (Canon) on Mar 28, 2020 at 16:07 UTC
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% --` [download] So if this is hot code, don't use johngg's version, however nice his solution is :) Read more... (1145 Bytes) Enjoy, Have FUN! H.Merijn	[reply] [d/l] [select]
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` [download]	[reply] [d/l]


Welcome to the Monastery
	PerlMonks