Excelbundle Laravel Package

Getting Started

Minimal Steps

1. Installation: Add the bundle via Composer (note: requires Symfony2 compatibility):

   composer require liuggio/excelbundle
   

   For Laravel, manually register the service in a ServiceProvider (see Implementation Patterns).

2. First Use Case: Generate a simple Excel file in a Laravel controller:

   use Liuggio\ExcelBundle\Services\PHPExcelService;
   
   class ExcelController extends Controller {
       public function export() {
           $excel = app()->make(PHPExcelService::class);
           $phpExcelObject = $excel->createPHPExcelObject();
           $phpExcelObject->setActiveSheetIndex(0)
               ->setCellValue('A1', 'Hello, Laravel!')
               ->setCellValue('B1', 'ExcelBundle');
   
           $writer = $excel->createWriter($phpExcelObject, 'Excel5');
           $response = $excel->createStreamedResponse($writer);
   
           return $response->setContentDisposition(
               'attachment',
               'export.xlsx'
           );
       }
   }
   

3. Where to Look First:

   • Service Interface: Liuggio\ExcelBundle\Services\PHPExcelService (core methods: createPHPExcelObject(), createWriter(), createStreamedResponse()).
   • Examples: Check the fake controller for Symfony2 patterns.
   • PHPOffice Docs: PHPExcel Examples for advanced usage (charts, formulas, etc.).

Implementation Patterns

Usage Patterns

1. Data Export Workflow

• Generate Excel from Database:

  $excel = app()->make(PHPExcelService::class);
  $phpExcel = $excel->createPHPExcelObject();
  $sheet = $phpExcel->getActiveSheet();
  
  $users = User::all();
  $sheet->fromArray($users->toArray(), null, 'A1', true, false);
  
  return $excel->createStreamedResponse(
      $excel->createWriter($phpExcel, 'Excel2007')
  )->setContentDisposition('attachment', 'users.xlsx');
  

• Dynamic Column Headers:

  $headers = ['ID', 'Name', 'Email'];
  $sheet->fromArray([$headers], null, 'A1');
  $sheet->fromArray($users->pluck(['id', 'name', 'email'])->toArray(), null, 'A2');
  

2. Data Import Workflow

• Read Excel File:

  $excel = app()->make(PHPExcelService::class);
  $phpExcel = $excel->createPHPExcelObject('path/to/file.xlsx');
  $sheet = $phpExcel->getActiveSheet();
  $data = $sheet->toArray(null, true, true, true); // Associative arrays
  
  foreach ($data as $row) {
      User::create($row);
  }
  

3. Streaming Large Files

• Use Excel2007 writer for chunked processing:

  $writer = $excel->createWriter($phpExcel, 'Excel2007');
  $writer->setUseWriteCache(false); // Disable cache for large files
  $response = $excel->createStreamedResponse($writer);
  

4. Images and Styling

• Add images:

  $drawing = $excel->createPHPExcelWorksheetDrawing();
  $drawing->setPath(public_path('logo.png'))
          ->setCoordinates('A1')
          ->setWorksheet($sheet);
  

• Apply styles:

  $style = $sheet->getStyle('A1');
  $style->getFont()->setBold(true);
  $style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  

Integration Tips

Laravel-Specific Adaptations

1. Service Registration: Create a ExcelServiceProvider:

   use Liuggio\ExcelBundle\Services\PHPExcelService;
   use Symfony\Component\HttpFoundation\StreamedResponse;
   
   class ExcelServiceProvider extends ServiceProvider {
       public function register() {
           $this->app->singleton(PHPExcelService::class, function ($app) {
               $service = new PHPExcelService();
               // Mock Symfony's StreamedResponse if needed
               $service->setStreamedResponseFactory(function ($writer) {
                   return new StreamedResponse(function () use ($writer) {
                       $writer->save('php://output');
                   });
               });
               return $service;
           });
       }
   }
   

2. Dependency Conflicts:

   • If using spatie/laravel-excel or maatwebsite/excel, avoid mixing with this bundle (conflicting PHPOffice versions).
   • For PHP 8.x, patch PHPExcel or use PhpSpreadsheet via maatwebsite/excel.

Performance Optimizations

• For Large Datasets:

  • Use Excel2007 format (better compression).
  • Disable caching: $writer->setUseWriteCache(false).
  • Stream data in chunks (see Gotchas).

• Memory Management:

  • Clear PHPExcel objects after use:

    unset($phpExcel);
    gc_collect_cycles();
    

Common Extensions

1. Custom Templates: Load a template and merge data:

   $template = $excel->createPHPExcelObject('template.xlsx');
   $sheet = $template->getActiveSheet();
   $sheet->setCellValue('B5', 'Dynamic Data');
   

2. Dynamic Sheets:

   $phpExcel = $excel->createPHPExcelObject();
   foreach ($groups as $group) {
       $sheet = $phpExcel->createSheet();
       $sheet->setTitle($group->name);
       $sheet->fromArray($group->users->toArray());
   }
   $phpExcel->removeSheetByIndex(0); // Remove default sheet
   

3. Charts:

   $sheet->getChartCollection()->createChart('chart1', 'pie');
   $series = $sheet->getChartCollection()->getChartByName('chart1')->createSeries();
   $series->addDataRange('B2:B10');
   

Gotchas and Tips

Pitfalls

1. Symfony Dependency Hell:

   • Error: Class 'Symfony\Component\HttpFoundation\StreamedResponse' not found.
   • Fix: Manually include Symfony components or mock StreamedResponse (see Integration Tips).

2. PHPExcel Deprecation:

   • Error: Class 'PHPExcel' not found or PHPExcel_Reader_Exception.
   • Fix: Use PhpSpreadsheet via maatwebsite/excel (recommended for Laravel). If stuck with this bundle:

     composer require phpoffice/phpexcel ~1.8.1
     

3. Memory Limits:

   • Error: Allowed memory size exhausted for files >5MB.
   • Fix:
     • Increase memory_limit in php.ini.
     • Use Excel2007 format and stream responses.
     • Process data in batches.

4. Encoding Issues:

   • Error: Garbled text in Excel (e.g., é instead of é).
   • Fix: Set UTF-8 encoding:

     $phpExcel->getProperties()->setCreator('UTF-8 Creator');
     $writer->setPreCalculateFormulas(false);
     $writer->setUseDiskCaching(false);
     

5. Large File Timeouts:

   • Error: Script times out during generation.
   • Fix:
     • Use setOutputEncoding('UTF-8') on the writer.
     • Offload to a queue (e.g., Laravel Queues) with shouldQueue() on the job.

6. Image Paths:

   • Error: Image not found when adding drawings.
   • Fix: Use absolute paths:

     $drawing->setPath(public_path('images/logo.png'));
     

Debugging Tips

1. Validate Excel Objects:

   if (!$phpExcel instanceof PHPExcel) {
       throw new \RuntimeException('Invalid PHPExcel object');
   }
   

2. Log Writer Errors:

   try {
       $writer->save('php://output');
   } catch (\Exception $e) {
       \Log::error('Excel export failed: ' . $e->getMessage());
       throw $e;
   }
   

3. Check File Permissions:

   • Ensure the web server (e.g., www-data) can read/write to the target directory.

Configuration Quirks

1. Writer Formats:
   • Excel5 (.xls):