Algorithm to get the excel-like column name of a number
PhpAlgorithmOptimizationPhp Problem Overview
I'm working on a script that generate some Excel documents and I need to convert a number into its column name equivalent. For example:
1 => A
2 => B
27 => AA
28 => AB
14558 => UMX
I have already written an algorithm to do so, but I'd like to know whether are simpler or faster ways to do it:
function numberToColumnName($number){
$abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
$abc_len = strlen($abc);
$result_len = 1; // how much characters the column's name will have
$pow = 0;
while( ( $pow += pow($abc_len, $result_len) ) < $number ){
$result_len++;
}
$result = "";
$next = false;
// add each character to the result...
for($i = 1; $i<=$result_len; $i++){
$index = ($number % $abc_len) - 1; // calculate the module
// sometimes the index should be decreased by 1
if( $next || $next = false ){
$index--;
}
// this is the point that will be calculated in the next iteration
$number = floor($number / strlen($abc));
// if the index is negative, convert it to positive
if( $next = ($index < 0) ) {
$index = $abc_len + $index;
}
$result = $abc[$index].$result; // concatenate the letter
}
return $result;
}
Do you know a better way to do it? Maybe something to keep it simpler? or a performance improvement?
Edit
ircmaxell's implementation works pretty fine. But, I'm going to add this nice short one:
function num2alpha($n)
{
for($r = ""; $n >= 0; $n = intval($n / 26) - 1)
$r = chr($n%26 + 0x41) . $r;
return $r;
}
Php Solutions
Solution 1 - Php
Here's a nice simple recursive function (Based on zero indexed numbers, meaning 0 == A, 1 == B, etc)...
function getNameFromNumber($num) {
$numeric = $num % 26;
$letter = chr(65 + $numeric);
$num2 = intval($num / 26);
if ($num2 > 0) {
return getNameFromNumber($num2 - 1) . $letter;
} else {
return $letter;
}
}
And if you want it one indexed (1 == A, etc):
function getNameFromNumber($num) {
$numeric = ($num - 1) % 26;
$letter = chr(65 + $numeric);
$num2 = intval(($num - 1) / 26);
if ($num2 > 0) {
return getNameFromNumber($num2) . $letter;
} else {
return $letter;
}
}
Tested with numbers from 0 to 10000...
Solution 2 - Php
Using PhpSpreadsheet (PHPExcel is deprecated)
// result = 'A'
\PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(1);
Note index 0 results in 'Z'
https://phpspreadsheet.readthedocs.io/en/develop/
The correct answer (if you use PHPExcel Library) is:
// result = 'A'
$columnLetter = PHPExcel_Cell::stringFromColumnIndex(0); // ZERO-based!
and backwards:
// result = 1
$colIndex = PHPExcel_Cell::columnIndexFromString('A');
Solution 3 - Php
Indexed for 1 -> A, 2 -> B, etc
function numToExcelAlpha($n) {
$r = 'A';
while ($n-- > 1) {
$r++;
}
return $r;
}
Indexed for 0 -> A, 1 -> B, etc
function numToExcelAlpha($n) {
$r = 'A';
while ($n-- >= 1) {
$r++;
}
return $r;
}
Takes advantage of the fact that PHP follows Perl's convention when dealing with arithmetic operations on character variables and not C's. Note that character variables can be incremented but not decremented.
Solution 4 - Php
This will do to conversion (assuming integer arithmetic), but I agree with the other posters; just use base_convert
function numberToColumnName($number)
{
$abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
$len = strlen($abc);
$result = "";
while ($number > 0) {
$index = $number % $len;
$result = $abc[$index] . $result;
$number = floor($number / $len);
}
return $result;
}
Solution 5 - Php
Late answer, but here's what I did (for 1==A indexed):
function num_to_letters($num, $uppercase = true) {
$letters = '';
while ($num > 0) {
$code = ($num % 26 == 0) ? 26 : $num % 26;
$letters .= chr($code + 64);
$num = ($num - $code) / 26;
}
return ($uppercase) ? strtoupper(strrev($letters)) : strrev($letters);
}
Then if you want to convert the other way:
function letters_to_num($letters) {
$num = 0;
$arr = array_reverse(str_split($letters));
for ($i = 0; $i < count($arr); $i++) {
$num += (ord(strtolower($arr[$i])) - 96) * (pow(26,$i));
}
return $num;
}
Solution 6 - Php
Number convert to Excel column letters:
/**
* Number convert to Excel column letters
*
* 1 = A
* 2 = B
* 3 = C
* 27 = AA
* 1234567789 = CYWOQRM
*
* @link https://vector.cool/php-number-convert-to-excel-column-letters-2
*
* @param int $num 欄數
* @param bool $uppercase 大小寫
* @return void
*/
function num_to_letters($n)
{
$n -= 1;
for ($r = ""; $n >= 0; $n = intval($n / 26) - 1)
$r = chr($n % 26 + 0x41) . $r;
return $r;
}
ex:
echo num_to_letters(1); // A
echo num_to_letters(2); // B
echo num_to_letters(3); // C
echo num_to_letters(27); // AA
echo num_to_letters(1234567789); // CYWOQRM
Excel column letters convert to Number:
/**
* Excel column letters convert to Number
*
* A = 1
* B = 2
* C = 3
* AA = 27
* CYWOQRM = 1234567789
*
* @link https://vector.cool/php-number-convert-to-excel-column-letters-2
*
* @param string $letters
* @return mixed
*/
function letters_to_num($a)
{
$l = strlen($a);
$n = 0;
for ($i = 0; $i < $l; $i++)
$n = $n * 26 + ord($a[$i]) - 0x40;
return $n;
}
ex:
echo letters_to_num('A'); // 1
echo letters_to_num('B'); // 2
echo letters_to_num('C'); // 3
echo letters_to_num('AA'); // 27
echo letters_to_num('CYWOQRM'); // 1234567789
Solution 7 - Php
<?php
function numberToColumnName($number){
$abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
$abc_len = strlen($abc);
$result = "";
$tmp = $number;
while($number > $abc_len) {
$remainder = $number % $abc_len;
$result = $abc[$remainder-1].$result;
$number = floor($number / $abc_len);
}
return $abc[$number-1].$result;
}
echo numberToColumnName(1)."\n";
echo numberToColumnName(25)."\n";
echo numberToColumnName(26)."\n";
echo numberToColumnName(27)."\n";
echo numberToColumnName(28)."\n";
echo numberToColumnName(14558)."\n";
?>
Solution 8 - Php
Combining ircmaxell's recursive answer I've got this one:
function getNameFromNumber($num, $index=0) {
$index = abs($index*1); //make sure index is a positive integer
$numeric = ($num - $index) % 26;
$letter = chr(65 + $numeric);
$num2 = intval(($num -$index) / 26);
if ($num2 > 0) {
return getNameFromNumber($num2 - 1 + $index) . $letter;
} else {
return $letter;
}
}
I'm using the default indexing as 0 based, but it can be any positive integer for when juggling with arrays in PHP.
Solution 9 - Php
I'd never use this in production because it's not readable, but for fun... Only does up to ZZ.
<?php
$col = 55;
print (($n = (int)(($col - 1) / 26)) ? chr($n + 64) : '') . chr((($col - 1) % 26) + 65);
?>
Solution 10 - Php
To anyone looking for a Javascript implementation of this, here is @ircmaxell's answer in Javascript..
function getNameFromNumber(num){
let numeric = num%26;
let letter = String.fromCharCode(65+numeric);
let num2 = parseInt(num/26);
if(num2 > 0) {
return getNameFromNumber(num2 - 1)+letter;
} else {
return letter;
}
}
Solution 11 - Php
Here's another impl I just wrote:
function excelColumnName($number) {
$ordA = ord('A');
$ordZ = ord('Z');
$len = $ordZ - $ordA + 1;
$result = '';
while($number >= 0) {
$result = chr($number % $len + $ordA) . $result;
$number = intval($number/$len) - 1;
}
return $result;
}
Some test inputs:
>>> excelColumnName(0)
=> "A"
>>> excelColumnName(1)
=> "B"
>>> excelColumnName(25)
=> "Z"
>>> excelColumnName(26)
=> "AA"
>>> excelColumnName(27)
=> "AB"
>>> excelColumnName(28)
=> "AC"
>>> excelColumnName(52)
=> "BA"
>>> excelColumnName(51)
=> "AZ"