後台管理系統常需導出報表、導入批量數據。本文基於 PHPExcel(現建議遷移至 PhpSpreadsheet)演示采購報表導出與數據導入全流程。
composer require phpoffice/phpspreadsheet
// 或舊版
require_once 'Classes/PHPExcel.php';
$objPHPExcel = new PHPExcel();$objPHPExcel->getActiveSheet()->setTitle('祖名');setCellValue、mergeCells 合併單元格。PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007')->save('php://output');// 樣式數組
$head = array('font'=>array('bold'=>true), 'alignment'=>array('horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
$title = array('font'=>array('bold'=>true));
$linestyle = array('borders'=>array('outline'=>array('style'=>PHPExcel_Style_Border::BORDER_THIN, 'color'=>array('argb'=>'FF000000'))));
// 合併標題行
$objPHPExcel->getActiveSheet()->mergeCells('A1:D3')->setCellValue('A1', 'zhongyi清單');
$objPHPExcel->getActiveSheet()->mergeCells('A4:D4')->setCellValue('A4', "采購日期:".$date_now." ".$apm);
// 設置列寬
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
$objPHPExcel->getColumnDimension('C')->setWidth(44);
// 寫入表頭
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A5','商品編碼')
->setCellValue('B5','貨號')
->setCellValue('C5','商品名稱')
->setCellValue('D5','采購量');
// 循環數據
$row_count = 5;
foreach ($re as $r => $dataRow) {
$row = 6 + $r;
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.$row, $dataRow['b'])
->setCellValue('B'.$row, $dataRow['h'])
->setCellValue('C'.$row, $dataRow['name'])
->setCellValue('D'.$row, $all);
// 應用邊框樣式
$objPHPExcel->getActiveSheet()->getStyle('A'.$row_count)->applyFromArray($linestyle);
// ... B,C,D 同理
$row_count++;
}
// 輸出
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="report_'.date('YmdHis').'.xlsx"');
header('Cache-Control: max-age=0');
$writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$writer->save('php://output');
exit;
$objPHPExcel = PHPExcel_IOFactory::load($filePath); 遍歷行列。ini_set('memory_limit', '512M')、使用 setTimeLimit(0)。