Spatie Simple Excel (spatie/simple-excel)

Spatie Simple Excel

https://github.com/spatie/simple-excel
Admin
A PHP package that enables reading and writing simple Excel and CSV files with low memory usage...

Tokens:13,757
Snippets:133
Trust Score:8.5
Update:3 days ago
Show doc for...
Context Summary (auto-generated)
Raw
# Spatie Simple Excel

Spatie Simple Excel is a PHP package that provides an elegant, fluent API for reading and writing Excel (XLSX), CSV, and ODS files with minimal memory consumption. Built on top of OpenSpout, it uses PHP generators to ensure efficient memory usage even when processing large files with thousands of rows.

The package integrates seamlessly with Laravel's LazyCollection for reading operations, allowing developers to process spreadsheet data using familiar collection methods like `filter()`, `map()`, and `each()`. It supports multiple spreadsheets within Excel files, custom headers, various CSV delimiters and encodings, cell styling, and both file-based and browser streaming output modes.

## Installation

Install the package via Composer:

```bash
composer require spatie/simple-excel
```

## Reading Files

### SimpleExcelReader::create() - Create a Reader Instance

Creates a new reader instance for CSV, XLSX, or ODS files. The file type is automatically detected from the file extension.

```php
use Spatie\SimpleExcel\SimpleExcelReader;

// Basic reading - returns LazyCollection with header row as keys
$rows = SimpleExcelReader::create('/path/to/users.csv')->getRows();

$rows->each(function(array $rowProperties) {
    // First row: ['email' => 'john@example.com', 'first_name' => 'John']
    echo $rowProperties['email'] . ' - ' . $rowProperties['first_name'];
});

// Reading Excel files (same API)
$rows = SimpleExcelReader::create('/path/to/data.xlsx')->getRows();

// Explicitly specify file type when extension doesn't match
$rows = SimpleExcelReader::create('/path/to/file.txt', 'csv')->getRows();
```

### noHeaderRow() - Reading Files Without Headers

Reads files that don't contain a header row, returning rows with numeric indices instead of associative arrays.

```php
use Spatie\SimpleExcel\SimpleExcelReader;

$rows = SimpleExcelReader::create('/path/to/data.csv')
    ->noHeaderRow()
    ->getRows();

$rows->each(function(array $rowProperties) {
    // Row data with numeric keys: [0 => 'john@example.com', 1 => 'John']
    echo $rowProperties[0] . ' - ' . $rowProperties[1];
});
```

### useHeaders() - Set Custom Headers

Replaces or provides custom header names for the data columns. If the file has headers, they are replaced; if used with `noHeaderRow()`, these become the keys.

```php
use Spatie\SimpleExcel\SimpleExcelReader;

// Replace existing headers with custom names
$rows = SimpleExcelReader::create('/path/to/users.csv')
    ->useHeaders(['email_address', 'given_name', 'family_name'])
    ->getRows();

$rows->each(function(array $rowProperties) {
    // ['email_address' => 'john@example.com', 'given_name' => 'John', 'family_name' => 'Doe']
    echo $rowProperties['email_address'];
});

// Use custom headers for headerless files
$rows = SimpleExcelReader::create('/path/to/raw-data.csv')
    ->noHeaderRow()
    ->useHeaders(['id', 'name', 'value'])
    ->getRows();
```

### headerOnRow() - Headers Not on First Line

Specifies which row contains the headers when they're not on the first line. Rows above the header row are discarded.

```php
use Spatie\SimpleExcel\SimpleExcelReader;

// Headers are on row 3 (0-indexed, blank lines not counted)
$rows = SimpleExcelReader::create('/path/to/report.xlsx')
    ->trimHeaderRow()
    ->headerOnRow(3)
    ->getRows();

$rows->each(function(array $rowProperties) {
    // Data starts after the header row
    print_r($rowProperties);
});
```

### getHeaders() and getOriginalHeaders() - Retrieve Header Values

Returns the header row as an array. `getHeaders()` returns custom headers if set, while `getOriginalHeaders()` always returns the file's actual headers.

```php
use Spatie\SimpleExcel\SimpleExcelReader;

$reader = SimpleExcelReader::create('/path/to/users.csv');
$headers = $reader->getHeaders();
// ['email', 'first_name', 'last_name']

// With custom headers
$reader = SimpleExcelReader::create('/path/to/users.csv')
    ->useHeaders(['custom_email', 'custom_name']);

$customHeaders = $reader->getHeaders();
// ['custom_email', 'custom_name']

$originalHeaders = $reader->getOriginalHeaders();
// ['email', 'first_name', 'last_name']
```

### trimHeaderRow() - Trim Whitespace from Headers

Removes whitespace or specified characters from header values.

```php
use Spatie\SimpleExcel\SimpleExcelReader;

// Trim default whitespace characters
$rows = SimpleExcelReader::create('/path/to/messy-headers.csv')
    ->trimHeaderRow()
    ->getRows();

// Trim specific characters (uses PHP trim() syntax)
$rows = SimpleExcelReader::create('/path/to/data.csv')
    ->trimHeaderRow(" \t\n\r\0\x0B*")
    ->getRows();
```

### headersToSnakeCase() - Convert Headers to Snake Case

Automatically converts all header names to snake_case format.

```php
use Spatie\SimpleExcel\SimpleExcelReader;

// CSV headers: "Email Address", "First Name", "Last Name"
$rows = SimpleExcelReader::create('/path/to/users.csv')
    ->headersToSnakeCase()
    ->getRows();

$rows->each(function(array $rowProperties) {
    // Keys are now: ['email_address', 'first_name', 'last_name']
    echo $rowProperties['email_address'];
});
```

### formatHeadersUsing() - Custom Header Formatting

Applies a custom callback function to transform header names.

```php
use Spatie\SimpleExcel\SimpleExcelReader;

$rows = SimpleExcelReader::create('/path/to/data.csv')
    ->formatHeadersUsing(fn($header) => 'prefix_' . strtolower($header))
    ->getRows();

$rows->each(function(array $rowProperties) {
    // Headers prefixed: ['prefix_email', 'prefix_name', 'prefix_status']
    echo $rowProperties['prefix_email'];
});

// Using uppercase transformation
$rows = SimpleExcelReader::create('/path/to/data.csv')
    ->formatHeadersUsing(fn($header) => strtoupper(str_replace(' ', '_', $header)))
    ->getRows();
```

### skip() and take() - Limit Result Set

Controls which rows are returned by skipping rows and/or limiting the total count.

```php
use Spatie\SimpleExcel\SimpleExcelReader;

// Only get first 5 rows
$rows = SimpleExcelReader::create('/path/to/large-file.csv')
    ->take(5)
    ->getRows();

// Skip first 10 rows, then take 5 (rows 11-15)
$rows = SimpleExcelReader::create('/path/to/large-file.csv')
    ->skip(10)
    ->take(5)
    ->getRows();

// Process in batches
$batchSize = 100;
$offset = 0;

while (true) {
    $rows = SimpleExcelReader::create('/path/to/huge-file.csv')
        ->skip($offset)
        ->take($batchSize)
        ->getRows();

    if ($rows->isEmpty()) {
        break;
    }

    // Process batch
    $rows->each(fn($row) => processRow($row));
    $offset += $batchSize;
}
```

### fromSheet() and fromSheetName() - Multiple Sheets

Selects which sheet to read from in multi-sheet Excel documents.

```php
use Spatie\SimpleExcel\SimpleExcelReader;

// Select by sheet index (1-based)
$rows = SimpleExcelReader::create('/path/to/workbook.xlsx')
    ->fromSheet(2)  // Second sheet
    ->getRows();

// Select by sheet name
$rows = SimpleExcelReader::create('/path/to/workbook.xlsx')
    ->fromSheetName('Sales Data')
    ->getRows();

// Get all sheet names
$sheets = SimpleExcelReader::create('/path/to/workbook.xlsx')
    ->getSheetNames();
// ['Sheet1', 'Sales Data', 'Summary']

// Check if sheet exists
$hasSheet = SimpleExcelReader::create('/path/to/workbook.xlsx')
    ->hasSheet('Sales Data');
// true or false
```

### useDelimiter() - Custom CSV Delimiter

Sets a custom field delimiter for CSV files (default is comma).

```php
use Spatie\SimpleExcel\SimpleExcelReader;

// Semicolon-separated values
$rows = SimpleExcelReader::create('/path/to/european-data.csv')
    ->useDelimiter(';')
    ->getRows();

// Tab-separated values
$rows = SimpleExcelReader::create('/path/to/data.tsv')
    ->useDelimiter("\t")
    ->getRows();

// Pipe-separated values
$rows = SimpleExcelReader::create('/path/to/data.csv')
    ->useDelimiter('|')
    ->getRows();
```

### useEncoding() - Specify File Encoding

Sets the character encoding for reading CSV files.

```php
use Spatie\SimpleExcel\SimpleExcelReader;

// Read Shift-JIS encoded file (Japanese)
$rows = SimpleExcelReader::create('/path/to/japanese-data.csv')
    ->useEncoding('SJIS')
    ->getRows();

// Read ISO-8859-1 encoded file (Latin-1)
$rows = SimpleExcelReader::create('/path/to/legacy-data.csv')
    ->useEncoding('ISO-8859-1')
    ->getRows();
```

### keepFormulas() - Preserve Formula Strings

Returns the formula string instead of the computed value for cells containing formulas.

```php
use Spatie\SimpleExcel\SimpleExcelReader;

// By default, formulas are computed
$rows = SimpleExcelReader::create('/path/to/spreadsheet.xlsx')->getRows();
// Cell with "=SUM(A1:A10)" returns: 150

// Keep formula as string
$rows = SimpleExcelReader::create('/path/to/spreadsheet.xlsx')
    ->keepFormulas()
    ->getRows();
// Cell with "=SUM(A1:A10)" returns: "=SUM(A1:A10)"
```

### preserveDateTimeFormatting() - Keep Date Formatting

Returns dates as formatted strings instead of DateTimeImmutable objects.

```php
use Spatie\SimpleExcel\SimpleExcelReader;

// By default, dates are returned as DateTimeImmutable
$rows = SimpleExcelReader::create('/path/to/dates.xlsx')->getRows();
// Date cell returns: DateTimeImmutable object

// Preserve formatting from spreadsheet
$rows = SimpleExcelReader::create('/path/to/dates.xlsx')
    ->preserveDateTimeFormatting()
    ->getRows();
// Date cell returns: "9/20/2024" (as formatted in Excel)
```

### preserveEmptyRows() - Keep Empty Rows

Includes empty rows in the output instead of skipping them.

```php
use Spatie\SimpleExcel\SimpleExcelReader;

$rows = SimpleExcelReader::create('/path/to/data-with-gaps.xlsx')
    ->preserveEmptyRows()
    ->getRows();

$rows->each(function(array $rowProperties, $index) {
    if (empty(array_filter($rowProperties))) {
        echo "Row {$index} is empty\n";
    }
});
```

### getReader() - Access Underlying OpenSpout Reader

Returns the underlying OpenSpout ReaderInterface for advanced operations.

```php
use Spatie\SimpleExcel\SimpleExcelReader;

$simpleReader = SimpleExcelReader::create('/path/to/data.xlsx');
$openSpoutReader = $simpleReader->getReader();

// Access OpenSpout-specific functionality
// Note: The reader is already opened by SimpleExcelReader
```

## Writing Files

### SimpleExcelWriter::create() - Create a Writer Instance

Creates a new writer for generating CSV, XLSX, or ODS files.

```php
use Spatie\SimpleExcel\SimpleExcelWriter;

// Write a CSV file
$writer = SimpleExcelWriter::create('/path/to/output.csv')
    ->addRow(['first_name' => 'John', 'last_name' => 'Doe'])
    ->addRow(['first_name' => 'Jane', 'last_name' => 'Doe']);

// File contains:
// first_name,last_name
// John,Doe
// Jane,Doe

// Write an Excel file (same API, just change extension)
$writer = SimpleExcelWriter::create('/path/to/output.xlsx')
    ->addRow(['first_name' => 'John', 'last_name' => 'Doe'])
    ->addRow(['first_name' => 'Jane', 'last_name' => 'Doe']);

// Important: Excel files are finalized when the writer is garbage collected
// or when close() is called explicitly
$writer->close();
```

### addRow() - Add Single Row

Adds a single row to the spreadsheet. Header row is automatically created from array keys on the first row.

```php
use Spatie\SimpleExcel\SimpleExcelWriter;

$writer = SimpleExcelWriter::create('/path/to/users.csv');

// Add rows - headers are auto-generated from first row's keys
$writer->addRow([
    'email' => 'john@example.com',
    'first_name' => 'John',
    'last_name' => 'Doe',
]);

$writer->addRow([
    'email' => 'jane@example.com',
    'first_name' => 'Jane',
    'last_name' => 'Smith',
]);

// Output:
// email,first_name,last_name
// john@example.com,John,Doe
// jane@example.com,Jane,Smith
```

### addRows() - Add Multiple Rows

Adds multiple rows at once from an iterable (array, collection, or generator).

```php
use Spatie\SimpleExcel\SimpleExcelWriter;

$users = [
    ['email' => 'john@example.com', 'name' => 'John Doe'],
    ['email' => 'jane@example.com', 'name' => 'Jane Smith'],
    ['email' => 'bob@example.com', 'name' => 'Bob Wilson'],
];

$writer = SimpleExcelWriter::create('/path/to/users.csv')
    ->addRows($users);

// Works with Laravel collections
$users = User::all();
$writer = SimpleExcelWriter::create('/path/to/users.xlsx')
    ->addRows($users->map(fn($user) => [
        'id' => $user->id,
        'email' => $user->email,
        'name' => $user->name,
    ]));
```

### addHeader() - Manually Set Header Row

Explicitly defines the header row instead of auto-detecting from first data row.

```php
use Spatie\SimpleExcel\SimpleExcelWriter;

$writer = SimpleExcelWriter::create('/path/to/output.csv')
    ->addHeader(['First Name', 'Last Name', 'Email'])
    ->addRow(['John', 'Doe', 'john@example.com'])
    ->addRow(['Jane', 'Smith', 'jane@example.com']);

// Output:
// First Name,Last Name,Email
// John,Doe,john@example.com
// Jane,Smith,jane@example.com
```

### noHeaderRow() - Write Without Headers

Writes data rows without automatically adding a header row.

```php
use Spatie\SimpleExcel\SimpleExcelWriter;

$writer = SimpleExcelWriter::create('/path/to/raw-data.csv')
    ->noHeaderRow()
    ->addRow(['first_name' => 'John', 'last_name' => 'Doe'])
    ->addRow(['first_name' => 'Jane', 'last_name' => 'Smith']);

// Output (no header):
// John,Doe
// Jane,Smith
```

### setHeaderStyle() - Style Header Row

Applies styling to the header row (Excel files only).

```php
use Spatie\SimpleExcel\SimpleExcelWriter;
use OpenSpout\Common\Entity\Style\Color;
use OpenSpout\Common\Entity\Style\Style;

$headerStyle = (new Style())
    ->setFontBold()
    ->setFontSize(12)
    ->setFontColor(Color::WHITE)
    ->setBackgroundColor(Color::BLUE);

$writer = SimpleExcelWriter::create('/path/to/styled.xlsx')
    ->setHeaderStyle($headerStyle)
    ->addRow(['name' => 'John', 'email' => 'john@example.com'])
    ->addRow(['name' => 'Jane', 'email' => 'jane@example.com']);
```

### addRow() with Style - Style Data Rows

Applies styling to individual data rows (Excel files only).

```php
use Spatie\SimpleExcel\SimpleExcelWriter;
use OpenSpout\Common\Entity\Style\Color;
use OpenSpout\Common\Entity\Style\Style;
use OpenSpout\Common\Entity\Style\Border;
use OpenSpout\Common\Entity\Style\BorderPart;

// Create a border
$border = new Border(
    new BorderPart(Border::BOTTOM, Color::LIGHT_BLUE, Border::WIDTH_THIN, Border::STYLE_SOLID),
    new BorderPart(Border::LEFT, Color::LIGHT_BLUE, Border::WIDTH_THIN, Border::STYLE_SOLID),
    new BorderPart(Border::RIGHT, Color::LIGHT_BLUE, Border::WIDTH_THIN, Border::STYLE_SOLID),
    new BorderPart(Border::TOP, Color::LIGHT_BLUE, Border::WIDTH_THIN, Border::STYLE_SOLID)
);

$highlightStyle = (new Style())
    ->setFontBold()
    ->setFontColor(Color::RED)
    ->setBackgroundColor(Color::YELLOW)
    ->setBorder($border);

$normalStyle = (new Style())
    ->setFontSize(11);

$writer = SimpleExcelWriter::create('/path/to/styled.xlsx');

foreach ($records as $record) {
    $style = $record['priority'] === 'high' ? $highlightStyle : $normalStyle;
    $writer->addRow($record, $style);
}
```

### configureWriter - Set Column Widths and Row Heights

Configures the underlying OpenSpout writer for column widths and default row heights.

```php
use Spatie\SimpleExcel\SimpleExcelWriter;

$writer = SimpleExcelWriter::create(
    file: '/path/to/formatted.xlsx',
    configureWriter: function ($writer) {
        $options = $writer->getOptions();
        $options->DEFAULT_COLUMN_WIDTH = 25;
        $options->DEFAULT_ROW_HEIGHT = 15;

        // Set specific column widths (columns 1, 3, and 8 to width 40)
        $options->setColumnWidth(40, 1, 3, 8);

        // Set column range width (columns 9-12 to width 10)
        $options->setColumnWidthForRange(10, 9, 12);
    }
);

$writer->addRow(['id' => 1, 'name' => 'Product', 'description' => 'Long description text']);
```

### addNewSheetAndMakeItCurrent() - Create Multiple Sheets

Creates additional sheets in Excel workbooks.

```php
use Spatie\SimpleExcel\SimpleExcelWriter;

$writer = SimpleExcelWriter::create('/path/to/multi-sheet.xlsx');

// First sheet - Users
$writer->nameCurrentSheet('Users');
$writer->addRow(['id' => 1, 'name' => 'John']);
$writer->addRow(['id' => 2, 'name' => 'Jane']);

// Second sheet - Products
$writer->addNewSheetAndMakeItCurrent('Products');
$writer->addRow(['sku' => 'ABC123', 'name' => 'Widget']);
$writer->addRow(['sku' => 'DEF456', 'name' => 'Gadget']);

// Third sheet - Orders
$writer->addNewSheetAndMakeItCurrent('Orders');
$writer->addRow(['order_id' => 1001, 'total' => 99.99]);

$writer->close();
```

### streamDownload() - Stream to Browser

Creates a writer that streams directly to the browser for download.

```php
use Spatie\SimpleExcel\SimpleExcelWriter;

// In a Laravel controller
public function export()
{
    $writer = SimpleExcelWriter::streamDownload('users-export.xlsx')
        ->addRow(['id' => 1, 'name' => 'John', 'email' => 'john@example.com'])
        ->addRow(['id' => 2, 'name' => 'Jane', 'email' => 'jane@example.com'])
        ->toBrowser();
}

// Streaming large datasets with flush
public function exportLarge()
{
    $writer = SimpleExcelWriter::streamDownload('large-export.xlsx');

    User::chunk(1000, function ($users) use ($writer) {
        foreach ($users as $user) {
            $writer->addRow([
                'id' => $user->id,
                'name' => $user->name,
                'email' => $user->email,
            ]);
        }
        flush(); // Flush buffer every 1000 rows
    });

    $writer->toBrowser();
}
```

### createWithoutBom() - Create Without BOM

Creates a CSV writer without adding a Byte Order Mark at the start of the file.

```php
use Spatie\SimpleExcel\SimpleExcelWriter;

// Create CSV without BOM
$writer = SimpleExcelWriter::createWithoutBom('/path/to/no-bom.csv')
    ->addRow(['name' => 'John', 'value' => 100])
    ->addRow(['name' => 'Jane', 'value' => 200]);

// Alternative syntax with explicit parameter
$writer = SimpleExcelWriter::create(
    file: '/path/to/no-bom.csv',
    shouldAddBom: false
);
```

### Custom Delimiter for Writing

Sets a custom field delimiter when writing CSV files.

```php
use Spatie\SimpleExcel\SimpleExcelWriter;

// Write with semicolon delimiter
$writer = SimpleExcelWriter::create(
    file: '/path/to/european.csv',
    delimiter: ';'
)
    ->addRow(['name' => 'John', 'amount' => '1.234,56'])
    ->addRow(['name' => 'Jane', 'amount' => '2.345,67']);

// Output:
// name;amount
// John;1.234,56
// Jane;2.345,67
```

### getNumberOfRows() - Get Row Count

Returns the total number of rows written, including the header row.

```php
use Spatie\SimpleExcel\SimpleExcelWriter;

$writer = SimpleExcelWriter::create('/path/to/output.csv')
    ->addRow(['name' => 'John', 'email' => 'john@example.com'])
    ->addRow(['name' => 'Jane', 'email' => 'jane@example.com']);

$rowCount = $writer->getNumberOfRows();
// Returns 3 (1 header + 2 data rows)
```

### close() - Finalize and Close Writer

Explicitly closes the writer and finalizes the file. Required for Excel files if you need to access the file before the writer is garbage collected.

```php
use Spatie\SimpleExcel\SimpleExcelWriter;

$writer = SimpleExcelWriter::create('/path/to/output.xlsx')
    ->addRow(['name' => 'John'])
    ->addRow(['name' => 'Jane']);

// Finalize the file
$writer->close();

// Now safe to read/move/upload the file
$fileSize = filesize('/path/to/output.xlsx');
```

## Summary

Spatie Simple Excel excels at handling common spreadsheet operations with minimal boilerplate code. The primary use cases include importing data from user-uploaded CSV/Excel files, exporting database records to downloadable spreadsheets, processing large data files with memory efficiency through LazyCollections, and generating formatted reports with styled headers and cells. The fluent API allows chaining multiple configuration methods for complex scenarios like reading specific sheets, handling non-standard headers, or applying custom transformations.

Integration patterns typically involve using `SimpleExcelReader` in Laravel commands or jobs for batch imports, combining with Eloquent's `chunk()` method for memory-efficient exports, and leveraging `streamDownload()` in controllers for on-demand report generation. The package works seamlessly with Laravel's collection pipeline, allowing developers to filter, transform, and aggregate spreadsheet data using familiar methods. For advanced customization, direct access to the underlying OpenSpout reader/writer instances provides full control over format-specific options while maintaining the simplified interface for common operations.