首页-教程-开发语言

手把手教你使用ThinkPHP+phpExcel导入导出Excel数据

 3740

PHP如何导入带图片的Excel表格呢？图片又如何导出到Excel中呢？导出的Excel如何定义样式使其更加漂亮？下面本篇文章就来给大家一一解决，希望对大家有所帮助！


手把手教你使用ThinkPHP+phpExcel导入导出Excel数据


Excel 是常用的数据整理统计的工具，通常在一些信息化平台中为了更好的实现无纸化或者上云，需要对办公数据进行迁移，从办公电脑迁移到平台上，又或者将平台上的数据下载下来给非开发人员使用，势必会涉及到数据的导入导出，而数据格式非 Excel 不可。

本文将结合实际的开发需求，总结开发过程中 Excel 的导入和导出，涉及的开发框架：

ThinkPHP 3.2

phpExcel

在部署上，对于Excel中图片较多的数据，需要加长超时或者运行时间及增加上传大小限制

代码仓库：https://github.com/QuintionTang/crayon-thinkphp

导入

数据的导入，开始之前需要定义导入数据的格式，而且必须严格按照规定的格式程序才能正确的解析数据。通常的数据导入只是纯文本的数据，本文将导入Excel中带图片的数据，以最大可能覆盖导入需求。

模板

模板是数据导入的基础，下面定义一个简单的数据模板，如下格式：


手把手教你使用ThinkPHP+phpExcel导入导出Excel数据


有文本，有图片，导入数据首选需要读取到Excel文件，因此还需要涉及文件的上传，文件上传成功之后，先检测图片列，直接看代码：

  1. public function excel_import(){
  2.     $usedfor = empty($_GET['usedfor']) ? 'picture' : trim($_GET['usedfor']);
  3.     $used_for = $usedfor;
  4.     import('ORG.Net.UploadFile');
  5.     $upload = $this->_upload_init(new \Org\Net\UploadFile(),$usedfor);// 实例化上传类
  6.     $attach = array();
  7.     $attachment = array();
  8.     $attach["success"] = 0;
  9.     $info = "";
  10.     if(!$upload->upload()) { // 上传错误提示错误信息
  11.         $upload_error = $upload->getErrorMsg();
  12.         $attach["msg"] = $upload_error;
  13.     }else{ // 上传成功 获取上传文件信息
  14.         $info =  $upload->getUploadFileInfo();
  15.     }
  16.     // 上传成功后开始处理
  17.     if(is_array($info)){
  18.         $info = $info[0];
  19.         // PHPExcel 类引入
  20.         import("Org.Util.PHPExcel");
  21.         import("Org.Util.PHPExcel.Reader.Excel5");
  22.         import("Org.Util.PHPExcel.Reader.Excel2007");
  23.          
  24.         import("Org.Util.PHPExcel.IOFactory.php");
  25.         $filePath = $info["savepath"] . $info["savename"];
  26.         $input_file_type = \PHPExcel_IOFactory::identify($filePath);
  27.         // 开始读取Excel数据
  28.         $objExcel = new \PHPExcel();
  29.         $objReader = \PHPExcel_IOFactory::createReader($input_file_type);
  30.         // 加载Excel文件
  31.         $objPHPExcel = $objReader->load($filePath); 
  32.         $objWorksheet = $objPHPExcel->getActiveSheet();
  33.         $data = $objWorksheet->toArray();
  34.         $attach_path = C('attach_path');
  35.         $subpath = date('YmdHm', time());
  36.         // Excel图片存储路径
  37.         $imageFileRealPath = $attach_path . "excel_img/".$subpath ."/" ; 
  38.         mkdirs($imageFileRealPath);
  39.         $i = 0;
  40.         $rebarRows = array();
  41.         // 下面开始处理图片
  42.         foreach ($objWorksheet->getDrawingCollection() as $img) {
  43.             list($startColumn, $startRow) = \PHPExcel_Cell::coordinateFromString($img->getCoordinates()); //获取图片所在行和列
  44.             $imageFileName = uniqid();
  45.             try {
  46.                 switch($img->getExtension()) {
  47.                     case 'jpg':
  48.                     case 'jpeg':
  49.                         $imageFileName .= '.jpeg';
  50.                         $source = imagecreatefromjpeg($img->getPath());
  51.                         imagejpeg($source, $imageFileRealPath.$imageFileName,100);
  52.                         break;
  53.                     case 'gif':
  54.                         $imageFileName .= '.gif';
  55.                         $source = imagecreatefromgif($img->getPath());
  56.                         $width = imagesx($source);
  57.                         $height = imagesy($source);
  58.                         if (function_exists("imagecreatetruecolor")) {
  59.                             $newImg = imagecreatetruecolor($width, $height);
  60.                             /* --- 用以处理缩放png图透明背景变黑色问题开始 --- */
  61.                             $color = imagecolorallocate($newImg,255,255,255);
  62.                             imagecolortransparent($newImg,$color);
  63.                             imagefill($newImg,0,0,$color);
  64.                             ImageCopyResampled($newImg, $source, 0, 0, 0, 0, $width, $height, $width, $height);
  65.                         } else {
  66.                             $newImg = imagecreate($width, $height);
  67.                             ImageCopyResized($newImg, $source, 0, 0, 0, 0, $width, $height, $width, $height);
  68.                         }
  69.                         imagejpeg($source, $imageFileRealPath.$imageFileName,100);
  70.                         break;
  71.                     case 'png':
  72.                         $imageFileName .= '.png';
  73.                         $source = imagecreatefrompng($img->getPath());
  74.                         $width = imagesx($source);
  75.                         $height = imagesy($source);
  76.                         if (function_exists("imagecreatetruecolor")) {
  77.                             $newImg = imagecreatetruecolor($width, $height);
  78.                              
  79.                             /* --- 用以处理缩放png图透明背景变黑色问题开始 --- */
  80.                             $color = imagecolorallocate($newImg,255,255,255);
  81.                             imagecolortransparent($newImg,$color);
  82.                             imagefill($newImg,0,0,$color);
  83.                             ImageCopyResampled($newImg, $source, 0, 0, 0, 0, $width, $height, $width, $height);
  84.                         } else {
  85.                             $newImg = imagecreate($width, $height);
  86.                             ImageCopyResized($newImg, $source, 0, 0, 0, 0, $width, $height, $width, $height);
  87.                         }
  88.                         imagejpeg($newImg, $imageFileRealPath.$imageFileName,100);
  89.                         break;
  90.                 }
  91.                 $startColumn = $this->ABC2decimal($startColumn);
  92.                 $data[$startRow-1][$startColumn] = $imageFileRealPath . $imageFileName;
  93.             } catch (\Throwable $th) {
  94.                 throw $th;
  95.             }
  96.              
  97.         }
  98.         $rowsData = array();
  99.         foreach ($data as $key => $rowData) {
  100.             $serial = safty_value($rowData[0],0,'intval'); // 第一列 序号
  101.             $title = safty_value($rowData[1],'','trim'); // 第二列 名称
  102.             $logo_save_path = safty_value($rowData[2],'','trim');  // logo图形保存路径
  103.             $remark = safty_value($rowData[3],'','trim');  //备注
  104.             if ($serial >0 && $logo_save_path!=="" && $title!==""){
  105.                 array_push($rowsData,array(
  106.                     "serial"=>$serial,
  107.                     "title"=>$title,
  108.                     "logo_path"=>$logo_save_path,
  109.                     "remark"=>$remark
  110.                 ));
  111.                      
  112.             }
  113.         }
  114.         // 将导入的数据生成文件缓存
  115.         $this->update_excel_data($rowsData); 
  116.         $upload_result = array(
  117.             "count" => count($rowsData),
  118.             "success" => 1,
  119.             "state"=>"SUCCESS"
  120.         );
  121.          
  122.     } else {
  123.         $upload_result = array(
  124.             "message" => "上传失败!",
  125.             "success" => 0
  126.         );
  127.     }
  128.     echo json_encode($upload_result);
  129. }

下面是操作流程，如下：


手把手教你使用ThinkPHP+phpExcel导入导出Excel数据


选择文件上传并导入，导出成功之后提示并刷当前列表页面。


手把手教你使用ThinkPHP+phpExcel导入导出Excel数据


导出成功后的列表：


手把手教你使用ThinkPHP+phpExcel导入导出Excel数据


至此，数据导入已经完成了。

不足，导入的Excel文件在数据导入后没有处理，因此建议最好删除掉

导出

现在就来将上面的数据导出，导出Excel的格式定义，先需要定义表头：

  1. $first_cells = array(
  2.     array("serial","序号"),
  3.     array("title","名称"),
  4.     array("logo","logo"),
  5.     array("remark","描述")
  6. );

接下来就是按照表头的格式，封装数据，如下：

  1. foreach ($excel_data as $key => $row_info) {
  2.     array_push($first_rows_data,array(
  3.         "serial"=>$row_info['serial'],
  4.         "title"=>$row_info['title'],
  5.         "logo"=>$row_info['logo_path'],
  6.         "remark"=>$row_info['remark']
  7.     ));
  8. }

至此，数据封装已经完成，完整代码如下：

  1. public function export(){
  2.     $excel_detail = array(
  3.         "author"=>"devpoint",
  4.         "date"=>join(" ",$artifacts_full)
  5.     );
  6.     // 定义导出Excel表格信息
  7.     $sheets = array(); // Excel表信息，一维代表一个数据表
  8.     // 定义表头
  9.     $first_cells = array(
  10.         array("serial","序号"),
  11.         array("title","名称"),
  12.         array("logo","logo"),
  13.         array("remark","描述")
  14.     );
  15.     // 为表增加数据
  16.     $excel_data = get_file_cache("excel_data");
  17.     $first_rows_data = array();
  18.     // 数据与上面表头对应
  19.     foreach ($excel_data as $key => $row_info) {
  20.         array_push($first_rows_data,array(
  21.             "serial"=>$row_info['serial'],
  22.             "title"=>$row_info['title'],
  23.             "logo"=>$row_info['logo_path'],
  24.             "remark"=>$row_info['remark']
  25.         ));
  26.     }
  27.     array_push($sheets,array(
  28.         "title"=>"前端项目流行框架",
  29.         "cells"=>$first_cells,
  30.         "rows"=>$first_rows_data
  31.     ));
  32.     $xlsName  = "Excel数据导出";
  33.     $xlsName = $xlsName  . date('YmdHis');
  34.     $this->exportExcel($xlsName,$sheets,$excel_detail);
  35. }

函数exportExcel将数据写入到Excel，并定义表格的样式，完整代码如下：

  1. protected function exportExcel($expTitle,$xlsSheets,$detail){
  2.     import("Org.Util.PHPExcel");
  3.     import("Org.Util.PHPExcel.Writer.Excel5");
  4.     import("Org.Util.PHPExcel.IOFactory.php");
  5.     $fileName = $expTitle;
  6.     $objPHPExcel = new \PHPExcel();
  7.     $objPHPExcel->getDefaultStyle()->getFont()->setName('宋体');
  8.     // Excel列名称
  9.     $cellName = array(
  10.         'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U',
  11.         'V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM',
  12.         'AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'
  13.     );
  14.     foreach ($xlsSheets as $index => $sheet_info) {
  15.         $sheet_title = $sheet_info['title'];
  16.         if ($index>0){
  17.             // Excel默认已经建好的数据表，超过一张需要执行这里创建一个工作表
  18.             $newSheet = new \PHPExcel_Worksheet($objPHPExcel, $sheet_title); //创建一个工作表
  19.             $objPHPExcel->addSheet($newSheet);
  20.         } else {
  21.             $objPHPExcel->getActiveSheet($index)->setTitle($sheet_title);
  22.         }
  23.         $expCellName = $sheet_info['cells'];
  24.         $expTableData = $sheet_info['rows'];
  25.         $cellNum = count($expCellName);
  26.         $dataNum = count($expTableData);
  27.         $cellmerget = "";
  28.         $cellWidths = array();
  29.         $sheet_head_title = $sheet_title;
  30.         // 下面需要为每个工作表定义宽度
  31.         switch ($index) {
  32.             case 1: // 每张表的索引从 0 开始计算
  33.                 $cellmerget = 'A1:E1';
  34.                 $cellWidths=array(16,16,16,28,16);
  35.                 break;
  36.             default:
  37.                 $cellmerget = 'A1:D1';
  38.                 $sheet_head_title = $sheet_title ;
  39.                 $cellWidths=array(16,16,16,36);
  40.                 break;
  41.         }
  42.         $activeSheet = $objPHPExcel->setActiveSheetIndex($index);
  43.  
  44.         for($i=0;$i<$cellNum;$i++){
  45.             $currentCellName = $cellName[$i];
  46.             $activeSheet->getRowDimension(1)->setRowHeight(36);
  47.             $activeSheet->getColumnDimension($currentCellName)->setWidth($cellWidths[$i]);
  48.             $activeSheet->getStyle($currentCellName.'1')->getFont()->setSize(12)->setBold(true);
  49.             $activeSheet->getStyle($currentCellName.'1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
  50.         }
  51.  
  52.         $activeSheet->mergeCells($cellmerget);//合并单元格
  53.         $activeSheet->setCellValue('A1', $sheet_head_title);
  54.         $activeSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  55.         $activeSheet->getStyle('A1')->getFont()->setSize(20);
  56.         $activeSheet->getRowDimension(1)->setRowHeight(50);
  57.         $styleThinBlackBorderOutline = array(  
  58.                 'borders' => array (  
  59.                     'outline' => array (  
  60.                             'style' => \PHPExcel_Style_Border::BORDER_MEDIUM,   //设置border样式
  61.                             'color' => array ('argb' => 'FF9b9b9b'),          //设置border颜色  
  62.                     ),  
  63.             ),  
  64.         );  
  65.         for($i=0;$i<$cellNum;$i++){
  66.             $currentCellName = $cellName[$i];
  67.             $activeSheet->getRowDimension(2)->setRowHeight(36);
  68.             $activeSheet->getColumnDimension($currentCellName)->setWidth($cellWidths[$i]);
  69.             $activeSheet->setCellValue($currentCellName.'2', $expCellName[$i][1]);
  70.             $activeSheet->getStyle($currentCellName.'2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
  71.             $activeSheet->getStyle($currentCellName.'2')->getFill()->getStartColor()->setARGB('FFc6efcd');
  72.             $activeSheet->getStyle($currentCellName.'2')->getFont()->setSize(12)->setBold(true);
  73.             $activeSheet->getStyle($currentCellName.'2')->applyFromArray($styleThinBlackBorderOutline);  
  74.             $activeSheet->getStyle($currentCellName.'2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
  75.             $activeSheet->freezePane($currentCellName.'3');  // 锁定表头，3 意味着锁定第3行上面的
  76.         }
  77.         switch ($index) {
  78.             case 1:
  79.  
  80.                 break;
  81.             default:
  82.                 $start_row_index = 3; // 数据开始索引行
  83.                 for($i1=0;$i1<$dataNum;$i1++){
  84.                     $objPHPExcel->getActiveSheet()->getRowDimension($i1+3)->setRowHeight(60);
  85.                     for($j1=0;$j1<$cellNum;$j1++){
  86.                         if ($j1===2){
  87.                             $logo_path = $expTableData[$i1][$expCellName[$j1][0]];
  88.                             if ($logo_path!=="" && file_exists($logo_path)){
  89.                                 $objDrawing = new \PHPExcel_Worksheet_Drawing();
  90.                                 $objDrawing->setPath($logo_path);
  91.                                 $objDrawing->setHeight(60);
  92.                                 $objDrawing->setWidth(60);
  93.                              
  94.                                 $objDrawing->setOffsetX(5);
  95.                                 $objDrawing->setOffsetY(5);
  96.                                 $objDrawing->setCoordinates($cellName[$j1].($i1+$start_row_index));
  97.                                 $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
  98.                             } else {
  99.                                 $objPHPExcel->getActiveSheet()->setCellValue($cellName[$j1].($i1+$start_row_index), "");
  100.                                 $objPHPExcel->getActiveSheet()->getStyle($cellName[$j1].($i1+$start_row_index))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
  101.                                 $objPHPExcel->getActiveSheet()->getStyle($cellName[$j1].($i1+$start_row_index))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  102.                                 $objPHPExcel->getActiveSheet()->getStyle($cellName[$j1].($i1+$start_row_index))->getAlignment()->setWrapText(true);
  103.                             }
  104.                         } else {
  105.                             $objPHPExcel->getActiveSheet()->setCellValue($cellName[$j1].($i1+$start_row_index), $expTableData[$i1][$expCellName[$j1][0]]);
  106.                             $objPHPExcel->getActiveSheet()->getStyle($cellName[$j1].($i1+$start_row_index))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
  107.                             $objPHPExcel->getActiveSheet()->getStyle($cellName[$j1].($i1+$start_row_index))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  108.                             $objPHPExcel->getActiveSheet()->getStyle($cellName[$j1].($i1+$start_row_index))->getAlignment()->setWrapText(true);
  109.                         }
  110.                     }
  111.                 }
  112.                 break;
  113.         }
  114.          
  115.     }
  116.     $objPHPExcel->setActiveSheetIndex(0);
  117.  
  118.     header('pragma:public');
  119.     header('Content-type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8;name="'.$fileName.'.xlsx"');
  120.     header("Content-Disposition:attachment;filename=$fileName.xlsx"); // attachment新窗口打印inline本窗口打印
  121.     $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  122.     $objWriter->save('php://output');
  123.     exit;
  124. }

导出后的格式如下：


手把手教你使用ThinkPHP+phpExcel导入导出Excel数据


锁定表头

锁定表头是Excel比较常见的功能，可以方便查阅者查阅数据，使用 phpExcel 设置表头的代码如下：

  1. $activeSheet->freezePane($currentCellName.'3'); // 3 意味着锁定第3行上面的行数

表格边框样式

上面的代码设置表格边框样式的代码为\PHPExcel_Style_Border::BORDER_MEDIUM，在 phpExcel 中有14个配置可选项目。

  1. PHPExcel_Style_Border::BORDER_NONE;
  2. PHPExcel_Style_Border::BORDER_THIN;
  3. PHPExcel_Style_Border::BORDER_MEDIUM;
  4. PHPExcel_Style_Border::BORDER_DASHED;
  5. PHPExcel_Style_Border::BORDER_DOTTED;
  6. PHPExcel_Style_Border::BORDER_THICK;
  7. PHPExcel_Style_Border::BORDER_DOUBLE;
  8. PHPExcel_Style_Border::BORDER_HAIR;
  9. PHPExcel_Style_Border::BORDER_MEDIUMDASHED;
  10. PHPExcel_Style_Border::BORDER_DASHDOT;
  11. PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT;
  12. PHPExcel_Style_Border::BORDER_DASHDOTDOT;
  13. PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT;
  14. PHPExcel_Style_Border::BORDER_SLANTDASHDOT;

1. BORDER_NONE

对应的完整配置项为 PHPExcel_Style_Border::BORDER_NONE，效果如下：


手把手教你使用ThinkPHP+phpExcel导入导出Excel数据


2. BORDER_THIN

  1. \PHPExcel_Style_Border::BORDER_THIN


手把手教你使用ThinkPHP+phpExcel导入导出Excel数据


3. BORDER_MEDIUM

  1. \PHPExcel_Style_Border::BORDER_MEDIUM


手把手教你使用ThinkPHP+phpExcel导入导出Excel数据


4. BORDER_DASHED

  1. \PHPExcel_Style_Border::BORDER_DASHED


手把手教你使用ThinkPHP+phpExcel导入导出Excel数据


5. BORDER_DOTTED

  1. \PHPExcel_Style_Border::BORDER_DOTTED


手把手教你使用ThinkPHP+phpExcel导入导出Excel数据


6. BORDER_THICK

  1. \PHPExcel_Style_Border::BORDER_THICK


手把手教你使用ThinkPHP+phpExcel导入导出Excel数据


7. BORDER_DOUBLE

  1. \PHPExcel_Style_Border::BORDER_DOUBLE


手把手教你使用ThinkPHP+phpExcel导入导出Excel数据


8. BORDER_HAIR

  1. \PHPExcel_Style_Border::BORDER_HAIR


手把手教你使用ThinkPHP+phpExcel导入导出Excel数据


9. BORDER_MEDIUMDASHED

  1. \PHPExcel_Style_Border::BORDER_MEDIUMDASHED


手把手教你使用ThinkPHP+phpExcel导入导出Excel数据


10. BORDER_DASHDOT

  1. \PHPExcel_Style_Border::BORDER_DASHDOT


手把手教你使用ThinkPHP+phpExcel导入导出Excel数据


11. BORDER_MEDIUMDASHDOT

  1. \PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT


手把手教你使用ThinkPHP+phpExcel导入导出Excel数据


12. BORDER_DASHDOTDOT

  1. \PHPExcel_Style_Border::BORDER_DASHDOTDOT


手把手教你使用ThinkPHP+phpExcel导入导出Excel数据


13. BORDER_MEDIUMDASHDOTDOT

  1. \PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT


手把手教你使用ThinkPHP+phpExcel导入导出Excel数据


14. BORDER_SLANTDASHDOT

  1. \PHPExcel_Style_Border::BORDER_SLANTDASHDOT


手把手教你使用ThinkPHP+phpExcel导入导出Excel数据


部署

在部署上，通常的架构是 nginx + php-fpm，对于Excel中图片比较多的数据导入需要设置加大上传文件的限制和超时时间。

在文件上传上，通常会出现 413 request Entity too Large 错误，解决的办法是在 nginx 配置中增加以下配置：

  1. client_max_body_size  2048m;

相应的 PHP 配置也需要修改，需要修改 php.ini ：

  1. upload_max_filesize = 2048M
  2. post_max_size = 2048M

Excel数据导入，通常会触发504错误，这种情况一般是执行时间太短，涉及的 nginx 配置：

  1. fastcgi_connect_timeout 600;

php-fpm 中的 www.conf 

  1. request_terminate_timeout = 1800

环境问题个人觉得是后台开发经常发生的，最佳的方式是实际运行出一个最佳的配置，将其制作成 docker 镜像，这样可以确保环境迁移或者其他场合需要，可以快速完成环境配置，而且不容易出问题。


原文地址：https://juejin.cn/post/6982953271933550628

作者：天行无忌


TAG标签：
本文网址：https://www.zztuku.com/detail-9234.html
站长图库 - 手把手教你使用ThinkPHP+phpExcel导入导出Excel数据
申明：如有侵犯，请 联系我们 删除。

评论（0）条

您还没有登录，请 登录 后发表评论！

提示：请勿发布广告垃圾评论，否则封号处理！！

    编辑推荐