🦉 Fast Excel import/export for Laravel

Overview

Version License Build Status Scrutinizer Code Quality Codacy Badge Total Downloads

Fast Excel import/export for Laravel, thanks to Spout. See benchmarks below.

Quick start

Install via composer:

composer require rap2hpoutre/fast-excel

Export a Model to .xlsx file:

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

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

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

Export

Export a Model or a Collection:

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

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

Export xlsx, ods and csv:

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

Export only some attributes specifying columns names:

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

Download (from a controller method):

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

Import

import returns a Collection:

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

Import a csv with specific delimiter, enclosure characters and "gbk" encoding:

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

Import and insert to database:

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

Facades

You may use FastExcel with the optional Facade. Add the following line to config/app.php under the aliases key.

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

Using the Facade, you will not have access to the constructor. You may set your export data using the data method.

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

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

Global helper

FastExcel provides a convenient global helper to quickly instantiate the FastExcel class anywhere in a Laravel application.

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

Advanced usage

Export multiple sheets

Export multiple sheets by creating a SheetCollection:

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

Use index to specify sheet name:

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

Import multiple sheets

Import multiple sheets by using importSheets:

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

You can also import a specific sheet by its number:

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

Export large collections with chunk

Export rows one by one to avoid memory_limit issues using yield:

function usersGenerator() {
    foreach (User::cursor() as $user) {
        yield $user;
    }
}

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

Add header and rows style

Add header and rows style with headerStyle and rowsStyle methods.

$header_style = (new StyleBuilder())->setFontBold()->build();

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

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

Why?

FastExcel is intended at being Laravel-flavoured Spout: a simple, but elegant wrapper around Spout with the goal of simplifying imports and exports. It could be considered as a faster (and memory friendly) alternative to Laravel Excel, with less features. Use it only for simple tasks.

Benchmarks

Tested on a MacBook Pro 2015 2,7 GHz Intel Core i5 16 Go 1867 MHz DDR3. Testing a XLSX export for 10000 lines, 20 columns with random data, 10 iterations, 2018-04-05. Don't trust benchmarks.

Average memory peak usage Execution time
Laravel Excel 123.56 M 11.56 s
FastExcel 2.09 M 2.76 s

Still, remember that Laravel Excel has many more features.

Comments
  • Could not open file to import

    Could not open file to import

    I cannot read my imported file, here is my function:

     public function import(Request $request)
        {
            $products = (new FastExcel)->import($request->file('file'), function ($reader) {
                return Product::create([
                    'title' => $reader['title'],
                    'slug' => $reader['slug'],
                    'imageOne' => $reader['imageOne'],
                    'imageTwo' => $reader['imageTwo'],
                    'imageThree' => $reader['imageThree'],
                    'short_description' => $reader['short_description'],
                    'description' => $reader['description'],
                    'stock' => $reader['stock'],
                    'price' => $reader['price'],
                    'installation_price' => $reader['installation_price'],
                    'meta_description' => $reader['meta_description'],
                    'meta_tags' => $reader['meta_tags'],
                    'discounted_price' => $reader['discounted_price'],
                    'start_discounted' => $reader['start_discounted'],
                    'end_discounted' => $reader['end_discounted'],
                    'demo_link' => $reader['demo_link'],
                    'status' => $reader['status'],
                    'category_id' => $reader['category_id']
                ]);
            });
    
            Session::flash('success', 'Your products imported successfully.');
        }
    

    error i get:

    Could not open C:\Users\usename\AppData\Local\Temp\php32D9.tmp for reading! (Could not open C:\Users\usename\AppData\Local\Temp\php32D9.tmp for reading.)
    

    PS: I have tried documents way: using return Product::create(['title' => $reader['title'],........ instead of foreach, `same result (same error).

    any idea?

    question 
    opened by robertnicjoo 24
  • Refactoring & Return nested Sheet Collection instead of flat Rows

    Refactoring & Return nested Sheet Collection instead of flat Rows

    My shot on fixing #17.

    There are still a couple of things to discuss about this repo's strategy, and the resulting import() method signature will become convoluted as features get added. But it's still 0.2.1, so that should be fine and understandable.

    After merging this, I would suggest to work on how the lib is instantiated. I'm thinking maybe pulling the $path variable into FastExcel.php itself so that the method changes to (new FastExcel('myfile.xlsx'))->doSomething. I can see how you wanted to maintain flexibility as to when an import reads from (an export method saves to) a path, but as mentioned in my comment there are some caveats to my current approach.

    Furthermore interesting to delve into would be passing the respective callbacks into the FastExcel object using public void setSheetListener($callback) or similar methods and removing those from the import() method signature. But I didn't develop that yet as I wanted to hear what you think.

    I'll be happy to hear back from you!

    enhancement question on hold 
    opened by Harti 22
  • Using chunk in collection and export to Excel

    Using chunk in collection and export to Excel

    Hi Thanks for this package, it is extremely fast,

    I have problems with memory exhausted in exporting over 300K, rows.

    I need to chunk the exported data, to save memory, how can this be done.?

    enhancement help wanted priority-high 
    opened by Elshaden 17
  • how can i break in FastExcel loop

    how can i break in FastExcel loop

    after i import a xls, the loop is working .but i want stop the loop and get some params for the next step. i cannot find a solution , someone may help me ,thx!

    opened by mengdodo 9
  • Specify export/download path & download not working

    Specify export/download path & download not working

    Hello there, Thanks for this wonderful package of yours and it's simplicity.

    I am a beginner to laravel and php as a whole. I am using laravel v5.6.

    I used the awesome package and realized that the exported files can be found on the public folder, I don't see in your readme where I can specify where the files should be exported to.

    e.g. if I want to store it in a path like /public/storage/export/file.xlsx

    Secondly, If I use ->download() as shown below, nothing happens, nothing was either exported or downloaded.

    return (new FastExcel($data))->download('Transaction_'.date('d_M_Y-h-i-s-A').'.xlsx'); I expect the above to download the file for me...

    The two question remains, specify path url & how to make the file downloadable for the user.

    Below is my code in my controller.

    ` private function export($data){

        //Modifying the $data Content that we want to export to Excel.
        $data->transform(function ($txn) {
            
            if ($txn['status'] == '00') {
                $txn['status'] = 'Successful';
            }elseif ($txn['status'] == '02') {
                $txn['status'] = 'Pending';
            }elseif ($txn['status'] == '03') {
                $txn['status'] = 'Failed';
            }elseif ($txn['status'] == '04') {
                $txn['status'] = 'Refunded';
            }
    
            if(isset($txn['mid'])){
                $auth = Auth::where('merchantID', $txn['mid'])->get();
    
                if($auth->count() > 0){
                    $auth = $auth->first();
                    $merchant_name =  $auth->merchant_name;
                }
            }
    
            return [
                'Merchant Name' => $merchant_name,
                'Transaction ID' => $txn['txnRef'],
                'Currency' => $txn['currency'],
                'Amount' => $txn['value'],
                'Customer Name' => $txn['user_fname'].' '.$txn['user_lname'],
                'Customer Email' => $txn['user_email'],
                'Type' => $txn['paymentType'],
                'Status' => $txn['status'],
                'Date' => $txn['updated_at'],
            ];
    
        });
    
        //Export the transactions
        return (new FastExcel($data))->export('Transaction_'.date('d_M_Y-h-i-s-A').'.xlsx');
    
    }
    

    `

    Oh, and I forgot to add that all these are on localhost.

    Thanks in Advance.

    opened by solamichealolawale 9
  • Styling rows and header

    Styling rows and header

    First step, just two methods:

    • apply a style to all rows
    • apply a style to header row

    Something like styleRows and styleHeader (or maybe rowsStyle and headerStyle...)

    See: http://opensource.box.com/spout/docs/#styling-rows

    enhancement Hacktoberfest 
    opened by rap2hpoutre 8
  • Error after upgrading to PHP 8 (ERR_INVALID_RESPONSE)

    Error after upgrading to PHP 8 (ERR_INVALID_RESPONSE)

    Hi there, after upgrading from PHP 7.4 to PHP 8, I got an error local.ERROR: During class fetch: Uncaught ErrorException: The magic method Box\Spout\Common\Singleton::__wakeup() must have public visibility in C:\Users\jadam\projects\zdrap\vendor\box\spout\src\Spout\Common\Singleton.php:39

    How to fix this, please?

    bug resolved in v3 
    opened by jadamec 7
  • Sloooooow Exports

    Sloooooow Exports

    I have a question, I have set this up hoping exporting would be faster, but I am finding that there is something that is just slow in the process.... It is taking sometimes 30+ Minutes to process 1200 records to export

    I run this job in the background, as server command

    ` (new FastExcel($this->getClicksOneByOne($query))) ->headerStyle($header_style) ->export($this->job_data['file_path']);

    function getClicksOneByOne($click_records) {
            foreach($click_records->cursor() as $click) {
                yield $this->map($click);
            }
    }
    

    ` $this->map, maps the data from the query into the proper column, while also returning a readable text header rather then the column name...

    When it's done doing its thing, it makes it available for download on the dashboard and emails the user the file....

    Everything works fine, except how long it is taking... and I really do not know why... especially for a puny number like 1200 records.

    I also tried on the same record set, not using the generator, same result, I even tried it without the map function with no changes in the time to actually generate.

    I do not believe this is a memory issue, as I watch memory usage during execution, remain within my server normal numbers. and no errors are throw/recorded and eventually the job usually does finish.

    Anyone have some ideas?

    opened by zhxscript 6
  • Error when importing excel file

    Error when importing excel file

    i got this error when importing excel file to mysql database. weird thing is, the data is imported succesfully in my mysql database but it still throw that error page.

    SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '' for column 'plandate' at row 1 (SQL: insert into plannings (staffno, plant_id, plandate, workcentre, partno, partdesc, model, itemgroup, oph, status, planout, updated_at, created_at) values (M0542, 1, , ?, ?, ?, ?, ?, ?, Not Started, ?, 2019-08-02 10:17:03, 2019-08-02 10:17:03))

    here is my function on controller. $plannings = (new FastExcel)->import($request->file, function ($line) { return Planning::Create([ 'staffno' => Auth::user()->staffno, 'plant_id' => Auth::user()->plant_id, 'plandate' => $line['PLAN DATE'], 'workcentre' => $line['WORK CENTRE'], 'partno' => $line['PART NUMBER'], 'partdesc' => $line['PART DESCRIPTION'], 'model' => $line['MODEL'], 'itemgroup' => $line['ITEM GROUP'], 'oph' => $line['CT/SPH'], 'status' => 'Not Started', 'planout' => $line['PLAN OUTPUT'], ]); });

    Thanks

    question 
    opened by nikshakirin 6
  • Set Maximum row & download multiple file excel

    Set Maximum row & download multiple file excel

    Hi, thank you before for this plugin very helping me, i want to ask how to i can set maximum Row & download multiple file, example : I have 500 row data collection and i want to divide 150, so i have 4 file and download it. Thank you

    opened by cybersoftmedia 6
  • Download on safari

    Download on safari

    Hi,

    I use it for download an export of table basically. It work perfectly on google chrome but on safari, he had .html after my .xlsx

    have you an idea ? Thanks

    bug question 
    opened by yoan1005 6
  • Support PHP 8.2 (update `openspout/openspout`)

    Support PHP 8.2 (update `openspout/openspout`)

    This package uses dependency openspout/openspout of the old major version v3 that doesn't support PHP 8.2

    https://github.com/rap2hpoutre/fast-excel/blob/724557e844170a231fd4515bbc3ea435ca622e7d/composer.json#L15

    openspout/openspout supports PHP 8.2 from https://github.com/openspout/openspout/releases/tag/v4.8.0

    Partially duplicate of the https://github.com/rap2hpoutre/fast-excel/issues/288 (#288 is a single solution to solve PHP 8.2 issue)

    opened by lptn 0
  • HOW TO GET MY FORMULA IN EXCEL (.xlsx FORMAT)?

    HOW TO GET MY FORMULA IN EXCEL (.xlsx FORMAT)?

    Hello , Thanks for this module, very helpful, i try import data using 10.000 data , need only 2 second, but i found difficulty to get my formula in my excel, please help me

    opened by dimaskumara11 0
  • Large collection with chunk

    Large collection with chunk

    I read https://github.com/rap2hpoutre/fast-excel#export-large-collections-with-chunk but I want use this on usual laravel collection collect without Eloquent. How to do it ? Now I receive error "Unsupported type for ..."

    opened by pietrach 0
  • How to apply different columns

    How to apply different columns

    I am trying to export to an xls file, there is multiple tabs in this export, but it uses the same set of columns, even though each sheet within the export does not contain the same columns

    below is an example SheetCollection, is there a way using the FastExcel ->export to set what columns each array within that SheetCollection?

    $sheets = new SheetCollection([
        'colors' => [
            'favorite' => 'green',
            'favorite' => 'blue',
            'favorite' => 'orange',
        ],
        'food' => [
            'type'  => 'italian',
            'type'  => 'french',
        ],
        'dogs' => [
            'name' => 'ruffus',
            'name' => 'marley'
        ]
    ]);
    

    Trying to find a way so all 3 tabs in the spreadsheet use the appropriate columns per tab.

    Expected outcome:

    3 tabs colors tab would have one column: favorite food tab would have one column: type dogs tab would have one column: name

    opened by bkmorse 1
Releases(v4.1.0)
Owner
Raphaël Huchet
Tinkering @ beta.gouv.fr & fabrique.social.gouv.fr. Stodgy mixture of Rust, JS, PHP, Forth, SQL, GFA Basic, music and more.
Raphaël Huchet
Laravel Excel Export & Import example

Laravel Excel Export & Import example It's pretty easy to export to Excel and import from Excel, with great Laravel Excel package. This package superc

Syed Arsalan Ahmed 1 Oct 26, 2021
Merge Excel Files to single excel file per columns

Merge Excel Files to single excel file per columns

Max Base 3 Apr 26, 2021
Parse and retrieve data from old format Excel XLS files. MS Excel 97 workbooks PHP reader.

SimpleXLS class 0.9.15 Parse and retrieve data from old Excel .XLS files. MS Excel 97-2003 workbooks PHP reader. PHP BIFF reader. No additional extens

Sergey Shuchkin 157 Nov 23, 2022
Data import to excel without any package

About Laravel Laravel is a web application framework with expressive, elegant syntax. We believe development must be an enjoyable and creative experie

Dilshodbek 2 May 30, 2022
🚀 Supercharged Excel exports and imports in Laravel

Supercharged Excel exports and imports A simple, but elegant Laravel wrapper around PhpSpreadsheet exports and imports. Quickstart · Documentation · V

Maatwebsite 11.2k Dec 3, 2022
Simple yet powerful Excel manipulation library for PHP 5.4+

| | \ / \_/ __ /^\ __ ' `. \_/ ,' ` \/ \/ _,--./| |\.--._ _,' _.-\_/-._ `._ | / \ | |

Wisembly 68 Sep 20, 2022
Simplexcel.php - Easily read / parse / convert / write between Microsoft Excel XML / CSV / TSV / HTML / JSON / etc spreadsheet tabular file formats

Simple Excel Easily parse / convert / write between Microsoft Excel XML / CSV / TSV / HTML / JSON / etc formats For further deatails see the GitHuib P

Faisal Salman 547 Nov 24, 2022
You can convert any table to CSV/EXCEL file.

Convert MySQL to EXCEL You can convert any table to CSV/EXCEL file by this code. In this repository I've used Link1 and Link2 . Simply edit config.php

Arsalan 1 Dec 25, 2021
PhpSpreadsheet - a library written in pure PHP and offers a set of classes that allow you to read and write various spreadsheet file formats such as Excel and LibreOffice Calc

PhpSpreadsheet PhpSpreadsheet is a library written in pure PHP and offers a set of classes that allow you to read and write various spreadsheet file f

PHPOffice 11.7k Dec 3, 2022
ExcelAnt - Simple yet powerful Excel manipulation library for PHP 5.4+

ExcelAnt is an Excel manipulation library for PHP 5.4. It currently works on top of PHPExcel. If you want to add / use another library, feel free to fork and contribute !

Wisembly 68 Sep 20, 2022
PHPExcelFormatter is class to make getting data from Excel documents simpler.

PHPExcelFormatter PHPExcelFormatter is class to make getting data from Excel documents simpler. Read columns what you really need Set column names for

Rene Korss 4 Apr 28, 2022
Read and write spreadsheet files (CSV, XLSX and ODS), in a fast and scalable way

Spout Spout is a PHP library to read and write spreadsheet files (CSV, XLSX and ODS), in a fast and scalable way. Contrary to other file readers or wr

Box 4.2k Dec 6, 2022
Read and write spreadsheet files (CSV, XLSX and ODS), in a fast and scalable way

OpenSpout OpenSpout is a community driven fork of box/spout, a PHP library to read and write spreadsheet files (CSV, XLSX and ODS), in a fast and scal

null 206 Nov 25, 2022
CSV files from Eloquent model in seconds - a Laravel package.

LaraCSV A Laravel package to easily generate CSV files from Eloquent model. Basic usage $users = User::get(); // All users $csvExporter = new \Laracsv

Muhammad Usman 605 Sep 6, 2022
an excel export/import tool for laravel based on php-xlswriter

Laravel-xlswriter 一款基于xlswriter的laravel扩展包 php-xlswriter是一款高性能的excel读写扩展,laravel-xlswriter基于该扩展做了封装,旨在提供一个便于使用的xlswriter的laravel工具包。 目前laravel-xlswrit

lysice 54 Dec 3, 2022
Laravel Excel Export & Import example

Laravel Excel Export & Import example It's pretty easy to export to Excel and import from Excel, with great Laravel Excel package. This package superc

Syed Arsalan Ahmed 1 Oct 26, 2021
Improve default Magento 2 Import / Export features - cron jobs, CSV , XML , JSON , Excel

Improve default Magento 2 Import / Export features - cron jobs, CSV , XML , JSON , Excel , mapping of any format, Google Sheet, data and price modification, improved speed and a lot more!

Firebear Studio 174 Dec 3, 2022
Merge Excel Files to single excel file per columns

Merge Excel Files to single excel file per columns

Max Base 3 Apr 26, 2021
Parse and retrieve data from old format Excel XLS files. MS Excel 97 workbooks PHP reader.

SimpleXLS class 0.9.15 Parse and retrieve data from old Excel .XLS files. MS Excel 97-2003 workbooks PHP reader. PHP BIFF reader. No additional extens

Sergey Shuchkin 157 Nov 23, 2022
This project is about import CSV to Laravel with Laravel Excel library.

About Laravel Laravel is a web application framework with expressive, elegant syntax. We believe development must be an enjoyable and creative experie

Abraham Víctor Zaragoza Rodríguez 1 Nov 20, 2021