在 Laravel 6.0 處理 Excel 試算表

Laravel
, , ,

做法

安裝 maatwebsite/excel 套件，此套件封裝了 PHPOffice/PhpSpreadsheet 套件。

1
composer require maatwebsite/excel

建立 BookingsExport 匯出類別。

1
php artisan make:export BookingsExport

修改 BookingsExport 匯出類別。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
namespace App\Exports;

use App\Repositories\BookingRepository;
use Maatwebsite\Excel\Events\AfterSheet;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\BeforeExport;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithHeadings;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use App\Http\Requests\BookingRequest as Request;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\RegistersEventListeners;

class BookingsExport implements
    FromCollection,
    WithHeadings,
    WithMapping,
    WithColumnFormatting,
    ShouldAutoSize,
    WithEvents
{
    use Exportable; // 使得匯出類別可以被依賴注入
    use RegistersEventListeners; // 使得匯出類別可以被註冊事件

    /**
     * @var \App\Http\Requests\BookingRequest
     */
    protected $request;

    /**
     * @var \App\Contracts\BookingRepositoryInterface
     */
    protected $bookingRepo;

    /**
     * @var string
     */
    protected $title = 'Bookings';

    /**
     * @var string
     */
    protected $creator = 'Memo Chou';

    /**
     * @var \Illuminate\Database\Eloquent\Collection
     */
    protected $rows;

    public function __construct(
        Request $request,
        BookingRepository $bookingRepo
    ) {
        $this->request = $request;
        $this->bookingRepo = $bookingRepo;
    }

    /**
     * @return array
     */
    public function registerEvents(): array
    {
        return [
            BeforeExport::class => function (BeforeExport $event) {
                $event->writer
                    ->getProperties()
                    ->setCreator($this->creator) // 設置作者
                    ->setLastModifiedBy($this->creator) // 設置修改作者
                    ->setTitle($this->title); // 設置標題

                $event->writer
                    ->getDefaultStyle()
                    ->getFont()
                    ->setName('Times New Roman') // 設置字型
                    ->setSize(12); // 設置字體大小
            },
            AfterSheet::class => function (AfterSheet $event) {
                $firstColumn = 'A'; // 第一行座標
                $lastColumn = Coordinate::stringFromColumnIndex(count($this->headings())); // 最後行座標
                $firstRow = 1; // 第一列座標
                $lastRow = count($this->rows) + /** title */ 1 + /** header */ 1 + /** empty rows */ 2; // 最後列座標

                // 置中樣式
                $alignCenter = [
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER
                    ]
                ];

                // 在第一列之前插入一列
                $event->sheet->insertNewRowBefore($firstRow, 1);

                // 合併儲存格
                $event->sheet->mergeCells(sprintf('%s%d:%s%d', $firstColumn, $firstRow, $lastColumn, $firstRow));

                // 設置儲存格內容
                $event->sheet->setCellValue(sprintf('%s%d', $firstColumn, $firstRow), $this->title);

                // 設置儲存格樣式
                $event->sheet->getStyle(sprintf('%s%d', $firstColumn, $firstRow))->applyFromArray($alignCenter);
            },
        ];
    }

    /**
    * @return array
    */
    public function headings(): array
    {
        // 設置表頭
        return [
            'No.',
            'PNR',
            'Last Name',
            'First Name',
            'Departure City',
            'Arrival City',
            'Currency',
            'Amount',
            'Payment Type',
            'Card Type',
            'Application Type',
            'Transaction Date',
        ];
    }

    /**
    * @return array
    */
    public function map($row): array
    {
        // 修改資料集合
        return [
            [
                $row['number'],
                $row['pnr'],
                $row['last_name'],
                $row['first_name'],
                $row['departure_city'],
                $row['arrival_city'],
                $row['currency'],
                $row['amount'],
                $row['payment_type'],
                $row['card_vendor'],
                $row['application_type'],
                $row['transaction_date'],
            ],
        ];
    }

    /**
    * @return array
    */
    public function columnFormats(): array
    {
        // 設置儲存格格式
        return [
            'H' => NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1,
        ];
    }

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        // 設置資料集合
        $this->rows = $this->bookingRepo->getAllByRequest($this->request->all())
            ->map(function ($booking, $index) {
                // 設置流水號
                return collect((array) $booking)->prepend($index + 1, 'number');
            });

        return $this->rows;
    }
}

依賴注入至指定的控制器。

1
2
3
4
public function export(BookingsExport $bookingsExport)
{
    return $bookingsExport->download('bookings.xlsx');
}

參考資料