Simple Excel Laravel Package

Getting Started

Minimal Steps

1. Installation:

   composer require spatie/simple-excel
   

2. First Use Case - Reading a CSV:

   use Spatie\SimpleExcel\SimpleExcelReader;
   
   SimpleExcelReader::create('path/to/file.csv')
       ->getRows()
       ->each(function (array $row) {
           // Process each row
       });
   

3. First Use Case - Writing a CSV:

   use Spatie\SimpleExcel\SimpleExcelWriter;
   
   SimpleExcelWriter::create('path/to/output.csv')
       ->addRow(['name' => 'John', 'email' => 'john@example.com'])
       ->close();
   

4. Key Documentation:

   • Laravel LazyCollections (for reading)
   • OpenSpout (underlying library)

Implementation Patterns

Reading Workflows

1. Basic Row Processing:

   SimpleExcelReader::create('users.csv')
       ->getRows()
       ->each(fn(array $row) => User::create($row));
   

2. Chunk Processing for Large Files:

   SimpleExcelReader::create('large_file.xlsx')
       ->chunk(100, fn(array $rows) => User::insert($rows));
   

3. Transforming Data:

   SimpleExcelReader::create('data.csv')
       ->getRows()
       ->map(fn(array $row) => [
           'full_name' => $row['first_name'] . ' ' . $row['last_name'],
           'email' => strtolower($row['email']),
       ]);
   

4. Multi-Sheet Handling:

   $sheetNames = SimpleExcelReader::create('report.xlsx')->getSheetNames();
   foreach ($sheetNames as $sheet) {
       SimpleExcelReader::create('report.xlsx')
           ->fromSheetName($sheet)
           ->getRows()
           ->each(fn(array $row) => /* ... */);
   }
   

Writing Workflows

1. Batch Writing:

   $writer = SimpleExcelWriter::create('output.xlsx');
   foreach ($users as $user) {
       $writer->addRow($user->toArray());
   }
   $writer->close();
   

2. Streaming to Browser:

   SimpleExcelWriter::streamDownload('export.xlsx')
       ->addRows($users->map(fn($user) => $user->toArray())->toArray())
       ->toBrowser();
   

3. Dynamic Headers:

   $writer = SimpleExcelWriter::create('dynamic.xlsx')
       ->addHeader(['name', 'email', 'created_at'])
       ->addRows($users->map(fn($user) => [
           $user->name,
           $user->email,
           $user->created_at->format('Y-m-d'),
       ])->toArray());
   $writer->close();
   

4. Custom Formatting:

   $writer = SimpleExcelWriter::create('formatted.xlsx')
       ->formatHeadersUsing(fn($header) => ucfirst($header))
       ->addRows($data);
   $writer->close();
   

Integration Tips

1. Laravel Artisan Commands:

   use Spatie\SimpleExcel\SimpleExcelReader;
   
   class ImportUsersCommand extends Command
   {
       protected $signature = 'import:users {file}';
       public function handle()
       {
           SimpleExcelReader::create($this->argument('file'))
               ->getRows()
               ->each(fn(array $row) => User::create($row));
       }
   }
   

2. API Endpoints:

   Route::post('/import', function (Request $request) {
       $request->validate(['file' => 'required|file']);
       $file = $request->file('file')->store('temp');
   
       SimpleExcelReader::create(storage_path("app/{$file}"))
           ->getRows()
           ->each(fn(array $row) => User::create($row));
   
       return response()->json(['status' => 'imported']);
   });
   

3. Queue Jobs:

   class ImportUsersJob implements ShouldQueue
   {
       use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;
   
       public function handle()
       {
           SimpleExcelReader::create(storage_path('app/import.csv'))
               ->chunk(50, fn(array $rows) => User::insert($rows));
       }
   }
   

4. Service Providers:

   class ExcelServiceProvider extends ServiceProvider
   {
       public function register()
       {
           $this->app->singleton(ExcelReader::class, fn() => new SimpleExcelReader());
       }
   }
   

Gotchas and Tips

Pitfalls

1. Memory Leaks with Large Files:

   • Issue: Forgetting to call close() on SimpleExcelWriter can cause memory leaks.
   • Fix: Always call $writer->close() or use streamDownload() for large files.

2. LazyCollection Pitfalls:

   • Issue: Chaining methods like filter() or map() on LazyCollection can lead to unexpected behavior if not used with getRows().
   • Fix: Ensure you call getRows() before chaining methods:

     SimpleExcelReader::create('file.csv')
         ->getRows()
         ->filter(fn(array $row) => $row['active'])
         ->each(/* ... */);
     

3. Excel Formula Handling:

   • Issue: Formulas are evaluated by default. Use keepFormulas() to preserve them.
   • Fix:

     SimpleExcelReader::create('formulas.xlsx')
         ->keepFormulas()
         ->getRows();
     

4. DateTime Handling:

   • Issue: Dates are converted to DateTimeImmutable by default. Use preserveDateTimeFormatting() to keep original formatting.
   • Fix:

     SimpleExcelReader::create('dates.xlsx')
         ->preserveDateTimeFormatting()
         ->getRows();
     

5. Empty Rows:

   • Issue: Empty rows are skipped by default. Use preserveEmptyRows() to include them.
   • Fix:

     SimpleExcelReader::create('file.xlsx')
         ->preserveEmptyRows()
         ->getRows();
     

6. Sheet Indexing:

   • Issue: fromSheet() uses 0-based indexing. Off-by-one errors can occur.
   • Fix: Verify sheet count first:

     $sheetNames = SimpleExcelReader::create('file.xlsx')->getSheetNames();
     $sheetIndex = array_search('Sheet1', $sheetNames);
     

Debugging Tips

1. Inspect Headers:

   $headers = SimpleExcelReader::create('file.csv')->getHeaders();
   dd($headers);
   

2. Log Rows:

   SimpleExcelReader::create('file.csv')
       ->getRows()
       ->take(5)
       ->each(fn(array $row) => \Log::info($row));
   

3. Check File Paths:

   • Ensure paths are absolute or relative to the Laravel root:

     $path = storage_path('app/imports/file.csv');
     

4. Validate Data:

   SimpleExcelReader::create('file.csv')
       ->getRows()
       ->each(fn(array $row) => validator($row, ['email' => 'required|email'])->validate());
   

Configuration Quirks

1. Custom OpenSpout Configuration:

   • Pass custom OpenSpout configurations via withOpenSpoutConfig():

     SimpleExcelReader::create('file.xlsx')
         ->withOpenSpoutConfig([
             'open_spout_config' => [
                 'readerType' => 'XLSX',
                 'chunkSize' => 1000,
             ],
         ]);
     

2. CSV Delimiters:

   • Override delimiters for custom CSV formats:

     SimpleExcelReader::create('custom.csv')
         ->setDelimiter(';')
         ->getRows();
     

3. Excel Encoding:

   • Specify encoding for Excel files:

     SimpleExcelReader::create('file.xlsx')
         ->setEncoding('UTF-8');
     

Extension Points

1. Custom Row Processing:

   • Use map() or transform() on LazyCollection:

     SimpleExcelReader::create('file.csv')
         ->getRows()
         ->map(fn(array $row) => [
             'name' => strtoupper($row['name']),
             'email' => strtolower($row['email']),
         ]);
     

2. Pre-Process Headers:

   • Use formatHeadersUsing() for custom header transformations:

     SimpleExcelReader::create('file.csv')
         ->formatHeadersUsing(fn($header) => str_replace(' ', '_', $header))
         ->getRows();
     

3. **Post-Process Rows