• 技术文章 >php框架 >ThinkPHP

    手把手教你使用ThinkPHP+phpExcel导入导出Excel数据(实践)

    青灯夜游青灯夜游2021-09-15 20:00:34转载105
    PHP如何导入带图片的Excel表格呢?图片又如何导出到Excel中呢?导出的Excel如何定义样式使其更加漂亮?下面本篇文章就来给大家一一解决,希望对大家有所帮助!

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

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

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

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

    导入

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

    模板

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

    1.png

    有文本,有图片,导入数据首选需要读取到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 \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.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);
        }

    下面是操作流程,如下:

    2.png

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

    3.png

    导出成功后的列表:

    4.png

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

    不足,导入的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;$i<$cellNum;$i++){
                    $currentCellName = $cellName[$i];
                    $activeSheet->getRowDimension(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;$i<$cellNum;$i++){
                    $currentCellName = $cellName[$i];
                    $activeSheet->getRowDimension(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;$i1<$dataNum;$i1++){
                            $objPHPExcel->getActiveSheet()->getRowDimension($i1+3)->setRowHeight(60);
                            for($j1=0;$j1<$cellNum;$j1++){
                                if ($j1===2){
                                    $logo_path = $expTableData[$i1][$expCellName[$j1][0]];
                                    if ($logo_path!=="" && file_exists($logo_path)){
                                        $objDrawing = new \PHPExcel_Worksheet_Drawing();
                                        $objDrawing->setPath($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;
        }

    导出后的格式如下:

    5.png

    锁定表头

    锁定表头是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,效果如下:

    6.png

    2. BORDER_THIN

    \PHPExcel_Style_Border::BORDER_THIN

    7.png

    3. BORDER_MEDIUM

    \PHPExcel_Style_Border::BORDER_MEDIUM

    8.png

    4. BORDER_DASHED

    \PHPExcel_Style_Border::BORDER_DASHED

    9.png

    5. BORDER_DOTTED

    \PHPExcel_Style_Border::BORDER_DOTTED

    10.png

    6. BORDER_THICK

    \PHPExcel_Style_Border::BORDER_THICK

    11.png

    7. BORDER_DOUBLE

    \PHPExcel_Style_Border::BORDER_DOUBLE

    12.png

    8. BORDER_HAIR

    \PHPExcel_Style_Border::BORDER_HAIR

    13.png

    9. BORDER_MEDIUMDASHED

    \PHPExcel_Style_Border::BORDER_MEDIUMDASHED

    14.png

    10. BORDER_DASHDOT

    \PHPExcel_Style_Border::BORDER_DASHDOT

    15.png

    11. BORDER_MEDIUMDASHDOT

    \PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT

    16.png

    12. BORDER_DASHDOTDOT

    \PHPExcel_Style_Border::BORDER_DASHDOTDOT

    17.png

    13. BORDER_MEDIUMDASHDOTDOT

    \PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT

    18.png

    14. BORDER_SLANTDASHDOT

    \PHPExcel_Style_Border::BORDER_SLANTDASHDOT

    19.png

    部署

    在部署上,通常的架构是 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

    作者:天行无忌

    推荐学习:《PHP视频教程

    以上就是手把手教你使用ThinkPHP+phpExcel导入导出Excel数据(实践)的详细内容,更多请关注php中文网其它相关文章!

    声明:本文转载于:掘金--天行无忌,如有侵犯,请联系admin@php.cn删除
    专题推荐:ThinkPHP phpExcel
    上一篇:一文讲解thinkphp5如何进行异步验证邮箱 下一篇:解析TP5框架从入口到输出界面的加载流程
    线上培训班

    相关文章推荐

    • ThinkPHP有三种方式收集表单数据• 一文详解thinkphp控制器的定义和使用• thinkphp中.htaccess文件的巧妙运用• thinkphp5整合phpsocketio过程亲自踩的坑!• thinkphp5与laravel的区别是什么

    全部评论我要评论

  • 取消发布评论发送
  • 1/1

    PHP中文网