simple-excel

Read and write simple Excel and CSV files

This package allows you to easily read and write simple Excel and CSV files. Behind the scenes generators are used to ensure low memory usage, even when working with large files.

Here's an example on how to read an Excel or CSV.

use Spatie\SimpleExcel\SimpleExcelReader;

SimpleExcelReader::create($pathToFile)->getRows()
   ->each(function(array $rowProperties) {
        // process the row
    });

If $pathToFile ends with .csv a CSV file is assumed. If it ends with .xlsx, an Excel file is assumed.

Support us

We invest a lot of resources into creating best in class open source packages. You can support us by buying one of our paid products.

We highly appreciate you sending us a postcard from your hometown, mentioning which of our package(s) you are using. You'll find our address on our contact page. We publish all received postcards on our virtual postcard wall.

Installation

You can install the package via composer:

composer require spatie/simple-excel

Usage

Reading a CSV

Imagine you have a CSV with this content.

email,first_name
[email protected],john
[email protected],jane

use Spatie\SimpleExcel\SimpleExcelReader;

// $rows is an instance of Illuminate\Support\LazyCollection
$rows = SimpleExcelReader::create($pathToCsv)->getRows();

$rows->each(function(array $rowProperties) {
   // in the first pass $rowProperties will contain
   // ['email' => '[email protected]', 'first_name' => 'john']
});

Reading an Excel file

Reading an Excel file is identical to reading a CSV file. Just make sure that the path given to the create method of SimpleExcelReader ends with xlsx.

Working with LazyCollections

getRows will return an instance of Illuminate\Support\LazyCollection. This class is part of the Laravel framework. Behind the scenes generators are used, so memory usage will be low, even for large files.

You'll find a list of methods you can use on a LazyCollection in the Laravel documentation.

Here's a quick, silly example where we only want to process rows that have a first_name that contains more than 5 characters.

SimpleExcelReader::create($pathToCsv)->getRows()
    ->filter(function(array $rowProperties) {
       return strlen($rowProperties['first_name']) > 5;
    })
    ->each(function(array $rowProperties) {
        // processing rows
    });

Reading a file without headers

If the file you are reading does not contain a header row, then you should use the noHeaderRow() method.

// $rows is an instance of Illuminate\Support\LazyCollection
$rows = SimpleExcelReader::create($pathToCsv)
    ->noHeaderRow()
    ->getRows()
    ->each(function(array $rowProperties) {
       // in the first pass $rowProperties will contain
       // [0 => 'john@example', 1 => 'john']
});

Manually setting the headers

If you would like to use a specific array of values for the headers, you can use the useHeaders() method.

// $rows is an instance of Illuminate\Support\LazyCollection
$rows = SimpleExcelReader::create($pathToCsv)
    ->useHeaders(['email_address', 'given_name'])
    ->getRows()
    ->each(function(array $rowProperties) {
       // in the first pass $rowProperties will contain
       // ['email_address' => 'john@example', 'given_name' => 'john']
});

If your file already contains a header row, it will be ignored and replaced with your custom headers.

If your file does not contain a header row, you should also use noHeaderRow(), and your headers will be used instead of numeric keys, as above.

Working with multiple sheet documents

Excel files can include multiple spreadsheets. You can select the sheet you want to use with the fromSheet() method to select by index.

$rows = SimpleExcelReader::create($pathToXlsx)
    ->fromSheet(3)
    ->getRows();

You can also retrieve all sheet names in a document using the getSheetNames() method.

$sheets = SimpleExcelReader::create($pathToXlsx)
    ->getSheetNames();

With multiple spreadsheets, you can too select the sheet you want to use with the fromSheetName() method to select by name.

$rows = SimpleExcelReader::create($pathToXlsx)
    ->fromSheetName("sheet1")
    ->getRows();

If you want to check if a sheet exists, use the hasSheet() method.

$hasSheet = SimpleExcelReader::create($pathToXlsx)
    ->hasSheet("sheet1");

Retrieving header row values

If you would like to retrieve the header row as an array, you can use the getHeaders() method.

If you have used useHeaders() to set custom headers, these will be returned instead of the actual headers in the file. To get the original headers from the file, use getOriginalHeaders().

$headers = SimpleExcelReader::create($pathToCsv)->getHeaders();

// $headers will contain
// [ 'email', 'first_name' ]

Dealing with headers that are not on the first line

If your file has headers that are not on the first line, you can use the headerOnRow() method to indicate the line at which the headers are present. Any data above this line will be discarded from the result.

headerOnRow accepts the line number as an argument, starting at 0. Blank lines are not counted.

Since blank lines will not be counted, this method is mostly useful for files that include formatting above the actual dataset, which can be the case with Excel files.

This is my data sheet
See worksheet 1 for the data, worksheet 2 for the graphs.

email , firstname
[email protected],john
[email protected],jane

// $rows is an instance of Illuminate\Support\LazyCollection
$rows = SimpleExcelReader::create($pathToCsv)
    ->trimHeaderRow()
    ->headerOnRow(3)
    ->getRows()
    ->each(function(array $rowProperties) {
       // in the first pass $rowProperties will contain
       // ['email' => 'john@example', 'first_name' => 'john']
});

Trimming headers

If the file you are reading contains a title row, but you need to trim additional characters on the title values, then you should use the trimHeaderRow() method. This functionality mimics the trim method, and the default characters it trims, matches that function.

Imagine you have a csv file with this content.

email , first_name
[email protected],john
[email protected],jane

// $rows is an instance of Illuminate\Support\LazyCollection
$rows = SimpleExcelReader::create($pathToCsv)
    ->trimHeaderRow()
    ->getRows()
    ->each(function(array $rowProperties) {
       // in the first pass $rowProperties will contain
       // ['email' => 'john@example', 'first_name' => 'john']
});

trimHeaderRow() additionally accepts a param to specify what characters to trim. This param can utilize the same functionality allowed by the trim function's $characters definition including a range of characters.

Convert headers to snake_case

If you would like all the headers to be converted to snake_case, use the the headersToSnakeCase() method.

Email,First Name,Last Name
[email protected],john,doe
[email protected],mary jane,doe

$rows = SimpleExcelReader::create($pathToCsv)
    ->headersToSnakeCase()
    ->getRows()
    ->each(function(array $rowProperties) {
        // rowProperties converted to snake_case
        // ['email' => 'john@example', 'first_name' => 'John', 'last_name' => 'doe']
    });

Manually formatting headers

You can use a custom formatter to change the headers using the formatHeadersUsing method and passing a closure.

email,first_name,last_name
[email protected],john,doe
[email protected],mary jane,doe

$rows = SimpleExcelReader::create($pathToCsv)
    ->formatHeadersUsing(fn($header) => "{$header}_simple_excel")
    ->getRows()
    ->each(function(array $rowProperties) {
        // ['email_simple_excel' => 'john@example', 'first_name_simple_excel' => 'John', 'last_name_simple_excel' => 'doe']
    });

Trimming and formatting values

Just like you can clean up header names, you can clean up the data values themselves. This is handy when importing files where cells contain stray whitespace or need to be normalized.

Use trimValues() to strip whitespace from every value.

email,first_name
  [email protected]  ,  john
[email protected],jane

$rows = SimpleExcelReader::create($pathToCsv)
    ->trimValues()
    ->getRows()
    ->each(function(array $rowProperties) {
       // in the first pass $rowProperties will contain
       // ['email' => '[email protected]', 'first_name' => 'john']
    });

Like trim, trimValues() accepts an optional argument specifying which characters to trim. This argument is a set of characters stripped from both ends of each value (exactly like PHP's trim), not a suffix. For example, trimValues('*') removes any leading or trailing * from every value. Be careful with letters: trimValues('.com') would also turn Tom into T.

$rows = SimpleExcelReader::create($pathToCsv)
    ->trimValues('*')
    ->getRows();

For full control, use formatValuesUsing() and pass a closure. The closure receives the value and its header key, so you can normalize values per column. Non-string values (such as dates read from an Excel file) are passed through untouched by trimValues(), but your own closure is responsible for handling them.

$rows = SimpleExcelReader::create($pathToCsv)
    ->formatValuesUsing(function ($value, $key) {
        return $key === 'email' ? strtolower($value) : $value;
    })
    ->getRows();

Manually working with the reader object

Under the hood this package uses the openspout/spout package. You can get to the underlying reader that implements \OpenSpout\Reader\ReaderInterface by calling the getReader method.

$reader = SimpleExcelReader::create($pathToCsv)->getReader();

Limiting the result set

The take method allows you to specify a limit on how many rows should be returned.

// $rows is an instance of Illuminate\Support\LazyCollection
$rows = SimpleExcelReader::create($pathToCsv)
    ->take(5)
    ->getRows();

The skip method allows you to define which row to start reading data from. In this example we get rows 11 to 16.

$rows = SimpleExcelReader::create($pathToCsv)
    ->skip(10)
    ->take(5)
    ->getRows();

Reading cells that contain formulas

Normally, cells containing formulas are parsed and their computed value will be returned. If you want to keep the actual formula as a string, you can use the keepFormulas method.

$rows = SimpleExcelReader::create($pathToXlsx)
    ->keepFormulas()
    ->getRows();

Preserve date formatting

By default, when reading a spreadsheet with dates or times, the values are returned as DateTimeImmutable objects. To return a formatted date (e.g., “9/20/2024”) instead, use the preserveDateTimeFormatting method. The date format will match what’s specified in the spreadsheet.

$rows = SimpleExcelReader::create($pathToXlsx)
    ->preserveDateTimeFormatting()
    ->getRows();

Preserve empty rows

You can preserve empty rows by using the preserveEmptyRows method.

$rows = SimpleExcelReader::create($pathToXlsx)
    ->preserveEmptyRows()
    ->getRows();

Writing files

Here's how you can write a CSV file:

use Spatie\SimpleExcel\SimpleExcelWriter;

$writer = SimpleExcelWriter::create($pathToCsv)
     ->addRow([