萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP 導出 Excel 實戰:考核統計報表產生與下載

整體流程

  1. 接收考核代碼參數
  2. 查詢統計資料
  3. 設定 HTTP 標頭觸發瀏覽器下載 .xls
  4. 使用 SimpleExcel 類別串流輸出 BIFF 格式(Excel 97-2003)

控制器代碼

<?php
function exportAssessmentStats() {
    require_once 'outExcel.class.php';

    $kaoheCode = $_GET['kaoheCode'] ?? '';
    if (!$kaoheCode) exit('缺少參數');

    $dao = new PerformanceServices();
    $info = $dao->getKaoheInfo($kaoheCode);
    $stats = $dao->getTongjiInfo($kaoheCode);

    // 檔名編碼相容各瀏覽器
    $filename = $info['KH_NAME'] . '考核統計';
    $encoded = rawurlencode($filename);
    $ua = $_SERVER['HTTP_USER_AGENT'] ?? '';

    header('Content-Type: application/vnd.ms-excel; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $encoded . '.xls"; filename*=UTF-8\'\'' . $encoded . '.xls');
    header('Cache-Control: max-age=0');
    header('Expires: 0');

    $excel = new SimpleExcel();
    $excel->excelItem(['統計時間', date('Y-m-d H:i:s', $stats['KH_TIME'])]);
    $excel->excelItem(['序號', '姓名', '部門', '分數']);
    $excel->colsAttrib(['1', 'a', 'a', '1']); // 1=數值, a=字串

    $scores = explode(',', $stats['KH_SCORES']);
    $i = 0;
    foreach ($scores as $item) {
        [$userCode, $score] = explode(':', $item);
        $user = $dao->getUserInfo($userCode);
        $dept = $dao->getCellName($userCode);
        $excel->excelWrite([++$i, $user['QT_NAME'], $dept, $score]);
    }
    $excel->excelEnd();
}
?>

SimpleExcel 類別 (outExcel.class.php)

<?php
class SimpleExcel {
    private $row = 0;
    private $attrib = [];
    private $charset = 'UTF-8';

    public function __construct() {
        // BIFF8 Header
        echo pack('ssssss', 0x809, 0x08, 0x00, 0x10, 0x00, 0x00);
    }

    public function excelItem(array $cells): void {
        foreach ($cells as $col => $val) {
            $val = $this->convert($val);
            $len = strlen($val);
            echo pack('ssssss', 0x0204, 8 + $len, $this->row, $col, 0x00, $len);
            echo $val;
        }
        $this->row++;
    }

    public function colsAttrib(array $types): void {
        $this->attrib = $types;
    }

    public function excelWrite(array $cells): void {
        foreach ($cells as $col => $val) {
            $val = $this->convert($val);
            if (($this->attrib[$col] ?? 'a') === '1') {
                echo pack('sssss', 0x0203, 14, $this->row, $col, 0x00);
                echo pack('d', (float)$val); // IEEE 754 double
            } else {
                $len = strlen($val);
                echo pack('ssssss', 0x0204, 8 + $len, $this->row, $col, 0x00, $len);
                echo $val;
            }
        }
        $this->row++;
    }

    public function excelEnd(): void {
        echo pack('ss', 0x000A, 0x00); // EOF
    }

    private function convert(string $s): string {
        return iconv($this->charset, 'GB2312//IGNORE', $s); // BIFF 內碼為 GB2312/CP936
    }
}
?>

現代化替代方案:PhpSpreadsheet

<?php
// composer require phpoffice/phpspreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->fromArray([
    ['序號', '姓名', '部門', '分數'],
    [1, '張三', '研發部', 95],
    [2, '李四', '行銷部', 88],
]);

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="report.xlsx"');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
?>

優勢:支援 .xlsx、樣式、公式、圖表、大數據流式寫入、記憶體優化。

copyright © 萬盛學電腦網 all rights reserved