Phpspreadsheet Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require phpoffice/phpspreadsheet
   

   Requires PHP 8.1+ and PHP Zip extension (for .xlsx/.ods support).

2. First Use Case: Generate a simple .xlsx file with sample data:

   use PhpOffice\PhpSpreadsheet\Spreadsheet;
   use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
   
   $spreadsheet = new Spreadsheet();
   $sheet = $spreadsheet->getActiveSheet();
   $sheet->setCellValue('A1', 'Hello, PhpSpreadsheet!');
   $writer = new Xlsx($spreadsheet);
   $writer->save('hello.xlsx');
   

3. Key Entry Points:

   • Spreadsheet: Root object (creates new files).
   • Writer\*: Handles file output (e.g., Xlsx, Csv).
   • Reader\*: Handles file input (e.g., Xlsx, Csv).

4. Where to Look First:

   • Official Docs (API reference + tutorials).
   • GitHub Issues for common pitfalls.
   • examples/ directory in the repo for practical examples.

Implementation Patterns

Core Workflows

1. Reading Spreadsheets

• Basic Read:

  use PhpOffice\PhpSpreadsheet\IOFactory;
  
  $spreadsheet = IOFactory::load('input.xlsx');
  $sheetData = $spreadsheet->getActiveSheet()->toArray();
  

• Iterate Rows:

  foreach ($spreadsheet->getActiveSheet()->getRowIterator() as $row) {
      $cellIterator = $row->getCellIterator();
      $cellIterator->setIterateOnlyExistingCells(false); // Include empty cells
      foreach ($cellIterator as $cell) {
          echo $cell->getValue();
      }
  }
  

2. Writing Spreadsheets

• From Arrays:

  $data = [
      ['Name', 'Age'],
      ['John', 30],
      ['Jane', 25]
  ];
  $sheet->fromArray($data, null, 'A1'); // null = default style, A1 = top-left cell
  

• Batch Cell Updates:

  $sheet->fromArray(
      ['Product', 'Price'],
      null,
      'A1',
      false, // Don't overwrite existing data
      false // Don't add new rows
  );
  

3. Styling

• Conditional Formatting:

  $sheet->getStyle('A1:A10')
        ->getConditionalFormatting()
        ->createConditionalFormat('greaterThan')
        ->setFormula(['value' => 50])
        ->getFill()
        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
        ->getStartColor()
        ->setARGB('FFFF0000'); // Red
  

• Reuse Styles:

  $style = $sheet->getStyle('A1');
  $sheet->getStyle('B1')->applyFromArray($style->getConditionalFormatting());
  

4. Formulas

• Dynamic References:

  $sheet->setCellValue('A1', '=SUM(B1:B10)');
  $sheet->setCellValue('B1', 10);
  $sheet->setCellValue('B2', 20);
  // ... update B1-B10, formula auto-adjusts
  

• Named Ranges:

  $sheet->getNamedRange('SalesData')->setRefersTo('Sheet1!$B$1:$B$100');
  

5. Merging/Protection

• Merge Cells:

  $sheet->mergeCells('A1:D1');
  $sheet->setCellValue('A1', 'Merged Header');
  

• Protect Sheet:

  $sheet->getProtection()->setSheet(true);
  $sheet->getProtection()->setPassword('secret');
  

Integration Tips

• Laravel Storage: Use Laravel’s Storage facade to save files to disk:

  use Illuminate\Support\Facades\Storage;
  
  Storage::disk('public')->put('reports/report.xlsx', $writer->saveToString());
  

• Queued Jobs: Offload heavy spreadsheet generation to queues:

  dispatch(new GenerateReportJob($data, $filename));
  

• Validation: Validate input data before writing to sheets to avoid corrupt files.

Gotchas and Tips

Pitfalls

1. Memory Management:

   • Issue: Large spreadsheets (>100K rows) may cause Allowed memory exhausted errors.
   • Fix:
     • Use setMemoryCacheSize() to limit cache size:

       $spreadsheet->getMemoryCacheSize() > 0 ? $spreadsheet->getMemoryCacheSize() : 1024; // 1MB
       

     • Stream output for .xlsx/ods:

       $writer->setUseMemoryCache(false);
       $writer->save('large_file.xlsx');
       

2. Formula References:

   • Issue: Copying formulas with copyformula() may fail if source cell is deleted.
   • Fix: Use absolute references ($A$1) or validate cells exist first.

3. Cell Caching:

   • Issue: Detached cell references (e.g., $cell = $sheet->getCell('A1');) break if the sheet is modified later.
   • Fix: Re-fetch cells after modifications:

     $cell = $sheet->getCell('A1'); // Re-fetch
     

4. Time Zones/Dates:

   • Issue: Dates may render incorrectly due to timezone mismatches.
   • Fix: Set Excel’s date system explicitly:

     $sheet->getStyle('A1')->getNumberFormat()->setFormatCode('yyyy-mm-dd');
     

5. CSV Delimiters:

   • Issue: CSV files may use ; or \t as delimiters, breaking parsing.
   • Fix: Specify delimiter when reading:

     $reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
     $reader->setDelimiter(';');
     

6. Large File Handling:

   • Issue: .xlsx files >20MB may time out or fail to upload.
   • Fix: Use chunked writing or compress output:

     $writer->setCompression(\PhpOffice\PhpSpreadsheet\Writer\Xlsx::COMPRESSION_MAX);
     

Debugging Tips

• Formula Errors:
  • Use getCell('A1')->getCalculatedValue() to debug formula results.
  • Check for circular references with:

    $spreadsheet->getActiveSheet()->getCell('A1')->getFormula();
    

• Style Conflicts:
  • Reset styles before applying new ones:

    $sheet->getStyle('A1')->applyFromArray(['font' => ['bold' => true]]);
    

• Performance:
  • Profile with memory_get_usage() and microtime() to identify bottlenecks.

Extension Points

1. Custom Writers:
   • Extend Writer\AbstractWriter to support new formats (e.g., .xlsb).
2. Cell Value Binders:
   • Implement PhpOffice\PhpSpreadsheet\Cell\ValueBinderInterface for custom data types (e.g., UUIDs).
3. Event Listeners:
   • Use PhpOffice\PhpSpreadsheet\Event\BeforeSave to modify files pre-save:

     $spreadsheet->getEventDispatcher()->addListener(
         \PhpOffice\PhpSpreadsheet\Event\BeforeSave::class,
         function ($event) {
             $event->getSpreadsheet()->getActiveSheet()->setTitle('Modified');
         }
     );
     

4. Reader Plugins:
   • Add support for proprietary formats by extending Reader\AbstractReader.

Configuration Quirks

• Default Font:
  • Override globally:

    $spreadsheet->getDefaultStyle()->getFont()->setName('Arial');
    

• Auto-Filter:
  • Enable for ranges:

    $sheet->getStyle('A1:C10')->getAutoFilter()->setAutoFilter(true);
    

• Hyperlinks:
  • Set with:

    $sheet->getCell('A1')->getHyperlink()->setUrl('https://example.com');
    

Pro Tips

• Template Files: Load a template `.