方式一
安裝&配置
使用Composer安裝依賴(lài)
composer create-project laravel/laravel laravel-excel --prefer-dist "5.5.*" && cd laravel-excel
composer require maatwebsite/excel ~2.1
要加上~2.1斥赋，因?yàn)楝F(xiàn)在已經(jīng)更新到3.0版本了，如果不加會(huì)報(bào)錯(cuò)故响，例如下
Symfony \ Component \ Debug \ Exception \ FatalThrowableError (E_ERROR)Call to undefined method Maatwebsite\Excel\Excel::create(),
修改項(xiàng)目設(shè)置
在config/app.php中注冊(cè)服務(wù)提供者到providers數(shù)組：
Maatwebsite\Excel\ExcelServiceProvider::class,
在config/app.php中注冊(cè)門(mén)面到aliases數(shù)組：
'Excel' => Maatwebsite\Excel\Facades\Excel::class,
生成excel.php配置文件
執(zhí)行Artisan命令：
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"
修改生成的config/excel.php文件 431行
'to_ascii' => false,
測(cè)試Excel文件
創(chuàng)建控制器
php artisan make:controller ExcelController
定義路由
Route::get('excel/export','ExcelController@export');
Route::get('excel/import','ExcelController@import');
控制器代碼
<?php
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;
}
}
直接訪問(wèn)路由即可下載xls文件
store方法,將該Excel文件保存到服務(wù)器上,文件默認(rèn)保存到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);
//設(shè)置A2單元格邊框
$sheet->cells('A2', function ($cells) {
$cells->setBorder('thin', 'thin', 'thin', 'thin');
});
方式二
安裝
composer require maatwebsite/excel
注冊(cè)config/app.php
'providers' => [
/*
* Package Service Providers...
*/
Maatwebsite\Excel\ExcelServiceProvider::class,
]
'aliases' => [
...
'Excel' => Maatwebsite\Excel\Facades\Excel::class,
]
創(chuàng)建一個(gè)新配置文件config/excel.php拇泣。
php artisan vendor:publish
創(chuàng)建導(dǎo)出類(lèi)
php artisan make:export OrderExport
導(dǎo)出類(lèi)代碼
<?php
namespace App\Exports;
use Maatwebsite\Excel\Concerns\FromCollection; // 導(dǎo)出集合
use Maatwebsite\Excel\Concerns\WithEvents; // 自動(dòng)注冊(cè)事件監(jiān)聽(tīng)器
use Maatwebsite\Excel\Concerns\WithStrictNullComparison; // 導(dǎo)出 0 原樣顯示，不為 null
use Maatwebsite\Excel\Concerns\WithTitle; // 設(shè)置工作?名稱(chēng)
use Maatwebsite\Excel\Events\AfterSheet; // 在工作表流程結(jié)束時(shí)會(huì)引發(fā)事件
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']);
// 凍結(jié)窗格
$event->sheet->getDelegate()->freezePane('A4');
// 設(shè)置單元格內(nèi)容居中
$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) {
// 設(shè)置列寬度
$event->sheet->getDelegate()->getColumnDimension($k)->setWidth($v);
}
// 其他樣式需求（設(shè)置邊框矮锈，背景色等）處理擴(kuò)展中給出的宏霉翔，也可以自定義宏來(lái)實(shí)現(xiàn)，詳情見(jiàn)官網(wǎng)說(shuō)明
...
},
];
}
/**
* 需要導(dǎo)出的數(shù)據(jù)統(tǒng)一在這個(gè)方法里面處理 這個(gè)方法里面也可以直接用 Model取數(shù)據(jù)
* 我這里的數(shù)據(jù)是 Controller 傳過(guò)來(lái)的苞笨，至于怎么傳的看下面給出的 Controller 里面的代碼就知道了
* 里面數(shù)據(jù)處理太長(zhǎng)了债朵，多余的我都用 ... 表示，大家明白就行
* @return \Illuminate\Support\Collection
*/
public function collection()
{
$i = 1;
$total_amount = 0; // 交易金額總計(jì)
if (empty($this->data)) {
foreach ($this->data as $key => $vo) {
...
$data[$key]['num'] = $i; // 編號(hào)
$data[$key]['consume_type'] = $vo['consume_type']; // 項(xiàng)目類(lèi)型
...
$total_amount += $data[$key]['total_amount ']; // 交易金額總計(jì)
$i++;
}
}
$total = ['總計(jì)', ...];
$data[] = $total;
$title = [$this->payways . '對(duì)賬單'];
$headings = ['編號(hào)', ...];
$date = ['下載時(shí)間：' . date('Y-m-d H:i:s'), '', '', '數(shù)據(jù)時(shí)間范圍：' . $this->dateTime];
array_unshift($data, $title, $date, $headings);
// 此處數(shù)據(jù)需要數(shù)組轉(zhuǎn)集合
return collect($data);
}
public function title(): string
{
// 設(shè)置工作?的名稱(chēng)
return $this->payways . '賬單明細(xì)';
}
}
Controller 中的調(diào)用方法
public function billExportExcel(OrderRequest $request)
{
$start_date = $request->get('start_date', '');
$end_date = $request->get('end_date', '');
$payways = $request->get('payways', '');
$data = $this->OrdersLogic->BillExportExcel($request->all());
// download 方法直接下載瀑凝，store 方法可以保存序芦。具體的導(dǎo)出類(lèi)型等看官方的文檔吧
return Excel::download(new BillExport($data, $start_date . '-' . $end_date, $payways, '**賬單-' . date('Y-m-d_H_i_s') . '.xls');
}
