PHP excel导出 表格 ,带图片

PHP excel导出 表格 ,带图片

PHP知识总结

安装

安装命令:composer require phpoffice/phpexcel

先看下结果
《PHP excel导出 表格 ,带图片》
这里是方法

 /** * 该方法是生成excel表格的 * @param $data * @param string $filename * @param string $format * @throws \PHPExcel_Exception * @throws \PHPExcel_Reader_Exception * @throws \PHPExcel_Writer_Exception */
    public static function export($data,$filename='excel',$format='xls')
    { 
        $excel = new PHPExcel();
        /*设置文本对齐方式*/
        $excel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $excel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objActSheet = $excel->getActiveSheet();

        $header = array_keys($data[0]);
        $arr = 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',);
        if (count($header)>26){ 
            $n=0;
            foreach ($arr as  $v){ 
                foreach ($arr as  $m){ 
                    $letter[] = $v.$m;
                    $n+=1;
                }
                if ($n>200){ 
                    break;
                }
            }
            $letter = array_merge($arr,$letter);
        }else{ 
            $letter = $arr;
        }

        //设置表头
        for ($i = 0;$i<count($header) ;$i++){ 
            $objActSheet->setCellValue($letter[$i].'1', $header[$i]);
        }

        $nn = count($header);
        foreach ($data as $key => $val) { 
            $n = $key+2 ;
            $excel->getActiveSheet()->getColumnDimension($letter[$n])->setWidth(10);   //设置单元格宽度
            $excel->getActiveSheet()->getColumnDimension($letter[$n])->setAutoSize(true);   //内容自适应
            $excel->getActiveSheet()->getStyle($letter[$key])->getAlignment()->setWrapText(true);//文本自适应
            $excel->getActiveSheet()->getRowDimension($n)->setRowHeight(100);//单元格高度
            for ( $i = 0;$i<count($val) ; $i++){ 
                $objActSheet->setCellValue($letter[$i] . $n, $val[$header[$i]]);    //数据行
            }
            if ($val['icon']){ 
                /*实例化插入图片类*/
                $objDrawing = new \PHPExcel_Worksheet_Drawing();
                /*设置图片路径*/
                $path = str_replace('http://www.tp6mang.com','',$data[$key]['icon']);
                $objDrawing->setPath('.'.$path);

                /*设置图片高度*/
                $excel->getActiveSheet()->getColumnDimension($letter[$nn])->setWidth(20);   //设置单元格宽度
                $objDrawing->setHeight(100);
                /*设置图片要插入的单元格*/
                $objDrawing->setCoordinates($letter[$nn].$n);
                /*设置图片所在单元格的格式*/
                $objDrawing->setOffsetX(0);
                $objDrawing->setRotation(0);
                $objDrawing->getShadow()->setVisible(true);
                $objDrawing->getShadow()->setDirection(50);
                $objDrawing->setWorksheet($objActSheet);
                continue;
            }
        }
        self::downloadExcel($excel,$filename, $format);

    }

 /** * 下载数据EXCEL表格导出 * @param $newExcel * @param $filename * @param $format * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception */
    public static function downloadExcel($newExcel, $filename, $format)
    { 
        // $format只能为 Xlsx 或 Xls
        $format = strtolower($format);
        if ($format == 'xlsx') { 
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        } elseif ($format == 'xls') { 
            header('Content-Type: application/vnd.ms-excel');
        }
        header('Content-Disposition: attachment;filename=' . $filename .date('Y-m-d').'.'. $format);
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($newExcel, 'Excel2007');
        $objWriter->save('php://output');

        //通过php保存在本地的时候需要用到
        //$objWriter->save($dir.'/demo.xlsx');
        //以下为需要用到IE时候设置
        // If you're serving to IE 9, then the following may be needed
        //header('Cache-Control: max-age=1');
        //If you're serving to IE over SSL, then the following may be needed
        //header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        //header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        //header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        //header('Pragma: public'); // HTTP/1.0
        exit;
    }

到这里就好了,方法是自己写的 ,有什么不足,欢迎留言
《PHP excel导出 表格 ,带图片》

    原文作者:jweicao
    原文地址: https://blog.csdn.net/jweicao/article/details/120043712
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
点赞