Laravel第三方库 之 Laravel-Excel

本文的示例代码参考laravel-excel

目录

• Startup

  • Composer

  • Route

  • Controller

• Cell-Size

• Merging-Cells

• Alignment

• Cell-Border

Startup

Composer

composer create-project laravel/laravel laravel-excel --prefer-dist "5.5.*" && cd laravel-excel

composer require "maatwebsite/excel:~2.1.0"

Route

vim routes/web.php

<?php

Route::get('/', function () {
    return view('welcome');
});

Route::get('excel/export', 'ExcelController@export');

Controller

php artisan make:controller ExcelController

vim app/Http/Controllers/ExcelController.php

<?php

namespace App\Http\Controllers;

use Maatwebsite\Excel\Facades\Excel;

class ExcelController extends Controller
{
    public function export()
    {
        $cellData = [
            ['台班签证单', '', '', ''],
            ['时间段', '开始时间', '截止时间', '备注'],
            ['上午', '8时30分钟', '11时00分钟', '无'],
            ['', '11时00分钟', '14时00分钟', ''],
            ['注: 此表24小时内审批签字 否则无效', '', '', ''],
        ];
        Excel::create('台班签证单', function ($excel) use ($cellData) {
            $excel->sheet('第一页', function ($sheet) use ($cellData) {
                $sheet->rows($cellData);
            });
        })->export('xlsx');
    }
}

• 测试

php artisan serve

浏览机访问http://localhost:8000/excel/export Excel效果如下

laravel-excel-01.png

Cell Size

vim app/Http/Controllers/ExcelController.php

// 省略了未修改代码
        Excel::create('台班签证单', function ($excel) use ($cellData) {
            $excel->sheet('第一页', function ($sheet) use ($cellData) {
                $sheet->rows($cellData);

                // Cell Size
                $sheet->setWidth(array(
                    'A' => 10,
                    'B' => 15,
                    'C' => 15,
                    'D' => 10
                ));
                $sheet->setHeight(array(
                    1 => 20,
                    2 => 20,
                    3 => 20,
                    4 => 20,
                    5 => 20
                ));
            });
        })->export('xlsx');
// 省略了未修改代码

• 测试

php artisan serve

浏览机访问http://localhost:8000/excel/export Excel效果如下

laravel-excel-02.png

Merging Cells

vim app/Http/Controllers/ExcelController.php

// 省略了未修改代码
        Excel::create('台班签证单', function ($excel) use ($cellData) {
            $excel->sheet('第一页', function ($sheet) use ($cellData) {
                $sheet->rows($cellData);

                // Cell Size
                // 省略了未修改代码

                // Merging cells
                $sheet->mergeCells('A1:D1');
                $sheet->mergeCells('A5:D5');
                $sheet->setMergeColumn(array(
                    'columns' => array('A'),
                    'rows' => array(
                        array(3, 4),
                    )
                ));
                $sheet->setMergeColumn(array(
                    'columns' => array('D'),
                    'rows' => array(
                        array(3, 4),
                    )
                ));
            });
        })->export('xlsx');
// 省略了未修改代码

• 测试

php artisan serve

浏览机访问http://localhost:8000/excel/export Excel效果如下

laravel-excel-03.png

Alignment

vim app/Http/Controllers/ExcelController.php

// 省略了未修改代码
        Excel::create('台班签证单', function ($excel) use ($cellData) {
            $excel->sheet('第一页', function ($sheet) use ($cellData) {
                $sheet->rows($cellData);

                // Cell Size
                // 省略了未修改代码

                // Merging cells
                // 省略了未修改代码

                // Alignment
                $style = array(
                    'alignment' => array(
                        'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
                        'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                    )
                );
                $sheet->getDefaultStyle()->applyFromArray($style);
                $sheet->getStyle("A1:D1")->applyFromArray($style);
                $sheet->getStyle("A3:A4")->applyFromArray($style);
                $sheet->getStyle("D3:D4")->applyFromArray($style);
            });
        })->export('xlsx');
// 省略了未修改代码

• 测试

php artisan serve

浏览机访问http://localhost:8000/excel/export Excel效果如下

laravel-excel-04.png

Cell Border

vim app/Http/Controllers/ExcelController.php

// 省略了未修改代码
        Excel::create('台班签证单', function ($excel) use ($cellData) {
            $excel->sheet('第一页', function ($sheet) use ($cellData) {
                $sheet->rows($cellData);

                // Cell Size
                // 省略了未修改代码

                // Merging cells
                // 省略了未修改代码

                // Alignment
                // 省略了未修改代码

                // Cell Border
                $sheet->cells('A2', function ($cells) {
                    $cells->setBorder('thin', 'thin', 'thin', 'thin');
                });
                $sheet->cells('B2', function ($cells) {
                    $cells->setBorder('thin', 'thin', 'thin', 'thin');
                });
                $sheet->cells('C2', function ($cells) {
                    $cells->setBorder('thin', 'thin', 'thin', 'thin');
                });
                $sheet->cells('D2', function ($cells) {
                    $cells->setBorder('thin', 'thin', 'thin', 'thin');
                });
                $sheet->cells('A3:A4', function ($cells) {
                    $cells->setBorder('thin', 'thin', 'thin', 'thin');
                });
                $sheet->cells('B3', function ($cells) {
                    $cells->setBorder('thin', 'thin', 'thin', 'thin');
                });
                $sheet->cells('C3', function ($cells) {
                    $cells->setBorder('thin', 'thin', 'thin', 'thin');
                });
                $sheet->cells('B4', function ($cells) {
                    $cells->setBorder('thin', 'thin', 'thin', 'thin');
                });
                $sheet->cells('C4', function ($cells) {
                    $cells->setBorder('thin', 'thin', 'thin', 'thin');
                });
                $sheet->cells('D3:D4', function ($cells) {
                    $cells->setBorder('thin', 'thin', 'thin', 'thin');
                });
                $sheet->row(3, function ($row) {
                    $row->setBorder('thin', 'thin', 'thin', 'thin');
                });
                $sheet->row(4, function ($row) {
                    $row->setBorder('thin', 'thin', 'thin', 'thin');
                });
            });
        })->export('xlsx');

• 测试

php artisan serve

浏览机访问http://localhost:8000/excel/export Excel效果如下

laravel-excel-05.png

参考

• Laravel Excel

• PhpSpreadsheet

• How to center the text in PHPExcel merged cell

• How to do the phpexcel Outside Border