php 匯出excel資料執行個體代碼

很多網站都有匯出資料為excel的功能，最開始想用js匯出但是網上的文章幾乎都一樣而且瀏覽器安全色性還有問題，總之不太好用，那隻好用php匯出了伺服器端處理不存在瀏覽器安全色的問題。

 

    上圖是項目中要要匯出的資料，前兩行是thead，後面是tbody（裡面的tr數量不定）。首先是擷取資料發送到php端處理。

 

代碼如下

複製代碼

require_once '../Classes/PHPExcel.php'; require_once '../Classes/PHPExcel/IOFactory.php'; $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); $objPHPExcel->setActiveSheetIndex(0); $objRichText = new PHPExcel_RichText(); $objRichText->createText(''); $objPayable = $objRichText->createTextRun('即時監測資料'); $objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_RED )); $objPayable->getFont()->setBold(true); $objPayable->getFont()->setSize(24); $objPHPExcel->getActiveSheet()->getCell('B1')->setValue($objRichText); $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);  // 加粗 $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(24);   // 字型大小 $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED); // 文本顏色 $objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFill()->getStartColor()->setARGB('00FFFFE3');//底紋 // 列寬 //$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(18); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(18); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(18); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20); // 行高 for($i = 2; $i <= 17; $i++) {  $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(22); } $objPHPExcel->getActiveSheet()->setCellValue('A2', '姓名'); $objPHPExcel->getActiveSheet()->setCellValue('A3', 'PHP'); $objPHPExcel->getActiveSheet()->setCellValue('A4', 'EXCEL'); $objPHPExcel->getActiveSheet()->setCellValue('A5', ''); $objPHPExcel->getActiveSheet()->setCellValue('A6', 'XXX'); $objPHPExcel->getActiveSheet()->setCellValue('A7', 'V'); $objPHPExcel->getActiveSheet()->setCellValue('A8', 'd'); $objPHPExcel->getActiveSheet()->setCellValue('A9', 'name'); $objPHPExcel->getActiveSheet()->setCellValue('A10', '描述'); for($i = 2; $i <= 16; $i++) {  $objPHPExcel->getActiveSheet()->getStyle('A' . $i)->getFont()->setBold(true);  // 加粗 } $objPHPExcel->getActiveSheet()->setCellValue('B2', '[姓名]'); $objPHPExcel->getActiveSheet()->setCellValue('B3', '匯出帶圖片的EXCEL'); $objPHPExcel->getActiveSheet()->setCellValue('B4', 'XXXXX'); // 為excel加圖片 //$objDrawing = new PHPExcel_Worksheet_Drawing(); //$objDrawing->setName('Photo'); //$objDrawing->setDescription('Photo'); //$objDrawing->setPath('../images/touxiang.jpg'); //$objDrawing->setHeight(170); //$objDrawing->setWidth(120); //$objDrawing->setCoordinates('G2'); //$objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); $objPHPExcel->getActiveSheet()->setCellValue('A17', '其它要求'); $objPHPExcel->getActiveSheet()->setCellValue('B17', '[其它要求]'); $objPHPExcel->getActiveSheet()->getStyle('A17')->getFont()->setBold(true);  // 加粗 $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setTitle('即時監測資料'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('PHPExcel.xls');