Posted on

phpexcelのEXCEL列表記のテーブル作成

phpExcelにて列を指定して値を表示する際に、
EXCEL列の配列になったものをインデックスで指定して表示できれば、
横に長い表を作成する際に役に立つと思いこのテーブルを作成しました。


// EXCELの横座標を設定する。
$col1 = array("","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");
$col2 = array("","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");
$col3 = array("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");
$col4 = array("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");
$colArr = array();
$idx = 0;
foreach ($col1 as $val1) {
    if ($val1 == "") {
        foreach ($col2 as $val2) {
            foreach ($col3 as $val3) {
                $colArr[$idx] = $val1 . $val2 . $val3;
                $idx++;
            }
        }
    } else {
        foreach ($col4 as $val2) {
            foreach ($col3 as $val3) {
                $colArr[$idx] = $val1 . $val2 . $val3;
                $idx++;
            }
        }
    }
}
$col = 0;
$row = 1;
$sheet->setCellValue("{$colArr[$col]}{$row}", "日付");  // A1に「日付」表示
$col = 26;
$sheet->setCellValue("{$colArr[$col]}{$row}", "日付");  // AA1に「日付」表示
$col = 702;
$sheet->setCellValue("{$colArr[$col]}{$row}", "日付");  // AAA1に「日付」表示
$col = 1378;
$sheet->setCellValue("{$colArr[$col]}{$row}", "日付");  // BAA1に「日付」表示
$col = 16383;
$sheet->setCellValue("{$colArr[$col]}{$row}", "日付");  // XFD1に「日付」表示 ここがEXCEL横の最大値です。

PHPEXCELにて、列表記をインデックスで扱いたい時の配列です。