您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 相關知識

Yii2 Excel 導出完美解決 26 列限制：PHPExcel 列索引自動遞增實戰

編輯：PHP 相關知識

Yii2 Excel 導出完美解決 26 列限制：PHPExcel 列索引自動遞增實戰

PHPExcel（及其繼任者 PhpSpreadsheet）默認列索引僅支持 A-Z（26 列），超出需用 AA、AB... 表示。本文提供 Yii2 完整導出方案：控制器接收 GridView 篩選參數、前端動態構造 URL、公共組件 CommonFunc::exportData() 利用 PHPExcel_Cell::stringFromColumnIndex() 自動處理任意列數，附帶完整代碼與避坑指南。

一、整體架構流程

用戶點擊「導出」按鈕
    ↓
前端 JS 收集 GridView filter 參數 + 自定義篩選（如日期範圍）
    ↓
拼接完整查詢字符串 → window.location.href = '/controller/export-data?...'
    ↓
控制器 actionExportData() 解析參數 → 構建 WHERE 條件 → 查詢數據
    ↓
調用 CommonFunc::exportData($data, $header, $title, $filename)
    ↓
PHPExcel 生成 .xlsx 並輸出至 php://output（瀏覽器直接下載）

二、控制器層：參數接收與條件構建

public function actionExportData() {
    $where = '1=1';
    $conditions = [];

    if ($_GET) {
        foreach ($_GET as $k => $v) {
            if ($k === 'start_time') {
                $conditions[] = "create_time >= '" . date('Y-m-d 00:00:00', strtotime($v)) . "'";
            } elseif ($k === 'end_time') {
                $conditions[] = "create_time <= '" . date('Y-m-d 23:59:59', strtotime($v)) . "'";
            } elseif ($k !== 'export') { // 排除非業務參數
                $conditions[] = "$k = '$v'"; // 實際項目請用參數綁定防注入
            }
        }
    }
    if ($conditions) $where .= ' AND ' . implode(' AND ', $conditions);

    // 查詢數據（示例用 Query Builder，實際建議用 Model::find()）
    $data = (new \yii\db\Query())
        ->select(['id', 'username', 'email', 'create_time', 'status', 'last_login_ip', 'balance', 'level', 'avatar', 'nickname', 'phone', 'address', 'city', 'province', 'country', 'zip', 'source', 'device', 'channel', 'utm_source', 'utm_medium', 'utm_campaign', 'utm_content', 'utm_term', 'referrer', 'landing_page', 'session_id', 'visit_count', 'order_count', 'total_amount'])
        ->from('user_profile')
        ->where($where)
        ->all();

    $header = ['ID', '用戶名', '郵箱', '註冊時間', '狀態', '最後登錄 IP', '餘額', '等級', '頭像', '暱稱', '手機', '地址', '城市', '省份', '國家', '郵編', '來源', '設備', '渠道', 'UTM Source', 'UTM Medium', 'UTM Campaign', 'UTM Content', 'UTM Term', 'Referrer', '落地頁', 'Session ID', '訪問次數', '訂單數', '總金額'];
    // 共 30 列，超過 26 列驗證自動列索引

    \common\components\CommonFunc::exportData($data, $header, '用戶明細', 'user_export_' . date('YmdHis'));
}

三、核心組件：CommonFunc::exportData() 完美支援無限列

namespace common\components;
use Yii;

class CommonFunc {
    public static function exportData($data, $header, $title = 'Export', $filename = 'data') {
        if (!is_array($data) || !is_array($header)) return false;

        // 引入 PHPExcel（建議遷移 PhpSpreadsheet）
        require_once Yii::getAlias('@common') . '/components/phpexcel/PHPExcel.php';
        require_once Yii::getAlias('@common') . '/components/phpexcel/PHPExcel/Writer/Excel2007.php';

        $objPHPExcel = new \PHPExcel();
        $objPHPExcel->getProperties()
            ->setCreator("System")
            ->setLastModifiedBy("System")
            ->setTitle($title)
            ->setSubject($title)
            ->setDescription("Auto generated by Yii2");

        $objPHPExcel->setActiveSheetIndex(0);
        $sheet = $objPHPExcel->getActiveSheet();

        // 1. 寫入表頭：自動列索引
        foreach ($header as $colIdx => $colName) {
            $colString = \PHPExcel_Cell::stringFromColumnIndex($colIdx); // 0→A, 25→Z, 26→AA...
            $sheet->setCellValue($colString . '1', $colName);
        }

        // 2. 寫入數據行
        $rowIdx = 2;
        foreach ($data as $row) {
            $colIdx = 0;
            foreach ($row as $value) {
                $colString = \PHPExcel_Cell::stringFromColumnIndex($colIdx);
                $sheet->setCellValue($colString . $rowIdx, $value);
                $colIdx++;
            }
            $rowIdx++;
        }

        $sheet->setTitle($title);

        // 3. 輸出下載
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');
        exit;
    }
}

四、關鍵點解析：stringFromColumnIndex 實現原理

// PHPExcel_Cell::stringFromColumnIndex 簡化邏輯
public static function stringFromColumnIndex($colIdx) {
    $str = '';
    while ($colIdx >= 0) {
        $str = chr(65 + ($colIdx % 26)) . $str; // 65 = ord('A')
        $colIdx = floor($colIdx / 26) - 1;
    }
    return $str;
}
// 示例：0→A, 25→Z, 26→AA, 27→AB, 701→ZZ, 702→AAA

五、前端導出按鈕與 JS 參數收集

<div class="export-toolbar">
    <?= \yii\helpers\Html::a('導出 Excel', 'javascript:;', ['class' => 'btn btn-success', 'id' => 'btn-export']) ?>
    開始時間：<input type="text" name="start_time" class="form-control datepicker" />
    結束時間：<input type="text" name="end_time" class="form-control datepicker" />
</div>

<script>
$('#btn-export').click(function() {
    var params = '';
    // 1. 獲取 GridView filter 參數
    $('#w0-filters input, #w0-filters select').each(function() {
        var val = $(this).val();
        if (val) params += (params ? '&' : '?') + $(this).attr('name') + '=' + encodeURIComponent(val);
    });
    // 2. 獲取自定義篩選
    var st = $('input[name=start_time]').val();
    var et = $('input[name=end_time]').val();
    if (st) params += (params ? '&' : '?') + 'start_time=' + encodeURIComponent(st);
    if (et) params += (params ? '&' : '?') + 'end_time=' + encodeURIComponent(et);
    // 3. 跳轉下載
    window.location.href = '/user/export-data' + params;
});
</script>

六、遷移建議：PHPExcel → PhpSpreadsheet

PHPExcel 已停止維護，建議遷移至 PhpSpreadsheet：

Composer 安裝：composer require phpoffice/phpspreadsheet
命名空間變更：PHPExcel → PhpOffice\PhpSpreadsheet\Spreadsheet，PHPExcel_Writer_Excel2007 → PhpOffice\PhpSpreadsheet\Writer\Xlsx
stringFromColumnIndex 移至 PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex()
性能提升 30%+，內存佔用降低，支持 PHP 8.1+

七、大數據導出優化方案

分塊查詢：Yii::$app->db->createCommand()->queryBatch(1000) 游標讀取，避免一次性載入幾萬行爆內存。
異步導出：導出請求寫入隊列，Worker 後台生成文件存入對象存儲，前端輪詢/websocket 通知下載鏈接。
流式寫入：PhpSpreadsheet 支持 createWriter()->save('php://output') 邊生成邊輸出，減少峰值內存。

掌握 stringFromColumnIndex 自動列索引技巧，徹底告別 26 列硬編碼限制，配合異步隊列架構，支撐百萬級數據導出無壓力。