Spreadsheet Parser Laravel Package

Getting Started

Minimal Setup

1. Installation

   composer require akeneo-labs/spreadsheet-parser
   

   Add to composer.json if using a monorepo or legacy setup:

   "repositories": [
       { "type": "vcs", "url": "https://github.com/akeneo-labs/spreadsheet-parser" }
   ]
   

2. First Use Case: Parsing a Simple XLSX

   use Akeneo\Tool\Bundle\SpreadsheetBundle\Parser\Parser;
   
   $parser = new Parser();
   $filePath = storage_path('app/uploads/example.xlsx');
   $result = $parser->parse($filePath);
   
   // Access parsed data (returns an associative array)
   foreach ($result as $row) {
       dd($row); // Debug first row
   }
   

3. Key Files to Reference

   • src/Akeneo/Tool/Bundle/SpreadsheetBundle/Parser/Parser.php (Core class)
   • tests/ (Unit/integration tests for edge cases)
   • Resources/config/services.yml (Service container config if using Symfony)

Implementation Patterns

Workflow: Laravel Integration

1. Service Provider Binding

   // app/Providers/AppServiceProvider.php
   public function register()
   {
       $this->app->bind(Parser::class, function ($app) {
           return new Parser();
       });
   }
   

2. Controller Usage

   use Akeneo\Tool\Bundle\SpreadsheetBundle\Parser\Parser;
   
   public function uploadExcel(Request $request)
   {
       $request->validate(['file' => 'required|file|mimes:xlsx']);
   
       $parser = app(Parser::class);
       $data = $parser->parse($request->file('file')->getRealPath());
   
       // Process $data (e.g., save to DB)
       return response()->json(['success' => true]);
   }
   

3. Chunked Processing for Large Files

   $parser = new Parser();
   $parser->setChunkSize(1000); // Process 1000 rows at a time
   $data = $parser->parse($filePath);
   
   foreach ($data as $chunk) {
       Model::insert($chunk); // Batch insert
   }
   

Common Patterns

• Header Row Handling

  $parser->setHeaderRow(1); // Skip first row (assume row 0 is headers)
  

• Column Mapping

  $mappedData = [];
  foreach ($result as $row) {
      $mappedData[] = [
          'id' => $row['ID'],
          'name' => $row['Product Name'],
      ];
  }
  

• Validation

  if (!$parser->isValid($filePath)) {
      throw new \Exception('Invalid Excel file');
  }
  

Gotchas and Tips

Pitfalls

1. Memory Limits

   • Large files (>10MB) may hit PHP’s memory_limit. Use setChunkSize() or process in streams.
   • Fix: Increase memory_limit in php.ini or use ini_set('memory_limit', '256M') in code.

2. Encoding Issues

   • Non-ASCII characters (e.g., é, ü) may corrupt. Use mb_convert_encoding():

     $row = mb_convert_encoding($row, 'UTF-8', 'auto');
     

3. Date/Time Parsing

   • Excel dates are stored as serial numbers. Use PhpOffice\PhpSpreadsheet for conversion if needed:

     $date = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row['Date']);
     

4. Symfony Dependency

   • The package is Symfony-bundled. For pure Laravel, mock dependencies like LoggerInterface:

     $parser = new Parser(new NullLogger());
     

Debugging Tips

• Log Parsed Data

  \Log::debug('Parsed row', ['data' => $row]);
  

• Validate File Structure

  if (empty($result) || !isset($result[0]['ExpectedColumn'])) {
      throw new \RuntimeException('Malformed Excel structure');
  }
  

Extension Points

1. Custom Parsing Logic Override Parser::parse() or extend the class:

   class CustomParser extends Parser {
       protected function transformRow($row) {
           return [
               'slug' => Str::slug($row['name']),
               'price' => (float) $row['price'],
           ];
       }
   }
   

2. Event Listeners Use Laravel’s events to hook into parsing:

   event(new SpreadsheetParsed($data));
   

3. Queue Jobs for Heavy Processing

   dispatch(new ProcessExcelJob($filePath, $data));