Laravel Maatwebsite-Excel 3.1 实现导出导入
写在前面
导出导入的功能是平时开发比较常见的功能了,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 $sheetName; //设置sheet页名称
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
protected $horizontal = []; //设置水平定位 key:A1:K8 value:center
protected $wrapText = []; //设置是否自动换行 key:A1:K8 value:bool
//设置页面属性时如果无效 更改excel格式尝试即可
//构造函数传值
public function __construct($data, $headings)
{
$this->data = $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) {
//设置sheet页名称
$event->getSheet()->getDelegate()->setTitle($this->sheetName);
//设置自动换行
foreach ($this->wrapText as $column => $bool) {
$event->sheet->getDelegate()
->getStyle($column)
->getAlignment()
->setWrapText(true);
}
//设置列宽
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->horizontal as $region => $position) {
$event->sheet->getDelegate()
->getStyle($region)
->getAlignment()
->setHorizontal($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
*/
public function setSheetName(string $sheetName)
{
$this->sheetName = trim($sheetName);
}
/**
* @return array
* @2020/3/22 10:33
* [
* A1:K7 => true
* ]
*/
public function setWrapText (array $wrapText)
{
$this->wrapText = array_change_key_case($this->symbol($wrapText),CASE_UPPER);
}
/**
* @return array
* @2020/3/22 10:33
* [
* A1:K7 => center
* ]
*/
public function setVertical (array $vertical)
{
$this->vertical = array_change_key_case($this->symbol($vertical),CASE_UPPER);
}
/**
* @return array
* @2020/3/22 10:33
* [
* A1:K7 => center
* ]
*/
public function setHorizontal (array $horizontal)
{
$this->horizontal = array_change_key_case($this->symbol($horizontal),CASE_UPPER);
}
/**
* @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);
}
public function symbol ($param) {
$record = [];
$count = count($this->data) + 1;//加上表头
foreach ($param as $key => $value) {
$str = str_replace("*", $count, $key);
$record[$str] = $value;
}
return $record;
}
}
在控制器内调用。
use App\Http\Libraries\ExcelUtil\export;
use Maatwebsite\Excel\Facades\Excel;
public function export () {
$data = [];//要导出的数据
$header = [];//导出头 例如:["名称", "价格", "数量"]
//导出的头和导出的数据的长度要一致
$excel = new export($data, $header);
//从上面的封装类中可以看到都有哪些属性可以设置,下面是一些例子,大写字母是列,字母后面的数字是行,支持*号设置整行或整列。
//例如表头是["名称", "价格", "数量"],就有下面的设置
$excel->setColumnWidth(['A' => 40,'B' => 40, 'C' => 40]);//设置单元格宽度
$excel->setRowHeight([1 => 40]);//设置行高
$excel->setFont(['A1:C1' => 12]);//设置字体大小
$excel->setBold(['A1:C1' => true]);//设置字体粗细
$excel->setBackground(['A1:C1' => '808080']);//设置背景色
return Excel::download($excel, '导出的文件名.xlsx');
}
根据自己的需求可设置行、列、字体、背景颜色等;
样式在有的excel格式上不起作用,具体不知道为什么,没有再去研究了;
数字过大时展示科学计数法问题,我借鉴了一个大佬的方法
打开默认配置文件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 = \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 = $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); //这个地方就可以获取到文件内的集合了
//在这可以验证数据 入库操作
}
代码都是粘贴就可以使用的,记录完毕
本作品采用《CC 协议》,转载必须注明作者和本文链接
推荐文章: