spreadsheet 导出 excel表格

最新推荐文章于 2024-05-15 13:10:51 发布

qq_27878777

最新推荐文章于 2024-05-15 13:10:51 发布

阅读量940

点赞数

分类专栏： php thinkphp 文章标签： php

原文链接：https://www.csdn.net/tags/MtTaEgysNzE2NDQ1LWJsb2cO0O0O.html

版权

php 同时被 2 个专栏收录

18 篇文章 0 订阅

订阅专栏

thinkphp

3 篇文章 0 订阅

订阅专栏

一. 导出excel 到本地,含有微信图像列

/**
     * 导出会员数据组织表格
     */
    public function exportsMem($lst)
    {
        //添加程序执行不限制时间
        ini_set("memory_limit", "500M");
        set_time_limit(0);
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        //设置工作表标题名称
        $worksheet->setTitle('会员数据导出表');

        //表头
        //设置单元格内容
        $worksheet->setCellValueByColumnAndRow(1, 1, ' APP会员数据导出表');
        $worksheet->setCellValueByColumnAndRow(1, 2, '用户手机号');
        $worksheet->setCellValueByColumnAndRow(2, 2, '用户头像');
        $worksheet->setCellValueByColumnAndRow(3, 2, '微信登录');
        $worksheet->setCellValueByColumnAndRow(4, 2, '绑定微信');
        $worksheet->setCellValueByColumnAndRow(5, 2, '支付宝用户名');
        $worksheet->setCellValueByColumnAndRow(6, 2, '支付宝账号');
        $worksheet->setCellValueByColumnAndRow(7, 2, '昵称');
        $worksheet->setCellValueByColumnAndRow(8, 2, '金币余额');
        $worksheet->setCellValueByColumnAndRow(9, 2, '现金余额');
        $worksheet->setCellValueByColumnAndRow(10, 2, '提现金额');
        $worksheet->setCellValueByColumnAndRow(11, 2, '注册时间');
        $worksheet->setCellValueByColumnAndRow(12, 2, '所属应用');
        $worksheet->setCellValueByColumnAndRow(13, 2, '应用市场');

        //设置列宽
        $worksheet->getColumnDimension('A')->setWidth(20);
        $worksheet->getColumnDimension('B')->setWidth(20);
        $worksheet->getColumnDimension('C')->setWidth(40);
        $worksheet->getColumnDimension('D')->setWidth(20);
        $worksheet->getColumnDimension('E')->setWidth(20);
        $worksheet->getColumnDimension('F')->setWidth(20);
        $worksheet->getColumnDimension('G')->setWidth(20);
        $worksheet->getColumnDimension('H')->setWidth(20);
        $worksheet->getColumnDimension('I')->setWidth(20);
        $worksheet->getColumnDimension('J')->setWidth(20);
        $worksheet->getColumnDimension('K')->setWidth(20);
        $worksheet->getColumnDimension('L')->setWidth(20);
        $worksheet->getColumnDimension('M')->setWidth(20);
        // $spreadsheet->getDefaultStyle()->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER); //设置所有的列水平居中
        //合并单元格
        $worksheet->mergeCells('A1:M1');
        $styleArray = [
            'font' => [
                'bold' => true
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        //设置单元表头格样式
        $worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(28);
        $worksheet->getStyle('A2:M2')->applyFromArray($styleArray)->getFont()->setSize(14);



        //获取全部企业上报的数据 进行excel导出

        foreach ($lst as $key => $value) {
            $k = $key + 3;
            $worksheet->setCellValue('A' . $k, $value['phone']);
            // $worksheet->setCellValue('B' . $k, $value['headimgurl']);
            // $worksheet->setCellValue('B' . $k, '');
            $worksheet->setCellValue('C' . $k, $value['isLoginWechat']);
            $worksheet->setCellValue('D' . $k, $value['hasWechat']);
            $worksheet->setCellValue('E' . $k, $value['alipay_name']);
            $worksheet->setCellValue('F' . $k, $value['alipay_account']);
            $worksheet->setCellValue('G' . $k, $value['nickname']);
            $worksheet->setCellValue('H' . $k, $value['gold']);
            $worksheet->setCellValue('I' . $k, $value['goldCash']);
            $worksheet->setCellValue('J' . $k, $value['outCash']);
            $worksheet->setCellValue('K' . $k, $value['addTime']);
            $worksheet->setCellValue('L' . $k, $value['app_name']);
            $worksheet->setCellValue('M' . $k, $value['market_name']);

            if(!empty($value['headimgurl'])){
                $img = self::curlGet($value['headimgurl']);
                $drawing[$k] = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                $drawing[$k]->setName('图片');
                $drawing[$k]->setDescription('图片');
                $drawing[$k]->setPath($img);
                // $drawing[$key]->setPath($dir . $basename);
                $drawing[$k]->setWidth(80);
                $drawing[$k]->setHeight(80);
                $drawing[$k]->setCoordinates('B'.$k);
                $drawing[$k]->setOffsetX(12);
                $drawing[$k]->setOffsetY(12);
                $drawing[$k]->setWorksheet($spreadsheet->getActiveSheet());
                $spreadsheet->getActiveSheet()->getRowDimension($k)->setRowHeight(80);

            } else {
                $worksheet->setCellValue('B' . $k, '');
            }

        }

        $len = count($lst);
        $styleArrayBody = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => '666666'],
                ],
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $total_jzInfo = $len + 2;
        //添加所有边框/居中
        $worksheet->getStyle('A1:M'.$total_jzInfo)->applyFromArray($styleArrayBody);

        $filename = '会员数据导出表.xlsx';
        //清空输出缓冲区
        ob_end_clean();
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        return $writer->save('php://output');
        exit();
    }

二. 解析微信图片


function getImage(string $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_HTTPHEADER,$header);
    $dataimg = curl_exec($curl);
    $code = curl_getinfo($curl, CURLINFO_HTTP_CODE);
    curl_close($curl);
    if ($code == 200) {
        $imgBase64Code = "data:image/jpeg;base64," . base64_encode($dataimg);
    }
    $img_content=$imgBase64Code;
    if (preg_match('/^(data:\s*image\/(\w+);base64,)/', $img_content, $result)){
        $type = $result[2];
        $dir = "Uploads/wxpic";
        is_dir($dir) OR mkdir($dir, 0777, true);

        $new_file = "Uploads/wxpic/".md5(uniqid(rand())).".{$type}";
        if (file_put_contents($new_file, base64_decode(str_replace($result[1],'', $img_content))))
        {
            return $new_file;
        }
    }
    return false;
}

 $s = getImage("https://thirdwx.qlogo.cn/mmopen/vi_32/yLnIhxnZuXgZN2dGCibx5oZWmQnBj6PT77e7MJBLbH0q8rPLjXUpFFdSxm2Z8tB6ia5SwFicl56kxgJIa0mAbXCZA/132");
var_dump($s);