Phpexcel Laravel Package

Getting Started

Minimal Setup

1. Installation Add the package via Composer:

   composer require agence-gw/phpexcel
   

   Ensure php-xml and php-zip extensions are enabled in your php.ini.

2. First Use Case: Reading a Spreadsheet

   use PhpOffice\PhpSpreadsheet\IOFactory;
   
   $spreadsheet = IOFactory::load('path/to/file.xlsx');
   $worksheet = $spreadsheet->getActiveSheet();
   $data = $worksheet->toArray();
   

   • Where to look first: The PhpSpreadsheet Documentation (this package is a fork of PHPExcel, but follows the same API).
   • Key classes:
     • IOFactory (for reading/writing files)
     • Spreadsheet (main container)
     • Worksheet (individual sheets)

Implementation Patterns

Common Workflows

1. Reading Data

• Batch Processing:

  $reader = IOFactory::createReader('Xlsx');
  $spreadsheet = $reader->load('large_file.xlsx');
  foreach ($spreadsheet->getActiveSheet()->getRowIterator() as $row) {
      $cellIterator = $row->getCellIterator();
      $cellIterator->setIterateOnlyExistingCells(false);
      $data[] = $cellIterator->current()->getValue();
  }
  

• Streaming Large Files (avoid memory issues):

  $reader = IOFactory::createReader('Xlsx');
  $reader->setReadDataOnly(true); // Optimize for read-only
  $spreadsheet = $reader->load('large_file.xlsx');
  

2. Writing Data

• Dynamic Sheets:

  $spreadsheet = new Spreadsheet();
  $sheet = $spreadsheet->createSheet();
  $sheet->setTitle('DynamicSheet');
  $sheet->fromArray($data, null, 'A1');
  $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
  $writer->save('output.xlsx');
  

• Styling Cells:

  $style = new Style();
  $style->getFont()->setBold(true);
  $spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($style->getConditionalStyles());
  

3. Integration with Laravel

• Service Provider: Bind the factory to the container:

  $this->app->bind('phpspreadsheet', function () {
      return new IOFactory();
  });
  

• Requests/Responses:

  // Export to Excel
  return Excel::download(function () use ($data) {
      $spreadsheet = new Spreadsheet();
      $sheet = $spreadsheet->getActiveSheet();
      $sheet->fromArray($data);
      return $spreadsheet;
  }, 'export.xlsx');
  

  (Note: Use maatwebsite/excel for Laravel-specific helpers, but this package works standalone.)

4. Merging/Combining Files

$spreadsheet = IOFactory::load('template.xlsx');
$newSheet = IOFactory::load('data.xlsx')->getActiveSheet();
$spreadsheet->addSheet($newSheet, 'ImportedData');
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('merged.xlsx');

Gotchas and Tips

Pitfalls

1. Memory Limits:

   • Large files (>10MB) may hit PHP’s memory_limit. Use setReadDataOnly(true) or chunk processing.
   • Fix: Increase memory_limit in php.ini or use a streaming approach.

2. Time Zones and Dates:

   • Dates may render incorrectly if the system timezone isn’t set.
   • Fix: Set timezone explicitly:

     date_default_timezone_set('UTC');
     

3. Formula Evaluation:

   • Formulas are evaluated on write, not read. Complex formulas may break silently.
   • Tip: Use setReadDataOnly(true) to skip evaluation during reads.

4. File Locking:

   • Writing to files while they’re open (e.g., in a web request) can cause corruption.
   • Fix: Use temporary files or ensure exclusive access.

5. Deprecated Methods:

   • Some methods (e.g., PHPExcel_ namespace) are deprecated. Prefer PhpOffice\PhpSpreadsheet\ namespace.

Debugging

• Check for Warnings: Enable error reporting:

  error_reporting(E_ALL);
  ini_set('display_errors', 1);
  

• Validate Data: Use isReadOnly() to check if a file is corrupted:

  $reader = IOFactory::createReader('Xlsx');
  if (!$reader->canRead('suspect.xlsx')) {
      throw new Exception('File is corrupted or unsupported.');
  }
  

Configuration Quirks

• Default Styles: Override global styles in app/Config/spreadsheet.php (if using Laravel):

  'default' => [
      'font' => ['name' => 'Arial', 'size' => 10],
  ],
  

• Custom Writers: Extend Writer\Xlsx for custom formats:

  class CustomXlsxWriter extends Writer\Xlsx {
      public function save($filename) {
          // Custom logic
          parent::save($filename);
      }
  }
  

Extension Points

1. Custom Cell Data Types: Override getCalculationEngine() to handle custom data:

   $spreadsheet->getCalculationEngine()->setCreateRangesFromCellReferences(true);
   

2. Event Listeners: Use PhpOffice\PhpSpreadsheet\Event\AfterCalculate for post-processing:

   $spreadsheet->getCalculationEngine()->addListener(
       new AfterCalculateListener($spreadsheet)
   );
   

3. Plugin System: Register custom functions:

   $spreadsheet->getCalculationEngine()->registerFunction('MYFUNC', 'MyCustomFunction');