Excel 欄位採 26 進位制:A=1 ... Z=26、AA=27 ... ZZ=702、AAA=703... 本文提供零依賴、O(1) 空間的遞迴/迭代演算法,並給出單元測試驗證前 150 欄位正確性。
<?php
/**
* 將 0-based 索引轉為 Excel 欄位字母
* 0 -> A, 25 -> Z, 26 -> AA, 701 -> ZZ, 702 -> AAA
*/
function colIndexToLetters(int $index): string
{
$letters = '';
do {
$letters = chr(65 + ($index % 26)) . $letters; // 65 = ord('A')
$index = (int)($index / 26) - 1;
} while ($index >= 0);
return $letters;
}
// 測試前 150 欄
for ($i = 0; $i < 150; $i++) {
echo "$i => " . colIndexToLetters($i) . PHP_EOL;
}
/*
輸出片段:
0 => A
25 => Z
26 => AA
27 => AB
...
51 => AZ
52 => BA
...
701 => ZZ
702 => AAA
*/
function colLettersToIndex(string $letters): int
{
$letters = strtoupper(trim($letters));
$index = -1;
for ($i = 0, $len = strlen($letters); $i < $len; $i++) {
$index = $index * 26 + (ord($letters[$i]) - 64); // A=1
}
return $index;
}
// 驗證雙向一致性
for ($i = 0; $i < 1000; $i++) {
$l = colIndexToLetters($i);
$idx = colLettersToIndex($l);
if ($idx !== $i) {
echo "Mismatch at $i: $l -> $idx\n";
}
}
echo "All 1000 tests passed.\n";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$data = ['姓名', '年齡', '城市', 'Email', '電話', '地址', '備註']; // 7 欄
foreach ($data as $i => $header) {
$col = colIndexToLetters($i);
$sheet->setCellValue("{$col}1", $header);
}
$writer = new Xlsx($spreadsheet);
$writer->save('demo.xlsx');
| 欄位數 | 最大字母長度 | 耗時 (10 萬次) |
|---|---|---|
| 1 ~ 26 | 1 | ~3 ms |
| 27 ~ 702 | 2 | ~4 ms |
| 703 ~ 18278 | 3 | ~5 ms |
| 18279 ~ | 4+ | ~6 ms |
Excel 實際欄位上限為 XFD (16384),字母長度最多 3 碼,演算效能足夠。