Simple Excel Laravel Package

Getting Started

Minimal Steps

1. Installation:

   composer require spatie/simple-excel
   

   No additional configuration is required—just start using it.

2. First Use Case: Read a CSV/Excel file and process its rows:

   use Spatie\SimpleExcel\SimpleExcelReader;
   
   SimpleExcelReader::create(public_path('file.xlsx'))
       ->getRows()
       ->each(function (array $row) {
           // Process each row (e.g., save to DB, transform data)
           \Log::info($row);
       });
   

3. Where to Look First:

   • README for basic usage.
   • API Docs for methods like getRows(), getRowIterator(), or getCollection().
   • Examples for CSV/Excel-specific patterns.

Implementation Patterns

Core Workflows

1. Reading Files:

   • Streaming Rows (Memory-Efficient):

     SimpleExcelReader::create($filePath)
         ->getRowIterator()
         ->each(function (array $row) {
             // Process row-by-row (ideal for large files)
         });
     

   • Collecting All Rows (For Small/Medium Files):

     $rows = SimpleExcelReader::create($filePath)->getCollection();
     

2. Writing Files:

   • CSV/Excel Export:

     use Spatie\SimpleExcel\SimpleExcelWriter;
     
     SimpleExcelWriter::create(public_path('output.xlsx'))
         ->addRows([['col1', 'col2'], ['data1', 'data2']])
         ->save();
     

   • Streaming Large Writes:

     $writer = SimpleExcelWriter::create($filePath);
     foreach ($largeDataset as $row) {
         $writer->addRow($row);
     }
     $writer->save();
     

3. Integration with Laravel:

   • File Uploads:

     $file = request()->file('excel_file');
     SimpleExcelReader::create($file->path())
         ->getRows()
         ->each(fn($row) => Model::create($row));
     

   • Queue Jobs:

     dispatch(new ProcessExcelJob($filePath));
     

     class ProcessExcelJob implements ShouldQueue {
         public function handle() {
             SimpleExcelReader::create($this->filePath)->getRows()->each(...);
         }
     }
     

4. Validation & Transformation:

   • Use getRows() with map() or filter():

     $validRows = SimpleExcelReader::create($filePath)
         ->getRows()
         ->filter(fn($row) => filled($row['required_field']));
     

Gotchas and Tips

Pitfalls

1. Memory Usage:

   • Avoid getCollection() for files >10,000 rows. Use getRowIterator() or chunk processing:

     SimpleExcelReader::create($filePath)
         ->getRows()
         ->chunk(1000)
         ->each(function ($chunk) {
             Model::insert($chunk->toArray());
         });
     

2. File Paths:

   • Ensure paths are absolute or relative to Laravel’s storage/public directory. Use storage_path() or public_path() helpers.

3. Excel-Specific Quirks:

   • Formulas: SimpleExcel treats formulas as strings. Use PhpOffice\PhpSpreadsheet directly if formula evaluation is needed.
   • Merged Cells: Not natively supported. Pre-process with PhpSpreadsheet if required.

4. CSV Delimiters:

   • Defaults to commas. Override with:

     SimpleExcelReader::create($filePath)->setCsvDelimiter(';');
     

Debugging

• Empty Rows: Add a ->skipEmptyRows() to ignore blank rows.
• Encoding Issues: Use ->setEncoding('UTF-8') for non-ASCII files.
• Large File Timeouts: Increase PHP’s max_execution_time or process in chunks.

Extension Points

1. Custom Row Processing:

   • Extend Spatie\SimpleExcel\SimpleExcelReader or use decorators:

     class CustomReader extends SimpleExcelReader {
         public function getProcessedRows() {
             return $this->getRows()->map(fn($row) => $this->transform($row));
         }
     }
     

2. Batch Processing:

   • Combine with Laravel’s Batch facade for DB inserts:

     use Illuminate\Support\Facades\Batch;
     
     Batch::group($rows, 500)
         ->then(function (Batch $batch) {
             Model::insert($batch->items());
         });
     

3. Testing:

   • Mock SimpleExcelReader in tests:

     $mockReader = Mockery::mock(SimpleExcelReader::class);
     $mockReader->shouldReceive('getRows')->andReturn(collect([['test']]));
     

4. Performance:

   • For repeated reads, cache the file path or use Laravel’s cache():

     $cachedRows = cache()->remember("excel_{$filePath}", now()->addHours(1), function() {
         return SimpleExcelReader::create($filePath)->getCollection();
     });