PHPExcel导出网络图片(如果是微信图片需将微信图片转成正常的图片地址)

博客介绍了如何使用PHPExcel库在后台处理用户数据,将包含网络图片的记录导出到Excel表格中,特别是处理微信图片并转换为正常图片URL的步骤。同时提到在php中设置Excel单元格宽度遇到的问题,期待读者提供解决方案。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

数据库的格式:
在这里插入图片描述
后台:
在这里插入图片描述
需求:将用户导到excel表中

use PHPExcel_IOFactory;use PHPExcel;

//将用户导出到excel表中,并保存用户头像
    public function export(){
        $exportId = json_decode(Request::param('exportId'),true);//需要导出数据的id
        $exportS  = Db::name('member');//表名

        $objPHPExcel = new PHPExcel();
        $objActSheet = $objPHPExcel->getActiveSheet();
        try {

            $objActSheet->setCellValue( 'A1','用户名' );
            $objActSheet->setCellValue( 'B1','电话' );
            $objActSheet->setCellValue( 'C1','用户类型' );
            $objActSheet->setCellValue( 'D1','性别' );
            $objActSheet->setCellValue( 'E1','学校' );
            $objActSheet->setCellValue( 'F1','班级' );
            $objActSheet->setCellValue( 'G1','地址' );
            $objActSheet->setCellValue( 'H1','注册时间' );
            $objActSheet->setCellValue( 'I1','头像' );
            $objActSheet->setCellValue( 'J1','头像链接' );

            $cell_counter = 1;
            if( $exportId == null){
                $store_ids = $exportS->select();

            }else{
                $store_ids = $exportS->where('id','in',$exportId)->select();
            }
            foreach ($store_ids as $shopinfo) {
                switch ($shopinfo['user_type']) {
                    case 1:
                        $shopinfo['user_type'] = '学生';
                        break;
                    case 2:
                        $shopinfo['user_type'] = '老师';
                        break;
                    case 3:
                        $shopinfo['user_type'] = '家长';
                        break;
                }

                switch ($shopinfo['gender']) {
                    case 1:
                        $shopinfo['gender'] = '男';
                        break;
                    case 0:
                        $shopinfo['gender'] = '女';
                        break;
                }

                $cell_counter++;
                $objPHPExcel->setActiveSheetIndex( 0 );

                if(strstr($shopinfo['avatar'], 'wx.qlogo')){
                    $avatar = substr($shopinfo['avatar'],strripos($shopinfo['avatar'],"//")+2);     //截取微信头像中的https://
                    $shopinfo['avatar']  =  $this->wx_image($avatar);
                    $shopinfo['customer_return_img'] = $shopinfo['avatar'];//头像地址
                }else{
                    $shopinfo['customer_return_img'] = $shopinfo['avatar'];
                }

                $objActSheet->setCellValue( 'A' . $cell_counter, $shopinfo['mobile'] );
                $objActSheet->setCellValue( 'B' . $cell_counter, $shopinfo['mobile'] );
                $objActSheet->setCellValue( 'C' . $cell_counter, $shopinfo['user_type'] );
                $objActSheet->setCellValue( 'D' . $cell_counter, $shopinfo['gender'] );
                $objActSheet->setCellValue( 'E' . $cell_counter, $shopinfo['school'] );
                $objActSheet->setCellValue( 'F' . $cell_counter, $shopinfo['class'] );
                $objActSheet->setCellValue( 'G' . $cell_counter, $shopinfo['address'] );
                $objActSheet->setCellValue( 'H' . $cell_counter, $shopinfo['create_time'] );
                $objActSheet->setCellValue( 'J' . $cell_counter, $shopinfo['avatar'] );



                if ($shopinfo['customer_return_img']) {
                    // 图片生成
                    $objDrawing[$cell_counter] = new \PHPExcel_Worksheet_MemoryDrawing();
                    // 截取图片的格式,用不同的方法
                    $end[$cell_counter] = substr($shopinfo['customer_return_img'], -3);

                    if ($end[$cell_counter] == 'jpg' || $end[$cell_counter] == 'peg') {
                        $img[$cell_counter] = @imagecreatefromjpeg($shopinfo['customer_return_img']);
                    }else if ($end[$cell_counter] == 'png') {
                        $img[$cell_counter] = @imagecreatefrompng($shopinfo['customer_return_img']);
                    }else if ($end[$cell_counter] == 'gif') {
                        $img[$cell_counter] = @imagecreatefromgif($shopinfo['customer_return_img']);
                    }
                    $objDrawing[$cell_counter]->setImageResource($img[$cell_counter]);
                    $objDrawing[$cell_counter]->setRenderingFunction(\PHPExcel_Worksheet_MemoryDrawing::RENDERING_DEFAULT);//渲染方法

                    $objDrawing[$cell_counter]->setMimeType(\PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
                    // // 设置宽度高度
                    $objDrawing[$cell_counter]->setHeight(40);//照片高度
                    $objDrawing[$cell_counter]->setWidth(60); //照片宽度
                    // /*设置图片要插入的单元格*/
                    $objDrawing[$cell_counter]->setCoordinates('I'.$cell_counter);
                    // // 图片偏移距离
                    $objDrawing[$cell_counter]->setOffsetX(8);
                    $objDrawing[$cell_counter]->setOffsetY(8);
                    $objDrawing[$cell_counter]->setWorksheet($objPHPExcel->getActiveSheet());
                    // 表格高度
                    $objActSheet->getRowDimension($cell_counter)->setRowHeight(80);
                }else{
                    $objActSheet->setCellValue('I'.$cell_counter, '');
                    // 表格高度
                    $objActSheet->getRowDimension($cell_counter)->setRowHeight(40);
                }
            }
            // Set column data auto width
            for ( $col = 'A'; $col !== 'N'; $col ++ ) {
                $objPHPExcel->getActiveSheet()->getColumnDimension( $col )->setAutoSize( true );
            }

            // Rename sheet
            $objPHPExcel->getActiveSheet()->setTitle( "用户统计表" );
            // Save Excel file
            $upload_dir = Env::get('root_path') . 'public/upload/excel/';
            if (!file_exists($upload_dir)){
                mkdir( $upload_dir, 0755, true );
            }
            $objWriter  = PHPExcel_IOFactory::createWriter( $objPHPExcel, 'Excel2007' );
            $filesname = time().'.xlsx';
            $objWriter->save( $upload_dir . '/'.$filesname );
            echo '1|'.config('website').'/upload/excel/'.$filesname;

        }catch ( Exception $e ){
            echo "导出失败";
        }
    }

    //微信头像转换成正常图片格式(如:jpg、gif、jpeg等)

    /**
     * @param $url  微信图片地址
     * @return string  正常图片地址
     */
    public function wx_image($url){
        $header = array(
            'User-Agent: Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:45.0) Gecko/20100101 Firefox/45.0',
            'Accept-Language: zh-CN,zh;q=0.8,en-US;q=0.5,en;q=0.3',
            'Accept-Encoding: gzip, deflate',);

        $curl = curl_init();
        curl_setopt($curl, CURLOPT_URL, $url);curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($curl, CURLOPT_FOLLOWLOCATION, true);curl_setopt($curl, CURLOPT_ENCODING, 'gzip');
        curl_setopt($curl, CURLOPT_HTTPHEADER, $header);
        $data = curl_exec($curl);$code = curl_getinfo($curl, CURLINFO_HTTP_CODE);
        curl_close($curl);
        if ($code == 200) {//把URL格式的图片转成base64_encode格式的!
            $imgBase64Code = "data:image/jpeg;base64," . base64_encode($data);
        }
        $img_content=$imgBase64Code;//图片内容
        if (preg_match('/^(data:\s*image\/(\w+);base64,)/', $img_content, $result)) {
            $type = $result[2];//得到图片类型png?jpg?gif?
            $fileData  =   base64_decode(str_replace($result[1], '', $img_content));
            $image_name = uniqid() .".{$type}";
            $upload_dir = ENV::get('root_path') . 'public/upload/img/';
            if (!file_exists($upload_dir)){
                mkdir( $upload_dir, 0755, true );
            }
            $filedir = $upload_dir . '/' . $image_name;
            $src     = config( 'website' ) . 'upload/img/' . $image_name;
            if (file_put_contents($filedir,$fileData)) {
                return $src;
            }
        }
    }

结果:
在这里插入图片描述

还有一个小问题:
php对Excel设置宽度,不起作用,会的小伙伴,留言告知一下!!!

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值