Excel Laravel Package

Getting Started

Minimal Steps

1. Installation:

   composer require maatwebsite/excel
   

   Publish config:

   php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
   

2. First Export (Collection):

   use Maatwebsite\Excel\Facades\Excel;
   use App\Exports\UsersExport;
   
   // In a controller
   return Excel::download(new UsersExport, 'users.xlsx');
   

3. First Import (CSV/Excel):

   use App\Imports\UsersImport;
   
   // In a controller
   $import = new UsersImport;
   $import->import(request()->file('file'));
   

4. Key Files to Review:

   • config/excel.php (configurations, chunk sizes, etc.)
   • app/Exports/ (export classes)
   • app/Imports/ (import classes)
   • routes/web.php (for testing endpoints)

Implementation Patterns

Common Workflows

1. Exporting Data

• From Eloquent Query:

  use Maatwebsite\Excel\Concerns\FromQuery;
  
  class UsersExport implements FromQuery
  {
      public function query()
      {
          return User::query()->where('active', 1);
      }
  }
  

• From Collection:

  use Maatwebsite\Excel\Concerns\FromCollection;
  
  class UsersExport implements FromCollection
  {
      public function collection()
      {
          return User::active()->get();
      }
  }
  

• From View (Blade):

  use Maatwebsite\Excel\Concerns\FromView;
  
  class UsersExport implements FromView
  {
      public function view(): string
      {
          return 'users.table';
      }
  }
  

• Customizing Headers/Columns:

  use Maatwebsite\Excel\Concerns\WithHeadings;
  
  class UsersExport implements WithHeadings
  {
      public function headings(): array
      {
          return [
              'ID', 'Name', 'Email', 'Created At'
          ];
      }
  }
  

• Chunking for Large Exports:

  use Maatwebsite\Excel\Concerns\WithChunkReading;
  
  class UsersExport implements WithChunkReading
  {
      public function chunkSize(): int
      {
          return 1000; // Process 1000 rows at a time
      }
  }
  

• Queued Exports (Background Jobs):

  use Maatwebsite\Excel\Concerns\WithQueueable;
  
  class UsersExport implements WithQueueable
  {
      public function queueable(): array
      {
          return ['queue' => 'exports'];
      }
  }
  

2. Importing Data

• Basic Import:

  use Maatwebsite\Excel\Concerns\ToModel;
  use Maatwebsite\Excel\Concerns\WithHeadingRow;
  
  class UsersImport implements ToModel, WithHeadingRow
  {
      public function model(array $row)
      {
          return new User([
              'name' => $row['name'],
              'email' => $row['email']
          ]);
      }
  }
  

• Validation During Import:

  use Maatwebsite\Excel\Concerns\WithValidation;
  
  class UsersImport implements WithValidation
  {
      public function rules(): array
      {
          return [
              'name' => 'required|string|max:255',
              'email' => 'required|email'
          ];
      }
  }
  

• Chunked Imports (Large Files):

  use Maatwebsite\Excel\Concerns\WithChunkReading;
  
  class UsersImport implements WithChunkReading
  {
      public function chunkSize(): int
      {
          return 500;
      }
  }
  

• Queued Imports:

  use Maatwebsite\Excel\Concerns\WithQueueable;
  
  class UsersImport implements WithQueueable
  {
      public function queueable(): array
      {
          return ['queue' => 'imports'];
      }
  }
  

• Handling Failures:

  use Maatwebsite\Excel\Concerns\WithFailuresTable;
  
  class UsersImport implements WithFailuresTable
  {
      public function failuresTable(): string
      {
          return 'user_import_failures';
      }
  }
  

3. Advanced Patterns

• Dynamic Filters:

  use Maatwebsite\Excel\Concerns\WithCustomStartCell;
  
  class UsersExport implements WithCustomStartCell
  {
      public function startCell(): string
      {
          return 'A2'; // Skip header row
      }
  }
  

• Styling Exports:

  use Maatwebsite\Excel\Concerns\WithStyles;
  use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
  
  class UsersExport implements WithStyles
  {
      public function styles(Worksheet $sheet)
      {
          return [
              // Style the first row as bold text
              1 => ['font' => ['bold' => true]],
          ];
      }
  }
  

• Custom File Naming:

  use Maatwebsite\Excel\Concerns\WithCustomName;
  
  class UsersExport implements WithCustomName
  {
      public function filename(): string
      {
          return 'users-' . now()->format('Y-m-d') . '.xlsx';
      }
  }
  

• API Responses:

  return Excel::download(new UsersExport)
      ->setResponseHeader('Content-Disposition', 'attachment; filename="custom-name.xlsx"');
  

Gotchas and Tips

Pitfalls

1. Memory Limits:

   • Large exports/imports can hit memory limits. Use WithChunkReading and queue jobs for background processing.
   • Configure chunk_size in config/excel.php (default: 750).

2. Column Mismatches:

   • Ensure headings() in exports match the actual data columns to avoid misaligned data.
   • Use WithHeadingRow in imports to skip header rows.

3. Queued Jobs:

   • Queued exports/imports require the database queue driver or a supported queue (e.g., Redis).
   • Monitor job failures in Laravel Horizon or Telescope.

4. File Handling:

   • Temporary files aren’t always cleaned up. Implement handleFailure() in imports to manually clean up.
   • Avoid uploading files >2GB (PHP default upload limit).

5. Validation Errors:

   • Validation failures are stored in the failures table by default. Clear it manually or implement WithFailuresTable to customize.

6. Time Zone Issues:

   • Ensure config/excel.php has the correct timezone setting to avoid date/time discrepancies.

7. Special Characters:

   • Excel may corrupt files with special characters (e.g., *, ?, /). Sanitize data before export.

Debugging Tips

1. Log Chunk Progress:

   use Maatwebsite\Excel\Concerns\WithEvents;
   
   class UsersImport implements WithEvents
   {
       public function registerEvents(): array
       {
           return [
               'chunk.started' => function () {
                   \Log::info('Chunk started');
               },
               'chunk.finished' => function () {
                   \Log::info('Chunk finished');
               },
           ];
       }
   }
   

2. Inspect Raw Data:

   use Maatwebsite\Excel\Concerns\WithCustomCsvSettings;
   
   class UsersImport implements WithCustomCsvSettings
   {
       public function csvSettings(): array
       {
           return [
               'input_encoding' => 'UTF-8',
               'output_encoding' => 'UTF-8',
               'delimiter' => ',',
               'enclosure' => '"',
               'line_ending' => "\n",
               'use_bom' => false,
               'include_path' => true,
               'read_data_only' => false, // Set to true to debug raw data
           ];
       }
   }
   

3. Test with Small Files:

   • Use WithLimit to test imports with a subset of data:

     use Maatwebsite\Excel\Concerns\WithLimit;
     
     class UsersImport implements WithLimit
     {
         public function limit(): int
         {
             return 10; // Test with first 10 rows
         }
     }
     

4. Check PhpSpreadsheet Cache:

   • Clear cache if styles/colors appear incorrect:

     php artisan cache:clear
     

   • Configure caching in config/excel.php:

     'cache' => [
         'driver' => 'file',
         'path' => storage_path('framework/cache/data'),
     ],
     

Extension Points

1. Custom Concerns:
   • Create reusable concerns (e.g., WithCustomFooter):

     namespace App\Concerns;
     
     use Maatwebsite\Excel\