Excel Laravel Package

Getting Started

Minimal Steps

1. Installation:

   composer require maatwebsite/excel
   

   Publish the config:

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

2. First Export: Create a new export class:

   php artisan make:export UsersExport --model=User
   

   Modify the generated class (app/Exports/UsersExport.php):

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

   Use in a controller:

   use App\Exports\UsersExport;
   use Maatwebsite\Excel\Facades\Excel;
   
   public function export()
   {
       return Excel::download(new UsersExport, 'users.xlsx');
   }
   

3. First Import: Create an import class:

   php artisan make:import UsersImport
   

   Modify the generated class (app/Imports/UsersImport.php):

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

   Use in a controller:

   public function import(Request $request)
   {
       $request->validate([
           'file' => 'required|file',
       ]);
   
       Excel::import('UsersImport', $request->file('file'));
       return back()->with('success', 'Import successful!');
   }
   

Where to Look First

• Official Documentation (Quickstart, Exports, Imports, Concerns)
• Concerns (WithHeadingRow, WithValidation, WithChunkReading, etc.) in app/Imports/ or app/Exports/
• Config File (config/excel.php) for customization (e.g., chunk size, disk settings, CSV encoding)
• Artisan Commands (make:export, make:import) for scaffolding

Implementation Patterns

Common Workflows

1. Exporting Data

• From Collections/Queries:

  // app/Exports/PostsExport.php
  public function collection()
  {
      return Post::query()->where('published', true)->get();
  }
  

  // Controller
  return Excel::download(new PostsExport, 'posts.xlsx');
  

• From Views (Blade):

  // app/Exports/PostsViewExport.php
  public function view()
  {
      return view('posts.export', ['posts' => Post::all()]);
  }
  

  <!-- resources/views/posts/export.blade.php -->
  <table>
      @foreach($posts as $post)
          <tr>
              <td>{{ $post->title }}</td>
              <td>{{ $post->body }}</td>
          </tr>
      @endforeach
  </table>
  

• Chunked Exports (Large Datasets):

  // app/Exports/UsersExport.php
  use WithChunkReading;
  
  public function chunk($result)
  {
      return $result->chunk(1000);
  }
  

  // Controller (queued)
  return Excel::queue(new UsersExport)->download();
  

2. Importing Data

• Basic Import:

  // app/Imports/UsersImport.php
  use WithHeadingRow;
  
  public function model(array $row)
  {
      return new User([
          'name' => $row['name'],
          'email' => $row['email'],
      ]);
   }
  

• Validated Import:

  // app/Imports/UsersImport.php
  use WithValidation;
  
  public function rules()
  {
      return [
          'name' => 'required|string|max:255',
          'email' => 'required|email',
      ];
  }
  
  public function customValidationRules()
  {
      return [
          'email' => 'unique:users',
      ];
  }
  

• Chunked Imports (Large Files):

  // app/Imports/UsersImport.php
  use WithChunkReading;
  
  public function chunk($rows)
  {
      foreach ($rows as $row) {
          User::create([
              'name' => $row[0],
              'email' => $row[1],
          ]);
      }
  }
  

  // Controller (queued)
  Excel::queue(new UsersImport)->chunk(500, null, true)->process();
  

3. Advanced Patterns

• Upserting Data:

  // app/Imports/UsersImport.php
  use WithUpserts;
  
  public function model(array $row)
  {
      return User::updateOrCreate(
          ['email' => $row['email']],
          ['name' => $row['name']]
      );
  }
  

• Custom Styling:

  // app/Exports/UsersExport.php
  use WithStyles;
  
  public function styles(Sheet $sheet)
  {
      $sheet->getStyle('A1')->applyFromArray([
          'font' => ['bold' => true],
          'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
      ]);
  }
  

• Event Handling:

  // app/Imports/UsersImport.php
  use BeforeImport, AfterImport;
  
  public function beforeImport()
  {
      Log::info('Starting import...');
  }
  
  public function afterImport()
  {
      Log::info('Import completed!');
  }
  

Integration Tips

1. Queue Exports/Imports: Use Excel::queue() for background processing with Laravel Queues. Example:

   Excel::queue(new LargeExport)->chain([
       new NotifyUserJob($user),
   ])->dispatch();
   

2. Custom Disk Storage: Configure temp_disk in config/excel.php to store temporary files:

   'temp_disk' => 's3',
   

3. Testing: Use the assertExportedInRaw() helper for unit tests:

   public function test_export()
   {
       $this->assertExportedInRaw(new UsersExport, 'users.xlsx');
   }
   

4. API Responses: Return Excel files with custom headers:

   return Excel::download(new UsersExport, 'users.xlsx', [
       'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
       'Cache-Control' => 'must-revalidate, post-check=0, pre-check=0',
   ]);
   

5. Laravel Nova Integration: Use the laravel-nova-excel package for Excel exports/imports in Nova.

Gotchas and Tips

Pitfalls

1. Memory Limits:

   • Large exports/imports can hit memory limits. Use WithChunkReading or queue jobs.
   • Example:

     // app/Exports/UsersExport.php
     use WithChunkReading;
     
     public function chunk($result)
     {
         return $result->cursor()->chunk(500);
     }
     

2. Column Mapping Issues:

   • Ensure heading rows match column indices in model(array $row) or rules().
   • Use WithHeadingRow to auto-map headers:

     use WithHeadingRow;
     
     public function model(array $row)
     {
         return new User([
             'name' => $row['name'], // Matches heading 'name'
         ]);
     }
     

3. Validation Failures:

   • Failed rows are logged but not stopped by default. Use SkipsErrors to skip or WithValidation to halt on failure.
   • Example:

     // app/Imports/UsersImport.php
     use WithValidation, SkipsErrors;
     
     public function rules()
     {
         return [
             'email' => 'required|email|unique:users',
         ];
     }
     

4. Timezone Issues:

   • Dates in Excel may shift due to timezone differences. Use WithDateFormats:

     // app/Exports/UsersExport.php
     use WithDateFormats;
     
     protected function headings(): array
     {
         return [
             'Created At',
         ];
     }
     
     protected function dateFormats(): array
     {
         return [
             'Created At' => 'Y-m-d H:i:s',
         ];
     }
     

5. File Locking:

   • Large imports can lock files. Use WithRetry or queue chunks:

     Excel::queue(new UsersImport)->chunk(200, null, true)->retry(3)->process();
     

6. Special Characters:

   • Excel may corrupt UTF-8 or special characters. Use WithEncoding:

     // config/excel.php
     'csv' => [
         'encoding