php 获取数字的类似excel的列名的算法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3302857/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Algorithm to get the excel-like column name of a number
提问by Cristian
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:
我正在编写一个生成一些 Excel 文档的脚本，我需要将一个数字转换为其等效的列名。例如：
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:
ircmaxell 的实现效果很好。但是，我要添加这个简短的内容：
function num2alpha($n)
{
for($r = ""; $n >= 0; $n = intval($n / 26) - 1)
$r = chr($n%26 + 0x41) . $r;
return $r;
}
回答by ircmaxell
Here's a nice simple recursive function (Based on zero indexed numbers, meaning 0 == A, 1 == B, etc)...
这是一个很好的简单递归函数（基于零索引数字，意味着 0 == A、1 == B 等）...
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):
如果你想要一个索引（1 == A，等等）：
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...
测试从 0 到 10000 的数字...
回答by ksn135
Using PhpSpreadsheet(PHPExcel is deprecated)
使用PhpSpreadsheet（不推荐使用PHPExcel）
// result = 'A'
\PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(1);
Note index 0 results in 'Z'
注意索引 0 结果为“Z”
https://phpspreadsheet.readthedocs.io/en/develop/
https://phpspreadsheet.readthedocs.io/en/develop/
The correct answer (if you use PHPExcelLibrary) is:
正确答案（如果您使用PHPExcel库）是：
// result = 'A'
$columnLetter = PHPExcel_Cell::stringFromColumnIndex(0); // ZERO-based!
and backwards:
和倒退：
// result = 1
$colIndex = PHPExcel_Cell::columnIndexFromString('A');
回答by Mark Baker
Indexed for 1 -> A, 2 -> B, etc
索引为 1 -> A、2 -> B 等
function numToExcelAlpha($n) {
$r = 'A';
while ($n-- > 1) {
$r++;
}
return $r;
}
Indexed for 0 -> A, 1 -> B, etc
索引为 0 -> A、1 -> B 等
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.
利用 PHP 在处理字符变量而不是 C 的算术运算时遵循 Perl 的约定这一事实。请注意，字符变量可以递增但不能递减。
回答by Lucas
This will do to conversion (assuming integer arithmetic), but I agree with the other posters; just use base_convert
这将用于转换（假设整数算术），但我同意其他海报；只是使用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;
}
回答by Mike
Late answer, but here's what I did (for 1==A indexed):
迟到的答案，但这是我所做的（对于 1==A 索引）：
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;
}
回答by Ann
Number convert to Excel column letters:
数字转换为 Excel 列字母：
/**
* 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 列字母转换为数字：
/**
* 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
回答by simesy
I'd never use this in production because it's not readable, but for fun... Only does up to ZZ.
我永远不会在生产中使用它，因为它不可读，但只是为了好玩……仅适用于 ZZ。
<?php
$col = 55;
print (($n = (int)(($col - 1) / 26)) ? chr($n + 64) : '') . chr((($col - 1) % 26) + 65);
?>
回答by Charlie Affumigato
Combining ircmaxell's recursive answer I've got this one:
结合 ircmaxell 的递归答案，我得到了这个：
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.
我使用默认索引作为基于 0 的索引，但它可以是任何正整数，以便在 PHP 中处理数组时使用。
回答by corsiKa
<?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";
?>
