xaamin / simple-excel
Excel reader and writer
Requires
- php: >=8.0.0
- openspout/openspout: ^4.8
Requires (Dev)
- pestphp/pest: ^5.3
- phpstan/phpstan: ^2.1
- squizlabs/php_codesniffer: ^3.8
Suggests
None
Provides
None
Conflicts
None
Replaces
None
README
This package allows you to easily read and write simple Excel (.xlsx), OpenDocument (.ods) and CSV files. Behind the scenes generators are used to ensure low memory usage, even when working with large files.
use Xaamin\SimpleExcel\ExcelReader;
$reader = ExcelReader::create($pathToFile);
foreach ($reader->rows() as $row) {
// Process the row
}
The file type is guessed from the extension (.csv, .xlsx or .ods). Plain text files (e.g. .txt) are read as CSV.
Requirements
- PHP 8.0 or higher
- openspout/openspout
^4.8(installed automatically)
Installation
composer require xaamin/simple-excel
Reading files
Imagine you have a CSV (or an Excel file) with this content:
email,first_name
john@example.com,john
jane@example.com,jane
By default the first row is used as header, so every row is returned as an associative array.
Importing every row
$rows = ExcelReader::create($pathToCsv)->import();
// [
// ['email' => 'john@example.com', 'first_name' => 'john'],
// ['email' => 'jane@example.com', 'first_name' => 'jane'],
// ]
Pass a callback to map every row:
$emails = ExcelReader::create($pathToCsv)->import(fn (array $row) => $row['email']);
// ['john@example.com', 'jane@example.com']
Iterating rows
For large files prefer the generators, they only keep one row (or one chunk) in memory.
$reader = ExcelReader::create($pathToCsv);
foreach ($reader->rows() as $row) {
// ['email' => 'john@example.com', 'first_name' => 'john']
}
foreach ($reader->chunk(500) as $rows) {
// Arrays of up to 500 rows
}
chunk() also accepts a closure. It is still a generator, so it must be iterated for the callback to run:
iterator_to_array($reader->chunk(500, function (array $rows) {
// Save the rows
}));
Other ways to read rows:
$reader = ExcelReader::create($pathToCsv);
$reader->first(); // ['email' => 'john@example.com', 'first_name' => 'john']
$reader->next(); // first row
$reader->next(); // second row
$reader->next(); // null, no more rows
Offset and limit
// Skip the first 100 rows and read the next 50
foreach (ExcelReader::create($pathToCsv)->offset(100)->limit(50)->rows() as $row) {
//
}
Reading a file without header
If the file does not contain a header row, disable it with withHeader(false). Rows are returned as indexed arrays.
$rows = ExcelReader::create($pathToCsv)->withHeader(false)->import();
// [['email', 'first_name'], ['john@example.com', 'john'], ...]
When the header is enabled every row is padded or trimmed to the number of header columns. You can get the computed header with getHeaders() after reading.
Normalizing the header
Use a header parser to transform the header cells into keys. The included SlugHeaderParser converts them into snake_case, removes diacritics and makes duplicated names unique:
use Xaamin\SimpleExcel\Support\SlugHeaderParser;
// Header: First Name, Correo Electrónico, first name
$rows = ExcelReader::create($pathToCsv)
->withHeaderParser(new SlugHeaderParser())
->import();
// [['first_name' => 'Ana', 'correo_electronico' => 'ana@example.com', 'first_name_1' => '...']]
Implement Xaamin\SimpleExcel\Contracts\ExcelHeaderParserInterface (getColumns(array $cells) and reset()) to write your own. Pass false as the second argument of withHeaderParser() when the file has no header row and your parser provides the column names, so the first row is read as data.
Working with sheets
Sheets are numbered starting at 1. The first sheet is used by default.
$rows = ExcelReader::create($pathToXlsx)->sheet(2)->import();
// One array of rows per sheet
$sheets = ExcelReader::create($pathToXlsx)->importSheets();
// Sheet names keyed by sheet number: [1 => 'People', 2 => 'Cities']
$names = ExcelReader::create($pathToXlsx)->getSheetNames();
Selecting a sheet that does not exist throws an OutOfRangeException.
CSV options
ExcelReader::create($pathToCsv)
->useDelimiter(';')
->useFieldEnclosure("'")
->import();
You can also pass an OpenSpout options object (OpenSpout\Reader\CSV\Options, XLSX\Options or ODS\Options) as the second argument of create().
Reusing the reader
fresh() resets the configuration (header, sheet, offset, limit and options) and reopens the same file.
$reader = ExcelReader::create($pathToCsv)->offset(10)->limit(5);
$reader->fresh()->first(); // first row again
Accessing the OpenSpout reader
$reader = ExcelReader::create($pathToCsv)->getReader(); // OpenSpout\Reader\ReaderInterface
Writing files
The keys of the first row are written as the header:
use Xaamin\SimpleExcel\ExcelWriter;
$writer = ExcelWriter::create($pathToCsv)
->addRow(['first_name' => 'John', 'last_name' => 'Doe'])
->addRow(['first_name' => 'Jane', 'last_name' => 'Doe']);
$writer->close();
The file at $pathToCsv will contain:
first_name,last_name
John,Doe
Jane,Doe
The file is written when close() is called (or when the writer is destroyed). Writing an .xlsx or .ods file works the same way, just change the extension.
Writing multiple rows at once
addRows() accepts any iterable, including generators:
ExcelWriter::create($pathToXlsx)
->addRows([
['first_name' => 'John', 'last_name' => 'Doe'],
['first_name' => 'Jane', 'last_name' => 'Doe'],
])
->close();
Writing a file without header
ExcelWriter::create($pathToCsv)
->withHeader(false)
->addRow(['first_name' => 'Jane', 'last_name' => 'Doe'])
->close();
// Jane,Doe
Multiple sheets
Every new sheet gets its own header row.
$writer = ExcelWriter::create($pathToXlsx);
$writer->setName('People')
->addRow(['name' => 'Ana', 'age' => 30])
->addNewSheet('Cities')
->addRow(['city' => 'Oaxaca'])
->setActiveSheet('People') // go back to a previous sheet by name
->addRow(['name' => 'Luis', 'age' => 41]);
$writer->close();
Styling rows
Styles only apply to .xlsx and .ods files.
use OpenSpout\Common\Entity\Style\Color;
use Xaamin\SimpleExcel\Support\Writer\Style\StyleBuilder;
$style = (new StyleBuilder())
->setFontBold()
->setFontSize(15)
->setFontColor(Color::BLUE)
->setShouldWrapText()
->setBackgroundColor(Color::YELLOW)
->build();
$writer->addRow(['name' => 'Ana', 'age' => 30], $style);
Borders can be built with Xaamin\SimpleExcel\Support\Writer\Style\BorderBuilder and applied with StyleBuilder::setBorder(). You can also add OpenSpout rows directly, e.g. created with Xaamin\SimpleExcel\Support\Writer\WriterEntityFactory::createRowFromArray($values, $style).
Streaming a file to the browser
Instead of writing a file to disk, you can stream it directly to the browser. The file type is taken from the download name.
ExcelWriter::downloadAs('your-export.xlsx')
->addRows($rows)
->sendToBrowser();
sendToBrowser() closes the writer and ends the script with exit(), so call it last.
CSV options
Set the CSV options before adding rows, the file is reopened when they change.
ExcelWriter::create($pathToCsv)
->useDelimiter(';')
->useFieldEnclosure("'")
->addRow(['name' => 'Ana', 'age' => 30])
->close();
You can also pass an OpenSpout options object (OpenSpout\Writer\CSV\Options, XLSX\Options or ODS\Options) as the third argument of create().
Getting the number of rows written
The count includes the automatically added header row.
$writer = ExcelWriter::create($pathToCsv)
->addRow(['first_name' => 'John', 'last_name' => 'Doe']);
$writer->getNumberOfRows(); // 2
Accessing the OpenSpout writer
$writer = ExcelWriter::create($pathToCsv)->getWriter(); // OpenSpout\Writer\WriterInterface
Testing
composer test
Contributing
Please see CONTRIBUTING for details.
License
The MIT License (MIT). Please see License File for more information.