A clean and beautiful API to read Excel/CSV sheet. This is a wrapper around box/spout package.
composer require mahmud/sheet
- php: ^7.1.3
- box/spout: ^3.0
Let's assume we have a csv file like this.
ID | Name | Age |
---|---|---|
1 | Mahmud | 27 |
2 | Mohor | 26 |
3 | Ayman | 1 |
use Mahmud\Sheet\SheetReader;
SheetReader::makeFromCsv('/path-to-csv-file/example-file.csv')
->delimiter(",") // Optional: You can set delimiter for CSV file
->ignoreRow(0) // Optional: Skip the header row
->columns(['id', 'name', 'age']) // Arbitary column name that will be mapped sequentially for each row
->onEachRow(function($row, $index){
// This callback will be executed for each row
var_dump($row); // Current row in associative array
var_dump($index); // Current index of the row
})->read();
You can modify data of each row with middleware. See the following example
use Mahmud\Sheet\SheetReader;
SheetReader::makeFromCsv('/path-to-csv-file/example-file.csv')
->delimiter(",")
->ignoreRow(0)
->columns(['id', 'name', 'age'])
->applyMiddleware(function($row, $index){
$row['age'] = $row['age'] . " Years";
return $row;
})
->onEachRow(function($row, $index){
var_dump($row);
})->read();
Another example using class as middleware
class AgeMiddleware{
public function handle($row, $index) {
$row['age'] = $row['age'] . " Years";
return $row;
}
}
SheetReader::makeFromCsv('/path-to-csv-file/example-file.csv')
->delimiter(",")
->ignoreRow(0)
->columns(['id', 'name', 'age'])
->applyMiddleware(new AgeMiddleware)
->onEachRow(function($row, $index){
var_dump($row);
})->read();
Also you can pass array of middlewares
SheetReader::makeFromCsv('/path-to-csv-file/example-file.csv')
->delimiter(",")
->ignoreRow(0)
->columns(['id', 'name', 'age'])
->applyMiddleware([
new AgeMiddleware,
new AnotherMiddleware,
])
->onEachRow(function($row, $index){
var_dump($row);
})->read();
If you return null
from middleware, That row will be skipped and won't pass to onEachRow
handler.
SheetReader::makeFromCsv('/path-to-csv-file/example-file.csv')
->delimiter(",")
->ignoreRow(0)
->columns(['id', 'name', 'age'])
->applyMiddleware(function($row){
if($row['id'] == 1){
return null;
}
return $row;
})
->onEachRow(function($row, $index){
var_dump($row);
})->read();
$total = SheetReader::makeFromCsv('/path-to-csv-file/example-file.csv')
->totalRows();
var_dump($total); // 4