xaamin / simple-excel
Excel reader and writer
Requires
- php: >=8.0.0
- openspout/openspout: ^4.8
README
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.
$reader = ExcelReader::create($pathToFile)
foreach ($reader->rows() as $row) {
// Process the row
}
If $pathToFile
ends with .csv
or .txt
a CSV file is assumed. If it ends with .xlsx
, an Excel file is assumed.
Support us
Installation
You can install the package via composer:
composer require xaamin/simple-excel
Usage
Reading a CSV
Imagine you have a CSV with this content.
email,first_name
john@example.com,john
jane@example.com,jane
$rows = ExcelReader::create($pathToCsv);
foreach ($reader->rows() as $row) {
// In the first pass $row will contain
// ['email' => 'john@example.com', 'first_name' => 'john']
});
foreach ($reader->chunk(2) as $chunk) {
// Each chunk contains 2 rows
});
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 ExcelReader
ends with xlsx
.
Reading a file without titles
If the file you are reading does not contain a title row, then you should use the noHeaderRow()
method.
// $rows is an instance of Illuminate\Support\LazyCollection
$rows = ExcelReader::create($pathToCsv)
->withHeader(false);
foreach ($reader->rows() as $row) {
// in the first pass $rowProperties will contain
// [0 => 'john@example', 1 => 'john']
}
Manually working with the reader object
Under the hood this package uses the box/spout package. You can get to the underlying reader that implements \Box\Spout\Reader\ReaderInterface
by calling the getReader
method.
$reader = ExcelReader::create($pathToCsv)->getReader();
Writing files
Here's how you can write a CSV file:
$writer = ExcelWriter::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
Writing an Excel file
Writing an Excel file is identical to writing a csv. Just make sure that the path given to the create
method of ExcelWriter
ends with xlsx
.
Streaming an Excel file to the browser
Instead of writing a file to disk, you can stream it directly to the browser.
$writer = ExcelWriter::downloadAs('your-export.xlsx')
->addRow([
'first_name' => 'John',
'last_name' => 'Doe',
])
->addRow([
'first_name' => 'Jane',
'last_name' => 'Doe',
])
->sendToBrowser();
Writing multiple rows at once
You can use addRows
instead of addRow
to add multiple rows at once.
$writer = ExcelWriter::downloadAs('your-export.xlsx')
->addRows([
[
'first_name' => 'John',
'last_name' => 'Doe',
],
[
'first_name' => 'Jane',
'last_name' => 'Doe',
],
])->sendToBrowser();
Writing a file without titles
If the file you are writing should not have a title row added automatically, then you should use the noHeaderRow()
method.
$writer = ExcelWriter::create($pathToCsv)
->withHeader(false)
->addRow([
'first_name' => 'Jane',
'last_name' => 'Doe',
]);
});
This will output:
Jane,Doe
Adding layout
Under the hood this package uses the box/spout package. That package contains a StyleBuilder
that you can use to format rows. Styles can only be used on excel documents.
use Box\Spout\Common\Entity\Style\Color;
use Box\Spout\Writer\Common\Creator\Style\StyleBuilder;
$style = (new StyleBuilder())
->setFontBold()
->setFontSize(15)
->setFontColor(Color::BLUE)
->setShouldWrapText()
->setBackgroundColor(Color::YELLOW)
->build();
$writer->addRow(['values, 'of', 'the', 'row'], $style)
For more information on styles head over to the Spout docs.
Using an alternative delimiter
By default the ExcelReader
will assume that the delimiter is a ,
.
This is how you can use an alternative delimiter:
ExcelWriter::create($pathToCsv)->useDelimiter(';');
Getting the number of rows written
You can get the number of rows that are written. This number includes the automatically added header row.
$writerWithAutomaticHeader = ExcelWriter::create($this->pathToCsv)
->addRow([
'first_name' => 'John',
'last_name' => 'Doe',
]);
$writerWithoutAutomaticHeader->getNumberOfRows() // returns 2
Manually working with the writer object
Under the hood this package uses the box/spout package. You can get to the underlying writer that implements \Box\Spout\Reader\WriterInterface
by calling the getWriter
method.
$writer = ExcelWriter::create($pathToCsv)->getWriter();
Testing
Coming soon
Contributing
Please see CONTRIBUTING for details.
Security
If you discover any security related issues, please email freek@spatie.be instead of using the issue tracker.
License
The MIT License (MIT). Please see License File for more information.