方式一
安装&配置
使用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', '');