README

Lightweight and very fast XLSX Excel Spreadsheet read/write library for Laravel in pure PHP (wrapper around FastExcelWriter and FastExcelReader)

Introduction

Exporting data from your Laravel application has never been so fast! Importing models into your Laravel application has never been so easy!

This library is a wrapper around avadim/fast-excel-writer and avadim/fast-excel-reader, so it's also lightweight, fast, and requires a minimum of memory. Using this library, you can export arrays, collections and models to a XLSX-file from your Laravel application, and you can import data to Laravel application.

Features

Easily export models, collections and arrays to Excel
Export huge datasets with very fast, and using a minimum of memory
Сan create multiple sheets and supports basic column, row and cell styling
You can set the height of the rows and the width of the columns (including auto width calculation)
Import workbooks and worksheets to Eloquent models very quickly and with minimal memory usage
Automatic field detection from imported table headers
Mapping import/export data
Supports auto formatter and custom formatter of datetime values for import data
The library can define and extract images from XLSX files
You can add active hyperlinks, formulas, notes, charts and images to output XLSX-files
Supports workbook and sheet protection with/without passwords
Supports page settings - page margins, page size

Installation

Install via composer:

composer require avadim/fast-excel-laravel

And then you can use facade Excel

// Create workbook...
$excel = \Excel::create();

// export model...
$excel->sheet()->withHeadings()->exportModel(Users::class);

// and save XLSX-file to default storage
$excel->saveTo('path/file.xlsx');

// or save file to specified disk
$excel->store('disk', 'path/file.xlsx');

// Open saved workbook
$excel = \Excel::open(storage_path('path/file.xlsx'));

// import records to database
$excel->withHeadings()->importModel(User::class);

Jump To:

Export Data
Import Data
More Features
Do you want to support FastExcelLaravel?

Export Data

Export a Model

Easy and fast export of a model. This way you export only model data without headers and without any styling

// Create workbook with sheet named 'Users'
$excel = \Excel::create('Users');

// Export all users to Excel file
$sheet->exportModel(Users::class);

$excel->saveTo('path/file.xlsx');

The following code will write the field names and styles (font and borders) to the first row, and then export all the data of the User model

// Create workbook with sheet named 'Users'
$excel = \Excel::create('Users');

// Write users with field names in the first row
$sheet->withHeadings()
    ->applyFontStyleBold()
    ->applyBorder('thin')
    ->exportModel(Users::class);

$excel->saveTo('path/file.xlsx');

Mapping Export Data

You can map the data that needs to be added as row

$sheet = $excel->getSheet();
$sheet->mapping(function($model) {
    return [
        'id' => $model->id, 'date' => $model->created_at, 'name' => $model->first_name . $model->last_name,
    ];
})->exportModel(User::class);
$excel->save($testFileName);

Export Any Collections and Arrays

// Create workbook with sheet named 'Users'
$excel = \Excel::create('Users');

$sheet = $excel->getSheet();
// Get users as collection
$users = User::where('age', '>', 35)->get();

// Write attribute names
$sheet->writeRow(array_keys(User::getAttributes()));

// Write all selected records
$sheet->writeData($users);

$sheet = $excel->makeSheet('Records');
// Get collection of records using Query Builder
$records = \DB::table('users')->where('age', '>=', 21)->get(['id', 'name', 'birthday']);
$sheet->writeData($records);

$sheet = $excel->makeSheet('Collection');
// Make custom collection of arrays
$collection = collect([
    [ 'id' => 1, 'site' => 'google.com' ],
    [ 'id' => 2, 'site.com' => 'youtube.com' ],
]);
$sheet->writeData($collection);

$sheet = $excel->makeSheet('Array');
// Make array and write to sheet
$array = [
    [ 'id' => 1, 'name' => 'Helen' ],
    [ 'id' => 2, 'name' => 'Peter' ],
];
$sheet->writeData($array);

$sheet = $excel->makeSheet('Callback');
$sheet->writeData(function () {
    foreach (User::cursor() as $user) {
        yield $user;
    }
});

Advanced Usage for Data Export

See detailed documentation for avadim/fast-excel-writer here: https://github.com/aVadim483/fast-excel-writer/tree/master#readme

$excel = \Excel::create('Users');
$sheet = $excel->getSheet();

// Set column B to 12
$sheet->setColWidth('B', 12);
// Set options for column C
$sheet->setColOptions('C', ['width' => 12, 'text-align' => 'center']);
// Set column width to auto
$sheet->setColWidth('D', 'auto');

$title = 'This is demo of avadim/fast-excel-laravel';
// Begin area for direct access to cells
$area = $sheet->beginArea();
$area->setValue('A2:D2', $title)
      ->applyFontSize(14)
      ->applyFontStyleBold()
      ->applyTextCenter();
      
// Write headers to area, column letters are case independent
$area
    ->setValue('a4:a5', '#')
    ->setValue('b4:b5', 'Number')
    ->setValue('c4:d4', 'Movie Character')
    ->setValue('c5', 'Birthday')
    ->setValue('d5', 'Name')
;

// Apply styles to headers
$area->withRange('a4:d5')
    ->applyBgColor('#ccc')
    ->applyFontStyleBold()
    ->applyOuterBorder('thin')
    ->applyInnerBorder('thick')
    ->applyTextCenter();
    
// Write area to sheet
$sheet->writeAreas();

// You can set value formats for some fields
$sheet->setFieldFormats(['birthday' => '@date', 'number' => '@integer']);

// Write data to sheet
$sheet->writeData($data);

// Save XLSX-file
$excel->saveTo($testFileName);

Import Data

Import a Model

To import models, you can use method importModel(). If the first row contains the names of the fields you can apply these using method withHeadings()

// Open XLSX-file 
$excel = Excel::open($file);

// Import a workbook to User model using the first row as attribute names
$excel->withHeadings()->importModel(User::class);

// Done!!!

You can define the columns or cells from which you will import

// Import row to User model from columns range A:B - only 'name' and 'birthday'
$excel->withHeadings()->importModel(User::class, 'A:B');

// Import from cells range
$excel->withHeadings()->importModel(User::class, 'B4:D7');

// Define top left cell only
$excel->withHeadings()->importModel(User::class, 'B4');

In the last two examples, we also assume that the first row of imported data (row 4) is the names of the attributes.

Mapping Import Data

However, you can set the correspondence between columns and field names yourself.

// Import row to User model from columns range B:E
$excel->mapping(function ($record) {
    return [
        'id' => $record['A'], 'name' => $record['B'], 'birthday' => $record['C'], 'random' => $record['D'],
    ];
})->importModel(User::class, 'B:D');

// Define top left cell only
$excel->mapping(['B' => 'name', 'C' => 'birthday', 'D' => 'random'])->importModel(User::class, 'B5');

// Define top left cell only (shorter way)
$excel->importModel(User::class, 'B5', ['B' => 'name', 'C' => 'birthday', 'D' => 'random']);

Advanced Usage for Data Import

See detailed documentation for avadim/fast-excel-reader here: https://github.com/aVadim483/fast-excel-reader/tree/master#readme

$excel = Excel::open($file);

$sheet = $excel->getSheet('Articles');
$sheet->setReadArea('B5');
foreach ($sheet->nextRow() as $rowNum => $rowData) {
    $user = User::create([
        'name' => $rowData['B'],
        'birthday' => new \Carbon($rowData['C']),
        'password' => bcrypt($rowData['D']),
    ]);
    Article::create([
        'user_id' => $user->id,
        'title' => $rowData['E'],
        'date' => new \Carbon($rowData['F']),
        'public' => $rowData['G'] === 'yes',
    ]);
}

More Features

You can see more features for export in the documentation for FastExcelWriter.

You can see more features for import in the documentation for FastExcelReader)

Do you want to support FastExcelLaravel?

if you find this package useful you can support and donate to me for a cup of coffee:

USDT (TRC20) TSsUFvJehQBJCKeYgNNR1cpswY6JZnbZK7
USDT (ERC20) 0x5244519D65035aF868a010C2f68a086F473FC82b
ETH 0x5244519D65035aF868a010C2f68a086F473FC82b

Or just give me a star on GitHub :)

avadim / fast-excel-laravel

Maintainers

Details