laravel 文件下载文件导出为csv格式

只问不答的个人博客 / 82 / 1 / 创建于 1天前 / 更新于 21小时前

laravel 文件下载导出

依赖 laravel-admin

自定义按钮文件
文件名： App\Admin\Extensions\Tools\ButtonLink.php

自定义导出文件
文件名： App\Admin\Extensions\Exporters\CsvExporter.php

在 admin 的route文件中加路由
文件名： App\Admin\routes.php

在 admin 的控制器文件中使用
文件名： App\Admin\Controllers\JiaZuV2GuanXiExportController.php

4个文件的大致文件内容

自定义按钮文件：App\Admin\Extensions\Tools\ButtonLink.php

<?php

namespace App\Admin\Extensions\Tools;

use Encore\Admin\Admin;
use Encore\Admin\Grid\Tools\AbstractTool;

class ButtonLink extends AbstractTool
{

    public $buttonElementClass = '';
    public $btnUrl = '';
    public $addGridRoute = true;
    public $btnName = '';

    public function getElementClass() {
        return $this->buttonElementClass;
    }

    public function setElementClass($className) {
        $this->buttonElementClass = $className;
        return $this;
    }

    public function getBtnUrl() {
        return $this->btnUrl;
    }

    public function setBtnUrl($url) {
        $this->btnUrl = $url;
        return $this;
    }

    public function getAddGridRoute() {
        return $this->addGridRoute;
    }

    public function setAddGridRoute($addGridRoute) {
        $this->addGridRoute = $addGridRoute;
        return $this;
    }

    public function getBtnName() {
        return $this->btnName;
    }

    public function setBtnName($name) {
        $this->btnName = $name;
        return $this;
    }

    protected function getBtnUrlReal() {
        $url = $this->getBtnUrl();
        if ($this->getAddGridRoute()) {
            $url = $this->grid->resource().$this->getBtnUrl();
        }
        return $url;
    }

    protected function script() {
        return <<<EOT

$('.{$this->getElementClass()}').on('click', function() {
    var url = '{$this->getBtnUrlReal()}';
    var name = '{$this->getBtnName()}';
    window.open(url, name);
});

EOT;
    }

    public function render() {
        Admin::script($this->script());
        return <<<HTML
        <a class="btn btn-sm btn-default {$this->getElementClass()}">{$this->getBtnName()}</a>
HTML;
    }
}

自定义导出文件：App\Admin\Extensions\Exporters\CsvExporter.php

<?php

namespace App\Admin\Extensions\Exporters;

use Encore\Admin\Grid\Column;
use Encore\Admin\Grid\Exporters\AbstractExporter;
use Illuminate\Database\Eloquent\Model;

class CsvExporter extends AbstractExporter
{
    /**
     * @var string
     */
    protected $filename;

    /**
     * @var []\Closure
     */
    protected $columnCallbacks;

    /**
     * @var []\Closure
     */
    protected $titleCallbacks;

    /**
     * @var array
     */
    protected $visibleColumns;

    /**
     * 时间
     */
    protected $time;

    /**
     * model
     */
    protected $model;

    /**
     * 分块处理数据 行数
     */
    protected $chunkNum;

    /**
     * Create a new exporter instance.
     *
     * 我不要grid
     */
    public function __construct()
    {
        set_time_limit(0);
        // 调整 内存大小
        ini_set('memory_limit', '1G');
        $this->setTime();
        $this->setChunkNum(100);
    }

    /**
     * 执行时间
     */
    public function setTime($now = false) {
        if ($now) {
            $this->time = time();
        }
        if (empty($this->time)) {
            $this->time = time();
        }
    }

    /**
     * 获取时间
     */
    public function getTimeFormat() {
        $this->setTime();
        return date('Y-m-d H:i:s', $this->time);
    }

    /**
     * 分块处理数据 行数
     */
    public function setChunkNum($num = 100): self
    {
        $num = intval($num);
        if ($num < 1) $num = 1;
        if ($num > 10000) $num = 10000;
        $this->chunkNum = $num;

        return $this;
    }

    /**
     * 分块处理数据 行数
     */
    public function getChunkNum() {
        $num = intval($this->chunkNum);
        if ($num < 1) $num = 1;
        if ($num > 10000) $num = 10000;
        return $num;
    }

    /**
     * @param string $filename
     *
     * @return $this
     */
    public function filename(string $filename = ''): self
    {
        $this->filename = $filename;

        return $this;
    }

    /**
     * @param string   $name
     * @param \Closure $callback
     *
     * @return $this
     */
    public function column(string $name, \Closure $callback): self
    {
        $this->columnCallbacks[$name] = $callback;

        return $this;
    }

    /**
     * @param string   $name
     * @param \Closure $callback
     *
     * @return $this
     */
    public function title(string $name, \Closure $callback): self
    {
        $this->titleCallbacks[$name] = $callback;

        return $this;
    }

    /**
     * Get download response headers.
     *
     * @return array
     */
    protected function getHeaders()
    {
        if (!$this->filename) {
            $this->filename = $this->getTimeFormat();
        }

        return [
            'Content-Encoding'    => 'UTF-8',
            'Content-Type'        => 'text/csv;charset=UTF-8',
            'Content-Disposition' => "attachment;filename=\"{$this->filename}.csv\"",
        ];
    }

    /**
    * 设置数据源（支持模型类或查询构建器）
    * 
    * @param  Model|Builder  $source
    * @return self
    */
    public function setModel($source): self
    {
        if ($source instanceof Model) {
            $this->model = $source->query();
        } else {
            $this->model = $source;
        }

        return $this;
    }

    /**
    * 设置要导出的字段（支持关联模型字段）
    * 
    * @param  array  $columns 如 ['id', 'profile.age']
    * @return self
    */
    public function setColumns(array $columns): self
    {
        $this->visibleColumns = $columns;
        return $this;
    }

    /**
    * 设置自定义表头
    * 
    * @param  array  $titles
    * @return self
    */
    public function setTitles(array $titles): self
    {
        $this->titleCallbacks = $titles;
        return $this;
    }

    /**
    * 核心导出逻辑重构
    */
    public function export_ai()
    {
        return new StreamedResponse(function () {
            $handle = fopen('php://output', 'w');
            fwrite($handle, chr(0xEF).chr(0xBB).chr(0xBF));

            // 生成表头
            fputcsv($handle, $this->buildHeaders());

            // 分块处理数据
            $this->model->chunk(1000, function ($records) use ($handle) {
                foreach ($records as $record) {
                    fputcsv($handle, $this->buildRow($record));
                }
            });

            fclose($handle);
        }, 200, $this->getHeaders());
    }

    /**
    * 构建CSV表头
    */
    protected function buildHeaders(): array
    {
        // 优先使用自定义标题
        if (!empty($this->titleCallbacks)) {
            return $this->titleCallbacks;
        }

        // 自动生成字段名称
        return array_map(function($column) {
            return ucwords(str_replace(['_', '.'], ' ', $column));
        }, $this->visibleColumns);
    }

    /**
    * 构建单行数据
    */
    protected function buildRow($record): array
    {
        $row = [];

        foreach ($this->visibleColumns as $column) {
            $value = $this->getColumnValue($record, $column);

            // 处理回调函数
            if (isset($this->columnCallbacks[$column])) {
                $value = call_user_func($this->columnCallbacks[$column], $value, $record);
            }

            $row[] = $value;
        }

        return $row;
    }

    /**
    * 获取字段值（支持关联模型）
    */
    protected function getColumnValue($record, string $column)
    {
        // 处理关联字段（如profile.age）
        if (str_contains($column, '.')) {
            return data_get($record, $column);
        }

        // 原始字段值
        if (gettype($record) === 'object') {
            return $record->$column;
        }
        return $record->getAttribute($column);
    }

    /**
    * 核心导出逻辑重构
    */
    public function export_debug()
    {
        $back = [];
        $back[] = $this->buildHeaders();
        // 分块处理数据 必须指定 orderBy
        $this->model->chunk(100, function ($records) use (&$back) {
            foreach ($records as $record) {
                $back[] = $this->buildRow($record);
            }
        });
        dd($back);
    }

    /**
     * {@inheritdoc}
     */
    public function export()
    {
        $chunkNum = $this->getChunkNum();
        $response = function () use ($chunkNum) {
            $handle = fopen('php://output', 'w');
            $titles = [];
            fwrite($handle, chr(0xEF).chr(0xBB).chr(0xBF)); //导出的CSV文件是无BOM编码UTF-8，而我们通常使用UTF-8编码格式都是有BOM的。所以添加BOM于CSV中

            // 生成表头
            fputcsv($handle, $this->buildHeaders());

            // 分块处理数据
            $this->model->chunk($chunkNum, function ($records) use ($handle) {
                foreach ($records as $record) {
                    fputcsv($handle, $this->buildRow($record));
                }
            });

            fclose($handle);
        };

        response()->stream($response, 200, $this->getHeaders())->send();

        exit;
    }
}

在 admin 的route文件中加路由文件：App\Admin\routes.php

<?php

use Illuminate\Routing\Router;

Admin::routes();

Route::group([
    'prefix'        => config('admin.route.prefix'),
    'namespace'     => config('admin.route.namespace'),
    'middleware'    => config('admin.route.middleware'),
    'as'            => config('admin.route.prefix') . '.',
], function (Router $router) {
    $router->get('/', 'HomeController@index')->name('home');

    $router->resource('jia-zu-v2-guan-xi-exports', JiaZuV2GuanXiExportController::class);
    $router->any('jia-zu-v2-guan-xi-exports-action/export1', 'JiaZuV2GuanXiExportController@export1');

});

在 admin 的控制器文件中使用文件：App\Admin\Controllers\JiaZuV2GuanXiExportController.php

<?php

namespace App\Admin\Controllers;

use Encore\Admin\Controllers\AdminController;
use Encore\Admin\Form;
use Encore\Admin\Grid;
use Encore\Admin\Show;

class JiaZuV2GuanXiExportController extends AdminController
{
    /**
     * Title for current resource.
     *
     * @var string
     */
    protected $title = '导出';

    /**
     * Make a grid builder.
     *
     * @return Grid
     */
    protected function grid()
    {
        $grid = new Grid(new JiaZuV2GuanXi());

        $grid->column('id', __('Id'))->sortable();
        $grid->column('created_at', __('Created at'))->display(function(){
            return $this->created_at;
        });
        $grid->column('updated_at', __('Updated at'))->display(function(){
            return $this->updated_at;
        });

        // 将 导出 操作加入到表格的工具条中
        $grid->tools(function ($tools) {
            $btn1 = new \App\Admin\Extensions\Tools\ButtonLink();
            $btn1->setBtnName('导出(需要的数据格式1)')->setElementClass('jia-zu-guan-xi-export-export1')->setBtnUrl('-action/export1');
            $tools->append($btn1);
        });

        $grid->disableCreateButton();
        $grid->disableRowSelector();
        // $grid->disableExport();
        $grid->export(function($export){
            set_time_limit(0);
            $export->except(['touxiang']);
        });

        return $grid;
    }

    /**
     * Make a show builder.
     *
     * @param mixed $id
     * @return Show
     */
    protected function detail($id)
    {

    }

    /**
     * Make a form builder.
     *
     * @return Form
     */
    protected function form()
    {

    }

    /**
     * 导出-需要的数据格式
     */
    public function export1(\Request $request) {
        // 类型
        $type = JiaZuV2List::gYuYueType();
        if (empty($type)) return abort(200, '无类型');
        $tableNameJiaZuGuanXiExport = JiaZuV2GuanXi::getModel()->getTable();
        $tableNameWxXcxYuYueAccountRecords = WxXcxYuyueAccountRecords::getModel()->getTable();
        // 用 union
        $left = \DB::table($tableNameJiaZuGuanXiExport . ' as t1j')
            ->leftJoin($tableNameWxXcxYuYueAccountRecords. ' as t1y', function($join) use($type) {
                $join->on('t1j.m_openid', '=', 't1y.account_openid')
                    ->where('t1y.yuyue_type', $type);
            })
            ->select('t1j.jiazu_id', 't1j.m_openid', 't1j.title', 't1j.zu_zhang', 't1y.yuyue_zhuangtai', 't1j.uid');

        $right = \DB::table($tableNameWxXcxYuYueAccountRecords.' as t2y')
            ->leftJoin($tableNameJiaZuGuanXiExport.' as t2j', 't2j.m_openid', '=', 't2y.account_openid')
            ->where('t2y.yuyue_type', $type)
            ->select('t2j.jiazu_id', 't2y.account_openid as m_openid', 't2j.title', 't2j.zu_zhang', 't2y.yuyue_zhuangtai', 't2y.account_uid as uid');

        // chunk 分块处理数据 必须指定 orderBy
        $query = $left->union($right)->orderBy('m_openid', 'asc');
        $exporter = new \App\Admin\Extensions\Exporters\CsvExporter();
        $exporter->setModel($query)
            ->setColumns(['jiazu_id', 'oid', 'title'])
            ->setTitles(['id', 'oid', 'title'])
            ->filename('导出（需要的数据格式1）')
            ->column('jiazu_id', function($value) {
                $value = trim($value);
                if ($value === '') return '';
                return $value;
            })
            // chunk 分块处理数据 每次查询并返回1000条数据
            ->setChunkNum(1000)
            ->export();
        die();
    }
}

CsvExporter.php可以去掉laravel-admin的依赖

自定义导出文件：App\Admin\Extensions\Exporters\CsvExporter.php 做如下修改

laravel 文件下载文件导出为csv格式

最终文件内容为：

<?php

namespace App\Admin\Extensions\Exporters;

use Illuminate\Database\Eloquent\Model;

class CsvExporter
{
    /**
     * @var string
     */
    protected $filename;

    /**
     * @var []\Closure
     */
    protected $columnCallbacks;

    /**
     * @var []\Closure
     */
    protected $titleCallbacks;

    /**
     * @var array
     */
    protected $visibleColumns;

    /**
     * 时间
     */
    protected $time;

    /**
     * model
     */
    protected $model;

    /**
     * 分块处理数据 行数
     */
    protected $chunkNum;

    /**
     * Create a new exporter instance.
     *
     * 我不要grid
     */
    public function __construct()
    {
        set_time_limit(0);
        // 调整 内存大小
        ini_set('memory_limit', '1G');
        $this->setTime();
        $this->setChunkNum(100);
    }

    /**
     * 执行时间
     */
    public function setTime($now = false) {
        if ($now) {
            $this->time = time();
        }
        if (empty($this->time)) {
            $this->time = time();
        }
    }

    /**
     * 获取时间
     */
    public function getTimeFormat() {
        $this->setTime();
        return date('Y-m-d H:i:s', $this->time);
    }

    /**
     * 分块处理数据 行数
     */
    public function setChunkNum($num = 100): self
    {
        $num = intval($num);
        if ($num < 1) $num = 1;
        if ($num > 10000) $num = 10000;
        $this->chunkNum = $num;

        return $this;
    }

    /**
     * 分块处理数据 行数
     */
    public function getChunkNum() {
        $num = intval($this->chunkNum);
        if ($num < 1) $num = 1;
        if ($num > 10000) $num = 10000;
        return $num;
    }

    /**
     * @param string $filename
     *
     * @return $this
     */
    public function filename(string $filename = ''): self
    {
        $this->filename = $filename;

        return $this;
    }

    /**
     * @param string   $name
     * @param \Closure $callback
     *
     * @return $this
     */
    public function column(string $name, \Closure $callback): self
    {
        $this->columnCallbacks[$name] = $callback;

        return $this;
    }

    /**
     * @param string   $name
     * @param \Closure $callback
     *
     * @return $this
     */
    public function title(string $name, \Closure $callback): self
    {
        $this->titleCallbacks[$name] = $callback;

        return $this;
    }

    /**
     * Get download response headers.
     *
     * @return array
     */
    protected function getHeaders()
    {
        if (!$this->filename) {
            $this->filename = $this->getTimeFormat();
        }

        return [
            'Content-Encoding'    => 'UTF-8',
            'Content-Type'        => 'text/csv;charset=UTF-8',
            'Content-Disposition' => "attachment;filename=\"{$this->filename}.csv\"",
        ];
    }

    /**
    * 设置数据源（支持模型类或查询构建器）
    * 
    * @param  Model|Builder  $source
    * @return self
    */
    public function setModel($source): self
    {
        if ($source instanceof Model) {
            $this->model = $source->query();
        } else {
            $this->model = $source;
        }

        return $this;
    }

    /**
    * 设置要导出的字段（支持关联模型字段）
    * 
    * @param  array  $columns 如 ['id', 'profile.age']
    * @return self
    */
    public function setColumns(array $columns): self
    {
        $this->visibleColumns = $columns;
        return $this;
    }

    /**
    * 设置自定义表头
    * 
    * @param  array  $titles
    * @return self
    */
    public function setTitles(array $titles): self
    {
        $this->titleCallbacks = $titles;
        return $this;
    }

    /**
    * 核心导出逻辑重构
    */
    public function export_ai()
    {
        return new StreamedResponse(function () {
            $handle = fopen('php://output', 'w');
            fwrite($handle, chr(0xEF).chr(0xBB).chr(0xBF));

            // 生成表头
            fputcsv($handle, $this->buildHeaders());

            // 分块处理数据
            $this->model->chunk(1000, function ($records) use ($handle) {
                foreach ($records as $record) {
                    fputcsv($handle, $this->buildRow($record));
                }
            });

            fclose($handle);
        }, 200, $this->getHeaders());
    }

    /**
    * 构建CSV表头
    */
    protected function buildHeaders(): array
    {
        // 优先使用自定义标题
        if (!empty($this->titleCallbacks)) {
            return $this->titleCallbacks;
        }

        // 自动生成字段名称
        return array_map(function($column) {
            return ucwords(str_replace(['_', '.'], ' ', $column));
        }, $this->visibleColumns);
    }

    /**
    * 构建单行数据
    */
    protected function buildRow($record): array
    {
        $row = [];

        foreach ($this->visibleColumns as $column) {
            $value = $this->getColumnValue($record, $column);

            // 处理回调函数
            if (isset($this->columnCallbacks[$column])) {
                $value = call_user_func($this->columnCallbacks[$column], $value, $record);
            }

            $row[] = $value;
        }

        return $row;
    }

    /**
    * 获取字段值（支持关联模型）
    */
    protected function getColumnValue($record, string $column)
    {
        // 处理关联字段（如profile.age）
        if (str_contains($column, '.')) {
            return data_get($record, $column);
        }

        // 原始字段值
        if (gettype($record) === 'object') {
            return $record->$column;
        }
        return $record->getAttribute($column);
    }

    /**
    * 核心导出逻辑重构
    */
    public function export_debug()
    {
        $back = [];
        $back[] = $this->buildHeaders();
        // 分块处理数据 必须指定 orderBy
        $this->model->chunk(100, function ($records) use (&$back) {
            foreach ($records as $record) {
                $back[] = $this->buildRow($record);
            }
        });
        dd($back);
    }

    /**
     * {@inheritdoc}
     */
    public function export()
    {
        $chunkNum = $this->getChunkNum();
        $response = function () use ($chunkNum) {
            $handle = fopen('php://output', 'w');
            $titles = [];
            fwrite($handle, chr(0xEF).chr(0xBB).chr(0xBF)); //导出的CSV文件是无BOM编码UTF-8，而我们通常使用UTF-8编码格式都是有BOM的。所以添加BOM于CSV中

            // 生成表头
            fputcsv($handle, $this->buildHeaders());

            // 分块处理数据
            $this->model->chunk($chunkNum, function ($records) use ($handle) {
                foreach ($records as $record) {
                    fputcsv($handle, $this->buildRow($record));
                }
            });

            fclose($handle);
        };

        response()->stream($response, 200, $this->getHeaders())->send();

        exit;
    }
}