Big Xlsx Bundle Laravel Package

Getting Started

Minimal Steps

1. Installation:

   composer require boenrobot/big-xlsx-bundle
   

   Register the bundle in config/bundles.php (Symfony 4+) or AppKernel.php (Symfony 2/3):

   Boenrobot\BigXlsxBundle\BoenrobotBigXlsxBundle::class => ['all' => true],
   

2. First Use Case: Generate a simple XLSX file with one sheet:

   use Boenrobot\BigXlsxBundle\Service\BigXlsxService;
   
   $service = $this->get('boenrobot_big_xlsx.service');
   $data = [["id", "name"], [1, "Test"]];
   $service->addSheet(0, "Sheet 1", $data);
   $file = $service->getFile();
   return new BinaryFileResponse($file, 200, ['Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet']);
   

Where to Look First

• Service Container: BigXlsxService is the core interface for sheet management.
• README: Focus on Basic Usage and Adding a Custom Sheet sections.
• PhpSpreadsheet Integration: Understand how getPHPExcel() exposes the underlying library for advanced use.

Implementation Patterns

Core Workflow

1. Data Preparation:

   • Structure data as an associative array (rows as arrays, columns as keys/values).
   • Example:

     $data = [["id", "name"], [1, "Alice"], [2, "Bob"]];
     

2. Sheet Management:

   • Add Sheets: Use addSheet($index, $name, $data) for pre-defined sheets.
   • Dynamic Sheets: Use getPHPExcel() to create sheets programmatically:

     $phpExcel = $service->getPHPExcel();
     $phpExcel->createSheet()->setCellValue('A1', 'Custom Data');
     

3. Memory Efficiency:

   • Chunked Writing: For large datasets, write data in batches (e.g., 1000 rows at a time) to avoid memory spikes.
   • Streaming: Use getFile() to stream the file directly to the client:

     $response = new StreamedResponse(function () use ($file) {
         echo $file->getContent();
     });
     $response->setCallback(null);
     $response->send();
     

4. Integration with Controllers:

   • Symfony Controller Example:

     public function exportAction() {
         $service = $this->get('boenrobot_big_xlsx.service');
         $data = $this->fetchLargeDataset(); // Assume this returns an array
         $service->addSheet(0, "Data Export", $data);
         return $this->file($service->getFile(), 'export.xlsx');
     }
     

5. Styling and Formatting:

   • Use getPHPExcel() to apply styles:

     $phpExcel = $service->getPHPExcel();
     $sheet = $phpExcel->getActiveSheet();
     $sheet->getStyle('A1')->getFont()->setBold(true);
     

Advanced Patterns

• Multi-Sheet Merging: Combine multiple datasets into separate sheets:

  $service->addSheet(0, "Sheet 1", $data1);
  $service->addSheet(1, "Sheet 2", $data2);
  

• Conditional Logic: Dynamically generate sheets based on user input or data analysis:

  if ($userRequestedSheet) {
      $service->addSheet(2, "Custom Sheet", $filteredData);
  }
  

• Template Inheritance: Use getPHPExcel() to clone a template sheet and populate it with data:

  $template = $this->loadTemplateExcel();
  $phpExcel = $service->getPHPExcel();
  $phpExcel->createSheet()->setCellValue('A1', 'Dynamic Data');
  

Gotchas and Tips

Pitfalls

1. Memory Leaks:

   • Issue: Large datasets in memory before writing to disk.
   • Fix: Use chunked data loading or streaming. Avoid loading entire datasets into arrays.

2. Sheet Index Conflicts:

   • Issue: Adding sheets with duplicate indices overwrites previous sheets.
   • Fix: Ensure unique indices or use getPHPExcel()->createSheet() for dynamic sheets.

3. PhpSpreadsheet Deprecations:

   • Issue: The bundle uses phpexcel (legacy), which may conflict with newer phpspreadsheet versions.
   • Fix: Pin phpexcel to a stable version in composer.json:

     "require": {
         "phpexcel/phpexcel": "1.8.*"
     }
     

4. File Handling:

   • Issue: getFile() returns a temporary file that may not persist after script execution.
   • Fix: Save the file to disk or stream it immediately:

     $file = $service->getFile();
     file_put_contents('export.xlsx', $file->getContent());
     

5. Encoding Issues:

   • Issue: Special characters (e.g., é, ü) may render incorrectly.
   • Fix: Explicitly set encoding when writing:

     $writer = \PhpOffice\PhpExcel\IOFactory::createWriter($phpExcel, 'Excel2007');
     $writer->setPreCalculateFormulas(false);
     $writer->save('export.xlsx');
     

Debugging Tips

1. Check Sheet Count:

   • Verify sheets were added correctly:

     $phpExcel = $service->getPHPExcel();
     echo $phpExcel->getSheetCount(); // Should match expected count
     

2. Inspect Data Structure:

   • Ensure data is a 2D array (rows as arrays, columns as values):

     var_dump($data); // Should be [ [col1, col2], [val1, val2], ... ]
     

3. Log Warnings:

   • Enable PhpSpreadsheet logging to debug issues:

     \PhpOffice\PhpExcel\Settings::setLogLevel(\PhpOffice\PhpExcel\Logger::LOG_DEBUG);
     

Extension Points

1. Custom Writers:

   • Extend the bundle to support additional file formats (e.g., CSV) by modifying the writer logic in BigXlsxService.

2. Event Listeners:

   • Hook into sheet creation to apply global styles or validation:

     $service->getPHPExcel()->registerCallback(function($sheet) {
         $sheet->getStyle('A1:Z1')->applyFromArray([
             'font' => ['bold' => true]
         ]);
     });
     

3. Dependency Injection:

   • Override BigXlsxService to customize behavior (e.g., add encryption):

     # config/services.yaml
     Boenrobot\BigXlsxBundle\Service\BigXlsxService:
         arguments:
             $customOption: '%kernel.project_dir%/config/xlsx_template.xlsx'
     

4. Performance Optimization:

   • Implement lazy loading for datasets:

     $data = [];
     foreach ($largeDataset as $item) {
         $data[] = [$item->id, $item->name];
         if (count($data) >= 1000) {
             $service->addSheet($sheetIndex++, "Sheet_" . $sheetIndex, $data);
             $data = [];
         }
     }