10W数据导出为xlsx

public function zyouMemberV2Action()
    {
        try {
            echo "开始生成8万条测试数据...\n";
            $largeData = self::generateLargeDataSet();
            echo "数据生成完成，开始导出...\n";
            $outputFile = 'export_80000_rows.xlsx';
            $result = self::exportLargeDataToXlsx($largeData, $outputFile);

            echo "导出完成，文件保存为: " . $result . "\n";

        } catch (\Exception $e) {
            echo "导出过程中出现错误: " . $e->getMessage() . "\n";
        }
    }

    function generateLargeDataSet() {
        $Mongodb = DiUtils::getDi()->get('Mongodb');
        $_id = null;
        $limit = 1000;
        $filter = [];
        $i = 1;
        $dataArr = [];
        do {
            if ($_id) {
                $filter['_id'] = ['$gt' => $_id];
            }
            $list = $Mongodb->getAll($filter, ['limit'=>$limit,'sort' => ['_id' => 1]], 'zyou_callback_openid_member');
            //var_dump($filter);
            if (!empty($list)) {
                foreach ($list as $data) {
                    $_id = $data->_id;
                    $data = Tools::object_to_array($data);
                    $dataArr[] = $data;
                }
            }
            echo $i . PHP_EOL;
            $i++;
        } while ($list);
        return $dataArr;
    }

    // 分批处理数据导出
    function exportLargeDataToXlsx($data, $filename = 'large_data_export.xlsx') {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $fieldToColumn = [
            'open_id'      => 'A',
            'click_time'  => 'B', // 假设你的 $record 中键名为 click_time
            'member_id'    => 'C',
            'username'   => 'D',
            'channel_key'     => 'E',
            'seq_time'    => 'F'
        ];

        // 设置表头（按映射顺序）
        $headers = [
            'open_id' => 'openID',
            'click_time' => '点击广告时间',
            'member_id' => 'memberID',
            'username' => 'W账号',
            'channel_key' => '渠道',
            'seq_time' => '染色时间'
        ];

        foreach ($fieldToColumn as $field => $col) {
            $sheet->setCellValue($col . '1', $headers[$field]);
        }

        // 写入数据
        $row = 2;
        $batchSize = 1000;

        foreach (array_chunk($data, $batchSize) as $batch) {
            foreach ($batch as $record) {
                // 按字段名精准写入对应列
                foreach ($fieldToColumn as $field => $col) {
                    $value = $record[$field] ?? ''; // 安全取值
                    $sheet->setCellValue($col . $row, $value);
                }
                $row++;
            }
            gc_collect_cycles(); // 内存回收
        }

        // 保存文件
        $writer = new Xlsx($spreadsheet);
        $writer->save($filename);

        return $filename;
    }

本作品采用《CC 协议》，转载必须注明作者和本文链接