laravel 表格操作 Maatwebsite/Laravel

Laravel-Excel最新版本

laravel安装

composer require maatwebsite/excel

config/app.php

'providers' => [
    /*
     * Package Service Providers...
     */
    Maatwebsite\Excel\ExcelServiceProvider::class,
]

'aliases' => [
    ...
    'Excel' => Maatwebsite\Excel\Facades\Excel::class,
]

要发布配置，请运行vendor publish命令：

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"

导出

类模式
该文件可在以下位置找到app/Exports：

php artisan make:export UsersExport --model=User

<?php

namespace App\Exports;

use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
  //使用了集合类
    public function collection()
    {
        return User::all();
    }
}

控制器导出

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;
use App\Http\Controllers\Controller;

class UsersController extends Controller 
{
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

集合方法可以自定义

public function collection()
    {
        return new Collection([
            [1, 2, 3],
            [4, 5, 6]
        ]);
    }

数组形式

namespace App\Exports;

use App\Invoice;
use Maatwebsite\Excel\Concerns\FromArray;

class InvoicesExport implements FromArray
{
    public function array(): array
    {
        return [
            [1, 2, 3],
            [4, 5, 6]
        ];
    }
}

控制器传递到导出，可以使用构造函数执行此操作：

namespace App\Exports;

use App\Invoice;
use Maatwebsite\Excel\Concerns\FromArray;

class InvoicesExport implements FromArray
{
    protected $invoices;

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

    public function array(): array
    {
        return $this->invoices;
    }
}

控制器

public function export() 
{
    $export = new InvoicesExport([
        [1, 2, 3],
        [4, 5, 6]
    ]);
    //传递值
    return Excel::download($export, 'invoices.xlsx');
}

依赖注入

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;

class InvoicesExport implements FromCollection
{
    public function __construct(InvoicesRepository $invoices)
    {
        $this->invoices = $invoices;
    }

    public function collection()
    {
        return $this->invoices->all();
    }
}

控制器

public function export(Excel $excel, InvoicesExport $export) 
{
    return $excel->download($export, 'invoices.xlsx');
}

希望您的0值是0,excel表中的实际值而不是null,WithStrictNullComparison

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;

class InvoicesExport implements FromCollection, WithStrictNullComparison
{
    public function __construct(InvoicesRepository $invoices)
    {
        $this->invoices = $invoices;
    }

    public function collection()
    {
        return $this->invoices->all();
    }
}

如果要接收导出文件的原始内容，可以使用以下raw()方法：

$contents = Excel::raw(new InvoicesExport);

模型宏

User:all()->downloadExcel(
    $filePath,//到处文件
    $writerType = null,
    $headings = false//头部
)

(new Collection([[1, 2, 3], [1, 2, 3]]))->downloadExcel(
    $filePath,
    $writerType = null,
    $headings = false
)

在磁盘上存储集合

User:all()->storeExcel(
    $filePath,
    $disk = null,
    $writerType = null,
    $headings = false
)

磁盘上存储导出

Excel::store(new InvoicesExport(2018), 'invoices.xlsx');

//自定义
public function storeExcel() 
{
    // Store on a different disk (e.g. s3)
    Excel::store(new InvoicesExport(2018), 'invoices.xlsx', 's3');
    
    // Store on a different disk with a defined writer type. 
    Excel::store(new InvoicesExport(2018), 'invoices.xlsx', 's3', Excel::XLSX);
}

导出格式

默认按判断名字来

(new InvoicesExport)->download('invoices.xlsx', \Maatwebsite\Excel\Excel::XLSX);
(new InvoicesExport)->download('invoices.html', \Maatwebsite\Excel\Excel::HTML);

查询

namespace App\Exports;

use App\Invoice;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\Exportable;

class InvoicesExport implements FromQuery
{
    use Exportable;

    public function query()
    {
        return Invoice::query();
    }
}

控制器

return (new InvoicesExport)->download('invoices.xlsx');

namespace App\Exports;

use App\Invoice;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\Exportable;

class InvoicesExport implements FromQuery
{
    use Exportable;

    public function forYear(int $year)
    {
        $this->year = $year;
        
        return $this;
    }

    public function query()
    {
        return Invoice::query()->whereYear('created_at', $this->year);
    }
}

控制器

return (new InvoicesExport)->forYear(2018)->download('invoices.xlsx');

Blade视图创建导出

namespace App\Exports;

use App\Invoice;
use Illuminate\Contracts\View\View;
use Maatwebsite\Excel\Concerns\FromView;

class InvoicesExport implements FromView
{
    public function view(): View
    {
        return view('exports.user', [
            'invoices' => Invoice::all()
        ]);
    }
}

<table>
    <thead>
    <tr>
        <th>Name</th>
        <th>Email</th>
    </tr>
    </thead>
    <tbody>
    @foreach($users as $user)
        <tr>
            <td>{{ $user->name }}</td>
            <td>{{ $user->email }}</td>
        </tr>
    @endforeach
    </tbody>
</table>

控制器

public function export() 
{
    return Excel::download(new InvoicesExport, 'invoices.xlsx');
}

添加标题行

use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;

class InvoicesExport implements FromQuery, WithHeadings
{   
    public function headings(): array
    {
        return [
            '#',
            'Date',
        ];
    }
}

多行

 public function headings(): array
    {
        return [
           ['First row', 'First row'],
           ['Second row', 'Second row']
        ];
    }