后端,PHP

使用Laravel Excel3 导入导出excel文件

安装Laravel Excel 3

参考一下文档:

https://docs.laravel-excel.com/3.1/getting-started/installation.html

导出

控制器里:

<?php

namespace App\Http\Controllers\admin;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
//引入导出模型
use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

class QuestionController extends Controller
{
    /**
     * 导出
     * @return \Symfony\Component\HttpFoundation\BinaryFileResponse
     */
    public function export()
    {
        return Excel::download(new UsersExport, '试题.xlsx');
    }
}

导出模型里:

<?php

namespace App\Exports;

//引入数据库对象模型
use App\admin\Question;
use Illuminate\Support\Facades\Input;
use Maatwebsite\Excel\Concerns\FromArray;


class UsersExport implements FromArray
{
    /**
     * 自定义导出excel
     * @return array
     */
    public function array(): array
    {
        //创建时间
        $start = Input::get('_start');
        //结束时间
        $end = Input::get('_end');
        //关键字
        $keyword = Input::get('keyword');
        //执行条件
        $date = Question::when($start, function ($query, $start) {
            return $query->where('created_at', '>=', $start);
        })->when($end, function ($query, $end) {
            return $query->where('created_at', '=<', $end);
        })->when($keyword, function ($query, $keyword) {
            return $query->where('id', 'like', '%' . $keyword . '%');
        })->get();
        //设置导出表头
        $va = [
            [
                'ID',
                '题干',
                '所属试卷',
                '分值',
                '选项',
                '答案',
                '试卷备注',
                '创建时间',
                '修改时间'
            ]
        ];
        //将数据结果对象转成数组
        foreach ($date as $val) {
            $va[] = [
                $val->id,
                $val->question,
                $val->link_paper->name,
                $val->score,
                $val->options,
                $val->answer,
                $val->remake,
                $val->created_at,
                $val->updated_at
            ];
        }
        return $va;
    }
}

导入

控制器里:

<?php

namespace App\Http\Controllers\admin;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;

class QuestionController extends Controller
{
    /**
     * 导入试题
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function import(Request $request)
    {
        $filePath = $request->get('excelpath');
        $res = Excel::import(new UsersImport, $filePath);
        return response()->json(['status' => '200', 'msg' => '文件上传成功!']);
    }
}

导入模型里:

<?php

namespace App\Imports;

//引入数据库对象模型
use App\admin\Question;
use Maatwebsite\Excel\Concerns\ToModel;

class UsersImport implements ToModel
{
    /**
     * 导入excel
     * @param array $row
     * @return Question|\Illuminate\Database\Eloquent\Model|\Illuminate\Database\Eloquent\Model[]|null
     */
    public function model(array $row)
    {
        //排除第一行表头
        if ($row[0] == '题干') {
            return null;
        }
        //要插入的字段
        return new Question([
            'question' => $row[0],
            'paper_id' => $row[1],
            'score' => $row[2],
            'options' => $row[3],
            'answer' => $row[4],
            'remake' => $row[5],
            'created_at' => date('Y-m-d H:i:s')
        ]);
    }
}

回复

This is just a placeholder img.