PHPExcel导出文件带图片样式

首先更新composer update

phpexcel下载       composer require phpoffice/phpexcel

 

//导出图片
    public function excelphoto()
    {
        $res = Db::table('user')->select();
        vendor("PHPExcel.Classes.PHPExcel");

        $resultPHPExcel = new \PHPExcel();
//设置样式

        $objActSheet = $resultPHPExcel->getActiveSheet();

        // 水平居中（位置很重要，建议在最初始位置）
        $resultPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $resultPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $resultPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        // 设置个表格宽度
        $resultPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
        $resultPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
        $resultPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);

        // 垂直居中
        $resultPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $resultPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $resultPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);



//设值
        $resultPHPExcel->getActiveSheet()->setCellValue('A1', 'id');
        $resultPHPExcel->getActiveSheet()->setCellValue('B1', '名称');
        $resultPHPExcel->getActiveSheet()->setCellValue('C1', '图片');
        $i = 2;
        foreach ($res as $k =>$item) {
            $resultPHPExcel->getActiveSheet()->setCellValue('A' . $i, $item['id']);
            $resultPHPExcel->getActiveSheet()->setCellValue('B' . $i, $item['name']);
            $objDrawing = new \PHPExcel_Worksheet_Drawing();
            $objDrawing->setPath('.' . $item['pic']);//这里拼接 . 是因为要在根目录下获取
            // 设置宽度高度
            $objDrawing->setHeight(50);//照片高度
            $objDrawing->setWidth(50); //照片宽度
            /*设置图片要插入的单元格*/
            $objDrawing->setCoordinates('C' . $i);
            // 图片偏移距离
            $objDrawing->setOffsetX(15);
            $objDrawing->setOffsetY(15);
            $objDrawing->setWorksheet($resultPHPExcel->getActiveSheet());
            $i++;
            $objActSheet->getRowDimension($k+2)->setRowHeight(80);

        }
        //设置导出文件名

        $outputFileName = 'yantao.xls';

        $xlsWriter = new \PHPExcel_Writer_Excel5($resultPHPExcel);

        ob_end_clean();
        header("Content-Type: application/force-download");

        header("Content-Type: application/octet-stream");

        header("Content-Type: application/download");

        header('Content-Disposition:inline;filename="' . $outputFileName . '"');

        header("Content-Transfer-Encoding: binary");

        header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");

        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");

        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

        header("Pragma: no-cache");

        $xlsWriter->save("php://output");
    }

 

 

 解决方案

 更改：vendor->PHPExcel->Vlasses->PHPExcel->Shared->OLE.php->

 

 

 

 效果演示

 

 

导入PHPExcel文件

 public function excel(){
        $request = \think\Request::instance();
        $excel = request()->file('excel')->getInfo();//excel为file中的name
        vendor("PHPExcel.PHPExcel.IOFactory");
        $objPHPExcel = \PHPExcel_IOFactory::load($excel['tmp_name']);//读取上传的文件
        $arrExcel = $objPHPExcel->getSheet(0)->toArray();//获取其中的数据
$word = $arrExcel[0];
array_shift($arrExcel);

        $data = array();
        $num = count($arrExcel) -1;
     foreach ($arrExcel as $key => $value) {

         $data[] = array(
            'company_name'=>$arrExcel[$key][0]
            );

     }

     $result = Db::table('ag_company')->insertAll($data);
    if($result){
        $this->success('导入成功');
    }else{
        $this->error('导入失败');
    }
    }