PHPExcel/PhpSpreadsheet 解決 Excel 欄位名超過 Z (26欄) 的列號轉換算法

問題背景

Excel 欄位命名規則：A~Z (1~26)、AA~ZZ (27~702)、AAA~ (703~)。PhpSpreadsheet 內建 PHPExcel_Cell::stringFromColumnIndex() 完美支援任意欄數，但理解其算法有助於自定義導出、不依賴庫時的轉換。

PhpSpreadsheet 官方實現 (源碼解析)

public static function stringFromColumnIndex(int $columnIndex): string
{
    // 靜態快取提升效能
    static $indexCache = [];
    
    if (!isset($indexCache[$columnIndex])) {
        if ($columnIndex < 26) {
            // A-Z (0-25)
            $indexCache[$columnIndex] = chr(65 + $columnIndex);
        } elseif ($columnIndex < 702) {
            // AA-ZZ (26-701)
            // 65='A', 64='@'
            $indexCache[$columnIndex] = 
                chr(64 + ($columnIndex / 26)) .       // 首字母: @ + (索引/26) → A=65...
                chr(65 + ($columnIndex % 26));        // 次字母: A + (索引%26)
        } else {
            // AAA+ (702+)
            $indexCache[$columnIndex] = 
                chr(64 + (($columnIndex - 26) / 676)) .                    // 首字母
                chr(65 + ((($columnIndex - 26) % 676) / 26)) .             // 次字母
                chr(65 + ($columnIndex % 26));                              // 尾字母
        }
    }
    return $indexCache[$columnIndex];
}

// 反向：列名 → 索引 (0-based)
public static function columnIndexFromString(string $columnString): int
{
    $columnString = strtoupper($columnString);
    $len = strlen($columnString);
    $index = 0;
    for ($i = 0; $i < $len; $i++) {
        $index = $index * 26 + (ord($columnString[$i]) - 64);
    }
    return $index - 1; // 轉為 0-based
}

純 PHP 獨立實現 (無依賴)

<?php
/**
 * 0-based 索引轉 Excel 列名 (A=0, Z=25, AA=26...)
 */
function colIndexToName(int $index): string {
    if ($index < 0) throw new \InvalidArgumentException('Index must be >= 0');
    $name = '';
    while ($index >= 0) {
        $name = chr(65 + ($index % 26)) . $name;
        $index = ($index / 26) - 1; // 關鍵：26進位但無「0」位
    }
    return $name;
}

/**
 * Excel 列名轉 0-based 索引
 */
function colNameToIndex(string $name): int {
    $name = strtoupper(trim($name));
    if (!preg_match('/^[A-Z]+$/', $name)) {
        throw new \InvalidArgumentException("Invalid column name: $name");
    }
    $index = 0;
    for ($i = 0, $len = strlen($name); $i < $len; $i++) {
        $index = $index * 26 + (ord($name[$i]) - 64);
    }
    return $index - 1;
}

// 測試
$testCases = [
    0 => 'A', 25 => 'Z', 26 => 'AA', 27 => 'AB', 
    51 => 'AZ', 52 => 'BA', 701 => 'ZZ', 702 => 'AAA', 
    18277 => 'ZZZ', 16383 => 'XFD'  // Excel 最大列
];

foreach ($testCases as $idx => $expected) {
    $name = colIndexToName($idx);
    $back = colNameToIndex($name);
    $ok = ($name === $expected && $back === $idx) ? '✓' : '✗';
    echo "$ok $idx → $name (back: $back) " . ($name === $expected ? '' : "EXPECTED $expected") . "\n";
}

應用場景：動態生成 Excel 導出

<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// 假設動態欄位
$headers = ['ID', '姓名', 'Email', '手機', '城市', '部門', '職位', '入職日期', '薪資', '備註', 
            '欄位11', '欄位12', '欄位13', '欄位14', '欄位15', '欄位16', '欄位17', '欄位18', 
            '欄位19', '欄位20', '欄位21', '欄位22', '欄位23', '欄位24', '欄位25', '欄位26', 
            '欄位27', '欄位28']; // 28欄，超過 Z

// 寫入表頭
foreach ($headers as $i => $header) {
    $col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($i);
    $sheet->setCellValue("{$col}1", $header);
    $sheet->getColumnDimension($col)->setAutoSize(true);
}

// 寫入數據 (示意)
$data = [
    [1, '張三', 'zhang@example.com', '13800000001', '北京', '技術部', '工程師', '2020-01-01', 15000, ''],
    // ...
];
foreach ($data as $rowIdx => $row) {
    foreach ($row as $colIdx => $value) {
        $col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($colIdx);
        $sheet->setCellValue("{$col}" . ($rowIdx + 2), $value);
    }
}

$writer = new Xlsx($spreadsheet);
$writer->save('export.xlsx');
echo "導出完成，含 " . count($headers) . " 欄\n";

常見錯誤與修正

效能提示

• 大量轉換時使用 靜態快取 (如官方實現)
• 批量寫入時預先計算所有欄位名陣列，避免重複調用
• Coordinate::stringFromColumnIndex() 內部已快取，直接用即可

核心算法：26 進位但無「0」位 (A=1...Z=26, AA=27)。索引轉名稱時每步 index = (index / 26) - 1；名稱轉索引時累加 index = index * 26 + (char - 'A' + 1) 最後 -1。