laravel excel3.1导出和导入

 

官方地址：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'=>'批量数据保存成功']);
    }
}