excel export 쿼리문으로 엑셀 추출

깃주소

https://github.com/Maatwebsite/Laravel-Excel

사용법 동영상

https://laraveldaily.teachable.com/courses/export-import-with-excel-in-laravel/lectures/6992855

사이트 메뉴얼

https://laravel-excel.com/

설치 메뉴

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

컴포저 설치

composer require maatwebsite/excel

구성 공개

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

1. export명령어로 파일을 생성 (파일은 app/Exports/BoardSearchExport 생성됨)

php artisan make:export BoardSearchExport --model=BoardSearchExport

2. 받을 인자와 쿼리문을 작성해준다.

<?php
 
namespace App\Exports;
 
use App\Product;
 
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\Exportable;
class BoardsearchExport implements FromQuery
{
    /**
    * @return \Illuminate\Support\Collection
    */
       use Exportable;
 
    public function __construct(int $year, string $board_name_search)
    {
     //dd($year,$boardname);
      $this->year = $year;
      $this->boardname = $board_name_search;
    }   
 
    public function query()
    {
        return Product::query()->whereYear('created_at', $this->year)->where('board_name', $this->boardname);
    }
}

3. SpcsController.php 

namespace App\Http\Controllers;
 
use App\Exports\BoardsearchExport;
use App\Exports\BoardsearchExportView;
use Illuminate\Http\Request;
use App\Product;
use App\Project;
use App\Works;
use Maatwebsite\Excel\Facades\Excel;

    public function export($board_name_search)
    {
        return Excel::download(new BoardsearchExport(2020,$board_name_search), 'qwer.xlsx');
    }

4. web.php

#excel export
Route::get('/bobo/{board_name_search?}','SpcsController@export')->middleware('lv2')->name('bobo'); //

5. 해당 블레이드파일에 버튼을 삽입한다.

 <div class="column">
            <div > <a href="/bobo/{{ $board_name_search }}" class="ui teal button">엑셀파일 다운로드</a></div>
 </div>

테이블 파일 인크루드

@include('main.table', $products)

table.blaed.php

<table class="ui twelve column celled table">
    <thead>
      <tr>
        <th>ID</th>
        <th>시리얼번호</th>
        <th>보드명</th>
        <th>생산일</th>
        <th>출하일</th>
        <th>출하내역</th>
        <th>불량</th>
        <th>불량내용</th>
        <th>타입</th>
        <th>인수자</th>
        <th>인계자</th>
        <th>메모</th>
      </tr>
    </thead>
 
    <tbody>
        @foreach($products as $product)
      <tr>
        <td>{{ $product->id }}</td>
 
        <td>
          <form action="/serialNameSearch" method="GET">
            <input type="hidden" name="serial_name" value="{{ $product->serial_name }}" >
            <button style=" background:none;border:none; margin:0px;cursor: pointer;">{{ $product->serial_name }}</button>
          </form>
        </td>
 
        <td>
          <form action="/pbas/" method="GET">
            <input type="hidden" name="board_name" value="{{ $product->board_name }}" >
            <button style=" background:none;border:none; margin:0px;cursor: pointer;">{{ $product->board_name }}</button>
          </form>
        </td>
 
        <td>{{ $product->product_date }}</td>
        <td>{{ $product->shipment }}</td>
        <td>{{ $product->shipment_daily }}</td>
        <td>{{ $product->faulty }}</td>
        <td>{{ $product->remarks }}</td>
        <td>{{ $product->type }}</td>
        <td>{{ $product->receiver }}</td>
        <td>{{ $product->ship_user }}</td>
        <td>{{ $product->note }}</td>
      </tr>
      @endforeach
 
    </tbody>
  </table>
 

6. 브라우저에서 해당버튼을 누르면 엑셀파일이 다운로드 받아진다.

역순으로 나왔지만 잘 추출된다.