PHP百万数据级导出excel

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Response;
use App\Models\User;

public function exportUsers(Request $request)
{
    $fileName = 'users.csv';

    $headers = [
        'Content-type' => 'text/csv',
        'Content-Disposition' => sprintf('attachment; filename="%s"', $fileName),
    ];

    $totalRows = User::count();
    $chunkSize = 1000;
    $totalChunks = ceil($totalRows / $chunkSize);

    $stream = fopen('php://output', 'w');

    // 写入表头
    fputcsv($stream, ['ID', 'Name', 'Email']);

    for ($i = 1; $i <= $totalChunks; $i++) {
        $offset = ($i - 1) * $chunkSize;
        $users = User::offset($offset)->limit($chunkSize)->get();

        foreach ($users as $user) {
            // 写入一行数据
            fputcsv($stream, [$user->id, $user->name, $user->email]);
        }

        // 强制刷新输出缓冲区
        ob_flush();
        flush();
    }

    fclose($stream);

    return Response::make('', 200, $headers);
}
Copy

        ini_set('memory_limit', '1024M');
        set_time_limit(0);

        $path = \think\facade\App::getRootPath() . 'public/upload/';

        $config = [
            'path' => $path,
        ];

        $fileName = 'tutorial01.xlsx';

        $excel      = new \Vtiful\Kernel\Excel($config);
        $fileObject = $excel->constMemory($fileName, NULL, false);
        $fileHandle = $fileObject->getHandle();

        $format    = new \Vtiful\Kernel\Format($fileHandle);
        $boldStyle = $format->bold()->toResource();

        $fileObject = $fileObject->setRow('A1', 10, $boldStyle) // 写入数据前设置行样式
                                 ->header(['id', 'person_id', 'person_name', 'gmt_create', 'gmt_modified']);

        for ($i = 5000; $i <= 1000000; $i += 5000) {
            $offset     = $i - 5000;
            $limit      = 5000;
            $data       = Db::table('t1')->limit($offset, $limit)->select();
            $data       = collect($data)->map(function ($item) {
                return collect($item)->values();
            })->toArray();
            $fileObject = $fileObject->data($data);
        }

        $filePath = $excel->output();

        header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        header('Content-Disposition: attachment;filename="' . $fileName . '"');
        header('Content-Length: ' . filesize($filePath));
        header('Content-Transfer-Encoding: binary');
        header('Cache-Control: must-revalidate');
        header('Cache-Control: max-age=0');
        header('Pragma: public');
        ob_clean();
        flush();
        if (copy($filePath, 'php://output') === false) {
            exit('copy file to path error');
        }
        @unlink($filePath);Copy

        set_time_limit(0);
        ini_set('memory_limit', '128M');
        $fileName = "零钱提现".date('YmdHis', time());
        header('Content-Type: application/vnd.ms-execl');
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');
        $begin = microtime(true);
        //打开php标准输出流
        //以写入追加的方式打开
        $fp = fopen('php://output', 'a');
        //我们试着用fputcsv从数据库中导出1百万的数据
        //我们每次取1万条数据，分100步来执行
        //如果线上环境无法支持一次性读取1万条数据，可把$nums调小，$step相应增大。
        $total = self::$model->getTotal($startDate, $endDate);
        $nums = 10000;
        $totalPage = ceil($total / $nums);
        //设置标题
        $title = [
            'recordID',
            'playerID',
            'showID',
            '玩家昵称',
            '提现金额(元)',
            '提现后零钱(元)',
            '时间',
            '状态',
        ];
        //将标题写到标准输出中
        fputcsv($fp, $title);
        for ($page = 1; $page <= $totalPage; ++$page) {
            //分页取
            $data = self::$model->getList($startDate, $endDate, $nums);
            //先取出玩家id
            $playerIds = array_unique(array_column($data, 'playerID'));
            //获取玩家信息
            $user = Obj::model('playerBasicData')->getUserInfoByAttr($playerIds);
            if ($data) {
                foreach ($data as $row) {
                    $temp = [
                        //recordID
                        $row['recordID'] . "\t",
                        //playerID
                        $row['playerID'] . "\t",
                        //showID
                        $user[$row['playerID']]['showID'] . "\t",
                        //昵称
                        $user[$row['playerID']]['nickName'] . "\t",
                        //'提现金额(元)'
                        ($row['amount'] / 100),
                        //'提现后零钱(元)'
                        ($row['changeAfter'] / 100),
                        //'时间'
                        $row['createTime'] . "\t",
                        //'状态'
                        Model_My_WithdrawRecord::STATUS_TEXT[$row['status']] . "\t"
                    ];
                    fputcsv($fp, $temp);
                }
                //每1万条数据就刷新缓冲区
                ob_flush();
                flush();
            }
        }Copy

set_time_limit(0);
        ini_set('memory_limit', '128M');
        $fileName = "零钱提现".date('YmdHis', time());
        header('Content-Type: application/vnd.ms-execl');
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');
        $begin = microtime(true);
        //打开php标准输出流
        //以写入追加的方式打开
        $fp = fopen('php://output', 'a');
        //我们试着用fputcsv从数据库中导出1百万的数据
        //我们每次取1万条数据，分100步来执行
        //如果线上环境无法支持一次性读取1万条数据，可把$nums调小，$step相应增大。
        $total = self::$model->getTotal($startDate, $endDate);
        $nums = 10000;
        $totalPage = ceil($total / $nums);
        //设置标题
        $title = [
            'recordID',
            'playerID',
            'showID',
            '玩家昵称',
            '提现金额(元)',
            '提现后零钱(元)',
            '时间',
            '状态',
        ];
        //将标题写到标准输出中
        fputcsv($fp, $title);
        for ($page = 1; $page <= $totalPage; ++$page) {
            //分页取
            $data = self::$model->getList($startDate, $endDate, $nums);
            //先取出玩家id
            $playerIds = array_unique(array_column($data, 'playerID'));
            //获取玩家信息
            $user = Obj::model('playerBasicData')->getUserInfoByAttr($playerIds);
            if ($data) {
                foreach ($data as $row) {
                    $temp = [
                        //recordID
                        $row['recordID'] . "\t",
                        //playerID
                        $row['playerID'] . "\t",
                        //showID
                        $user[$row['playerID']]['showID'] . "\t",
                        //昵称
                        $user[$row['playerID']]['nickName'] . "\t",
                        //'提现金额(元)'
                        ($row['amount'] / 100),
                        //'提现后零钱(元)'
                        ($row['changeAfter'] / 100),
                        //'时间'
                        $row['createTime'] . "\t",
                        //'状态'
                        Model_My_WithdrawRecord::STATUS_TEXT[$row['status']] . "\t"
                    ];
                    fputcsv($fp, $temp);
                }
                //每1万条数据就刷新缓冲区
                ob_flush();
                flush();
            }
        }Copy

        set_time_limit(0);
        ini_set('memory_limit', '128M');
        $fileName = "零钱提现".date('YmdHis', time());
        header('Content-Type: application/vnd.ms-execl');
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');
        $begin = microtime(true);
        //打开php标准输出流
        //以写入追加的方式打开
        $fp = fopen('php://output', 'a');
        //我们试着用fputcsv从数据库中导出1百万的数据
        //我们每次取1万条数据，分100步来执行
        //如果线上环境无法支持一次性读取1万条数据，可把$nums调小，$step相应增大。
        $total = self::$model->getTotal($startDate, $endDate);
        $nums = 10000;
        $totalPage = ceil($total / $nums);
        //设置标题
        $title = [
            'recordID',
            'playerID',
            'showID',
            '玩家昵称',
            '提现金额(元)',
            '提现后零钱(元)',
            '时间',
            '状态',
        ];
        //将标题写到标准输出中
        fputcsv($fp, $title);
        for ($page = 1; $page <= $totalPage; ++$page) {
            //分页取
            $data = self::$model->getList($startDate, $endDate, $nums);
            //先取出玩家id
            $playerIds = array_unique(array_column($data, 'playerID'));
            //获取玩家信息
            $user = Obj::model('playerBasicData')->getUserInfoByAttr($playerIds);
            if ($data) {
                foreach ($data as $row) {
                    $temp = [
                        //recordID
                        $row['recordID'] . "\t",
                        //playerID
                        $row['playerID'] . "\t",
                        //showID
                        $user[$row['playerID']]['showID'] . "\t",
                        //昵称
                        $user[$row['playerID']]['nickName'] . "\t",
                        //'提现金额(元)'
                        ($row['amount'] / 100),
                        //'提现后零钱(元)'
                        ($row['changeAfter'] / 100),
                        //'时间'
                        $row['createTime'] . "\t",
                        //'状态'
                        Model_My_WithdrawRecord::STATUS_TEXT[$row['status']] . "\t"
                    ];
                    fputcsv($fp, $temp);
                }
                //每1万条数据就刷新缓冲区
                ob_flush();
                flush();
            }
        }Copy