获取数字类似 Excel 的列名的算法

我正在编写一个脚本，用于生成一些 Excel 文档，我需要将一个数字转换为等效的列名。例如:

1 => A
2 => B
27 => AA
28 => AB
14558 => UMX

我已经写了一个算法来做到这一点，但我想知道是否有更简单或更快的方法来做到这一点:

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;
}

你知道一个更好的方法吗? 也许一些东西可以让它更简单? 或者性能提高？

剪辑

Ircmaxell 的实现工作得非常好，但是，我要添加一个简短的代码:

function num2alpha($n)
{
for($r = ""; $n >= 0; $n = intval($n / 26) - 1)
$r = chr($n%26 + 0x41) . $r;
return $r;
}
63095 次浏览
小开

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;
}
小开
最佳答案

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...

小开 
<?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";
?>
小开

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.

小开

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.

小开

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');
小开

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;
}
小开

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);
?>
小开

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
小开

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;
}
}
小开

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"

提交答案