fastadmin 大量数据导出excel

<?php
namespace app\api\controller;

use app\admin\command\Api;
use think\Db;
//use think\facade\Filesystem;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

class Exc extends Api
{
	protected $noNeedLogin = '*';
	protected $noNeedRight = '*';
	
	//$start开始id  $end结束id
	protected $exportDir = 'exports';
	public function exportWithImages($start = 260001, $end = 272144)
	{
		// 验证输入参数
		$start = max(1, intval($start));
		$end = max($start, intval($end));
		$total = $end - $start + 1;
		
		// 计算需要生成的文件数量
		$perFile = 1000;
		$fileCount = ceil($total / $perFile);
		
		// 确保导出目录存在
		if (!is_dir($this->exportDir)) {
			mkdir($this->exportDir, 0755, true);
		}
		
		$successFiles = [];
		$typeMap = [1 => '高值', 2 => '混合', 3 => '低值'];
		
		for ($i = 0; $i < $fileCount; $i++) {
			// 计算当前文件的起始和结束位置
			$fileStart = $start + ($i * $perFile);
			$fileEnd = min($fileStart + $perFile - 1, $end);
			$limit = $fileEnd - $fileStart + 1;
			$offset = $fileStart - 1;
			
			// 查询数据
			$data = Db::name('fporder')->alias('f')
				->join('wxuser w', 'w.id=f.wxuser_id')
				->field('f.*,w.nickname')
				->limit($offset, $limit)
				->select();
			
			// 创建Excel
			$spreadsheet = new Spreadsheet();
			$sheet = $spreadsheet->getActiveSheet();
			
			// 设置表头
			$headers = [
				'ID', '类型', '设备ID', '订单ID', '用户ID',
				'重量', '价格', '创建时间', '更新时间', '昵称', '订单图片'
			];
			$sheet->fromArray([$headers], null, 'A1');
			
			// 设置列宽
			$sheet->getColumnDimension('A')->setWidth(10);
			$sheet->getColumnDimension('B')->setWidth(10);
			$sheet->getColumnDimension('C')->setWidth(15);
			$sheet->getColumnDimension('D')->setWidth(20);
			$sheet->getColumnDimension('E')->setWidth(10);
			$sheet->getColumnDimension('F')->setWidth(10);
			$sheet->getColumnDimension('G')->setWidth(10);
			$sheet->getColumnDimension('H')->setWidth(20);
			$sheet->getColumnDimension('I')->setWidth(20);
			$sheet->getColumnDimension('J')->setWidth(15);
			$sheet->getColumnDimension('K')->setWidth(50);
			
			// 填充数据
			$row = 2;
			foreach ($data as $item) {
				// 处理数据
				$createtime = date('Y-m-d H:i:s', $item['createtime']);
				$updatetime = date('Y-m-d H:i:s', $item['updatetime']);
				$orderImage = 'https://fphs.szzyqy.net' . $item['order_image'];
				$typeId = $typeMap[$item['type_id']] ?? $item['type_id'];
				
				// 写入基本数据
				$sheet->setCellValue('A' . $row, $item['id']);
				$sheet->setCellValue('B' . $row, $typeId);
				$sheet->setCellValue('C' . $row, $item['facility_id']);
				$sheet->setCellValue('D' . $row, '`'.$item['order_id']);
				$sheet->setCellValue('E' . $row, $item['wxuser_id']);
				$sheet->setCellValue('F' . $row, $item['weight']);
				$sheet->setCellValue('G' . $row, $item['price']);
				$sheet->setCellValue('H' . $row, $createtime);
				$sheet->setCellValue('I' . $row, $updatetime);
				$sheet->setCellValue('J' . $row, $item['nickname']);
				$sheet->setCellValue('K' . $row, $orderImage);
				
				// 处理图片
				$row++;
			}
			
			// 生成文件名
			$fileName = sprintf('export_%d_%d_%d.xlsx', $fileStart, $fileEnd, time());
			$filePath = $this->exportDir . '/' . $fileName;
			
			// 保存文件
			$writer = new Xlsx($spreadsheet);
			try {
				$writer->save($filePath);
				$successFiles[] = [
					'file' => '/exports/' . $fileName,
					'range' => "{$fileStart}-{$fileEnd}",
					'total' => count($data)
				];
			} catch (\Exception $e) {
				return json(['code' => 0, 'msg' => '文件保存失败: ' . $e->getMessage()]);
			}
		}
		
		return json([
			'code' => 1,
			'msg' => '导出成功',
			'data' => [
				'total_files' => count($successFiles),
				'files' => $successFiles,
				'total_records' => $total
			]
		]);
	}

}

最终导出到public目录下中 一次大约能导出20000条 后续有其他需求可以在这个基础上继续封装