Xls Bundle Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require arodiss/xls-bundle
   

   Ensure Symfony\Process and phpoffice/phpexcel are installed (handled automatically via Composer).

2. Enable the Bundle (if not auto-discovered): Add to config/bundles.php:

   Arodiss\XlsBundle\ArodissXlsBundle::class => ['all' => true],
   

3. First Use Case: Read an XLS File Inject the reader service and read a file:

   use Symfony\Component\DependencyInjection\ContainerInterface;
   
   class ExcelController
   {
       private $container;
   
       public function __construct(ContainerInterface $container)
       {
           $this->container = $container;
       }
   
       public function readExcel()
       {
           $reader = $this->container->get('arodiss.xls.reader');
           $content = $reader->readAll('/path/to/file.xls');
           // $content is an array of rows (e.g., [["Header1", "Header2"], ["Data1", "Data2"]])
       }
   }
   

Implementation Patterns

Common Workflows

1. Reading Excel Files

   • Small Files: Use readAll() for simplicity:

     $reader->readAll('/path/to/file.xls');
     

   • Large Files: Use iterators to avoid memory issues:

     $iterator = $reader->getReadIterator('/path/to/file.xls');
     foreach ($iterator as $row) {
         // Process $row (e.g., [["Col1", "Col2"], ["Value1", "Value2"]])
     }
     

   • Performance-Critical Files: Use the Python-backed reader (requires xlrd/openpyxl):

     $pythonReader = $this->container->get('arodiss.xls.reader.python');
     $iterator = $pythonReader->getReadIterator('/path/to/large_file.xls');
     

2. Writing Excel Files

   • Buffered Writing (Recommended):

     $writer = $this->container->get('arodiss.xls.writer.buffered');
     $writer->create('/path/to/output.xlsx', ['Name', 'Email']); // Header row
     foreach ($users as $user) {
         $writer->appendRow('/path/to/output.xlsx', [$user->name, $user->email]);
     }
     $writer->flush(); // Write all data at once
     

   • Direct Writing (Avoid for Large Data): Use arodiss.xls.writer instead of buffered for non-buffered writes (not recommended for performance).

3. Exporting from Controllers Generate and return an XLSX file:

   $builder = $this->container->get('arodiss.xls.builder');
   $filePath = $builder->buildXlsFromArray([
       ['Header1', 'Header2'],
       ['Data1', 'Data2']
   ]);
   
   $response = new Response(file_get_contents($filePath));
   $response->headers->set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
   $response->headers->set('Content-Disposition', 'attachment;filename=export.xlsx');
   return $response;
   

4. Integration with Forms/Validation

   • Validate uploaded XLS files before processing:

     $file = $request->files->get('excel_file');
     if ($file && $file->getClientOriginalExtension() === 'xls') {
         $reader = $this->container->get('arodiss.xls.reader');
         $data = $reader->readAll($file->getPathname());
         // Validate $data (e.g., check required columns)
     }
     

Gotchas and Tips

Pitfalls

1. Memory Limits with readAll()

   • Issue: Large files (>1MB) may exhaust memory.
   • Fix: Always use iterators (getReadIterator()) for large files.

2. Python Reader Dependencies

   • Issue: The Python-backed reader requires xlrd (for .xls) and openpyxl (for .xlsx).
   • Fix: Install via pip:

     pip install xlrd openpyxl
     

   • Note: Error handling is rudimentary; wrap calls in try-catch blocks.

3. Write Performance

   • Issue: Direct writes (arodiss.xls.writer) are slow for large datasets.
   • Fix: Use buffered writer to minimize disk I/O.

4. File Format Quirks

   • Issue: Read operations may work for non-.xlsx formats (e.g., .xls, .csv), but no guarantees.
   • Fix: Stick to .xlsx for consistency. Convert files beforehand if needed.

5. Temporary File Handling

   • Issue: buildXlsFromArray() creates temp files; ensure they’re cleaned up.
   • Fix: Use unlink() or Symfony’s Filesystem component to delete temp files post-download.

Debugging Tips

1. Check PHPExcel Errors

   • Enable PHPExcel logging for diagnostics:

     PHPExcel_Settings::setLogFile('phpexcel.log');
     

2. Validate Python Reader Output

   • Compare output between PHPExcel and Python readers for edge cases (e.g., merged cells, formulas).

3. Monitor Buffer Usage

   • For buffered writes, log buffer size to avoid hitting memory limits:

     $writer->setMaxBufferSize(1000); // Limit rows in memory
     

Extension Points

1. Custom Cell Formatting

   • Extend PHPExcel styles via the builder:

     $builder->getPHPExcelObject()->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
     

2. Pre/Post-Processing Hooks

   • Override services to add logic (e.g., data transformation):

     # config/services.yaml
     services:
         arodiss.xls.reader:
             class: App\Service\CustomXlsReader
             decorates: 'arodiss.xls.reader'
             arguments: ['@arodiss.xls.reader.inner']
     

3. Batch Processing

   • Chain iterators with Symfony’s Iterator utilities for complex workflows:

     use Symfony\Component\Iterator\FilterIterator;
     
     $filteredIterator = new FilterIterator($reader->getReadIterator('/file.xls'), function($row) {
         return $row[0] === 'TargetValue';
     });