Fast Excel Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require rap2hpoutre/fast-excel
   

   Add to config/app.php (optional for facade):

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

2. First Export:

   use Rap2hpoutre\FastExcel\Facades\FastExcel;
   
   // Export a model collection to XLSX
   FastExcel::data(User::all())->export('users.xlsx');
   

3. First Import:

   $data = FastExcel::import('users.xlsx');
   

Key Starting Points

• Facade: FastExcel::data($collection)->export('file.xlsx')
• Global Helper: fastexcel(User::all())->download('file.xlsx')
• Chunked Export: Use yield for large datasets (see Implementation Patterns).

Implementation Patterns

Core Workflows

1. Model/Collection Export

// Basic export
(new FastExcel(User::all()))->export('users.xlsx');

// With custom column mapping
(new FastExcel(User::all()))->export('users.xlsx', function ($user) {
    return [
        'Full Name' => $user->name,
        'Email'     => $user->email,
    ];
});

2. Database Import

// Import CSV and create records
$users = FastExcel::configureCsv(';', '"', 'gbk')
    ->import('users.csv', function ($line) {
        return User::create([
            'name'  => $line['Name'],
            'email' => $line['Email'],
        ]);
    });

3. Chunked Exports (Memory Efficiency)

// Generator for large datasets
function userGenerator() {
    foreach (User::cursor() as $user) {
        yield $user;
    }
}

// Export without memory spikes
(new FastExcel(userGenerator()))->export('large_export.xlsx');

4. Multi-Sheet Exports

use Rap2hpoutre\FastExcel\SheetCollection;

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

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

Integration Tips

• Laravel Queues: Offload large exports to background jobs:

  ExportUsersJob::dispatch(User::all());
  

  class ExportUsersJob implements ShouldQueue {
      public function handle() {
          (new FastExcel(User::all()))->export(storage_path('app/users.xlsx'));
      }
  }
  

• API Endpoints: Secure file downloads:

  public function exportUsers() {
      return (new FastExcel(User::all()))
          ->download('users.xlsx')
          ->setHeader('Content-Disposition', 'attachment; filename="users.xlsx"');
  }
  

• Validation: Sanitize imports with Laravel Validation:

  $data = FastExcel::import('file.xlsx');
  $validated = $data->map(function ($row) {
      return Validator::make($row, [
          'email' => 'required|email',
          'name'  => 'required|string',
      ])->validate();
  });
  

• Styling: Apply OpenSpout styles:

  use OpenSpout\Common\Entity\Style\Style;
  
  $headerStyle = (new Style())
      ->setFontBold()
      ->setBackgroundColor('DDDDDD');
  
  (new FastExcel(User::all()))
      ->headerStyle($headerStyle)
      ->export('styled_users.xlsx');
  

Gotchas and Tips

Pitfalls

1. PHP Version:

   • Dropped PHP 7.1 support in v3.0.0. Ensure your environment uses PHP 8+.
   • Fix: Update php.ini or use a Docker container with PHP 8.1+.

2. Memory Limits:

   • Even with chunking, memory_limit may still be hit for >10M rows.
   • Fix: Increase memory_limit in php.ini or split exports into smaller files.

3. CSV Encoding:

   • Default encoding is UTF-8. Non-UTF-8 files (e.g., GBK) may corrupt.
   • Fix: Use configureCsv():

     FastExcel::configureCsv(';', '"', 'gbk')->import('file.csv');
     

4. Sheet Names:

   • Multi-sheet exports truncate names to 31 characters (Excel limit).
   • Fix: Shorten names or use numeric indices:

     $sheets = new SheetCollection([1 => User::all(), 2 => Project::all()]);
     

5. Facade Limitations:

   • Facade loses constructor access (e.g., new FastExcel($data)).
   • Fix: Use FastExcel::data($data) or instantiate directly.

Debugging

• Import Errors:

  • Check for missing columns or type mismatches (e.g., null vs. empty string).
  • Tip: Log raw imported data:

    $data = FastExcel::import('file.xlsx');
    \Log::debug('Imported data:', $data->toArray());
    

• Export Corruption:

  • Ensure no open file handles during export (e.g., closed streams).
  • Tip: Use storage_path() for temporary files:

    (new FastExcel(User::all()))->export(storage_path('temp/users.xlsx'));
    

• Performance Bottlenecks:

  • CPU-bound: Spout is single-threaded. For >50K rows, consider parallel processing (e.g., Laravel Queues).
  • Tip: Profile with memory_get_usage():

    $start = memory_get_usage();
    (new FastExcel(User::all()))->export('file.xlsx');
    \Log::info('Memory used:', memory_get_usage() - $start);
    

Extension Points

1. Custom Writers:

   • Extend Rap2hpoutre\FastExcel\FastExcel to add new formats (e.g., JSON Excel).
   • Example:

     class JsonExcel extends FastExcel {
         public function exportJson($file) {
             // Custom logic using Spout's WriterInterface
         }
     }
     

2. Event Hooks:

   • Decorate FastExcel to add pre/post-export hooks:

     class HookedFastExcel extends FastExcel {
         public function export($file) {
             \Log::info('Export started');
             parent::export($file);
             \Log::info('Export finished');
         }
     }
     

3. Batch Processing:

   • Split large exports into multiple files (e.g., by date):

     User::chunk(1000, function ($users) {
         (new FastExcel($users))->export("users_${chunk}.xlsx");
     });
     

4. Testing:

   • Mock FastExcel in unit tests:

     $mock = Mockery::mock(FastExcel::class);
     $mock->shouldReceive('export')->once();
     

Configuration Quirks

• Spout Under the Hood:

  • FastExcel uses Spout v3, which may have breaking changes from v2.
  • Tip: Check Spout’s changelog for updates.

• Facade vs. Direct Usage:

  • Facade does not support constructor arguments (e.g., new FastExcel($data)).
  • Tip: Use FastExcel::data($data) or instantiate directly in tests.

• Global Helper:

  • fastexcel() shares state across calls (e.g., CSV config).
  • Tip: Reset config between uses:

    fastexcel()->configureCsv(';', '"', 'gbk')->import('file.csv');
    fastexcel()->resetCsvConfig()->import('file2.csv'); // Reset for UTF-8