php导出excel文件

环境

php7.4+hyperf3

composer require phpoffice/phpspreadsheet

代码

class IndexController extends AbstractController
{ 
    /**
     * @Inject
     * @var Picture
     */
    private $picture;

	public function index(){
		$res_data[]=[
                "robot" => '哈哈机器人',
                "order" => 'TES001',
                "user" => '人01',
                "start_time" => '2024-02-01 00:12:13',
                "end_time" => '2024-02-01 20:12:13',
                "time" => 120,//时长
                "soc" => '12',
                "billing" => '1.1',
            ];
		return $this->exportData($res_data);
	}

	private function exportData($res_data)
	    {
	        $rows = $res_data;
	        $newExcel = new Spreadsheet();
	        $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象
	        $objSheet->setTitle('自动生成随机充电订单表');  //设置当前sheet的标题
	        //设置宽度为true,不然太窄了
	        $newExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
	        $newExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
	        $newExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
	        $newExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
	        $newExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
	        $newExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
	        $newExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
	        $newExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
	
	        //设置第一栏的标题
	        $objSheet->setCellValue('A1', '站点名称')
	            ->setCellValue('B1', '订单号')
	            ->setCellValue('C1', '用户')
	            ->setCellValue('D1', '开始时间')
	            ->setCellValue('E1', '结束时间')
	            ->setCellValue('F1', '时长')
	            ->setCellValue('G1', '充电量（KW）')
	            ->setCellValue('H1', '计费（元）');
	        $k = 1;
	        foreach ($rows as &$val) {
	            $k = $k + 1;
	            $objSheet->setCellValue('A' . $k, $val['robot'])
	                ->setCellValue('B' . $k, $val['order'])
	                ->setCellValue('C' . $k, $val['user'])
	                ->setCellValue('D' . $k, $val['start_time'])
	                ->setCellValue('E' . $k, $val['end_time'])
	                ->setCellValue('F' . $k, $val['time'])
	                ->setCellValue('G' . $k, $val['soc'])
	                ->setCellValue('H' . $k, $val['billing']);
	        }
	//        [$bool, $path] = $this->picture->save($newExcel, '自动生成随机充电订单表_' . date("YmdHis"));
	        [$bool, $path] = $this->picture->save($newExcel, '自动生成随机充电订单表_' . date("YmdHis"), true, false);
	        if ($bool) {
	            return response_api(true, '获取成功', ['path' => $path['path']]);
	        } else {
	            return response_api(false, $path, [], StateCode::error_confirm);
	        }
	
	    }

}

class Picture

<?php
declare(strict_types=1);

namespace App\utils;

use App\Logic\v1\CustomerLogic;
use League\Flysystem\Filesystem;
use PhpOffice\PhpSpreadsheet\IOFactory;

class Picture
{

    /**
     * 云存储文件路径
     */
    const CLOUD_SAVE_PATH = 'saas/uploads/';


    //保存文件到本地或云端
    function save($newExcel, string $fileName, bool $uploadCloud = false, bool $deleteLocal = true)
    {
        $fileName = $fileName . '.xlsx';
        $path = '/storage/excel/' . $fileName;
        $fullPath = BASE_PATH . $path;
        $writer = IOFactory::createWriter($newExcel, 'Xlsx');
        $writer->save($fullPath);
        $newExcel->disconnectWorksheets();
        if (!$uploadCloud) {
            return [true, ['full_path' => $fullPath, 'path' => $path]];
        } else {
            $url = env('CDNURL', 'http://cdn.***.cn') . "/" . $this->uploadImgFile($path, null, $deleteLocal);
            return !$path ? [false, '上传到云端失败'] : [true, ['full_path' => $fullPath, 'path' => $url]];
        }
    }


    /**
     * Notes:
     * User: Areom
     * Date: 2022/9/20
     */
    public function uploadImg($file, $filesystem, $type = 0)
    {
        /*$secretId = config('tencent.SECRETIDCOS');//"AKIDb5****AJDuOeeP"; //secretId
        $secretKey = config('tencent.SECRETKEYCOS');//"d2ECv****Y0EE1by84nI"; //secretKey
        $region = config('tencent.REGIONCOS');//"ap-chengdu"; //替换为用户的 region，已创建桶归属的region可以在控制台查看
        $cosClient = new \Qcloud\Cos\Client(
            array(
                'region' => $region,
                'schema' => 'https', //协议头部，默认为http
                'credentials'=> array(
                    'secretId'  => $secretId ,
                    'secretKey' => $secretKey)));
        $local_path = $fullName; //保存到用户本地路径

        try {
            $result = $cosClient->putObject([
                'Bucket' => config('tencent.BUCKETCOS'),//'ore-13****0927', //存储桶名称，由BucketName-Appid 组成，可以在COS控制台查看 https://console.cloud.tencent.com/cos5/bucket
                'Key' => $fileName, //此处的 key 为对象键
                'Body' => fopen($local_path, 'rb')]
            );
            //if ($file->getRealPath()) {
                @unlink($fullName);
            //}
            return "http://".$result["Location"];

        } catch (\Exception $e) {
            // 请求失败
            return $e;
        }*/
        if ($type == 1) {
            $stream = fopen($file, 'r+');
            $filename = ".xlsx";
        } else {
            $stream = fopen($file->getRealPath(), 'r+');
            $filename = $file->getClientFilename();
        }

        //重命名 拼接上传路径
        $rename = date('Ym') . "/" . uniqid() . '.' . explode('.', $filename)[1];
        $a = $filesystem->writeStream('saas/uploads/' . $rename, $stream);
        // Check if a file exists
        //if(!$filesystem->has('saas/uploads/'.$rename)){
        //return array('error' => 0, 'info' => '上传失败，请重新上传');
        //}
        return env('CDNURL', 'http://cdn.***.cn') . '/saas/uploads/' . $rename;
    }

    /**
     * Notes:上传本地文件到云端
     * Author: Areom
     * Date: 2023/1/5 0005 11:54
     * @param string $filePath 本地文件相对路径
     * @param string|null $cloudPath 指定云存储路径
     * @param bool $deleteLocal 上传成功后是否删除本地文件
     * @return string
     * @throws \League\Flysystem\FilesystemException
     */
    public function uploadImgFile(string $filePath, string $cloudPath = null, bool $deleteLocal = false, string $fileName = '')
    {
        try {
            $filePath = BASE_PATH . $filePath;
            $ymdPath = date('Y-m');
            if (!$cloudPath) {
                $cloudPath = self::CLOUD_SAVE_PATH;
            }
            $startIndex = strrpos($filePath, '.');
            $extension = substr($filePath, $startIndex);
            $stream = fopen($filePath, 'r+');
            $fileSystem = make(Filesystem::class);
            $fileName = $ymdPath . '/' . ((!empty($fileName)) ? $fileName : $this->createFileName()) . $extension;
            $fileSystem->writeStream($cloudPath . $fileName, $stream);
            if (is_resource($stream)) {
                fclose($stream);
            }
            if ($deleteLocal) {
                @unlink($filePath);
            }
            return config('oss_base_path') . $cloudPath . $fileName;
        } catch (\Exception $exception) {
//            var_dump($exception->getMessage());
            return '';
        }
    }

    /**
     * Notes: 创建文件名称
     * Author: Areom
     * Date: 2023/1/5 0005 11:50
     */
    private function createFileName()
    {
        return md5(date('YmdHis') . uniqid() . mt_rand(1000, 9999));
    }


}