寫在前面
匯出匯入的功能是平時開發比較常見的功能了，Laravel Maatwebsite-Excel更新了3.x，這次就自己封裝一個簡單的通用版并記錄一下遇到的問題，
環境
-
PHP:
^7.3.4 -
Laravel:
^5.7
安裝
composer require maatwebsite/excel
匯出excel
創建檔案
然后在composer.json添加
然后執行：
composer dump-autoload
export.php內
<?php
namespace App\Http\Libraries\ExcelUtil;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Events\AfterSheet;
class export implements FromCollection,WithHeadings, WithEvents
{
protected $data;
protected $headings;
protected $columnWidth = [];//設定列寬 key：列 value:寬
protected $rowHeight = []; //設定行高 key：行 value:高
protected $mergeCells = []; //合并單元格 key：第一個單元格 value:第二個單元格
protected $font = []; //設定字體 key：A1:K8 value:11
protected $bold = []; //設定粗體 key：A1:K8 value:true
protected $background = []; //設定背景顏色 key：A1:K8 value:#F0F0F0F
protected $vertical = []; //設定定位 key：A1:K8 value:center
//設定頁面屬性時如果無效 更改excel格式嘗試即可
//建構式傳值
public function __construct($data, $headings)
{
$this->data = https://www.cnblogs.com/guangsheng/p/$data;
$this->headings = $headings;
$this->createData();
}
public function headings(): array
{
return $this->headings;
}
//陣列轉集合
public function collection()
{
return new Collection($this->data);
}
//業務代碼
public function createData()
{
$this->data = collect($this->data)->toArray();
}
public function registerEvents(): array
{
return [
AfterSheet::class => function(AfterSheet $event) {
//設定列寬
foreach ($this->columnWidth as $column => $width) {
$event->sheet->getDelegate()
->getColumnDimension($column)
->setWidth($width);
}
//設定行高，$i為資料行數
foreach ($this->rowHeight as $row => $height) {
$event->sheet->getDelegate()
->getRowDimension($row)
->setRowHeight($height);
}
//設定區域單元格垂直居中
foreach ($this->vertical as $region => $position) {
$event->sheet->getDelegate()
->getStyle($region)
->getAlignment()
->setVertical($position);
}
//設定區域單元格字體
foreach ($this->font as $region => $value) {
$event->sheet->getDelegate()
->getStyle($region)
->getFont()
->setSize($value);
}
//設定區域單元格字體粗體
foreach ($this->bold as $region => $bool) {
$event->sheet->getDelegate()
->getStyle($region)
->getFont()
->setBold($bool);
}
//設定區域單元格背景顏色
foreach ($this->background as $region => $item) {
$event->sheet->getDelegate()->getStyle($region)->applyFromArray(['fill' => [
'fillType' => 'linear', //線性填充，類似漸變
'startColor' => [
'rgb' => $item //初始顏色
],
//結束顏色，如果需要單一背景色，請和初始顏色保持一致
'endColor' => [
'argb' => $item
]
]
]);
}
//合并單元格
foreach ($this->mergeCells as $start => $end) {
$event->sheet->getDelegate()->mergeCells($start.':'.$end);
}
}
];
}
/**
* @return array
* @2020/3/22 10:33
* [
* 'B' => 40,
* 'C' => 60
* ]
*/
public function setColumnWidth (array $columnwidth)
{
$this->columnWidth = array_change_key_case($columnwidth, CASE_UPPER);
}
/**
* @return array
* @2020/3/22 10:33
* [
* 1 => 40,
* 2 => 60
* ]
*/
public function setRowHeight (array $rowHeight)
{
$this->rowHeight = $rowHeight;
}
/**
* @return array
* @2020/3/22 10:33
* [
* A1:K7 => 12
* ]
*/
public function setFont (array $fount)
{
$this->font = array_change_key_case($fount, CASE_UPPER);
}
/**
* @return array
* @2020/3/22 10:33
* [
* A1:K7 => true
* ]
*/
public function setBold (array $bold)
{
$this->bold = array_change_key_case($bold, CASE_UPPER);
}
/**
* @return array
* @2020/3/22 10:33
* [
* A1:K7 => F0FF0F
* ]
*/
public function setBackground (array $background)
{
$this->background = array_change_key_case($background, CASE_UPPER);
}
}
在控制器內呼叫，
use App\Http\Libraries\ExcelUtil\export;
use Maatwebsite\Excel\Facades\Excel;
public function export () {
$data = https://www.cnblogs.com/guangsheng/p/[];//要匯入的資料
$header = [];//匯出頭
$excel = new export($data, $header);
$excel->setColumnWidth(['B' => 40, 'C' => 40]);
$excel->setRowHeight([1 => 40, 2 => 50]);
$excel->setFont(['A1:K7' => 12]);
$excel->setBold(['A1:K7' => true]);
$excel->setBackground(['A1:K7' => '808080']);
return Excel::download($excel, '匯出的檔案名.xlsx');
}
根據自己的需求可設定行、列、字體、背景顏色等；
樣式在有的excel格式上不起作用，具體不知道為什么，沒有再去研究了；
數字過大時展示科學計數法問題，我借鑒了一個大佬的方法
Laravel使用excel3.1匯出時，防止長數字變科學計數法的方法
打開默認組態檔vendor/maatwebsite/excel/src/DefaultValueBinder.php，添加
use PhpOffice\PhpSpreadsheet\Cell\DataType;
函式內部添加：
//超過10位的數字轉文本格式，防止科學計數法
if (strlen($value) > 10) {
$cell->setValueExplicit($value, DataType::TYPE_STRING);
return true;
}
檔案代碼最終如下：
namespace Maatwebsite\Excel;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder as PhpSpreadsheetDefaultValueBinder;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
class DefaultValueBinder extends PhpSpreadsheetDefaultValueBinder
{
/**
* @param Cell $cell Cell to bind value to
* @param mixed $value Value to bind in cell
*
* @return bool
*/
public function bindValue(Cell $cell, $value)
{
if (is_array($value)) {
$value = https://www.cnblogs.com/json_encode($value);
}
//超過10位的數字轉文本格式，防止科學計數法
if (strlen($value) > 10) {
$cell->setValueExplicit($value, DataType::TYPE_STRING);
return true;
}
return parent::bindValue($cell, $value);
}
}
如果要把匯出檔案先下載到服務器上可以使用store方法
匯入
import.php內
<?php
namespace App\Http\Libraries\ExcelUtil;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
class import implements ToCollection
{
public $data;
protected $delTitle;
/**
*
* @param $title integer //去掉幾行標題 默認一行
*/
public function __construct($delTitle = 1)
{
$this->delTitle = $delTitle;
}
/**
* @param Collection $rows
* @2020/3/23 9:53
*/
public function collection(Collection $rows)
{
$this->delTitle($rows);
//$rows 是陣列格式
$this->data = https://www.cnblogs.com/guangsheng/p/$rows;
}
public function delTitle (&$rows) {
$rows = $rows->slice($this->delTitle)->values();
}
}
控制器內呼叫
public function import (Request $request) {
$path = $request->file('file');
$delTitle = 1;//指定頭行數 洗掉它
$excel = new import($delTitle);
Excel::import($excel, $path->getRealPath());
dd($excel->data); //這個地方就可以獲取到檔案內的集合了
//在這可以驗證資料 入庫操作
}
代碼都是粘貼就可以使用的，記錄完畢
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/184112.html
標籤：PHP
上一篇：SSM環境搭建回顧