【hyperf】PhpSpreadsheet导出带图片数据，多图片.xlsx文件

最新推荐文章于 2024-11-18 15:57:41 发布

从兄

最新推荐文章于 2024-11-18 15:57:41 发布

阅读量1.3k

点赞数

分类专栏： mysql php web文件下载文章标签： javascript 前端 servlet

本文链接：https://blog.youkuaiyun.com/qq_33212312/article/details/125801896

版权

php 同时被 3 个专栏收录

50 篇文章

订阅专栏

mysql

13 篇文章

订阅专栏

web文件下载

2 篇文章

订阅专栏

该博客介绍了一个使用PHP实现的功能，即从数据库中读取数据并生成Excel文件，包括设置字体样式、单元格宽高、数据对齐等格式，并且能够将图片导出到Excel中。这个功能适用于数据报表或者房源信息的导出场景。

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

/**
     * @GetMapping(path="exporttest/{type_id}")
     */
    public function exporttest($type_id){
        set_time_limit(0);

        // 读取数据
        $data = House::with(['user'=>function($query){
            $query->select('id','user_nickname', 'mobile');
        }]);
        if($type_id){
            $data = $data->where('house_type_id', $type_id);
        }
        $data = $data->select('house.id', 'user_id', 'title', 'province_id', 'city_id', 'region_id', 'street_id', 'house_type_id', 'is_sale', 'label_id_list', 'area', 'address', 'price', 'deposit_type', 'user_id', 'images', 'video', 'floor', 'longitude', 'latitude', 'source', 'status', 'desc', 'house.created_at', 'house.updated_at')
            ->get()->toArray();
        if (!$data) {
            /*****发生意外错误*****/
            throw  new APIExceptionHandler('错误');
        }
        $newExcel = new Spreadsheet();             //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();   //获取当前操作sheet的对象
        $objSheet->setTitle('订单表');             //设置当前sheet的标题

        // 将默认字体设置为Arial
        $newExcel->getDefaultStyle()->getFont()->setName('微软雅黑');
        // 将字体大小设置为12
        $newExcel->getDefaultStyle()->getFont()->setSize(12);
        // 将D列单元格设置为粗体字
        $newExcel->getActiveSheet()->getStyle('H')->getFont()->setBold(true);
        // 设置默认行高为60
        $newExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(50);

        // 给标题单元格设置水平垂直居中对齐
        $newExcel->getActiveSheet()->getStyle('A:Q')->getAlignment()->setHorizontal('center');
        $newExcel->getActiveSheet()->getStyle('A:Q')->getAlignment()->setVertical('center');

        // 给每列单元格设置宽度
        $newExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
        $newExcel->getActiveSheet()->getColumnDimension('B')->setWidth(8);
        $newExcel->getActiveSheet()->getColumnDimension('C')->setWidth(40);
        $newExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
        $newExcel->getActiveSheet()->getColumnDimension('E')->setWidth(8);
        $newExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
        $newExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
        $newExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
        $newExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('J')->setWidth(30);
        $newExcel->getActiveSheet()->getColumnDimension('K')->setWidth(10);
        $newExcel->getActiveSheet()->getColumnDimension('L')->setWidth(10);
        $newExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
        $newExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
        $newExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15);
        $newExcel->getActiveSheet()->getColumnDimension('P')->setWidth(10);
        $newExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(50);
        $newExcel->getActiveSheet()->getColumnDimension('R')->setWidth(50);
        //设置第一栏的标题
        $objSheet->setCellValue('A1', '房源ID')
            ->setCellValue('B1', '用户ID')
            ->setCellValue('C1', '标题')
            ->setCellValue('D1', '价格')
            ->setCellValue('E1', '类型')
            ->setCellValue('F1', '省')
            ->setCellValue('G1', '市')
            ->setCellValue('H1', '区')
            ->setCellValue('I1', '街道')
            ->setCellValue('J1', '地址')
            ->setCellValue('K1', '面积')
            ->setCellValue('L1', '押金类型')
            ->setCellValue('M1', '联系人名称')
            ->setCellValue('N1', '联系人电话')
            ->setCellValue('O1', '联系人微信')
            ->setCellValue('P1', '房源类型')
            ->setCellValue('Q1', '描述')
            ->setCellValue('R1', '备用多图');
        // 第二行起，每一行的值，setCellValueExplicit是用来导出文本格式的
        foreach ($data as $key => $val) {
            $key = $key + 2;
            $objSheet->setCellValue('A' . $key, $val['id'])
                ->setCellValue('B' . $key, $val['user_id'])
                ->setCellValue('C' . $key, $val['title'])
                ->setCellValue('D' . $key, $val['price'])
                ->setCellValue('E' . $key, $val['is_sale'])
                ->setCellValue('F' . $key, $val['province_name'])
                ->setCellValue('G' . $key, $val['city_name'])
                ->setCellValue('H' . $key, $val['region_name'])
                ->setCellValue('I' . $key, $val['street_name'])
                ->setCellValue('J' . $key, $val['address'])
                ->setCellValue('K' . $key, $val['area'])
                ->setCellValue('L' . $key, $val['deposit_type_name'])
                ->setCellValue('M' . $key, $val['user']['user_nickname'])
                ->setCellValue('N' . $key, $val['user']['mobile'])
                ->setCellValue('O' . $key, $val['user']['mobile'])
                ->setCellValue('P' . $key, $val['house_type_id'])
                ->setCellValue('Q' . $key, $val['desc']);

            $num = 10;
            // 多图导出
            if (!empty($val['images'])) {
                $images = $val['images'];
                foreach ($images as $k => $v){
                    $image = 'public/'.$v;
                    // 获取本地文件夹路径
                    $dir = 'public/uploads/' . date('Ymd', time()) . '/';
                    if (!file_exists($dir)) {
                        //如果目录不存在则递归创建
                        mkdir($dir, 0777, true);
                    }
                    $file_info = pathinfo($image);

                    // 过滤非文件类型
                    if (!empty($file_info['basename'])) {
                        $basename = $file_info['basename'];
                        // 进行检测文件是否存在

                        is_dir($dir) OR mkdir($dir, 0777, true);
                        file_put_contents($dir . $basename, file_get_contents($image));

                        // 引入操作图片类
                        $drawings[$key] = new Drawing();
                        $drawings[$key]->setResizeProportional(false); // TODO 此处顺序不可调，因为导出默认是按原图像缩放的，设置成false才可以设置成可控制的宽度，要注意哦！
                        $drawings[$key]->setName('备用多图');
                        $drawings[$key]->setDescription('备用多图');
                        $drawings[$key]->setPath($dir . $basename);
                        $drawings[$key]->setWidth(60);
                        $drawings[$key]->setHeight(60);
                        $drawings[$key]->setOffsetX($num);
                        $drawings[$key]->setOffsetY(10);
                        $drawings[$key]->setCoordinates('R' . $key);
                        $drawings[$key]->setWorksheet($objSheet);
                    }
                    $num = $num + 70; // 增加每张图之间的间距
                }
            }
            else
            {
                $objSheet->setCellValue('R' . $key, '');
            }
        }
        return self::downloadExcel($newExcel, '房源表', 'Xlsx');
    }
    # 公共文件，用来传入xls并下载
    public static function downloadExcel($newExcel, $filename, $format)
    {
        // $format只能为 Xlsx 或 Xls
        if ($format == 'Xlsx') {
            $contentType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
        } elseif ($format == 'Xls') {
            $contentType = 'pplication/vnd.ms-excel';
        }
        $filenameurl = $filename . date('Y-m-d') . '.' . strtolower($format);
        $objWriter = IOFactory::createWriter($newExcel, $format);
        //保存到服务器的临时文件下
        $objWriter->save("./tmp.xlsx");
        //将文件转字符串
        $content = file_get_contents('./tmp.xlsx');
        //删除临时文件
        unlink("./tmp.xlsx");

        $response = new Response();
        return $response->withHeader('content-description', 'File Transfer')
            ->withHeader('content-type', $contentType)
            ->withHeader('content-disposition', "attachment; filename={$filenameurl}")
            ->withHeader('content-transfer-encoding', 'binary')
            ->withHeader('pragma', 'public')
            ->withBody(new SwooleStream((string)$content));
    }