PHP code example of rap2hpoutre / fast-excel

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

rap2hpoutre / fast-excel example snippets

use Rap2hpoutre\FastExcel\FastExcel;
use App\User;

// Load users
$users = User::all();

// Export all users
(new FastExcel($users))->export('file.xlsx');

$list = collect([
    [ 'id' => 1, 'name' => 'Jane' ],
    [ 'id' => 2, 'name' => 'John' ],
]);

// export() returns the absolute path to the written file
$path = (new FastExcel($list))->export('file.xlsx');

$invoices = App\Invoice::orderBy('created_at', 'DESC')->get();
(new FastExcel($invoices))->export('invoices.csv');

(new FastExcel(User::all()))->export('users.csv', function ($user) {
    return [
        'Email' => $user->email,
        'First Name' => $user->firstname,
        'Last Name' => strtoupper($user->lastname),
    ];
});

(new FastExcel(User::all()))->hideColumnsPrefixedWith()->export('users.csv', function ($user) {
    return [
        'Name'  => $user->name,
        '_role' => $user->role, // used for logic below, never written to the file
    ];
});

return (new FastExcel(User::all()))->download('file.xlsx');

$collection = (new FastExcel)->import('file.xlsx');

$collection = (new FastExcel)->configureCsv(';', '#', 'gbk')->import('file.csv');

$users = (new FastExcel)->import('file.xlsx', function ($line) {
    return User::create([
        'name' => $line['Name'],
        'email' => $line['Email']
    ]);
});

$collection = (new FastExcel)->limitRows(100)->import('file.xlsx');

// Headers from row 1, data from row 155 onwards.
$collection = (new FastExcel)->headerRow(1)->startRow(155)->import('file.xlsx');

$chunk = (new FastExcel)
    ->headerRow(1)
    ->startRow(2 + ($page * 1000)) // data begins on row 2
    ->limitRows(1000)
    ->import('file.xlsx');

$collection = (new FastExcel)->limitColumns('H')->import('file.xlsx');
$collection = (new FastExcel)->limitColumns(8)->import('file.xlsx');

$collection = (new FastExcel)->onlyColumns(['A', 'B', 'H'])->import('file.xlsx');
$collection = (new FastExcel)->onlyColumns([1, 2, 8])->import('file.xlsx');

'FastExcel' => Rap2hpoutre\FastExcel\Facades\FastExcel::class,

$list = collect([
    [ 'id' => 1, 'name' => 'Jane' ],
    [ 'id' => 2, 'name' => 'John' ],
]);

FastExcel::data($list)->export('file.xlsx');

$collection = fastexcel()->import('file.xlsx');
fastexcel($collection)->export('file.xlsx');

$sheets = new SheetCollection([
    User::all(),
    Project::all()
]);
(new FastExcel($sheets))->export('file.xlsx');

$sheets = new SheetCollection([
    'Users' => User::all(),
    'Second sheet' => Project::all()
]);

$sheets = (new FastExcel)->importSheets('file.xlsx');

$users = (new FastExcel)->sheet(3)->import('file.xlsx');

$users = (new FastExcel)->sheet('Users')->import('file.xlsx');

$sheets = (new FastExcel)->withSheetsNames()->importSheets('file.xlsx');

$sheets = (new FastExcel)
    ->withSheetContext()
    ->importSheets('file.xlsx', function ($sheetName, $row) {
        if ($sheetName === 'Users' && empty($row['email'])) {
            return null; // skip rows without an email on the Users sheet
        }

        return $row + ['_sheet' => $sheetName];
    });

// Export consumes only a few MB, even with 10M+ rows.
(new FastExcel(User::cursor()))->export('users.xlsx');

function rowsGenerator() {
    foreach (some_paginated_source() as $row) {
        yield $row;
    }
}

(new FastExcel(rowsGenerator()))->export('test.xlsx');

// Memory stays flat regardless of the number of rows.
(new FastExcel)->import('file.xlsx', function ($line) {
    User::create([
        'name'  => $line['Name'],
        'email' => $line['Email'],
    ]);

    return null; // don't keep the row in memory
});

use Illuminate\Support\LazyCollection;

(new FastExcel)->importLazy('file.xlsx')
    ->chunk(1000)
    ->each(function (LazyCollection $chunk) {
        User::insert($chunk->all());
    });

use OpenSpout\Common\Entity\Style\Style;

$header_style = (new Style())->setFontBold();

$rows_style = (new Style())
    ->setFontSize(15)
    ->setShouldWrapText()
    ->setBackgroundColor("EDEDED");

return (new FastExcel($list))
    ->headerStyle($header_style)
    ->rowsStyle($rows_style)
    ->download('file.xlsx');

use OpenSpout\Common\Entity\Style\Color;
use OpenSpout\Common\Entity\Style\Style;

return (new FastExcel($list))
    ->setHeaderColumnStyles([
        0 => (new Style())->setBackgroundColor(Color::YELLOW),
        1 => (new Style())->setFontColor(Color::BLUE),
    ])
    ->download('file.xlsx');

(new FastExcel($list))
    ->configureOptionsUsing(function ($options) {
        $options->setColumnWidth(40, 1);      // first column
        $options->setColumnWidth(15, 2, 3);   // second and third columns
    })
    ->export('file.xlsx');

(new FastExcel($list))
    ->configureOptionsUsing(function ($options) {
        $options->setColumnWidthForRange(20, 1, 4); // columns 1 through 4
    })
    ->export('file.xlsx');

->configureOptionsUsing(function ($options) {
    if (method_exists($options, 'setColumnWidth')) {
        $options->setColumnWidth(40, 1);
    }
})

// 0660123456 stays "0660123456" instead of becoming 660123456
(new FastExcel($users))->stringValues()->export('users.xlsx');

(new FastExcel($users))
    ->stringValues()                                  // text by default
    ->setColumnFormat([
        'id'    => 'number',                          // keep as number
        'phone' => 'string',                          // keep as text
    ])
    ->export('users.xlsx');

(new FastExcel($rows))->escapeFormulas()->export('file.xlsx');

(new FastExcel($users))->export('file.xlsx', function ($user) {
    return $user->active ? ['name' => $user->name, 'email' => $user->email] : null;
});

use OpenSpout\Common\Entity\Cell;
use OpenSpout\Common\Entity\Style\Style;

$users = collect([
    ['name' => 'John', 'note' => Cell::fromValue('paid', (new Style())->setFontBold())],
    ['name' => 'Jane', 'note' => 'pending'],
]);

(new FastExcel($users))->export('users.xlsx');