Phpspreadsheet Laravel Package

Getting Started

Begin by installing via Composer:

composer require phpoffice/phpspreadsheet

The entry point is the PhpOffice\PhpSpreadsheet\Spreadsheet class. A minimal example:

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World');

$writer = new Xlsx($spreadsheet);
$writer->save('hello-world.xlsx');

For quick starts, check the official samples repo — especially 01_Simple.php and 02_Reading_writing_spreadsheet.php.

For ODS (OpenDocument Spreadsheet) support, use IOFactory::load($path) with the .ods extension to read files, now with improved style handling.

Implementation Patterns

Core Usage

• Report Generation: Stream dynamic Excel/CSV exports from web controllers:

  header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  header('Content-Disposition: attachment;filename="report.xlsx"');
  $writer->save('php://output');
  

• Data Import: Parse uploaded files (CSV/XLSX/ODS) using IOFactory::load($path) and iterate over getActiveSheet()->getRowIterator(). For ODS files, styles (fonts, fills, borders) are now preserved more accurately.

New Features (v5.5.0)

• Checkboxes: Add interactive checkboxes in XLSX/HTML exports:

  $checkbox = new \PhpOffice\PhpSpreadsheet\Style\Conditional\Checkbox();
  $sheet->getStyle('A1')->applyFromArray($checkbox->getConditionalStyles());
  

• External Images: Whitelist external images during import to control security:

  $reader = IOFactory::createReaderForFile('file.xlsx');
  $reader->setReadDataOnly(true);
  $reader->setWhitelistImages(true); // Allow external images
  $spreadsheet = $reader->load('file.xlsx');
  

• PDF Headers/Footers: Customize PDF exports with user-supplied headers/footers:

  $writer = new \PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf();
  $writer->setHeaderFooter(true);
  $writer->setHeader('&L&O Custom Header &P/&N');
  $writer->save('output.pdf');
  

• Formula Handling: Access old calculated values in toArray() or getRelatives():

  $cell = $sheet->getCell('A1');
  $data = $cell->getValue()['value']; // Original value
  $data = $cell->getValue()['oldCalculatedValue']; // Old calculated value (if available)
  

• HTML Writer Improvements: Configure line endings and save formulas as attributes:

  $writer = new \PhpOffice\PhpSpreadsheet\Writer\Html();
  $writer->setLineEnding("\n"); // Custom line ending
  $writer->setSaveFormulas(true); // Save formulas as data attributes
  $writer->save('output.html');
  

ODS-Specific Workflows

• Reading ODS: Improved style retention (fonts, fills, borders, alignment):

  $reader = IOFactory::createReaderForFile('file.ods');
  $spreadsheet = $reader->load('file.ods');
  // Styles (e.g., fonts, fills) are now preserved more accurately
  

• Writing ODS: Use the Ods writer for ODS exports (styles may differ from XLSX).

Performance Optimizations

• For large datasets, use setReadDataOnly(true) or chunked processing. The new value binders in v5.5.0 improve performance when reading/writing cells.

Gotchas and Tips

Breaking Changes (v5.5.0)

• Deprecated Features:
  • Writer/Html::BODY_LINE constant is removed (use setLineEnding() instead).
  • Unused classes/methods (FormulaParser, FormulaToken, insertBitMap, etc.) are deprecated (no replacement needed unless actively using them).

ODS-Specific Gotchas

• Style Inconsistencies: ODS files may still have quirks in style inheritance (e.g., partial column styles). Test exports thoroughly.
• Number Formats: ODS now handles number formats better, but validate critical data (e.g., dates) post-import:

  $value = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($cellValue);
  

Formula and Calculation Tips

• Union Functions: Fixed handling of UNION in formulas (e.g., =SUM(UNION(A1:A10,B1:B10))).
• CONCATENATE: Resolved edge cases where CONCATENATE behaved unexpectedly with mixed data types.
• Old Calculated Values: Useful for auditing changes, but requires explicit opt-in:

  $cell->getValue()['oldCalculatedValue'] ?? null;
  

Image and Security Tips

• External Images: Whitelisting is now configurable, but validate sources to avoid XSS/CSRF risks:

  $reader->setWhitelistImages(['https://trusted.cdn.com']);
  

• Image Sizing: Fixed CSS size handling for images (now in millimeters):

  $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
  $drawing->setHeight(10); // Millimeters
  

PDF and HTML Exports

• Headers/Footers: Customize PDF exports, but test rendering in target browsers (e.g., Chrome vs. Firefox).
• HTML Formulas: Saving formulas as attributes may break client-side JavaScript. Use sparingly:

  $writer->setSaveFormulas(false); // Default (safer)
  

Debugging and Performance

• Memory Usage: Monitor large ODS/XLSX files—styles and images can bloat memory. Use setReadDataOnly(true) for reads.
• Formula Errors: Enable strict error handling:

  \PhpOffice\PhpSpreadsheet\Settings::setErrorHandlerCallback(function($error) {
      throw new \RuntimeException($error);
  });
  

• ODS Validation: Cross-check ODS imports with XLSX exports for style/format discrepancies.

Extension Points

• Custom Writers: Extend Writer\AbstractWriter for new formats (e.g., Markdown tables).
• Style Overrides: Override default styles via Style\StyleCollection for consistent branding.