Number to Excel column name conversion-CodePudding

In Excel, column names are characters from A to Z, if there are more columns needed, it continues with AA, AB ...

I want to write a function, that converts integers to those excel column names.

0 .... A
25 ... Z
26 ... AA

The solution I came up with is working up to AZ, but I want it to work further.

function indexToXlxsColumn($index, $prefix="")
{
    if($index < 26)
    {
        return $prefix.chr($index 65);
    }else{
        return indexToXlxsColumn($index % 26, "A");
    }
}

How to adapt this function to work for each index without producing spaghetti code?

CodePudding user response：

Here is the probleme

return indexToXlxsColumn($index % 26, "A");

You away set the next floor prefix to A , what happends when u have input = 53 , the resultat should be "BB"

Code on paper

function indexToXlxsColumn($index, $prefix="")
{
    if($index < 26) // 53 isnt less then 26
                    // second loop , 1 is less then 26 
    {
        return $prefix.chr($index 65);
     // $prefix = A 
     // chr($index 65) = B
     // return "A"."B" ;
    
    }else{ 
        return indexToXlxsColumn($index % 26, "A"); 
        // indexToXlxsColumn(53 % 26, "A") -> (1, "A")
    }
}

---UPDATE---

Follow the ask here is the answer

function indexToXlxsColumn($index, $suffix ="")
{
        if($index < 26){
            return chr($index 65).$suffix ;
        }
        return indexToXlxsColumn(($index - $index&)/26-1, chr($index& 65).$suffix );
}

CodePudding user response：

One of the implementations:

function indexToXlxsColumn($index)
{
        $name = '';

        while($index > 0) {
                $mod = ($index - 1) % 26;
                $name = chr(65   $mod).$name;
                $index = (int)(($index - $mod) / 26);
        }

        return $name;
}


// echo indexToXlxsColumn(26); // Z
echo indexToXlxsColumn(33); // AG
// echo indexToXlxsColumn(800); // ADT

CodePudding user response：

I would do it a little differently. It bothered me to work with chr(). So I once stored the alphabet in a string and iterated over it until the index was successfully resolved.

max "zz"

<?php
function getIn($i) {
    $str = 'abcdefghijklmnopqrstuvwxyz';    
    $r = (int) floor($i / 26) ;
    $c = $i % 26;    
 
    return ($r) < 1 ? $str[$c] : $str[$r-1] .  $str[$c];
}

echo getIn(52); // output: "ba"

Update with max "zzz"

function getIn($i) {
    $str = 'abcdefghijklmnopqrstuvwxyz';    
    
    $r = (int) floor($i / 26);
    $rr = $r >= 27 ? $r - 27 : null;
    $c = $i % 26;

    if ( $rr !== null) {
        return $str[$c] : $str[$r-1] .  $str[$c];    
    }
    return ($r) < 1 ? $str[$c] : $str[$r-1] .  $str[$c];
}

echo getIn(800); // ddu
echo getIn(1377); // zzz