repository·main·Indexed 23 days ago
https://github.com/spatie/simple-excelA PHP package for reading and writing simple Excel (.xlsx, .ods) and CSV files. It utilizes generators and Laravel's LazyCollection to maintain low memory consumption when processing large datasets. Features include automatic file type detection, header transformation (snake_case, custom headers), value formatting, and support for multiple sheets in Excel files. The SimpleExcelWriter allows for row styling via OpenSpout, streaming downloads to the browser, and custom CSV delimiters.
The SimpleExcelReader determines whether to treat a file as a CSV or an Excel file based on its file extension:
.csv, it is treated as a CSV file..xlsx, it is treated as an Excel file.Use SimpleExcelReader::create($path) to initialize a reader. For CSV files, ensure the path ends in .csv. For Excel files, ensure it ends in .xlsx.
Calling getRows() returns an instance of Illuminate extbackslash Support extbackslash LazyCollection, which allows for memory-efficient processing of large files using generators. You can use standard Laravel collection methods like each(), filter(), take(), or skip() on the result.
use Spatie\SimpleExcel\SimpleExcelReader;
// Returns a LazyCollection
$rows = SimpleExcelReader::create($pathToCsv)->getRows();
$rows->each(function(array $rowProperties) {
// $rowProperties contains column data as associative array
});Since styling is only supported for Excel (.xlsx), you can use OpenSpout styles.
Style object as the second argument to addRow($row, $style).setHeaderStyle($style) to style the header row.configureWriter callback in create() to set default column widths, row heights, or specific column widths.use Spatie\SimpleExcel\SimpleExcelWriter;
use OpenSpout\Common\Entity\Style\Style;
use OpenSpout\Common\Entity\Style\Color;
$style = (new Style())->setFontBold()->setFontColor(Color::BLUE);
$writer = SimpleExcelWriter::create('document.xlsx', configureWriter: function ($writer) {
$options = $writer->getOptions();
$options->DEFAULT_COLUMN_WIDTH = 25;
$options->setColumnWidth(40, 1, 3, 8);
});
$writer->setHeaderStyle($style);
$writer->addRow(['Header 1', 'Header 2'], $style);When upgrading from version 2.x to 3.0, note the following breaking changes:
openspout/openspout v4 is added, while support for v3 is dropped.useDelimiter() on SimpleExcelWriter and headerRowFormatter on SimpleExcelReader have been removed.Use SimpleExcelReader::create($pathToFile) to initialize a reader. The package automatically detects the file type based on the extension: .csv for CSV files and .xlsx for Excel files.
To process rows with low memory usage, call getRows() which returns a generator, and use the each() method to iterate through the rows.
use Spatie\
SimpleExcel\
\SimpleExcelReader;
SimpleExcelReader::create($pathToFile)->getRows()
->each(function(array $rowProperties) {
// process the row
});Use SimpleExcelWriter::create($path) to write files. For Excel, use a .xlsx extension.
Important: For Excel files, the file is not finalized on disk until the instance is garbage collected or you manually call $writer->close().
Key Methods:
addRow(array $row): Adds a single row.addRows(array $rows): Adds multiple rows at once.addHeader(array $headers): Manually sets the header row.noHeaderRow(): Prevents the writer from automatically adding a header row.getNumberOfRows(): Returns the count of rows written (including the header).Install the package using Composer to enable reading and writing of Excel and CSV files.
composer require spatie/simple-excelYou can stream an Excel file directly to a user's browser using streamDownload().
If you are generating a large file, call flush() periodically within your loop to prevent memory issues and ensure the browser receives data chunks. Finally, call toBrowser() to initiate the download.
In compatibility with OpenSpout v4, StyleBuilder is removed. You should now use the Style class directly to define cell styles.
// Old way
use OpenSpout\
Writer\Common\Creator\Style\StyleBuilder;
$builder = new StyleBuilder();
$builder
->setFontBold()
->setFontName('Sans');
// New way
use OpenSpout\Common\Entity\Style\Style;
$style = new Style();
$style
->setFontBold()
->setFontName('Sans');SimpleExcelWriter writes to the first sheet. To add more sheets, use addNewSheetAndMakeItCurrent() to create a new sheet and switch the writer's focus to it. You can also use nameCurrentSheet($name) to rename the active sheet.useDelimiter() method has been removed. Instead, pass the delimiter as a named parameter to the SimpleExcelWriter::create() method.Clean up data during the reading process:
headersToSnakeCase() to convert all header keys to snake_case.formatHeadersUsing(Closure $callback) to transform header names.trimHeaderRow() to strip whitespace from header names.trimValues() to strip whitespace from all cell values. You can pass an optional string of characters to trim (e.g., trimValues('*')).formatValuesUsing(Closure $callback) for full control. The closure receives ($value, $key).// Convert headers to snake_case and trim whitespace from values
$rows = SimpleExcelReader::create($pathToCsv)
->headersToSnakeCase()
->trimValues()
->getRows();
// Custom value formatting
$rows = SimpleExcelReader::create($pathToCsv)
->formatValuesUsing(function ($value, $key) {
return $key === 'email' ? strtolower($value) : $value;
})
->getRows();