.xlsSimpleExcel 類別串流輸出 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();
}
?>
<?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
}
}
?>
<?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、樣式、公式、圖表、大數據流式寫入、記憶體優化。