1, introduction

Laravel Excel integrates PHPExcel in the PHPOffice suite in Laravel 5 to facilitate the import and export of Excel/CSV files with elegant and expressive code.

The GitHub address of the project is https://github.com/Maatwebsite/Laravel-Excel.

2. Installation & Configuration

Using Composer to Install Dependencies

In this paper, we will use Laravel Excel in Laravel to simply implement the import and export of Excel files.

First enter the Laravel project root directory and install dependencies using Composer:

composer require maatwebsite/excel=~2.0

 

Settings after installation

Register the service provider to the providers array in config/app.php:

Maatwebsite\Excel\ExcelServiceProvider::class,

 

Also register the facade to the aliases array in config/app.php:

'Excel' => Maatwebsite\Excel\Facades\Excel::class,

 

If you want to configure Laravel Excel more customically, execute the following Artisan command:

php artisan vendor:publish

After successful execution, a configuration file excel.php is generated in the config directory.

3. Export Excel files

To demonstrate Laravel Excel related functions, we created a clean controller Excel Controller. PHP for this test:

php artisan make:controller ExcelController --plain

 

Then define the relevant routing in routes.php:

Route::get('excel/export','ExcelController@export');
Route::get('excel/import','ExcelController@import');

 

Next, we define the export method in Excel Controller. PHP to implement the export function:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use App\Http\Requests;
use App\Http\Controllers\Controller;

use Excel;

class ExcelController extends Controller
{
 //Excel File Around Function

 public function export()
 {
  $cellData = [
   ['Student ID','Full name','achievement'],
   ['10001','AAAAA','99'],
   ['10002','BBBBB','92'],
   ['10003','CCCCC','95'],
   ['10004','DDDDD','89'],
   ['10005','EEEEE','96'],
  ];
  Excel::create('Student achievement',function ($excel) use ($cellData){
   $excel->sheet('score', function ($sheet) use ($cellData){
    $sheet->rows($cellData);
   });
  })->export('xls');
 }

}

 

There is also a way to import directly from an array.

$sheet->fromArray($anyArray);

 

If you want to export csv or xlsx files, just change the parameters in export method to csv or xlsx.

If you want to save the Excel file to the server, you can use the store method:

Excel::create('Student achievement',function($excel) use ($cellData){
  $excel->sheet('score', function($sheet) use ($cellData){
   $sheet->rows($cellData);
  });
})->store('xls')->export('xls');

 

By default, the file is saved in the storage/exports directory. If there is any Chinese random code in the file name, the above code file name can be modified as follows:

iconv('UTF-8', 'GBK', 'Student achievement')

 

4. Import Excel files

We import the Excel file that we just saved on the server. The import is very simple. We can use the load method on the Excel front.

//Excel File Import Function By Laravel College
public function import(){
 $filePath = 'storage/exports/'.iconv('UTF-8', 'GBK', 'Student achievement').'.xls';
 Excel::load($filePath, function($reader) {
  $data = $reader->all();
  dd($data);
 });
}

 

The load method is based on the project root path as the root directory. Similarly, we transcode the Chinese title, otherwise the file will not exist.

Visit http://laravel.app:8000/excel/import in the browser. The page shows as follows:

 

Import files using Laravel Excel

Of course, Laravel Excel has many other functions, such as exporting Blade views to Excel or CSV, and controlling import/export with finer granularity. Refer to its official documentation: http://www.maatwebsite.nl/laravel-excel/docs.