官方地址:https://docs.laravel-excel.com/3.1/getting-started/
一、导出
1.执行命令php artisan make:export ResourceViewLogExport --model=ViewLog 创建一个导出类
实现FromCollection接口类的collection()方法取数据
实现WithMapping接口类的map()方法格式化数据
实现WithHeadings接口类的headings()方法设置每列标题说明
实现WithTitle接口类的title()方法设置工作薄名字
<?php
namespace App\Exports;
use App\Models\ViewLog;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithTitle;
class ResourceViewLogExport implements FromCollection, WithMapping, WithHeadings,WithTitle
{
public function __construct($start_time, $end_time)
{
$this->start_time = $start_time;
$this->end_time = $end_time;
}
/**
* @return \Illuminate\Support\Collection
*取数据
*/
public function collection()
{
return ViewLog::viewLog(ViewLog::DOCTOR)->time($this->start_time, $this->end_time)
->unionAll(
ViewLog::viewLog(ViewLog::STAFF)
->time($this->start_time, $this->end_time)
)->get();
}
//每列头部标题
public function headings(): array
{
return ['姓名', '用户类型', '文章标题', '客户端', '创建时间'];
}
//工作薄的名字
public function title(): string
{
return '工作薄名字';
}
//遍历数据,格式化数据进行输出
public function map($row): array
{
return [
$row->user_name,
$row->type == ViewLog::VIP ? 'VIP用户' : '普通用户',
$row->title,
$row->user_agent,
$row->created_at,
];
}
}
2.在控制器中调用导出类
通过download()方法进行导出
<?php
namespace App\Admin\Controllers;
use App\Exports\ResourceViewLogExport;
use App\Http\Controllers\Controller;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
class ExportData extends Controller
{
protected $start_time = 7;
protected $end_time = 23;
//导出文章阅读记录
public function exportResourceViewLog(Request $request)
{
set_time_limit(0);
$start_time = Carbon::parse($request->get('start_time'));
$end_time = Carbon::parse($request->get('end_time'))->addDays(1);
$filename = '文章阅读明细_' . date('Y-m-d') . '.xlsx';
return Excel::download(new ResourceViewLogExport($start_time, $end_time), $filename);
}
}
二、导入
1.执行php artisan admin:import InfoImport --model=Models\Info命令创建一个导入类
<?php
namespace App\Imports;
use App\Models\Info;
use Maatwebsite\Excel\Concerns\ToModel;
class InfoImport implements ToModel
{
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
return new Info([
//
]);
}
}
对于简单的导出,使用model()的方式(因为无法进行复杂的判断,比如导入的时候判断数据是否已存在等,也可能是我不会用此方式,欢迎留言指教)
我一般的处理方式都是Excel::toArray()的方式
2.在控制器里通过Excel::toArray()进行导入
<?php
namespace App\Http\Controllers;
use App\Models\Info;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
class InfoImportController extends Controller
{
public function index(Request $request)
{
set_time_limit(0);
$oFile = $request->file('file');
if (empty($oFile)) {
return json_encode(['success'=>false,'msg'=>'请选择文件']);
}
if ($oFile->isValid()) {
$sExtName = $oFile->getClientOriginalExtension();
$sExt = strtolower($sExtName);
if (!in_array($sExt, ['xls', 'csv', 'xlsx'])) {
return json_encode(['success'=>false,'msg'=>'应上传正确格式的文件']);
}
$aContest = Excel::toArray(new \App\Imports\InfoImport(), $oFile);
return $this->dealTableData($aContest);
} else {
return json_encode(['success'=>false,'msg'=>'您选上传文件无效']);
}
}
public function dealTableData($aWorkSheet)
{
//去掉首行每列的标题
unset($aWorkSheet[0][0]);
foreach ($aWorkSheet[0] as $k => $v) {
if ($v) {
//编辑excel时,如果只是清空内容,没有删除之前增加过的行,会有很多空的行
if (!$v[0]) {
continue;
}
//处理发布时间字段,因为上传的时候,会将时间转成正整数,所以需要再转成时间格式 如:2109-12-16
$time = gmdate('Y-m-d', intval(($v[5] - 25569) * 3600 * 24));
$aInfos[$k] = array(
'title' => $v[1],
'content' => $v[2],
'author' => $v[3],
'type' => $v[4],
'publish_time' => $time,
'created_at' => Carbon::now(),
'updated_at' => Carbon::now()
);
}
if (empty($aInfos[$k]['title']) || empty($aInfos[$k]['content']) || empty($aInfos[$k]['author']) || empty($aInfos[$k]['type'])) {
return json_encode(['success'=>false,'msg'=>'表格保存失败,第' . $k . '条信息不全!']);
} else {
$oInfo = Info::where('title',$aInfos[$k]['title'])->first();
if ($oInfo) {
return json_encode(['success'=>false,'msg'=>'每个标题只能有一个,第' . $k . '条标题重复!']);
}
}
}
$flag = Info::insert($aInfos);
if (!$flag) {
return json_encode(['success'=>false,'msg'=>'数据插入失败']);
}
return json_encode(['success'=>true,'msg'=>'批量数据保存成功']);
}
}