[Laravel] 라라벨에서 엑셀 다루기

설치

엑셀 패키지 설치

composer require maatwebsite/excel

 

config/app.php 파일 수정

'providers' => [
	/*
     * Package Service Providers...
     */
	Maatwebsite\Excel\ExcelServiceProvider::class,
],
'aliases' => [
	'Excel' => Maatwebsite\Excel\Facades\Excel::class,
]

 

venter publich 명령어 실행

php artisan vendor:publish

> 실행후 Provider: Maatwebsite\Excel\ExcelServiceProvider 의 번호 입력하고 엔터

 

---

 

삽입할 엑셀 파일

테이블

 

---

 

DB에 바로 import하기

conrtoller, model, import 생성

php artisan make:controller TestController

php artisan make:model TestTable

php artisan make:import TestImport --model=TestTable

 

Controllers/TestController

<?php

namespace App\Http\Controllers;

use App\Imports\TestImport;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;

class TestController extends Controller
{
    public function testImport()
    {
        Excel::import(new TestImport, 'test.xlsx');

        return '성공';
    }
}

 

Models/TestTable

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class TestTable extends Model
{
    use HasFactory;

    protected $table = "Test_table";

    public $timestamps = false; //created_at, updated_at 컬럼 사용 안함

    protected $fillable = [
        'name',
        'nickname',
        'phone',
        'address',
    ];

}

 

Imports/TestImport

<?php

namespace App\Imports;

use App\Models\TestTable;
use Maatwebsite\Excel\Concerns\ToModel;

class TestImport implements ToModel
{
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)
    {
        return new TestTable([
            'name'     => $row[0],
            'nickname'     => $row[1],
            'phone'     => $row[2],
            'address'     => $row[3],
        ]);
    }
}

 

routes/api

Route::get('/test', [TestController::class, 'testImport']);

 

실행

 

---

 

배열로 출력하기

controller, import 생성

php artisan make:controller TestController

php artisan make:import TestArrayImport

 

Controllers/TestController

<?php

namespace App\Http\Controllers;

use App\Imports\TestArrayImport;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;

class TestController extends Controller
{
    public function testArray()
    {
        $array = Excel::toArray(new TestArrayImport, 'test.xlsx');

        return $array;
    }
}

 

Imports/TestArrayImport

<?php

namespace App\Imports;

use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;

class TestArrayImport implements ToCollection
{
    /**
    * @param Collection $collection
    */
    public function collection(Collection $collection)
    {
        //
    }
}

 

route/api

Route::get('/test-array', [TestController::class, 'testArray']);

 

실행