phpspreadsheet-生成excel列名

需求

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

代码

//1.php
<?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
<?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"
}