Spreadsheet Parser Bundle Laravel Package

Getting Started

Minimal Setup

1. Installation

   composer require akeneo-labs/spreadsheet-parser-bundle
   

   Add to config/bundles.php (Symfony 4+):

   return [
       // ...
       Akeneo\Bundle\SpreadsheetParserBundle\AkeneoSpreadsheetParserBundle::class => ['all' => true],
   ];
   

   For Symfony <4, register in AppKernel.php.

2. First Use Case Parse an .xlsx file in a service:

   use Akeneo\Bundle\SpreadsheetParserBundle\Loader\SpreadsheetLoaderInterface;
   
   class SpreadsheetService
   {
       public function __construct(private SpreadsheetLoaderInterface $loader) {}
   
       public function parseFile(string $filePath): array
       {
           $spreadsheet = $this->loader->open($filePath);
           $worksheet = $spreadsheet->getWorksheet(0); // First sheet
   
           $data = [];
           foreach ($worksheet->createRowIterator() as $rowIndex => $row) {
               $data[] = $row;
           }
           return $data;
       }
   }
   

3. Key Classes

   • SpreadsheetLoaderInterface: Loads files (injected via DI).
   • WorksheetInterface: Represents a sheet; use getWorksheetIndex() or getWorksheet().
   • RowIterator: Iterates rows efficiently (avoids loading entire file into memory).

Implementation Patterns

Core Workflow

1. Dependency Injection Always inject SpreadsheetLoaderInterface (auto-wired in Symfony). Avoid ContainerAwareInterface (deprecated in modern Symfony).

2. Sheet Handling

   • Use getWorksheetIndex() to find sheets by name (case-sensitive).
   • Fallback to getWorksheet(0) for the first sheet if unsure.
   • Example:

     $worksheetIndex = $spreadsheet->getWorksheetIndex('Products');
     $worksheet = $spreadsheet->getWorksheet($worksheetIndex);
     

3. Row Processing

   • Lazy Loading: Use createRowIterator() to process rows one-by-one (memory-efficient for large files).
   • Header Handling: Skip headers by offset:

     $iterator = $worksheet->createRowIterator();
     $iterator->next(); // Skip header row
     

   • Column Access: Rows return arrays where keys are column indices (0-based).

4. Data Transformation Pair with Laravel’s Collection for mapping:

   $data = collect($iterator)->map(function ($row) {
       return [
           'sku' => $row[0],
           'name' => $row[1],
       ];
   });
   

5. Error Handling Wrap in try-catch for malformed files:

   try {
       $spreadsheet = $this->loader->open($filePath);
   } catch (\RuntimeException $e) {
       Log::error("Failed to parse spreadsheet: {$e->getMessage()}");
       throw new \InvalidArgumentException("Invalid spreadsheet format");
   }
   

Integration Tips

• Validation: Use Laravel’s Validator to validate parsed data against expected schemas.
• Chunking: Process large files in chunks with array_chunk() on the iterator results.
• Storage: Save parsed data to a database using Eloquent’s createMany():

  Product::insert($data);
  

• Commands: Create an Artisan command for bulk imports:

  php artisan import:spreadsheet --file=products.xlsx
  

Gotchas and Tips

Pitfalls

1. File Extensions

   • Only supports .xlsx and .xlsm (not .xls or .csv).
   • Fix: Validate extensions before parsing:

     $allowedExtensions = ['xlsx', 'xlsm'];
     $extension = pathinfo($filePath, PATHINFO_EXTENSION);
     if (!in_array($extension, $allowedExtensions)) {
         throw new \InvalidArgumentException("Unsupported file type");
     }
     

2. Memory Limits

   • Avoid loading entire sheets into arrays (e.g., toArray()). Use iterators instead.
   • Symptom: Allowed memory exhausted for large files.
   • Fix: Process rows in batches or use yield in a generator.

3. Encoding Issues

   • Excel files may contain UTF-8 or ANSI data. Use mb_convert_encoding if needed:

     $cleanedValue = mb_convert_encoding($value, 'UTF-8', 'Windows-1252');
     

4. Sheet Names

   • Case-sensitive and may contain spaces/special characters. Escape when querying:

     $worksheetIndex = $spreadsheet->getWorksheetIndex('Sheet 1');
     

5. Deprecated Patterns

   • Avoid ContainerAwareInterface (use constructor injection).
   • Avoid Akeneo\Bundle\SpreadsheetParserBundle\Loader\SpreadsheetLoader directly (use interface).

Debugging

• Check File Paths: Ensure paths are absolute or relative to the project root.
• Log Iterators: Dump a few rows to verify structure:

  foreach ($worksheet->createRowIterator() as $i => $row) {
      if ($i < 5) var_dump($row); // Log first 5 rows
  }
  

• Test with Small Files: Start with a 10-row .xlsx to validate logic before scaling.

Extension Points

1. Custom Parsers Extend functionality by creating a decorator for SpreadsheetLoaderInterface:

   class CustomSpreadsheetLoader implements SpreadsheetLoaderInterface
   {
       public function __construct(private SpreadsheetLoaderInterface $decorated) {}
   
       public function open($filePath)
       {
           $spreadsheet = $this->decorated->open($filePath);
           // Add custom logic (e.g., auto-convert columns to lowercase)
           return $spreadsheet;
       }
   }
   

2. Event Dispatching Trigger events (e.g., spreadsheet.parsed) using Symfony’s EventDispatcher:

   $dispatcher->dispatch(new SpreadsheetParsedEvent($data), 'spreadsheet.parsed');
   

3. Configuration Override default settings (e.g., max rows) via config/packages/akeneo_spreadsheet_parser.yaml:

   akeneo_spreadsheet_parser:
       max_rows: 100000  # Default may be lower
   

Performance Tips

• Disable Formulas: If formulas are irrelevant, use a library like PhpOffice/PhpSpreadsheet for raw data extraction.
• Streaming: For huge files, combine with fopen() to stream chunks to disk before parsing.
• Caching: Cache parsed results if reprocessing is common:

  $cacheKey = 'spreadsheet_'.md5($filePath);
  $data = Cache::remember($cacheKey, now()->addHours(1), function () use ($filePath) {
      return $this->parseFile($filePath);
  });