Laravel Excel Import Export Excel/csv file example

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, expressive code.

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

In this article, we will use Laravel Excel to easily import and export Excel files in Laravel.

2. Installation & Configuration

Using composer to install dependencies

First Use composer installation dependencies under the Laravel project root:

Composer require Maatwebsite/excel ~2.0.0
Settings after installation

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

Maatwebsite\excel\excelserviceprovider::class,

Also registers the façade to the aliases array in the config/app.php:

' Excel ' => maatwebsite\excel\facades\excel::class,

If you want to make more custom configurations of Laravel Excel, 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 File
To demonstrate laravel Excel-related features, we create a clean controller excelcontroller.php for this test:

PHP Artisan Make:controller Excelcontroller--plain

Then define the associated route in routes.php:

Route::get (' Excel/export ', ' excelcontroller@export ');
Route::get (' Excel/import ', ' excelcontroller@import ');

Next we define the export method in excelcontroller.php to implement the exporting 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 Export function by Laravel College
Public Function export () {
$cellData = [
[' School Number ', ' name ', ' result '],
[' 10001 ', ' AAAAA ', ' 99 '],
[' 10002 ', ' bbbbb ', ' 92 '],
[' 10003 ', ' CCCCC ', ' 95 '],
[' 10004 ', ' ddddd ', ' 89 '],
[' 10005 ', ' eeeee ', ' 96 '],
];
Excel::create (' Student score ', function ($excel) use ($cellData) {
$excel->sheet (' Score ', function ($sheet) use ($cellData) {
$sheet->rows ($cellData);
});
})->export (' xls ');
}
}

When we visit Http://laravel.app:8000/excel/export in the browser, we export an Excel file named Student score. XLS:

Export files using laravel Excel

If you are exporting a CSV or xlsx file, simply change the parameters in the export method to CSV or xlsx.

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

Excel::create (' Student score ', function ($excel) use ($cellData) {
$excel->sheet (' Score ', function ($sheet) use ($cellData) {
$sheet->rows ($cellData);
});
})->store (' xls ')->export (' xls ');
The file is saved to the Storage/exports directory by default, if the file name is garbled in Chinese, the above code file name can be modified as follows:

Iconv (' UTF-8 ', ' GBK ', ' student scores ')

4. Import Excel File

We will import the Excel file that we just saved to the server, and the import is simple, using the Load method on the Excel façade:

Excel file import by Laravel College
Public Function Import () {
$filePath = ' storage/exports/'. Iconv (' UTF-8 ', ' GBK ', ' student scores '). 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, and we also have a transcoding of the Chinese, otherwise the file will be prompted to not exist.

To access Http://laravel.app:8000/excel/import in the browser, the page appears as follows:

Import files using laravel Excel

Of course, Laravel Excel has many other features, such as exporting a blade view to Excel or CSV, and more granular control of import/export