Laravel表格导入导出

在你项目根目录找到composer.json里的require添加如下

"require": {
        "maatwebsite/excel": "^3.1"
    },

执行更新

composer update

OR

 composer require maatwebsite/excel

在config/app.php添加

'providers' => [
    Maatwebsite\Excel\ExcelServiceProvider::class,
]
'aliases' => [
    ...
    'Excel' => Maatwebsite\Excel\Facades\Excel::class,
]

发布资源命令:

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"

假设导入学生execl:
创建文件

<?php

namespace App\Imports;

use App\Http\Model\Student;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class StudentImport implements ToModel
{
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)
    {
        return new Student([
            'grade'=>$row[5],
            'class'=>$row[6],
            'student_number'=>$row[7],
            'student_name'=>$row[9],
            'sex'=>$row[10],
            'card'=>$row[11],
    
        ]);
    }

Controller

 public function import(Request $request):array {
        $arr = \Maatwebsite\Excel\Facades\Excel::toArray(new StudentImport,$request->file('file'));
        return ['code'=>200]
}

导出：
创建导出文件：

<?php
/*
   *author:mj.shu
   *time 2017/3/15 3:37 PM
   *All rights reserved
*/


namespace App\Http\Controllers\V1\Admin\Execl;


use App\Http\Model\Bunk;
use App\Http\Model\Dorm;
use App\Http\Model\Student;
use App\Http\Model\Tower;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithCustomValueBinder;
use Maatwebsite\Excel\Concerns\WithHeadings;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder;

class Execl extends DefaultValueBinder implements FromArray,WithHeadings,ShouldAutoSize,WithCustomValueBinder
{

   protected $arr;

   public function __construct(array $arr)
   {
       $this->arr = $arr;
   }

   public function bindValue(Cell $cell, $value)
   {
       if (strlen($value) > 10) {
           $cell->setValueExplicit($value, DataType::TYPE_STRING);

           return true;
       }

       // else return default behavior
       return parent::bindValue($cell, $value);
   }

   public function headings(): array
   {
      $where = $this->arr;
      switch ($where['where']){
          case 0: //未入住学生
              $array =  ['id','姓名','性别','年纪','班级','学号','证件','培养类型','入学时间','手机号','邮箱'];
              break;
          case 1:
              $array = ['id','楼宇','层数','房间号','年纪','性别','朝向','类型','价格','总床位','空闲','床位编号','居住人'];
              break;
          case 2:
              $array = ['id','楼宇','层数','房间号','年纪','性别','朝向','类型','价格','床位编号','居住人','总床位','空闲'];
              break;
          case 3:
              $array = ['id','楼宇','层数','房间号','年纪','性别','朝向','类型','价格','总床位','空闲'];
              break;
      }
      return $array;

   }

headings()是自定义标题,在导出长数字的时候比如身份证，会形成1.0E之类的科学计算，这个时候需要判断类型，将其定义为String类型

public function bindValue(Cell $cell, $value)
    {
        if (strlen($value) > 10) {
            $cell->setValueExplicit($value, DataType::TYPE_STRING);

            return true;
        }

        // else return default behavior
        return parent::bindValue($cell, $value);
    }

继续控制器调用:

 public function downXls(Request $request)
        {
            $data = $request->all();
            return \Maatwebsite\Excel\Facades\Excel::download(new Execl($where), 'data.xls');

        }

至此，Execl表格导入导出完成