php iofactory列,phpspreadsheet-生成excel列名

需求

调用 phpoffice/phpspreadsheet，需要根据列数自动生成列名

代码

//1.php

class Columns

{

/**

* 获取列名

* @param $count 列数，总共有几列

* @return array ['A', 'B', ...] 有几列返回几列列名

*/

public static function generateColumns($count)

{

if ($count == 0) {

return [];

}

if ($count > 100) {

return self::generateColumnsByDynamic($count);

} else {

return self::generateColumnsByStatic($count);

}

}

/**

* 取静态列名(查表法)

* @param $count

* @return array

*/

private static function generateColumnsByStatic($count)

{

$columns = [

0 => 'A', 1 => 'B', 2 => 'C', 3 => 'D', 4 => 'E', 5 => 'F', 6 => 'G', 7 => 'H', 8 => 'I', 9 => 'J',

10 => 'K', 11 => 'L', 12 => 'M', 13 => 'N', 14 => 'O', 15 => 'P', 16 => 'Q', 17 => 'R', 18 => 'S', 19 => 'T',

20 => 'U', 21 => 'V', 22 => 'W', 23 => 'X', 24 => 'Y', 25 => 'Z', 26 => 'AA', 27 => 'AB', 28 => 'AC', 29 => 'AD',

30 => 'AE', 31 => 'AF', 32 => 'AG', 33 => 'AH', 34 => 'AI', 35 => 'AJ', 36 => 'AK', 37 => 'AL', 38 => 'AM', 39 => 'AN',

40 => 'AO', 41 => 'AP', 42 => 'AQ', 43 => 'AR', 44 => 'AS', 45 => 'AT', 46 => 'AU', 47 => 'AV', 48 => 'AW', 49 => 'AX',

50 => 'AY', 51 => 'AZ', 52 => 'BA', 53 => 'BB', 54 => 'BC', 55 => 'BD', 56 => 'BE', 57 => 'BF', 58 => 'BG', 59 => 'BH',

60 => 'BI', 61 => 'BJ', 62 => 'BK', 63 => 'BL', 64 => 'BM', 65 => 'BN', 66 => 'BO', 67 => 'BP', 68 => 'BQ', 69 => 'BR',

70 => 'BS', 71 => 'BT', 72 => 'BU', 73 => 'BV', 74 => 'BW', 75 => 'BX', 76 => 'BY', 77 => 'BZ', 78 => 'CA', 79 => 'CB',

80 => 'CC', 81 => 'CD', 82 => 'CE', 83 => 'CF', 84 => 'CG', 85 => 'CH', 86 => 'CI', 87 => 'CJ', 88 => 'CK', 89 => 'CL',

90 => 'CM', 91 => 'CN', 92 => 'CO', 93 => 'CP', 94 => 'CQ', 95 => 'CR', 96 => 'CS', 97 => 'CT', 98 => 'CU', 99 => 'CV'

];

return array_slice($columns, 0, $count);

}

/**

* 动态生成列名

* @param $count

* @return array

*/

private static function generateColumnsByDynamic($count)

{

$columns = [];

for ($i = 0; $i < $count; $i++) {

$columns[] = self::numberToLetters($i);

}

return $columns;

}

/**

* 根据列数获取列名

* @param $num 第 num 列，从 0 开始的整数

* @return mixed|string

*/

private static function numberToLetters($num)

{

$letters = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',

'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];

if ($num == 0) {

return $letters[0];

}

$tmp = '';

while (true) {

$remain = $num % 26;

$tmp = $letters[$remain] . $tmp;

$num = intval($num / 26);

if ($num == 0) {

break;

}

$num--;

}

return $tmp;

}

}

//2.php

include './1.php';

$options = getopt("n:");

$columns = Columns::generateColumns($options['n']);

var_dump($columns);

使用示例

目录结构：

/tmp

├── 1.php

├── 2.php

cd /tmp

php ./2.php -n 10

array(10) {

[0]=>

string(1) "A"

[1]=>

string(1) "B"

[2]=>

string(1) "C"

[3]=>

string(1) "D"

[4]=>

string(1) "E"

[5]=>

string(1) "F"

[6]=>

string(1) "G"

[7]=>

string(1) "H"

[8]=>

string(1) "I"

[9]=>

string(1) "J"

}