Classes/PHPExcel.php 與 Classes/PHPExcel/ 目錄複製到 ThinkPHP/Library/Org/Util/PHPExcel.php → PHPExcel.class.php(符合 ThinkPHP 自動載入規範)public function look_down() {
$id = I('get.id');
$m = M('offer_goods');
$where['offer_id'] = $id;
$data = $m->field('goods_id,goods_sn,goods_name,barcode,goods_type,price')->select();
// 載入 PHPExcel 類別
import("Org.Util.PHPExcel");
import("Org.Util.PHPExcel.Worksheet.Drawing");
import("Org.Util.PHPExcel.Writer.Excel2007");
$objPHPExcel = new \PHPExcel();
$objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
$objActSheet = $objPHPExcel->getActiveSheet();
// === 樣式設定:建議在最開頭設定,影響全表 ===
// 水平置中
$cols = ['A','B','C','D','E','F'];
foreach ($cols as $col) {
$objPHPExcel->setActiveSheetIndex(0)->getStyle($col)->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
// 垂直置中
foreach (['A','B','D','E','F'] as $col) {
$objPHPExcel->getActiveSheet()->getStyle($col)->getAlignment()
->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
}
// === 表頭 ===
$headers = ['A1'=>'商品貨號','B1'=>'商品名稱','C1'=>'商品圖','D1'=>'商品條碼','E1'=>'商品屬性','F1'=>'報價(港幣)'];
foreach ($headers as $cell => $val) $objActSheet->setCellValue($cell, $val);
// === 欄寬 ===
$widths = ['A'=>16,'B'=>80,'C'=>15,'D'=>20,'E'=>12,'F'=>12];
foreach ($widths as $col => $w) $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setWidth($w);
// === 資料列 ===
foreach ($data as $k => $v) {
$row = $k + 2; // 從第 2 列開始
$objActSheet->setCellValue('A'.$row, $v['goods_sn']);
$objActSheet->setCellValue('B'.$row, $v['goods_name']);
// --- 圖片插入 ---
$img = M('goods')->where('goods_id = '.$v['goods_id'])->field('goods_thumb')->find();
$objDrawing = new \PHPExcel_Worksheet_Drawing();
$objDrawing->setPath('./Upload/'.$img['goods_thumb']); // 必須是本地絕對/相對路徑
$objDrawing->setHeight(80);
$objDrawing->setWidth(80);
$objDrawing->setCoordinates('C'.$row);
$objDrawing->setOffsetX(12);
$objDrawing->setOffsetY(12);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
$objActSheet->setCellValue('D'.$row, $v['barcode']);
$objActSheet->setCellValue('E'.$row, $v['goods_type']);
$objActSheet->setCellValue('F'.$row, $v['price']);
// 列高配合圖片
$objActSheet->getRowDimension($row)->setRowHeight(80);
}
// === 輸出下載 ===
$fileName = '報價表_'.date("Y-m-d").'.xls';
$fileName = iconv("utf-8", "gb2312", $fileName); // 中文檔名轉碼
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=\"{$fileName}\"");
header('Cache-Control: max-age=0');
// 使用 Excel5 (xls) 格式輸出,相容性較好
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output'); // 直接輸出到瀏覽器下載
}
import("Org.Util.PHPExcel.Worksheet.Drawing");
// 對應檔案:Org/Util/PHPExcel/Worksheet/Drawing.class.php
setPath('./Upload/'.$img['goods_thumb']) — 相對網站根目錄,或用絕對路徑。不支援 HTTP URL。
| 格式 | Writer 類別 | 副檔名 | 說明 |
|---|---|---|---|
| Excel5 | PHPExcel_Writer_Excel5 | .xls | BIFF 格式,相容舊版 Excel |
| Excel2007 | PHPExcel_Writer_Excel2007 | .xlsx | OOXML 格式,功能完整、檔案較小 |
代碼中建立 Excel2007 物件但最終用 Excel5 輸出,建議統一。
iconv("utf-8", "gb2312", $fileName) 避免下載檔名亂碼(IE/舊瀏覽器需要)。
memory_limit、分批處理、或用 PHPExcel_CachedObjectStorageFactory 快取到硬碟set_time_limit(0) 或分批輸出