算法得到的数字的类似Excel的列名 [英] Algorithm to get the excel-like column name of a number
问题描述
我的工作产生了一些Excel文档的脚本,我需要将数字转换成它的列名相同。例如:
1 =>一个
2 =>乙
27 => AA
28 => AB
14558 => UMX
我已经写了一个算法来做到这一点,但我想知道是否有更简单或更快的方式来做到这一点:
函数numberToColumnName($编号){
$ ABC =ABCDEFGHIJKLMNOPQRSTUVWXYZ;
$ abc_len =的strlen($ ABC);
$ result_len = 1; //多少个字符列的名字将有
$ POW = 0;
而(($ POW + = POW($ abc_len,$ result_len))< $号){
$ result_len ++;
}
$结果=;
$下一= FALSE;
//每个字符添加到结果...
为($ i = 1; $ I< = $ result_len; $ I ++){
$指数=($号%$ abc_len) - 1; //计算模块
//有时该指数应下降1
如果($下一|| $明年= FALSE){
$ index--;
}
//这是将在下一迭代中计算出的点
$数=地板($数/的strlen($ ABC));
//如果指数为负,将其转换为正
如果($明年=($指数< 0)){
$指数= $ abc_len + $指数;
}
$结果= $ ABC [$指数] $结果。 //串连信
}
返回$结果;
}
你知道一个更好的方式来做到这一点?也许什么能让它更简单?或性能改进?
修改
ircmaxell的执行工作pretty的罚款。但是,我要加入这个漂亮的短单:
函数num2alpha($ N)
{
为($ R =; $ N> = 0; $ N = INTVAL(N $ / 26) - 1)
$ R = CHR($ N%26 + 0×41)。 $ R;
返回$ R;
}
下面是一个不错的简单的递归功能(基于零的索引号,意为0 == A,1 = = B,等等)...
函数getNameFromNumber($ NUM){
$数值= $ NUM%26;
$信= CHR(65 + $数字);
$ NUM2 = INTVAL($ NUM / 26);
如果($ NUM2大于0){
返回getNameFromNumber($ NUM2 - 1)。 $信;
} 其他 {
返回$信;
}
}
如果你想让它之一进行索引(1 = = A,等等):
函数getNameFromNumber($ NUM){
$数值=($ NUM - 1)%26;
$信= CHR(65 + $数字);
$ NUM2 = INTVAL(($ NUM - 1)/ 26);
如果($ NUM2大于0){
返回getNameFromNumber($ NUM2)。 $信;
} 其他 {
返回$信;
}
}
测试与数字从0到10000 ...
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;
}
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...
这篇关于算法得到的数字的类似Excel的列名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!