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

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


개요

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


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

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

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

설치

라라벨 5.x  기준입니다


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

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

    composer.json
    "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 처리
$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


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'));


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

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


Related content

PHP Excel 에서 "simplexml_load_string huge input lookup" 에러가 나며 엑셀 로딩이 안 될 경우
PHP Excel 에서 "simplexml_load_string huge input lookup" 에러가 나며 엑셀 로딩이 안 될 경우
More like this
PHPStan 의 laravel 용 wrapper 인 larastan 사용하기
PHPStan 의 laravel 용 wrapper 인 larastan 사용하기
More like this
Confluence Page 를 Excel(엑셀)로 저장하기
Confluence Page 를 Excel(엑셀)로 저장하기
More like this
Laragon(라라곤)으로 Windows 에서 Laravel 개발 환경 구성하기 #1
Laragon(라라곤)으로 Windows 에서 Laravel 개발 환경 구성하기 #1
More like this
laravel 에서 오라클(Oracle) DB 사용
laravel 에서 오라클(Oracle) DB 사용
More like this
confluence 첨부 파일 인덱스 끄기(Disable Indexing of attachments)
confluence 첨부 파일 인덱스 끄기(Disable Indexing of attachments)
More like this