php如何導入帶圖片的excel表格呢？圖片又如何導出到excel中呢？導出的excel如何定義樣式使其更加漂亮？下面本篇文章就來給大家一一解決，希望對大家有所幫助！
Excel 是常用的數據整理統計的工具，通常在一些信息化平臺中為了更好的實現無紙化或者上云，需要對辦公數據進行遷移，從辦公電腦遷移到平臺上，又或者將平臺上的數據下載下來給非開發人員使用，勢必會涉及到數據的導入導出，而數據格式非 Excel 不可。
本文將結合實際的開發需求，總結開發過程中 ?Excel 的導入和導出，涉及的開發框架：
- thinkphp 3.2【相關教程推薦：thinkphp框架】
- phpExcel
在部署上，對于Excel中圖片較多的數據，需要加長超時或者運行時間及增加上傳大小限制
代碼倉庫：https://github.com/QuintionTang/crayon-thinkphp
立即學習“PHP免費學習筆記（深入）”；
導入
數據的導入，開始之前需要定義導入數據的格式，而且必須嚴格按照規定的格式程序才能正確的解析數據。通常的數據導入只是純文本的數據，本文將導入Excel中帶圖片的數據，以最大可能覆蓋導入需求。
模板
模板是數據導入的基礎，下面定義一個簡單的數據模板，如下格式：
有文本，有圖片，導入數據首選需要讀取到Excel文件，因此還需要涉及文件的上傳，文件上傳成功之后，先檢測圖片列，直接看代碼：
public function excel_import(){ $usedfor = empty($_GET['usedfor']) ? 'picture' : trim($_GET['usedfor']); $used_for = $usedfor; import('ORG.Net.UploadFile'); $upload = $this->_upload_init(new OrgNetUploadFile(),$usedfor);// 實例化上傳類 $attach = array(); $attachment = array(); $attach["success"] = 0; $info = ""; if(!$upload->upload()) { // 上傳錯誤提示錯誤信息 $upload_error = $upload->getErrorMsg(); $attach["msg"] = $upload_error; }else{ // 上傳成功 獲取上傳文件信息 $info = $upload->getUploadFileInfo(); } // 上傳成功后開始處理 if(is_array($info)){ $info = $info[0]; // PHPExcel 類引入 import("Org.Util.PHPExcel"); import("Org.Util.PHPExcel.Reader.Excel5"); import("Org.Util.PHPExcel.Reader.Excel2007"); import("Org.Util.PHPExcel.IOFactory.php"); $filePath = $info["savepath"] . $info["savename"]; $input_file_type = PHPExcel_IOFactory::identify($filePath); // 開始讀取Excel數據 $objExcel = new PHPExcel(); $objReader = PHPExcel_IOFactory::createReader($input_file_type); // 加載Excel文件 $objPHPExcel = $objReader->load($filePath); $objWorksheet = $objPHPExcel->getActiveSheet(); $data = $objWorksheet->toArray(); $attach_path = C('attach_path'); $subpath = date('YmdHm', time()); // Excel圖片存儲路徑 $imageFileRealPath = $attach_path . "excel_img/".$subpath ."/" ; mkdirs($imageFileRealPath); $i = 0; $rebarRows = array(); // 下面開始處理圖片 foreach ($objWorksheet->getDrawingCollection() as $img) { list($startColumn, $startRow) = PHPExcel_Cell::coordinateFromString($img->getCoordinates()); //獲取圖片所在行和列 $imageFileName = uniqid(); try { switch($img->getExtension()) { case 'jpg': case 'jpeg': $imageFileName .= '.jpeg'; $source = imagecreatefromjpeg($img->getPath()); imagejpeg($source, $imageFileRealPath.$imageFileName,100); break; case 'gif': $imageFileName .= '.gif'; $source = imagecreatefromgif($img->getPath()); $width = imagesx($source); $height = imagesy($source); if (function_exists("imagecreatetruecolor")) { $newImg = imagecreatetruecolor($width, $height); /* --- 用以處理縮放png圖透明背景變黑色問題開始 --- */ $color = imagecolorallocate($newImg,255,255,255); imagecolortransparent($newImg,$color); imagefill($newImg,0,0,$color); ImageCopyResampled($newImg, $source, 0, 0, 0, 0, $width, $height, $width, $height); } else { $newImg = imagecreate($width, $height); ImageCopyResized($newImg, $source, 0, 0, 0, 0, $width, $height, $width, $height); } imagejpeg($source, $imageFileRealPath.$imageFileName,100); break; case 'png': $imageFileName .= '.png'; $source = imagecreatefrompng($img->getPath()); $width = imagesx($source); $height = imagesy($source); if (function_exists("imagecreatetruecolor")) { $newImg = imagecreatetruecolor($width, $height); /* --- 用以處理縮放png圖透明背景變黑色問題開始 --- */ $color = imagecolorallocate($newImg,255,255,255); imagecolortransparent($newImg,$color); imagefill($newImg,0,0,$color); ImageCopyResampled($newImg, $source, 0, 0, 0, 0, $width, $height, $width, $height); } else { $newImg = imagecreate($width, $height); ImageCopyResized($newImg, $source, 0, 0, 0, 0, $width, $height, $width, $height); } imagejpeg($newImg, $imageFileRealPath.$imageFileName,100); break; } $startColumn = $this->ABC2decimal($startColumn); $data[$startRow-1][$startColumn] = $imageFileRealPath . $imageFileName; } catch (Throwable $th) { throw $th; } } $rowsData = array(); foreach ($data as $key => $rowData) { $serial = safty_value($rowData[0],0,'intval'); // 第一列 序號 $title = safty_value($rowData[1],'','trim'); // 第二列 名稱 $logo_save_path = safty_value($rowData[2],'','trim'); // logo圖形保存路徑 $remark = safty_value($rowData[3],'','trim'); //備注 if ($serial >0 && $logo_save_path!=="" && $title!==""){ array_push($rowsData,array( "serial"=>$serial, "title"=>$title, "logo_path"=>$logo_save_path, "remark"=>$remark )); } } // 將導入的數據生成文件緩存 $this->update_excel_data($rowsData); $upload_result = array( "count" => count($rowsData), "success" => 1, "state"=>"SUCCESS" ); } else { $upload_result = array( "message" => "上傳失敗!", "success" => 0 ); } echo json_encode($upload_result); }
下面是操作流程，如下：
選擇文件上傳并導入，導出成功之后提示并刷當前列表頁面。
導出成功后的列表：
至此，數據導入已經完成了。
不足，導入的Excel文件在數據導入后沒有處理，因此建議最好刪除掉
導出
現在就來將上面的數據導出，導出Excel的格式定義，先需要定義表頭：
$first_cells?=?array( ????array("serial","序號"), ????array("title","名稱"), ????array("logo","logo"), ????array("remark","描述") );
接下來就是按照表頭的格式，封裝數據，如下：
foreach?($excel_data?as?$key?=>?$row_info)?{ ????array_push($first_rows_data,array( ????????"serial"=>$row_info['serial'], ????????"title"=>$row_info['title'], ????????"logo"=>$row_info['logo_path'], ????????"remark"=>$row_info['remark'] ????)); }
至此，數據封裝已經完成，完整代碼如下：
????public?function?export(){ ????????$excel_detail?=?array( ????????????"author"=>"devpoint", ????????????"date"=>join("?",$artifacts_full) ????????); ????????//?定義導出Excel表格信息 ????????$sheets?=?array();?//?Excel表信息，一維代表一個數據表 ????????//?定義表頭 ????????$first_cells?=?array( ????????????array("serial","序號"), ????????????array("title","名稱"), ????????????array("logo","logo"), ????????????array("remark","描述") ????????); ????????//?為表增加數據 ????????$excel_data?=?get_file_cache("excel_data"); ????????$first_rows_data?=?array(); ????????//?數據與上面表頭對應 ????????foreach?($excel_data?as?$key?=>?$row_info)?{ ????????????array_push($first_rows_data,array( ????????????????"serial"=>$row_info['serial'], ????????????????"title"=>$row_info['title'], ????????????????"logo"=>$row_info['logo_path'], ????????????????"remark"=>$row_info['remark'] ????????????)); ????????} ????????array_push($sheets,array( ????????????"title"=>"前端項目流行框架", ????????????"cells"=>$first_cells, ????????????"rows"=>$first_rows_data ????????)); ????????$xlsName??=?"Excel數據導出"; ????????$xlsName?=?$xlsName??.?date('YmdHis'); ????????$this->exportExcel($xlsName,$sheets,$excel_detail); ????}
函數exportExcel將數據寫入到Excel，并定義表格的樣式，完整代碼如下：
????protected?function?exportExcel($expTitle,$xlsSheets,$detail){ ????????import("Org.Util.PHPExcel"); ????????import("Org.Util.PHPExcel.Writer.Excel5"); ????????import("Org.Util.PHPExcel.IOFactory.php"); ????????$fileName?=?$expTitle; ????????$objPHPExcel?=?new?PHPExcel(); ????????$objPHPExcel->getDefaultStyle()->getFont()->setName('宋體'); ????????//?Excel列名稱 $cellName?=?array( 'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U', 'V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM', 'AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ' ); ????????foreach?($xlsSheets?as?$index?=>?$sheet_info)?{ ????????????$sheet_title?=?$sheet_info['title']; ????????????if?($index>0){ ????????????????//?Excel默認已經建好的數據表，超過一張需要執行這里創建一個工作表 ????????????????$newSheet?=?new?PHPExcel_Worksheet($objPHPExcel,?$sheet_title);?//創建一個工作表 ????????????????$objPHPExcel->addSheet($newSheet); ????????????}?else?{ ????????????????$objPHPExcel->getActiveSheet($index)->setTitle($sheet_title); ????????????} ????????????$expCellName?=?$sheet_info['cells']; ????????????$expTableData?=?$sheet_info['rows']; ????????????$cellNum?=?count($expCellName); ????????????$dataNum?=?count($expTableData); ????????????$cellmerget?=?""; ????????????$cellWidths?=?array(); ????????????$sheet_head_title?=?$sheet_title; ????????????//?下面需要為每個工作表定義寬度 ????????????switch?($index)?{ ????????????????case?1:?//?每張表的索引從?0?開始計算 ????????????????????$cellmerget?=?'A1:E1'; ????????????????????$cellWidths=array(16,16,16,28,16); ????????????????????break; ????????????????default: ????????????????????$cellmerget?=?'A1:D1'; ????????????????????$sheet_head_title?=?$sheet_title?; ????????????????????$cellWidths=array(16,16,16,36); ????????????????????break; ????????????} ????????????$activeSheet?=?$objPHPExcel->setActiveSheetIndex($index); ????????????for($i=0;$igetRowDimension(1)->setRowHeight(36); ????????????????$activeSheet->getColumnDimension($currentCellName)->setWidth($cellWidths[$i]); ????????????????$activeSheet->getStyle($currentCellName.'1')->getFont()->setSize(12)->setBold(true); ????????????????$activeSheet->getStyle($currentCellName.'1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); ????????????} ????????????$activeSheet->mergeCells($cellmerget);//合并單元格 ????????????$activeSheet->setCellValue('A1',?$sheet_head_title); ????????????$activeSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); ????????????$activeSheet->getStyle('A1')->getFont()->setSize(20); ????????????$activeSheet->getRowDimension(1)->setRowHeight(50); ????????????$styleThinBlackBorderOutline?=?array(?? ????????????????????'borders'?=>?array?(?? ????????????????????????'outline'?=>?array?(?? ????????????????????????????????'style'?=>?PHPExcel_Style_Border::BORDER_MEDIUM,???//設置border樣式 ????????????????????????????????'color'?=>?array?('argb'?=>?'FF9b9b9b'),??????????//設置border顏色?? ????????????????????????),?? ????????????????),?? ????????????);?? ????????????for($i=0;$igetRowDimension(2)->setRowHeight(36); ????????????????$activeSheet->getColumnDimension($currentCellName)->setWidth($cellWidths[$i]); ????????????????$activeSheet->setCellValue($currentCellName.'2',?$expCellName[$i][1]); ????????????????$activeSheet->getStyle($currentCellName.'2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); ????????????????$activeSheet->getStyle($currentCellName.'2')->getFill()->getStartColor()->setARGB('FFc6efcd'); ????????????????$activeSheet->getStyle($currentCellName.'2')->getFont()->setSize(12)->setBold(true); ????????????????$activeSheet->getStyle($currentCellName.'2')->applyFromArray($styleThinBlackBorderOutline);?? ????????????????$activeSheet->getStyle($currentCellName.'2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); ????????????????$activeSheet->freezePane($currentCellName.'3');??//?鎖定表頭，3?意味著鎖定第3行上面的 ????????????} ????????????switch?($index)?{ ????????????????case?1: ????????????????????break; ????????????????default: ????????????????????$start_row_index?=?3;?//?數據開始索引行 ????????????????????for($i1=0;$i1getActiveSheet()->getRowDimension($i1+3)->setRowHeight(60); ????????????????????????for($j1=0;$j1setPath($logo_path); ????????????????????????????????????$objDrawing->setHeight(60); ????????????????????????????????????$objDrawing->setWidth(60); ???????????????????????????????? ????????????????????????????????????$objDrawing->setOffsetX(5); ????????????????????????????????????$objDrawing->setOffsetY(5); ????????????????????????????????????$objDrawing->setCoordinates($cellName[$j1].($i1+$start_row_index)); ????????????????????????????????????$objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); ????????????????????????????????}?else?{ ????????????????????????????????????$objPHPExcel->getActiveSheet()->setCellValue($cellName[$j1].($i1+$start_row_index),?""); ????????????????????????????????????$objPHPExcel->getActiveSheet()->getStyle($cellName[$j1].($i1+$start_row_index))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); ????????????????????????????????????$objPHPExcel->getActiveSheet()->getStyle($cellName[$j1].($i1+$start_row_index))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); ????????????????????????????????????$objPHPExcel->getActiveSheet()->getStyle($cellName[$j1].($i1+$start_row_index))->getAlignment()->setWrapText(true); ????????????????????????????????} ????????????????????????????}?else?{ ????????????????????????????????$objPHPExcel->getActiveSheet()->setCellValue($cellName[$j1].($i1+$start_row_index),?$expTableData[$i1][$expCellName[$j1][0]]); ????????????????????????????????$objPHPExcel->getActiveSheet()->getStyle($cellName[$j1].($i1+$start_row_index))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); ????????????????????????????????$objPHPExcel->getActiveSheet()->getStyle($cellName[$j1].($i1+$start_row_index))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); ????????????????????????????????$objPHPExcel->getActiveSheet()->getStyle($cellName[$j1].($i1+$start_row_index))->getAlignment()->setWrapText(true); ????????????????????????????} ????????????????????????} ????????????????????} ????????????????????break; ????????????} ???????????? ????????} ????????$objPHPExcel->setActiveSheetIndex(0); header('pragma:public'); header('Content-type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8;name="'.$fileName.'.xlsx"'); header("Content-Disposition:attachment;filename=$fileName.xlsx");?//?attachment新窗口打印inline本窗口打印 $objWriter?=?PHPExcel_IOFactory::createWriter($objPHPExcel,?'Excel2007'); $objWriter->save('php://output'); exit; ????}
導出后的格式如下：
鎖定表頭
鎖定表頭是Excel比較常見的功能，可以方便查閱者查閱數據，使用 phpExcel 設置表頭的代碼如下：
$activeSheet->freezePane($currentCellName.'3');?//?3?意味著鎖定第3行上面的行數
表格邊框樣式
上面的代碼設置表格邊框樣式的代碼為PHPExcel_Style_Border::BORDER_MEDIUM，在 phpExcel 中有14個配置可選項目。
PHPExcel_Style_Border::BORDER_NONE; PHPExcel_Style_Border::BORDER_THIN; PHPExcel_Style_Border::BORDER_MEDIUM; PHPExcel_Style_Border::BORDER_DASHED; PHPExcel_Style_Border::BORDER_DOTTED; PHPExcel_Style_Border::BORDER_THICK; PHPExcel_Style_Border::BORDER_DOUBLE; PHPExcel_Style_Border::BORDER_HAIR; PHPExcel_Style_Border::BORDER_MEDIUMDASHED; PHPExcel_Style_Border::BORDER_DASHDOT; PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT; PHPExcel_Style_Border::BORDER_DASHDOTDOT; PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT; PHPExcel_Style_Border::BORDER_SLANTDASHDOT;
1. BORDER_NONE
對應的完整配置項為 PHPExcel_Style_Border::BORDER_NONE，效果如下：
2. BORDER_THIN
PHPExcel_Style_Border::BORDER_THIN
3. BORDER_MEDIUM
PHPExcel_Style_Border::BORDER_MEDIUM
4. BORDER_DASHED
PHPExcel_Style_Border::BORDER_DASHED
5. BORDER_DOTTED
PHPExcel_Style_Border::BORDER_DOTTED
6. BORDER_THICK
PHPExcel_Style_Border::BORDER_THICK
7. BORDER_DOUBLE
PHPExcel_Style_Border::BORDER_DOUBLE
8. BORDER_HAIR
PHPExcel_Style_Border::BORDER_HAIR
9. BORDER_MEDIUMDASHED
PHPExcel_Style_Border::BORDER_MEDIUMDASHED
10. BORDER_DASHDOT
PHPExcel_Style_Border::BORDER_DASHDOT
11. BORDER_MEDIUMDASHDOT
PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT
12. BORDER_DASHDOTDOT
PHPExcel_Style_Border::BORDER_DASHDOTDOT
13. BORDER_MEDIUMDASHDOTDOT
PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT
14. BORDER_SLANTDASHDOT
PHPExcel_Style_Border::BORDER_SLANTDASHDOT
部署
在部署上，通常的架構是 nginx + php-fpm，對于Excel中圖片比較多的數據導入需要設置加大上傳文件的限制和超時時間。
在文件上傳上，通常會出現 413 request Entity too Large 錯誤，解決的辦法是在 nginx 配置中增加以下配置：
client_max_body_size??2048m;
相應的 PHP 配置也需要修改，需要修改 php.ini ：
upload_max_filesize?=?2048M post_max_size?=?2048M
Excel數據導入，通常會觸發504錯誤，這種情況一般是執行時間太短，涉及的 nginx 配置：
fastcgi_connect_timeout?600;
php-fpm 中的 www.conf
request_terminate_timeout?=?1800
環境問題個人覺得是后臺開發經常發生的，最佳的方式是實際運行出一個最佳的配置，將其制作成 docker 鏡像，這樣可以確保環境遷移或者其他場合需要，可以快速完成環境配置，而且不容易出問題。
原文地址：https://juejin.cn/post/6982953271933550628
作者：天行無忌
推薦學習：《thinkphp框架》
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
