PHPExcel(現已併入 PhpSpreadsheet)是處理 Excel 最成熟的函式庫。本文示範寫入與讀取 .xls (Excel 2003) 格式,含中文編碼、標題樣式、資料庫匯出實例。
<?php
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';
function write_xls($data = [], $title = [], $filename = 'report') {
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$sheet = $objPHPExcel->getActiveSheet();
// 標題列
$cols = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
foreach ($title as $i => $t) {
$sheet->setCellValue($cols[$i] . '1', $t);
}
// 標題樣式:粗體、靠右、邊框、漸層填滿
$titleCount = count($title);
$range = $cols[0] . '1:' . $cols[$titleCount - 1] . '1';
$sheet->getStyle($range)->applyFromArray([
'font' => ['bold' => true],
'alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT],
'borders' => ['top' => ['style' => PHPExcel_Style_Border::BORDER_THIN]],
'fill' => [
'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
'rotation' => 90,
'startcolor' => ['argb' => 'FFA0A0A0'],
'endcolor' => ['argb' => 'FFFFFFFF'],
],
]);
// 資料列
foreach ($data as $rowIdx => $row) {
foreach ($row as $colIdx => $val) {
$sheet->setCellValue($cols[$colIdx] . ($rowIdx + 2), $val);
}
}
// 輸出下載
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $filename . '.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}
// 測試資料
$data = [
[1111, '名稱', '品牌', '商品名', 'https://www.jb51.net'],
[1111, '名稱', '品牌', '商品名', 'https://www.jb51.net'],
];
write_xls($data, ['商品id', '供應商名稱', '品牌', '商品名', 'URL'], 'report');
<?php
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';
$inputFileName = 'import.xls';
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
$sheet = $objPHPExcel->getActiveSheet();
foreach ($sheet->getRowIterator() as $row) {
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false); // 含空儲存格
$rowData = [];
foreach ($cellIterator as $cell) {
$rowData[] = $cell->getValue();
}
print_r($rowData);
}
iconv('GBK', 'UTF-8//IGNORE', $str)。ChunkReadFilter 分塊讀取。$objPHPExcel->setPreCalculateFormulas(false);cell caching:PHPExcel_Settings::setCacheStorageMethod('MemoryGZip'); 或 'PHPTemp'。PHPExcel 官方已停止維護,**新專案請直接使用 PhpSpreadsheet**,API 高度相容,只需把命名空間從 PHPExcel 改為 PhpOffice\PhpSpreadsheet。