Laravel-Excel

方式一

安装&配置

使用Composer安装依赖

composer create-project laravel/laravel laravel-excel --prefer-dist "5.5.*" && cd laravel-excel
composer require maatwebsite/excel ~2.1

要加上~2.1,因为现在已经更新到3.0版本了,如果不加会报错,例如下

Symfony \ Component \ Debug \ Exception \ FatalThrowableError (E_ERROR)Call to undefined method Maatwebsite\Excel\Excel::create(),

修改项目设置

在config/app.php中注册服务提供者到providers数组:

Maatwebsite\Excel\ExcelServiceProvider::class,

在config/app.php中注册门面到aliases数组:

'Excel' => Maatwebsite\Excel\Facades\Excel::class,

生成excel.php配置文件

执行Artisan命令:

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

修改生成的config/excel.php文件 431行

'to_ascii' => false,

测试Excel文件

创建控制器

php artisan make:controller ExcelController 
Route::get('excel/export','ExcelController@export');
Route::get('excel/import','ExcelController@import');

控制器代码

namespace App\Http\Controllers; use App\Http\Requests; use Illuminate\Http\Request; use App\Http\Controllers\Controller; use Excel; class ExcelController extends Controller public function export() $cellData = [ ['id','姓名','年龄'], ['10001','张三','19'], ['10002','李四','22'], ['10003','王五','23'], ['10004','赵六','19'], ['10005','猴七','22'], $name = iconv('UTF-8', 'GBK', '成员信息'); Excel::create($name,function($excel) use ($cellData){ $excel->sheet('score', function($sheet) use ($cellData){ $sheet->rows($cellData); })->store('xls')->export('xls'); public function import(){ $filePath = 'storage/exports/'.iconv('UTF-8', 'GBK', '成员信息').'.xls'; Excel::load($filePath, function($reader) { $data = $reader->all(); dump($data); exit;

直接访问路由即可下载xls文件
store方法,将该Excel文件保存到服务器上,文件默认保存到storage/exports目录下,iconv()是为了防止文件名中文乱码。

//配置列宽
$sheet->setWidth(array(
     'A' => 10,
     'B' => 15,
     'C' => 15,
     'D' => 10
//配置行高
$sheet->setHeight(array(
     1 => 20,
     2 => 20,
     3 => 20,
     4 => 20,
     5 => 20
//A1到D1单元格合并
$sheet->mergeCells('A1:D1');
//A3到A4单元格合并
$sheet->setMergeColumn(array(
     'columns' => array('A'),
     'rows' => array(
          array(3, 4),
$style = array(
     'alignment' => array(
          'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
          'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
$sheet->getDefaultStyle()->applyFromArray($style);
//A1:D1合并单元格居中
$sheet->getStyle("A1:D1")->applyFromArray($style);
//A3:A4合并单元格居中
$sheet->getStyle("A3:A4")->applyFromArray($style);
//设置A2单元格边框
$sheet->cells('A2', function ($cells) {
     $cells->setBorder('thin', 'thin', 'thin', 'thin');
composer require maatwebsite/excel

注册config/app.php

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

创建一个新配置文件config/excel.php。

php artisan vendor:publish

创建导出类

php artisan make:export OrderExport

导出类代码

namespace App\Exports; use Maatwebsite\Excel\Concerns\FromCollection; // 导出集合 use Maatwebsite\Excel\Concerns\WithEvents; // 自动注册事件监听器 use Maatwebsite\Excel\Concerns\WithStrictNullComparison; // 导出 0 原样显示,不为 null use Maatwebsite\Excel\Concerns\WithTitle; // 设置工作䈬名称 use Maatwebsite\Excel\Events\AfterSheet; // 在工作表流程结束时会引发事件 class BillExport implements FromCollection, WithTitle, WithEvents, WithStrictNullComparison public $data; public $dateTime; public $payways; public function __construct(array $data, $dateTime, $payways) $this->data = $data; $this->dateTime = $dateTime; $this->payways = $payways; * registerEvents freeze the first row with headings * @return array * @author liuml <liumenglei0211@163.com> * @DateTime 2018/11/1 11:19 public function registerEvents(): array return [ AfterSheet::class => function(AfterSheet $event) { // 合并单元格 $event->sheet->getDelegate()->setMergeCells(['A1:O1', 'A2:C2', 'D2:O2']); // 冻结窗格 $event->sheet->getDelegate()->freezePane('A4'); // 设置单元格内容居中 $event->sheet->getDelegate()->getStyle('A1:A2')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER); // 定义列宽度 $widths = ['A' => 10, 'B' => 15, 'C' => 25,...]; foreach ($widths as $k => $v) { // 设置列宽度 $event->sheet->getDelegate()->getColumnDimension($k)->setWidth($v); // 其他样式需求(设置边框,背景色等)处理扩展中给出的宏,也可以自定义宏来实现,详情见官网说明 * 需要导出的数据统一在这个方法里面处理 这个方法里面也可以直接用 Model取数据 * 我这里的数据是 Controller 传过来的,至于怎么传的看下面给出的 Controller 里面的代码就知道了 * 里面数据处理太长了,多余的我都用 ... 表示,大家明白就行 * @return \Illuminate\Support\Collection public function collection() $i = 1; $total_amount = 0; // 交易金额总计 if (empty($this->data)) { foreach ($this->data as $key => $vo) { $data[$key]['num'] = $i; // 编号 $data[$key]['consume_type'] = $vo['consume_type']; // 项目类型 $total_amount += $data[$key]['total_amount ']; // 交易金额总计 $i++; $total = ['总计', ...]; $data[] = $total; $title = [$this->payways . '对账单']; $headings = ['编号', ...]; $date = ['下载时间:' . date('Y-m-d H:i:s'), '', '', '数据时间范围:' . $this->dateTime]; array_unshift($data, $title, $date, $headings); // 此处数据需要数组转集合 return collect($data); public function title(): string // 设置工作䈬的名称 return $this->payways . '账单明细';

Controller 中的调用方法

public function billExportExcel(OrderRequest $request)
    $start_date = $request->get('start_date', '');
    $end_date   = $request->get('end_date', '');
    $payways    = $request->get('payways', '');