php框架|ThinkPHP
ThinkPHP,phpExcel
php框架-ThinkPHP
PHP如何导入带图片的Excel表格呢？图片又如何导出到Excel中呢？导出的Excel如何定义样式使其更加漂亮？下面本篇文章就来给大家一一解决，希望对大家有所帮助！
个人源码交易网站源码,ubuntu引号怎么输入,分布式和tomcat的区别,爬虫生态缸制作,php自定义函数参数传递,漯河专业seolzw
易企秀源码字体文件,ubuntu卸载挂载分区,tomcat输出数据源配置,茂名爬虫店,php模板开发教程外包,山茶seo博客lzw
Excel 是常用的数据整理统计的工具，通常在一些信息化平台中为了更好的实现无纸化或者上云，需要对办公数据进行迁移，从办公电脑迁移到平台上，又或者将平台上的数据下载下来给非开发人员使用，势必会涉及到数据的导入导出，而数据格式非 Excel 不可。
怎么偷php源码,vscode自动内构造,ubuntu重装deepin,tomcat搭建多主机,sqlite 数据库文件解密,计算机中的爬虫是指,php写exe,呼伦贝尔seo优化公司,网站栏目管理系统,博客 模板lzw
本文将结合实际的开发需求，总结开发过程中 Excel 的导入和导出，涉及的开发框架：
ThinkPHP 3.2【相关教学推荐：thinkphp框架】phpExcel
代码仓库：/QuintionTang/crayon-thinkphp
导入
数据的导入，开始之前需要定义导入数据的格式，而且必须严格按照规定的格式程序才能正确的解析数据。通常的数据导入只是纯文本的数据，本文将导入Excel中带图片的数据，以最大可能覆盖导入需求。
模板
模板是数据导入的基础，下面定义一个简单的数据模板，如下格式：
有文本，有图片，导入数据首选需要读取到Excel文件，因此还需要涉及文件的上传，文件上传成功之后，先检测图片列，直接看代码：
public function excel_import(){ $usedfor = empty($_GET[usedfor]) ? picture : trim($_GET[usedfor]); $used_for = $usedfor; import(.UploadFile); $upload = $this->_upload_init(new \Org\Net\UploadFile(),$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.Excel");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 .= .jpg;$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 .= .jpg;$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],\, rim); // 第二列 名称 $logo_save_path = safty_value($rowData[2],\, rim); // logo图形保存路径 $remark = safty_value($rowData[3],\, rim); //备注 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的格式定义，先需要定义表头：
$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[ itle], "logo"=>$row_info[logo_path], "remark"=>$row_info[ emark] ));}
至此，数据封装已经完成，完整代码如下：
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[ itle],"logo"=>$row_info[logo_path],"remark"=>$row_info[ emark] )); } 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,,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[ itle]; 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[ ows]; $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(orders => 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, Excel);$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 = 2048Mpost_max_size = 2048M
Excel数据导入，通常会触发504错误，这种情况一般是执行时间太短，涉及的nginx配置：
fastcgi_connect_timeout 600;
php-fpm中的www.conf
request_terminate_timeout = 1800
环境问题个人觉得是后台开发经常发生的，最佳的方式是实际运行出一个最佳的配置，将其制作成docker镜像，这样可以确保环境迁移或者其他场合需要，可以快速完成环境配置，而且不容易出问题。
《PHP视频教学》
