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 = $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' 설정에서 확인할 수 있습니다.
'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 로 설정하면 숫자로 인덱스를 사용할 수 있습니다.
'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
latest Manual