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?
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)/26-1, chr($index%26+65).$suffix );
}
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.
<?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"
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
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