Simple Spreadsheet Reader Laravel Package

Getting Started

Minimal Steps

1. Installation:

   composer require alexain/simple-spreadsheet-reader
   

   (Automatically registers if using Symfony Flex.)

2. First Use Case: Inject the SimpleSpreadsheetReader service into a command, controller, or service:

   use Alexain\SimpleSpreadsheetReaderBundle\Service\SimpleSpreadsheetReader;
   
   final class ImportCommand extends Command {
       public function __construct(
           private SimpleSpreadsheetReader $reader
       ) {
           parent::__construct();
       }
   
       protected function execute(InputInterface $input, OutputInterface $output): int {
           $filePath = $input->getArgument('file');
           foreach ($this->reader->read($filePath) as $row) {
               $output->writeln(print_r($row, true));
           }
           return Command::SUCCESS;
       }
   }
   

3. Where to Look First:

   • Service API: SimpleSpreadsheetReader::read() returns an iterable of normalized rows.
   • Configuration: config/packages/simple_spreadsheet_reader.yaml (auto-generated if missing).
   • Supported Formats: CSV (via OpenSpout) and XLSX (via PhpSpreadsheet).

Implementation Patterns

Core Workflows

1. Streaming Large Files: Use the iterable return value to process rows one-by-one without loading the entire file into memory:

   foreach ($this->reader->read('large_file.xlsx') as $row) {
       $this->processRow($row); // Process incrementally
   }
   

2. Header Normalization: Enable header normalization in config to convert headers to snake_case:

   simple_spreadsheet_reader:
       header:
           normalize: true
   

   Example: "First Name" → "first_name".

3. Format-Specific Configuration: Override defaults per format (e.g., CSV delimiter):

   simple_spreadsheet_reader:
       csv:
           delimiter: ';'
       xlsx:
           sheet_index: 1  # Zero-based sheet index
   

4. Dependency Injection: Prefer constructor injection for testability:

   public function __construct(
       private SimpleSpreadsheetReader $reader,
       private DataMapper $mapper
   ) {}
   

5. Error Handling: Wrap read() in a try-catch for malformed files:

   try {
       foreach ($this->reader->read($path) as $row) {
           // ...
       }
   } catch (SpreadsheetException $e) {
       $this->logger->error('Failed to read spreadsheet', ['error' => $e->getMessage()]);
   }
   

Integration Tips

• Validation: Pair with Symfony’s Validator for row-level validation:

  use Symfony\Component\Validator\Validator\ValidatorInterface;
  
  public function __construct(
      private SimpleSpreadsheetReader $reader,
      private ValidatorInterface $validator
  ) {}
  
  public function import(string $path): void {
      foreach ($this->reader->read($path) as $row) {
          $errors = $this->validator->validate($row);
          if (count($errors) === 0) {
              $this->saveRow($row);
          }
      }
  }
  

• Batch Processing: Use array_chunk() on the iterable for batch inserts:

  $rows = iterator_to_array($this->reader->read('data.csv'));
  array_walk(array_chunk($rows, 100), fn($batch) => $this->bulkInsert($batch));
  

• Custom Mappings: Transform headers dynamically via a service:

  $this->reader->setHeaderMapper(function (array $headers): array {
      return array_combine($headers, $headers); // Custom logic
  });
  

Gotchas and Tips

Pitfalls

1. Memory Leaks:

   • Issue: Forgetting to consume the iterable fully (e.g., breaking early in a foreach).
   • Fix: Ensure all rows are processed or use iterator_to_array() cautiously for small files.

2. Sheet Indexing:

   • Issue: sheet_index is zero-based but undocumented in the API.
   • Fix: Defaults to 0; explicitly set in config if needed:

     simple_spreadsheet_reader:
         xlsx:
             sheet_index: 0  # First sheet
     

3. CSV Encoding:

   • Issue: Auto-detection may fail on non-UTF-8 files.
   • Fix: Force encoding in config:

     simple_spreadsheet_reader:
         csv:
             encoding: 'ISO-8859-1'
     

4. PhpSpreadsheet Dependencies:

   • Issue: XLSX parsing requires phpoffice/phpspreadsheet (installed as a dev dependency).
   • Fix: Add to composer.json:

     "require": {
         "phpoffice/phpspreadsheet": "^2.1"
     }
     

5. Header Normalization Quirks:

   • Issue: Normalization may not handle all edge cases (e.g., "User ID" → "user_id" vs "userID").
   • Fix: Extend the normalizer or disable it:

     simple_spreadsheet_reader:
         header:
             normalize: false
     

Debugging Tips

1. Inspect Raw Data: Use getRawRow() to debug unnormalized data:

   foreach ($this->reader->read('file.csv') as $row) {
       if ($row->hasRawData()) {
           $raw = $row->getRawData(); // Original unprocessed row
       }
   }
   

2. Log Configuration: Dump the active config for troubleshooting:

   $this->logger->debug('Spreadsheet config:', [
       'config' => $this->reader->getConfiguration(),
   ]);
   

3. Format Detection: Verify auto-detection works:

   $format = $this->reader->detectFormat('file.xlsx'); // Should return 'xlsx'
   

Extension Points

1. Custom Formats: Implement Alexain\SimpleSpreadsheetReaderBundle\Reader\ReaderInterface for new formats (e.g., ODS).

2. Row Transformers: Hook into the pipeline via setRowTransformer():

   $this->reader->setRowTransformer(function (array $row): array {
       $row['processed_at'] = now()->toDateTimeString();
       return $row;
   });
   

3. Event Dispatching: Extend to dispatch events (e.g., SpreadsheetReadStarted, RowProcessed) by subclassing SimpleSpreadsheetReader.

4. Validation Integration: Add a ValidatorAwareTrait to the service for built-in validation:

   use Symfony\Component\Validator\Validator\ValidatorInterface;
   
   class ExtendedReader extends SimpleSpreadsheetReader {
       public function __construct(
           private ValidatorInterface $validator,
           array $config = []
       ) {
           parent::__construct($config);
       }
   
       public function read(string $path): iterable {
           foreach (parent::read($path) as $row) {
               yield $this->validator->validate($row) ? $row : null;
           }
       }
   }
   

5. Progress Tracking: Wrap the iterable in a custom iterator to track progress:

   class ProgressIterator implements Iterator {
       private Iterator $iterator;
       private int $total = 0;
       private int $processed = 0;
   
       public function __construct(Iterator $iterator, int $total) {
           $this->iterator = $iterator;
           $this->total = $total;
       }
   
       public function current(): mixed {
           $row = $this->iterator->current();
           $this->processed++;
           $this->logger->info(sprintf('Progress: %d/%d', $this->processed, $this->total));
           return $row;
       }
       // ... implement other Iterator methods
   }