DcatAdmin 自定义导出多工作簿Excel表格

JefChen 的个人博客 / 3 / 0 / 创建于 2年前 / 更新于 2年前

DcatAdmin 自定义导出多工作簿Excel表格

安装依赖包

composer require maatwebsite/excel

以下是示例代码（导出用户信息数据）

<?php

namespace App\Admin\Controllers;

use App\Admin\Actions\Grid\Export\ExportConditionAction;
use App\Admin\Exports\User\UserExport;
use App\Admin\Repositories\User;
use Dcat\Admin\Form;
use Dcat\Admin\Grid;
use Dcat\Admin\Show;
use Dcat\Admin\Http\Controllers\AdminController;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;

class UserController extends AdminController
{
    protected function grid(): Grid
    {
        return Grid::make(new User(), function (Grid $grid) {
            $grid->column('id')->sortable();
            $grid->column('name');
            $grid->column('number');
            $grid->column('balance');
            $grid->column('birthday');

            $grid->tools(function (Grid\Tools $tools) {
                $tools->append(new ExportConditionAction('user','导出用户信息'));
            });
        });
    }

    protected function detail(mixed $id): Show
    {
        return Show::make($id, new User(), function (Show $show) {
            $show->field('id');
        });
    }

    protected function form(): Form
    {
        return Form::make(new User(), function (Form $form) {
            $form->display('id');
            $form->text('name');
        });
    }

    public function export_user(Request $request)
    {
        $input = $request->all();
        return Excel::download(new UserExport($input), date('YmdHis').'.xlsx')->deleteFileAfterSend();
    }
}

导出动作转发公共类（主要是懒，不想写很多Action）

<?php

namespace App\Admin\Actions\Grid\Export;

use App\Admin\Forms\Export\UserExportForm;
use Dcat\Admin\Grid\RowAction;
use Dcat\Admin\Widgets\Modal;

class ExportConditionAction extends RowAction
{
    protected string $scene = '';
    protected array $params = [];

    function __construct(string $scene, $title = '导出', array $params = [])
    {
        $this->scene = $scene;
        $this->params = $params;
        parent::__construct($title);
    }

    public function render()
    {
        // 实例化表单类并传递自定义参数
        switch ($this->scene){
            case 'user':
                //导出用户数据信息
                $form = UserExportForm::make()->payload($this->params);
                break;
            default:
                $this->response()->error('场景值错误');
        }

        return Modal::make()->lg()->title($this->title)->body($form)
            ->button('<button class="btn btn-primary btn-outline"><i class="feather icon-paperclip"></i><span class="d-none d-sm-inline">&nbsp;&nbsp;' . $this->title . '</span></button>');
    }
}

可以根据业务需求自定义导出要求

<?php

namespace App\Admin\Forms\Export;

use Dcat\Admin\Contracts\LazyRenderable;
use Dcat\Admin\Traits\LazyWidget;
use Dcat\Admin\Widgets\Form;

class UserExportForm extends Form implements LazyRenderable
{
    use LazyWidget;
    // 使用异步加载功能

    // 处理请求
    public function handle(array $input)
    {
        $param = build_url_query($input);
        switch ($input['type']){
            case "1":
            case "2":
                break;
            default:
                return $this->response()->error('暂未开通该功能!');
        }
        return $this->response()->download('user/export_user?'.$param)->message('导出成功');
    }

    public function form()
    {
        $this->select('type','导出方式')
            ->options(['1'=>'常规','2'=>'首列增加序号','3'=>'自定义选择字段'])
            ->default('1')
            ->required();
        $this->select('books','工作簿')
            ->options(['1'=>'单工作簿','2'=>'多工作簿'])
            ->default('1')
            ->required();
    }
}

// 写到这里了就转头去看一下UserController的export_user()方法再继续吧。

用户数据导出

<?php

namespace App\Admin\Exports\User;

use App\Admin\Exports\User\Sheets\UserSheet;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class UserExport implements FromArray, WithMultipleSheets
{
    use Exportable;

    protected array $input;

    public function __construct($input)
    {
        $this->input = $input;
    }

    public function array(): array
    {
        return [];
    }

    /**
     * 起始单元格
     * @return string
     */
    public function startCell(): string
    {
        return 'A1';
    }

    /**
     * 工作表
     * @return array
     */
    public function sheets(): array
    {
        $sheets = [];
        if($this->input['books'] == 2){ 
            //多工作簿
            $sheet_name = ['Test Book 1','Test Book 2'];
            foreach ($sheet_name as $item){
                $sheets[] = new UserSheet($item,$this->input);
            }
        }else{
            $sheets[] = new UserSheet('One Peace',$this->input);
        }
        return $sheets;
    }
}

<?php

namespace App\Admin\Exports\User\Sheets;

use App\Models\User;
use Illuminate\Support\Arr;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\FromCollection;
//use Maatwebsite\Excel\Concerns\WithColumnWidths;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

class UserSheet implements WithTitle, WithHeadings, FromCollection, WithEvents //, WithColumnWidths
{

    private string $title;              //工作簿名称
    private array  $headings = [];      //首行标题
    private array  $column_widths = []; //设置列宽
    private bool   $show_no;            //是否显示序号

    public function __construct($sheet_name,$param)
    {
        $this->title = $sheet_name;
        //这里是前面弹窗选择的参数,1-无序号 2-有序号
        if($param['type'] == 1){
            $this->show_no = false;
            $this->headings = ['姓名','证件号','生日','余额'];
            $this->column_widths = [15, 30, 15, 10];
        }else{
            $this->show_no = true;
            $this->headings = ['序号','姓名','证件号','生日','余额'];
            $this->column_widths = [10, 15, 30, 15, 10];
        }
    }

    /**
     * 工作簿名称
     * @return string
     */
    public function title(): string
    {
        return $this->title;
    }

    /**
     * 标题行
     * @return string[]
     */
    public function headings(): array
    {
        return $this->headings;
    }

    /**
     *  数组转集合
     * @return Collection
     */
    public function collection(): Collection
    {
        return new Collection();
    }

    /**
     * 注册事件 修改单元格的值/样式
     * @return \Closure[]
     */
    public function registerEvents(): array
    {
        return [
            AfterSheet::class  => function(AfterSheet $event) {
                $list = $this->getData();
                if($this->show_no){
                    foreach ($list as $k => &$v){
                        $v = Arr::prepend(array_values($v), $k+1);
                    }
                }else{
                    foreach ($list as &$v){
                        $v = array_values($v);
                    }
                }
                $_dat = array_merge([$this->headings],$list);

                //设置每个单元格
                $this->setEveryCell($_dat,$event);

                //设置列宽
                $col_number = count($this->headings);
                for ($col = 1; $col <= $col_number; $col++){
                    $event->getDelegate()->getColumnDimensionByColumn($col)->setWidth($this->column_widths[$col-1]);
                }

                //设置区域单元格水平居左
                $event->sheet->getDelegate()->getStyle($event->sheet->calculateWorksheetDimension())->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);

            }
        ];
    }

    /**
     * 获取数据
     * @return array
     */
    public function getData(): array
    {
        $list = User::query()->select(['name','number','birthday','balance'])->get();
        return $list ? $list->toArray() : [];
    }

    /**
     * 设置每个单元格值和样式
     * 主要是为了解决类似身份证这种长数字在excel中被转成科学计数法的问题
     * @param array $_dat 列表数据
     * @param $event
     */
    public function setEveryCell(array $_dat, $event)
    {
        //列数
        $column_number = $_dat ? count($_dat[0]) : 0;

        //行数
        $row_number = $_dat ? count($_dat) : 0;

        for($h = 1; $h <= $row_number; $h++) {
            for($l = 1; $l <= $column_number; $l++){
                //值
                $value = $_dat[$h-1][$l-1];
                //若值的数据类型是数字型,并且长度小于15则为数字型,否则为字符串型
                $dataType = (is_integer($value) && strlen($value) < 15) ? DataType::TYPE_NUMERIC : DataType::TYPE_STRING2;
                //设置单元格对应的值和数据类型
                $event->sheet->setCellValueExplicitByColumnAndRow(
                    $l, //第几列
                    $h, //第几行
                    $value,
                    $dataType
                );
            }
        }
    }

//    /**
//     * 设置列宽
//     * @return array
//     */
//    public function columnWidths(): array
//    {
//        return [
//            'A' => 10,
//            'B' => 20,
//            'C' => 30,
//        ];
//    }
}

是不是突然发现没有 build_url_query() 这个方法（拿去拿去）

/**
 * 把数组所有元素按照“参数=参数值”的模式用“&”字符拼接成字符串
 *
 * @param array  $params 关联数组
 * @param string $handleFunc 值处理函数
 * @return string
 */
if (!function_exists('build_url_query')) {
    function build_url_query($params, $handleFunc = null): string
    {
        if (!is_callable($handleFunc)) $handleFunc = function ($key, $val) {
            $type = gettype($val);
            if ($type == 'object' || $type == 'array') return '';

            $val = urlencode($val);
            return $key . '=' . $val;
        };

        $result = '';
        $i = 0;
        foreach ($params as $key => $val) {
            $str = $handleFunc($key, $val);
            if ($str === '') continue;
            $result .= ($i === 0 ? '' : '&') . $str;
            $i++;
        }
        return $result;
    }
}