Fastadmin使用插件xlswriter导出数据

一、列表页增加导出按钮

index.html：

<a href="javascript:;" class="btn btn-danger {$auth->check('client/export') ? '' : 'hide'}" id="excel_dc">{:__('导出')}</a>

对应JS文件的index方法中：

$('#excel_dc').click(function () {
    var url = 'client/export';
    window.open(url);
});

// 为表格绑定事件
Table.api.bindevent(table);

二、在 export() 查询并导出

use think\Db;
use Vtiful\Kernel\Excel;

public function export()
{
    // 查询数据
    $list = Db::table('fa_client')
        ->alias('c')
        ->join('admin a', 'a.id = c.admin_id')
        ->where('c.promotion_platform', '1')
        ->field('c.id, c.admin_id, c.account_id, c.account, c.accountname, c.authorization_id, c.adv_id, c.adv_name, c.mobile, c.promote_products, c.onlinetime, c.createtime, c.updatetime, c.promotion_platform, a.username as admin_username, a.nickname as admin_nickname')
        ->select();

    // Excel 表头
    $title = ['ID', '平台', '管理员ID', '管理员账号', '管理员昵称', '账户ID', '推广账号', '客户简称', '授权ID', '广告主ID', '广告主名称', '手机号', '推广产品', '上线时间', '创建时间', '更新时间'];

    // Excel 数据
    $data = [];

    foreach ($list as $row) {

        $data[] = [
            $row['id'],
            $row['promotion_platform'] == 1 ? 'JL' : '',
            $row['admin_id'],
            $row['admin_username'],
            $row['admin_nickname'],
            $row['account_id'],
            $row['account'],
            $row['accountname'],
            $row['authorization_id'],
            $row['adv_id'],
            $row['adv_name'],
            $row['mobile'],
            $row['promote_products'],
            $row['onlinetime'],
            date('Y-m-d H:i:s', $row['createtime']),
            date('Y-m-d H:i:s', $row['updatetime'])
        ];
    }

    // 文件名
    $filename = '客户数据';
    $filenames = $filename . '_' . date('Ymd_His') . '.xlsx';

    // 保存目录
    $paths = ROOT_PATH . 'public/export/';

    // 目录不存在则创建
    if (!is_dir($paths)) {
        mkdir($paths, 0755, true);
    }

    // XlsWriter 配置
    $config = [
        'path' => $paths
    ];

    // 创建 Excel
    $excel = new Excel($config);

    $excel->fileName($filename, '客户数据')
        ->header($title)
        ->data($data)
        ->output();

    // 下载地址
    $downloadUrl = '/export/' . $filename;

    echo '<a href="' . $downloadUrl . '" target="_blank">';
    echo '<h3>下载 ' . $filename . '</h3>';
    echo '</a>';

    exit;
}

三、如果数据比较多，应该使用游标/分批查询 + XlsWriter 分批写入

public function export()
{
    set_time_limit(0);

    // 导出目录
    $path = ROOT_PATH . 'public/export/';

    if (!is_dir($path)) {
        mkdir($path, 0755, true);
    }

    // 文件名
    $filename = '客户数据_' . date('Ymd_His') . '.xlsx';

    // XlsWriter
    $excel = new \Vtiful\Kernel\Excel([
        'path' => $path
    ]);

    // 表头
    $title = [
        'ID',
        '管理员ID',
        '账户ID',
        '账户',
        '账户名称',
        '授权ID',
        '广告ID',
        '广告名称',
        '手机号',
        '推广产品',
        '上线时间',
        '创建时间',
        '更新时间',
        '推广平台',
        '管理员账号',
        '管理员昵称',
    ];

    $excel->fileName($filename, '客户数据')
        ->header($title);

    // 每次查询 5000 条
    $pageSize = 5000;
    $lastId = 0;

    while (true) {

        $list = Db::table('rhtg_client')
            ->alias('c')
            ->join('admin a', 'a.id = c.admin_id', 'LEFT')
            ->where('c.promotion_platform', 1)
            ->where('c.id', '>', $lastId)
            ->field([
                'c.id',
                'c.admin_id',
                'c.account_id',
                'c.account',
                'c.accountname',
                'c.authorization_id',
                'c.adv_id',
                'c.adv_name',
                'c.mobile',
                'c.promote_products',
                'c.onlinetime',
                'c.createtime',
                'c.updatetime',
                'c.promotion_platform',
                'a.username' => 'admin_username',
                'a.nickname' => 'admin_nickname',
            ])
            ->order('c.id', 'ASC')
            ->limit($pageSize)
            ->select();

        if (empty($list)) {
            break;
        }

        $data = [];

        foreach ($list as $row) {

            $data[] = [
                $row['id'],
                $row['admin_id'],
                $row['account_id'],
                $row['account'],
                $row['accountname'],
                $row['authorization_id'],
                $row['adv_id'],
                $row['adv_name'],
                $row['mobile'],
                $row['promote_products'],
                $row['onlinetime'],
                date('Y-m-d H:i:s', $row['createtime']),
                date('Y-m-d H:i:s', $row['updatetime']),
                'JL',
                $row['admin_username'],
                $row['admin_nickname'],
            ];

            $lastId = $row['id'];
        }

        // 写入当前批次
        $excel->data($data);

        // 释放当前批次
        unset($list, $data);
    }

    // 输出文件
    $excel->output();

    // 下载地址
    $downloadUrl = '/uploads/client_export/' . $filename;

    echo '<a href="' . $downloadUrl . '" target="_blank">';
    echo '<h3>下载 ' . $filename . '</h3>';
    echo '</a>';

    exit;
}