Stars: 948
Forks: 103
Pull Requests: 67
Issues: 0
Watchers: 20
Last Updated: 2023-06-21 10:59:27
Read and write simple Excel and CSV files
License: MIT License
Languages: PHP
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.
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.
You can install the package via composer:
composer require spatie/simple-excel
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 is identical to reading a CSV file. Just make sure that the path given to the create
method of SimpleExcelReader
ends with xlsx
.
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
});
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']
});
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.
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();
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 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' ]
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']
});
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.
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']
});
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']
});
Under the hood this package uses the box/spout package. You can get to the underlying reader that implements \OpenSpout\Reader\ReaderInterface
by calling the getReader
method.
$reader = SimpleExcelReader::create($pathToCsv)->getReader();
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();
Here's how you can write a CSV file:
use Spatie\SimpleExcel\SimpleExcelWriter;
$writer = SimpleExcelWriter::create($pathToCsv)
->addRow([
'first_name' => 'John',
'last_name' => 'Doe',
])
->addRow([
'first_name' => 'Jane',
'last_name' => 'Doe',
]);
The file at pathToCsv
will contain:
first_name,last_name
John,Doe
Jane,Doe
Instead of automatically let the package dedecting a header row, you can set it manually.
use Spatie\SimpleExcel\SimpleExcelWriter;
$writer = SimpleExcelWriter::create($pathToCsv)
->addHeader(['first_name', 'last_name'])
->addRow(['John', 'Doe'])
->addRow(['Jane', 'Doe'])
Writing an Excel file is identical to writing a csv. Just make sure that the path given to the create
method of SimpleExcelWriter
ends with xlsx
.
Instead of writing a file to disk, you can stream it directly to the browser.
$writer = SimpleExcelWriter::streamDownload('your-export.xlsx')
->addRow([
'first_name' => 'John',
'last_name' => 'Doe',
])
->addRow([
'first_name' => 'Jane',
'last_name' => 'Doe',
])
->toBrowser();
Make sure to call flush()
if you're sending large streams to the browser
$writer = SimpleExcelWriter::streamDownload('your-export.xlsx');
foreach (range(1, 10_000) as $i) {
$writer->addRow([
'first_name' => 'John',
'last_name' => 'Doe',
]);
if ($i % 1000 === 0) {
flush(); // Flush the buffer every 1000 rows
}
}
$writer->toBrowser();
You can use addRows
instead of addRow
to add multiple rows at once.
$writer = SimpleExcelWriter::streamDownload('your-export.xlsx')
->addRows([
[
'first_name' => 'John',
'last_name' => 'Doe',
],
[
'first_name' => 'Jane',
'last_name' => 'Doe',
],
]);
If the file you are writing should not have a title row added automatically, then you should use the noHeaderRow()
method.
$writer = SimpleExcelWriter::create($pathToCsv)
->noHeaderRow()
->addRow([
'first_name' => 'Jane',
'last_name' => 'Doe',
]);
This will output:
Jane,Doe
Under the hood this package uses the openspout/openspout package. That package contains a Style
builder that you can use to format rows. Styles can only be used on excel documents.
use OpenSpout\Common\Entity\Style\Color;
use OpenSpout\Common\Entity\Style\CellAlignment;
use OpenSpout\Common\Entity\Style\Style;
use OpenSpout\Common\Entity\Style\Border;
use OpenSpout\Common\Entity\Style\BorderPart;
/* Create a border around a cell */
$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)
);
$style = (new Style())
->setFontBold()
->setFontSize(15)
->setFontColor(Color::BLUE)
->setShouldWrapText()
->setBackgroundColor(Color::YELLOW)
->setBorder($border);
$writer->addRow(['values', 'of', 'the', 'row'], $style);
To style your HeaderRow simply call the setHeaderStyle($style)
Method.
$writer->setHeaderStyle($style);
For more information on styles head over to the Spout docs.
By accessing the underlying OpenSpout Writer you can set default column widths and row heights and change the width of specific columns.
SimpleExcelWriter::create(
file: 'document.xlsx',
configureWriter: function ($writer) {
$options = $writer->getOptions();
$options->DEFAULT_COLUMN_WIDTH=25; // set default width
$options->DEFAULT_ROW_HEIGHT=15; // set default height
// set columns 1, 3 and 8 to width 40
$options->setColumnWidth(40, 1, 3, 8);
// set columns 9 through 12 to width 10
$options->setColumnWidthForRange(10, 9, 12);
}
)
By default, the writer will write to the first sheet. If you want to write to an additional sheet, you can use the addNewSheetAndMakeItCurrent
method.
$writer = SimpleExcelWriter::create($pathToXlsx);
Posts::all()->each(function (Post $post) use ($writer) {
$writer->nameCurrentSheet($post->title);
$post->comments->each(function (Comment $comment) use ($writer) {
$writer->addRow([
'comment' => $comment->comment,
'author' => $comment->author,
]);
});
if(!$post->is($posts->last())) {
$writer->addNewSheetAndMakeItCurrent();
}
});
By default the SimpleExcelReader
will assume that the delimiter is a ,
.
This is how you can use an alternative delimiter:
SimpleExcelWriter::create(file: $pathToCsv, delimiter: ';');
You can get the number of rows that are written. This number includes the automatically added header row.
$writerWithAutomaticHeader = SimpleExcelWriter::create($this->pathToCsv)
->addRow([
'first_name' => 'John',
'last_name' => 'Doe',
]);
$writerWithAutomaticHeader->getNumberOfRows(); // returns 2
You can also disable adding a BOM to the start of the file. BOM must be disabled on create and cannot be disabled after creation of the writer.
A BOM, or byte order mark, indicates a number of things for the file being written including the file being unicode as well as it's UTF encoding type.
SimpleExcelWriter::createWithoutBom($this->pathToCsv, $type);
Additional information about BOM can be found here.
Under the hood this package uses the openspout/openspout package. You can get to the underlying writer that implements \OpenSpout\Reader\WriterInterface
by calling the getWriter
method.
$writer = SimpleExcelWriter::create($pathToCsv)->getWriter();
composer test
Please see CHANGELOG for more information on what has changed recently.
Please see CONTRIBUTING for details.
If you've found a bug regarding security please mail [email protected] instead of using the issue tracker.
You're free to use this package, but if it makes it to your production environment we highly appreciate you sending us a postcard from your hometown, mentioning which of our package(s) you are using.
Our address is: Spatie, Kruikstraat 22, 2018 Antwerp, Belgium.
We publish all received postcards on our company website.
The MIT License (MIT). Please see License File for more information.