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

Contents


개요

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


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

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

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

설치

라라벨 5.x  기준입니다


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

  1. composer.json 의 require 항목에 다음 내용을 추가하고 composer update 를 실행합니다.

    "require": {
    	"maatwebsite/excel": "~2.1.0"
    }

    또는 아래 명령을 커맨드에서 실행합니다.

    composer require "maatwebsite/excel" "~2.1.0"
  2. config/app.php 에 서비스 프로바이더와 파사드를 등록합니다.

    'providers' => [
    	// provider 추가
    	Maatwebsite\Excel\ExcelServiceProvider::class, 
    ],
    'aliases' => [
    	// facade 추가
    	'Excel' => Maatwebsite\Excel\Facades\Excel::class,
    ],
  3. 기본 엑셀 설정을 퍼블리싱합니다. 이제 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 의 값을 속성으로 사용합니다. 

nameUser Address전화번호생일
홍길동서울111110-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



Ref


Write a comment…