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],
   ];
   

2. First Use Case: Parse an .xlsx file and iterate over its rows:

   use Akeneo\Bundle\SpreadsheetParserBundle\Loader\SpreadsheetLoaderInterface;
   
   class SpreadsheetService
   {
       public function __construct(private SpreadsheetLoaderInterface $loader) {}
   
       public function parseFile(string $filePath, string $worksheetName): array
       {
           $spreadsheet = $this->loader->open($filePath);
           $worksheetIndex = $spreadsheet->getWorksheetIndex($worksheetName);
   
           return $spreadsheet->createRowIterator($worksheetIndex);
       }
   }
   

3. Key Classes:

   • SpreadsheetLoaderInterface: Loads and parses files.
   • Spreadsheet: Represents the parsed workbook.
   • Worksheet: Represents a sheet within the workbook.

Implementation Patterns

Core Workflows

1. Dependency Injection: Inject akeneo_spreadsheet_parser.spreadsheet_loader as a service:

   # config/services.yaml
   services:
       App\Service\SpreadsheetService:
           arguments:
               $loader: '@akeneo_spreadsheet_parser.spreadsheet_loader'
   

2. Row Iteration: Process rows with optional column mapping:

   foreach ($spreadsheet->createRowIterator($worksheetIndex) as $rowIndex => $row) {
       $data = [
           'id' => $row[0],
           'name' => $row[1],
           'price' => $row[2],
       ];
       // Process $data
   }
   

3. Handling Large Files: Use chunked iteration to avoid memory overload:

   $iterator = $spreadsheet->createRowIterator($worksheetIndex);
   $chunkSize = 1000;
   $chunk = [];
   
   foreach ($iterator as $rowIndex => $row) {
       $chunk[] = $row;
       if (count($chunk) === $chunkSize) {
           $this->processChunk($chunk);
           $chunk = [];
       }
   }
   

4. Validation: Validate headers or required columns before processing:

   $headers = $spreadsheet->getRowIterator($worksheetIndex)->current();
   if (!in_array('name', $headers)) {
       throw new \RuntimeException('Missing required column: "name"');
   }
   

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

   try {
       $spreadsheet = $this->loader->open($filePath);
   } catch (\Exception $e) {
       $this->handleParseError($e);
   }
   

Gotchas and Tips

Pitfalls

1. File Extensions:

   • Only .xlsx and .xlsm are supported. .xls (Excel 97-2003) is not supported.
   • Rename files to .xlsx if needed (e.g., file.xlsx instead of file.xls).

2. Memory Usage:

   • Large files (>10MB) may cause memory issues. Use chunked iteration (see Implementation Patterns).
   • Avoid loading entire sheets into arrays; iterate row-by-row.

3. Encoding Issues:

   • Non-ASCII characters (e.g., é, ü) may render incorrectly. Ensure your file is UTF-8 encoded.
   • Add BOM (Byte Order Mark) if needed:

     $spreadsheet = $this->loader->open($filePath, ['encoding' => 'UTF-8']);
     

4. Case Sensitivity:

   • Worksheet names are case-sensitive. Use exact matches:

     $worksheetIndex = $spreadsheet->getWorksheetIndex('MySheet'); // Not 'mysheet'
     

5. Deprecated Methods:

   • Avoid getWorksheet() if possible; prefer getWorksheetIndex() + createRowIterator() for consistency.

Debugging Tips

1. Verify Worksheet Exists:

   $worksheetNames = $spreadsheet->getWorksheetNames();
   if (!in_array('ExpectedSheet', $worksheetNames)) {
       throw new \RuntimeException("Worksheet not found");
   }
   

2. Log Row Data:

   foreach ($spreadsheet->createRowIterator($worksheetIndex) as $rowIndex => $row) {
       if ($rowIndex < 5) { // Log first 5 rows for debugging
           \Log::debug("Row $rowIndex: " . json_encode($row));
       }
   }
   

3. Check for Empty Cells:

   • Empty cells return null. Handle explicitly:

     $value = $row[0] ?? null;
     if ($value === null) {
         $value = 'N/A'; // Default value
     }
     

Extension Points

1. Custom Row Processing: Create a decorator for SpreadsheetLoaderInterface to add preprocessing:

   class CustomSpreadsheetLoader implements SpreadsheetLoaderInterface
   {
       public function __construct(private SpreadsheetLoaderInterface $decorated) {}
   
       public function open($filePath, array $options = []): Spreadsheet
       {
           $spreadsheet = $this->decorated->open($filePath, $options);
           // Add custom logic (e.g., transform rows)
           return $spreadsheet;
       }
   }
   

2. Support for .xls: If you need .xls support, consider wrapping this bundle with PhpOffice/PhpSpreadsheet for backward compatibility.

3. Configuration: Override default options via DI:

   # config/packages/akeneo_spreadsheet_parser.yaml
   akeneo_spreadsheet_parser:
       default_options:
           encoding: 'UTF-8'
           read_only: true
   

4. Testing: Mock the loader in unit tests:

   $mockLoader = $this->createMock(SpreadsheetLoaderInterface::class);
   $mockLoader->method('open')->willReturn($mockSpreadsheet);
   $service = new SpreadsheetService($mockLoader);