Excel Laravel Package

Getting Started

Minimal Steps

1. Installation:

   composer require maatwebsite/excel
   

   Publish config (optional but recommended):

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

2. First Export:

   use Maatwebsite\Excel\Facades\Excel;
   use App\Exports\UsersExport;
   
   // In a controller
   return Excel::download(new UsersExport);
   

3. First Import:

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

Where to Look First

• Documentation: Laravel Excel Docs
• Quickstart: Exports and Imports
• Stub Files: vendor/maatwebsite/excel/stubs/ (for generating export/import classes)

First Use Case

Export a Collection to Excel:

php artisan make:export UsersExport --model=User

Edit the generated UsersExport.php:

public function collection()
{
    return User::all();
}

Use in a controller:

return Excel::download(new UsersExport);

Implementation Patterns

Core Workflows

1. Exports

• From Collections:

  public function collection()
  {
      return User::query()->where('active', 1)->get();
  }
  

• From Queries (Chunked for Performance):

  public function query()
  {
      return User::query()->where('active', 1);
  }
  

• From Views (Blade):

  public function view()
  {
      return view('users.table', ['users' => User::all()]);
  }
  

2. Imports

• Basic Import:

  public function model(array $row)
  {
      return new User([
          'name' => $row['name'],
          'email' => $row['email']
      ]);
  }
  

• Chunked Imports (Large Files):

  use Maatwebsite\Excel\Concerns\WithChunkReading;
  
  public function chunkSize(): int
  {
      return 1000;
  }
  

3. Queued Exports/Imports

• Queue Exports:

  use Maatwebsite\Excel\Concerns\WithQueueable;
  
  public function queueable(): bool
  {
      return true;
  }
  

  Trigger via:

  Excel::queue(new UsersExport);
  

• Queue Imports:

  Excel::queueImport(new UsersImport, request()->file('file'));
  

Integration Tips

1. Customizing Headers

use Maatwebsite\Excel\Concerns\WithHeadings;

public function headings(): array
{
    return [
        'Name', 'Email', 'Role'
    ];
}

2. Validation

use Maatwebsite\Excel\Concerns\WithValidation;

public function rules(): array
{
    return [
        'email' => 'required|email',
        'name' => 'required|min:3'
    ];
}

3. Styling

use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

public function styles(Worksheet $sheet)
{
    return [
        1 => ['font' => ['bold' => true]],
    ];
}

4. Events

use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;

public function registerEvents(): array
{
    return [
        AfterSheet::class => function(AfterSheet $event) {
            // Custom logic after sheet creation
        }
    ];
}

5. Testing

use Maatwebsite\Excel\Tests\TestCase;

public function testExport()
{
    $this->assertExport($this->exportClass, 'users.xlsx');
}

Advanced Patterns

1. Dynamic File Names

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithCustomStartCell;
use Maatwebsite\Excel\Concerns\WithTitle;

public function title(): string
{
    return 'Users_' . now()->format('Y-m-d');
}

public function fileName(): string
{
    return 'users_' . now()->format('Ymd') . '.xlsx';
}

2. Multi-Sheet Exports

use Maatwebsite\Excel\Concerns\WithMultipleSheets;

public function sheets(): array
{
    return [
        new ActiveUsersExport,
        new InactiveUsersExport
    ];
}

3. Upsert Logic

use Maatwebsite\Excel\Concerns\WithUpserts;

public function uniqueBy(): array
{
    return ['email'];
}

public function updateModel($row, $original)
{
    $original->name = $row['name'];
    $original->save();
}

4. Custom Cell Formatting

use Maatwebsite\Excel\Concerns\WithCustomStartCell;
use Maatwebsite\Excel\Concerns\WithFormatData;

public function formatData(array $row)
{
    $row['created_at'] = \Carbon\Carbon::parse($row['created_at'])->format('d/m/Y');
    return $row;
}

Gotchas and Tips

Pitfalls

1. Memory Limits:

   • Large exports/imports may hit memory limits. Use chunkSize() or queue jobs.
   • Example:

     public function chunkSize(): int
     {
         return 500; // Adjust based on server memory
     }
     

2. Time Zone Issues:

   • Ensure config/app.php has correct timezone. Excel exports may reflect server timezone by default.

3. CSV Delimiters:

   • Auto-detection may fail. Explicitly set delimiters for complex CSVs:

     use Maatwebsite\Excel\Concerns\WithCustomCsvSettings;
     
     public function getCsvSettings(): array
     {
         return [
             'delimiter' => ';',
             'enclosure' => '"',
             'line_ending' => "\n",
             'use_bom' => true,
             'input_encoding' => 'UTF-8',
             'output_encoding' => 'UTF-8'
         ];
     }
     

4. Sheet Name Limitations:

   • Excel has a 31-character limit for sheet names. Use:

     use Maatwebsite\Excel\Concerns\WithTitle;
     
     public function title(): string
     {
         return str_limit('Very Long Sheet Name', 31);
     }
     

5. Empty Rows in Imports:

   • SkipsEmptyRows may skip rows with null values. Customize with:

     use Maatwebsite\Excel\Concerns\SkipsEmptyRows;
     
     public function isEmptyWhen($row, $key)
     {
         return empty($row[$key]) && $row[$key] !== '0';
     }
     

6. Queue Failures:

   • Queued jobs may fail silently. Implement failed() in your export/import class:

     public function failed(\Throwable $e)
     {
         \Log::error('Export failed: ' . $e->getMessage());
         // Notify admin via email, etc.
     }
     

Debugging Tips

1. Log Rows During Import:

   use Maatwebsite\Excel\Concerns\WithBatchInserts;
   use Maatwebsite\Excel\Concerns\WithHeadingRow;
   
   public function batchSize(): int
   {
       \Log::info('Processing batch');
       return 100;
   }
   

2. Inspect PhpSpreadsheet Object:

   use Maatwebsite\Excel\Concerns\WithEvents;
   use Maatwebsite\Excel\Events\AfterSheet;
   
   public function registerEvents(): array
   {
       return [
           AfterSheet::class => function(AfterSheet $event) {
               \Log::info($event->sheet->getTitle());
               \Log::info($event->sheet->getHighestRow());
           }
       ];
   }
   

3. Test with Small Datasets:

   • Use ->limit(10) in queries to debug before scaling up.

4. Check Temporary Files:

   • Large imports may leave temporary files. Clean up with:

     php artisan excel:clean
     

Extension Points

1. Custom Value Binders:

   use Maatwebsite\Excel\Concerns\