Laravel 에서 손쉽게 Excel 파일 다루기(Laravel Excel + PHP Excel)

개요

PHPExcel 이라는 패키지를 사용하면 excel 파일을 PHP에서 생성하고 읽을 수 있습니다.

만약 라라벨 프레임워크에서 개발할 경우 PHP Excel 패키지를 Laravel 에서 손쉽게 사용할 수 있도록 만든 Laravel Excel 패키지를 사용하면 더욱 쉽게 엑셀 파일을 처리할 수 있습니다.

엑셀 파일 작성시 다음과 같은 사항을 지켜주면 파싱 작업을 손쉽게 처리할 수 있습니다.

셀 병합 최소화(merge 된 셀들은 파싱이 어렵습니다.)
데이타의 특성에 맞는 셀 서식 지정(예: 날자 데이타의 경우 일반이나 텍스트보다 문자열 형식을 지정)
시트나 데이타 숨김 사용하지 않기 - 파싱시 foreach 등의 루프를 돌면서 데이타를 처리할 때 index 가 잘못되어 원하지 않는 결과를 얻을 수 있습니다.

설치

라라벨 5.x  기준입니다

composer 패키지이므로 일반적인 컴포저 설치 절차를 따르면 됩니다.

composer.json 의 require 항목에 다음 내용을 추가하고 composer update 를 실행합니다.
composer.json
```
"require": {
	"maatwebsite/excel": "~2.1.0"
}
```
또는 아래 명령을 커맨드에서 실행합니다.
```
composer require "maatwebsite/excel" "~2.1.0"
```

config/app.php 에 서비스 프로바이더와 파사드를 등록합니다.

'providers' => [
	// provider 추가
	Maatwebsite\Excel\ExcelServiceProvider::class, 
],
'aliases' => [
	// facade 추가
	'Excel' => Maatwebsite\Excel\Facades\Excel::class,
],

기본 엑셀 설정을 퍼블리싱합니다. 이제 config/excel.php 에 설정 파일이 생성됩니다.
```
php artisan vendor:publish
```

excel import

loading

엑셀 파일 로딩은 Excel 파사드의 load 메소드를 사용하면 됩니다.

Excel::load('file.xlsx', function($reader) {
    // reader methods
});

Web 상에서 Form 으로 엑셀 파일을 업로드하며 필드 이름이 excel 일 경우 다음과 같이 getClientOriginalName() 를 사용하여 파일 명을 알아낸 후에 읽으면 됩니다.

업로드 excel 처리

$excel = $request->file('excel');
if ($excel == null)
    abort(400, "excel entry is null");

// tmp 에서 storage 로 이동
$excel->move(storage_path() . '/app/', $excel->getClientOriginalName());

$path = storage_path() . '/app/' . $excel->getClientOriginalName();
Excel::load($path, function($reader) {
	// 모든 시트와 레코드 로딩
    $results = $reader->all();
    dump($results);
});

ExcelFile injections

Laravel 5 부터는 ExcelFile 클래스를 상속받아서 getFile(), getFilters() 를 구현하면 손쉽게 엑셀 파일을 주입할 수 있습니다.

class UserListImport extends \Maatwebsite\Excel\Files\ExcelFile {

	// 엑셀 파일을 가져올 경로 리턴
    public function getFile()
    {
        return storage_path('exports') . '/file.csv';
    }

	// 적용할 필터 설정
    public function getFilters()
    {
        return [
            'chunk'
        ];
    }

}

chunk loading

큰 엑셀 파일을 다룰 일이 있으면 Windows 환경에서 C# 이나 VB.NET 을 사용하는 게 정신건강에 좋습니다.

큰 엑셀 파일을 다룰 경우 load()를 사용하면 메모리 부족때문에 제대로 처리를 못 할 수 있습니다. 이경우 chunk 메소드로 정해진 갯수만큼만 읽어들이면 큰 엑셀 파일 처리도 가능합니다.

chunk 의 첫 번째 파라미터는 청크의 갯수이고 두 번째 파라미터는 결과를 전달 받아 처리할 클로저입니다.

Excel::filter('chunk')->load('file.csv')->chunk(250, function($results)
{
        foreach($results as $row)
        {
            // do stuff
        }
});

현재 버전에는 memory leak 버그가 있으며 큰 파일을 제대로 다루지 못할 경우 https://github.com/Maatwebsite/Laravel-Excel/issues/1391 를 참고해서 Maatwebsite/Excel/Readers/ChunkedReadJob.php 을 아래와 같이 수정해야 합니다.

// 주석 처리
//$results = $reader->get()->slice($this->startIndex, $this->chunkSize);

$results = $reader->limitRows($this->chunkSize, $this->startIndex)->get();

Sheet

모든 시트 가져오기

시트 정보를 가져오려면 Excel::load() 호출의 리턴 객체인 \Maatwebsite\Excel\LaravelExcelReader 의 get() 또는 all() 메서드를 호출하면 됩니다.

$sheets = Excel::load('file.xls', function($reader) {

})->get();

또는

Excel::load('file.xls', function($reader) {

    // Getting all results
    $sheets = $reader->get();

    // ->all() is a wrapper for ->get() and will work the same
    $sheets = $reader->all();

});

$results 는 sheet 가 여러개일때는 SheetCollection, 시트가 하나일 때는 RowCollection 입니다.

각 클래스가 달라서 foreach 등의 루프를 돌때 혼란을 주므로 config/excel.php 에 다음과 같이 설정하면 시트의 갯수와 상관없이 SheetCollection을 리턴합니다.

'import'     => [
	'force_sheets_collection' => true,
]

시트 선택

시트를 선택하려면 selectSheets($name) 을 사용하면 되며 아래는 이름이 "sheet first " 인 시트를 가져옵니다.

Excel::selectSheets('sheet_first')->load();

또는 selectSheetsByIndex($index) 를 사용해서 인덱스로 시트를 가져올 수 있습니다.

Excel::selectSheetsByIndex(0)->load();

테이블 heading 및 attribute

Laravel Excel은 첫 번째 row 의 값을 속성으로 사용합니다.

name	User Address	전화번호	생일
홍길동	서울	1111	10-23

즉 위와 같은 엑셀이 있을 경우 다음과 같이 첫 번째 row 의 값을 속성으로 하여 foreach 루프를 돌수 있습니다.

Excel::load($path, function($reader) {
    $sheets = $reader->all();
	foreach($sheets as $s) {
		if (!empty($s->name)){
			dump($s->name);
		}
	}    
});

Laravel Excel은 첫 번째 컬럼을 slug 형식의 속성으로 변환하며 이는 config/excel.php 의 'import.heading' 설정에서 확인할 수 있습니다.

config/excel.php

 'import'     => array(
	'heading'                 => 'slugged',

위의 User Address 컬럼은 공백은 _ 로 치환한 후에 slug(user_address) 로 변환되므로 다음과 같이 속성값을 사용할 수 있습니다.

foreach($results as $r) {
	echo $r->user_address;
}

사용할 수 있는 값은 다음과 같으며 구현부는 vendor\maatwebsite\excel\src\Maatwebsite\Excel\Parsers\ExcelParser.php의 getIndex($cell) 를 참고하면 됩니다.

값	의미	비고
slugged	슬러그로 변환	default
ascii	아스키로 변환	Str::ascii($value);
trans	trans 메소드를 사용하여 언어 파일에서 읽어옴
original	원래 컬럼명을 속성명으로 사용

하지만 많은 사람들이 한글을 첫 번째 row 로 사용하므로(당연히!) slugged나 ascii 일 경우 제대로 변환을 못하는 문제가 있습니다. original 로 사용하면 한글 컬럼명을 제대로 가져오지만 다음과 같이 잘 작동할 지 의문이 생기는 코드를 작성해야 합니다.

foreach($results as $r) {
	echo $r->전화번호; // ???
}

컬럼이 영어라면 slugged 를 사용해도 되지만 그렇지 않을 경우 다음과 같이 import.heading을 true 로 설정하면 숫자로 인덱스를 사용할 수 있습니다.

config/excel.php

  'import'     => array(
	'heading'                 => 'true',

숫자 인덱스 사용

foreach($results as $r) {
	echo $r[0]; //name
	echo $r[2]; //전화번호
}

시작 row 가 1이 아닐 경우

만약 엑셀의 시작 row 가 1이 아닌 경우 config/excel.php 의 import.startRow 항목을 시작 row 숫자로 변경하면 됩니다.

'import'     => [
     'startRow'                => 6,

만약 파일을 수정하는 게 부담된다면 Excel::load 전에 아래와 같이 config 헬퍼 함수로 관련 항목를 설정해도 됩니다.

// 시작 row 를 6으로 설정
config(['excel.import.startRow' => 6 ]);

Excel::load($file, function($reader) {

Date Formatting

날자 형식의 컬럼은 기본적으로 Carbon 객체를 생성하여 처리하며 config/excel.php 의 dates.enabled 를 false 로 설정하면 처리하지 않습니다. 또는 다음과 같이 code 로 설정할 수 있습니다.

// Format the dates
$reader->formatDates(false);
 
// Format dates + set date format
$reader->formatDates(true, 'Y-m-d');

Calculate formulas

$reader->calculate(false);

excel export

simple export

excel-filename 이라는 이름으로 새로운 excel 생성

Excel::create('excel-filename', function ($excel)
{
    // writer excel data
});

download

생성한 엑셀을 Browser 에 전송할 경우 download($param), 또는 alias인 export 메서드를 사용. 파라미터는 export 할 포맷이며 이전 엑셀 형식일 경우 'xls' 를 전달

Excel::create('excel-filename', function ($excel)
{
    // writer excel data
})->download('xlsx');

excel 을 서버에 저장

브라우저에서 다운로드하지 않고 서버에 저장할 경우 download 대신 store 메서드 사용

Excel::create('excel-filename', function ($excel)
{
    // writer excel data
})->store('xlsx');

기본 저장 폴더는 storage_path('exports')' 의 결과 폴더이며 다른 폴더에 저장할 경우 store 의 두 번째 파라미터로 저장할 폴더 경로 기술

Excel::create('excel-filename', function ($excel)
{
    // writer excel data
})->store('xlsx');

Excel::create('excel-filename', function ($excel)
{
    // writer excel data
})->store('xlsx', storage_path('excel/export'));

서버에 저장하고 브라우저에서 전달하려면 download 와 store 메서드를 체이닝으로 사용

Excel::create('excel-filename', function ($excel)
{
    // writer excel data
})->store('xlsx', storage_path('excel/export'))->download('xlsx');

Sheet 처리

sheet() 메서드로 생성할 시트를 지정

Excel::create('Filename', function($excel) {

    $excel->sheet('Sheetname', function($sheet) {

        // Sheet manipulation

    });

})->export('xls');

Row 처리

$excel->sheet('Sheetname', function($sheet) {
    $num = 1;

	// Manipulate first row
    $sheet->row($num++, [
        'head 1', 'head 2'
    ]);

    $sheet->row($num++, [
        'test1', 'test2'
    ]);

    // Manipulate 2nd row
    $sheet->row($num++, [
        'test3', 'test4'
    ]);
});

row 의 첫 번째는 0 이 아니라 1로 시작해야 함.

예제

$data = [
    ['file' => 'log1', 'name' => 'value'],
    ['file' => 'log2', 'name' => 'value2'],
];

Excel::create('excel-filename', function ($excel) use($data)
{
    // Set the title
    $excel->setTitle('Our new awesome title');

    // Chain the setters
    $excel->setCreator('Maatwebsite')
        ->setCompany('Maatwebsite');

    // Call them separately
    $excel->setDescription('A demonstration to change the file properties');

    // header
    $excel->sheet('Sheetname', function($sheet) use($data) {
        // Set auto size for sheet
        $sheet->setAutoSize(true);

        // Manipulate first row
        $num = 1;

        $sheet->row($num++, [
            'log file', 'name'
        ]);

        // Set cyan background
        $sheet->row(1, function($row) {
            $row->setBackground('#00FFFF');
        });

        foreach ($data as $d) {
            $sheet->row($num++, [
                $d['file'], $d['name'],
            ]);
        }
    });

})->store('xlsx');

Ref

2.1 Manual

https://laravel-excel.maatwebsite.nl/2.1

latest Manual

https://laravel-excel.maatwebsite.nl/