library spreadsheet

nealyip/spreadsheet

Spreadsheet read writer abstraction for box/spout and phpexcel

Description

Spreadsheet read writer abstraction for box/spout and phpexcel, support Laravel, _(*1)

Updates

v1.1.2 Fix header for downloading xlsx, throw generic exception on boxspout write function
v1.1.0 Add PHPSpreadsheet. PHPExcel was deprecated. Allow use of class with namespace for SPREADSHEET_WRITER and SPREADSHEET_READER from .env, _(*2)

Installation

composer require nealyip/spreadsheet

Add this provider to config/app.php, _(*3)

\Nealyip\Spreadsheet\SpreadsheetServiceProvider::class,

Configuration

Publish config, _(*4)

php artisan vendor:publish --provider="Nealyip\Spreadsheet\SpreadsheetServiceProvider"

Simply change config/spreadsheet.php to select one spreadsheet data provider
PHPSpreadsheet is used by default, _(*5)

or configure on the .env file, _(*6)

SPREADSHEET_WRITER=PHPSpreadsheet  
SPREADSHEET_READER=BoxSpout  

Or if you implement your own Writer or Reader, you may use full class name here., _(*7)

SPREADSHEET_WRITER=App\Spreadsheet\CustomerWriter

be remember to implement, _(*8)

Nealyip\Spreadsheet\Writer

How to use

Dependency Injection, _(*9)

Reader

use Nealyip\Spreadsheet\Reader;
class Sth{
    protected $_reader;

    public function __construct(Reader $reader) {
        $this->_reader = $reader;
    }

    public function readFile($filename){
        $data = $this->_reader->toKeyValueArray($filename);

    }

Reader using generator

use Nealyip\Spreadsheet\Reader;
class Sth{
    protected $_reader;

    public function __construct(Reader $reader) {
        $this->_reader = $reader;
    }

    public function readFile($filename){
        $data = $this->_reader->toKeyValueArray($filename);
        foreach ($this->_reader->read($filename) as $item){
            // $item is a row in array form        
        } 
    }

Writer

use Nealyip\Spreadsheet\Writer;
class Sth{
    protected $_writer;

    public function __construct(Writer $writer) {
        $this->_writer = $writer;
    }

    public function writeFile($filename){

        $headers = ['Name', 'Gender', 'Age'];

        $this->_writer
            ->setup("report.xlsx")
            ->useSheet('Report')
            ->writeArray([['Tom','M','20'], ['Ann','F','24']], $headers)
            ->save();

    }

Writer using generator

use Nealyip\Spreadsheet\Writer;
class Sth{
    protected $_writer;

    public function __construct(Writer $writer) {
        $this->_writer = $writer;
    }

    /**    
     * Data source from DB/API etc
     * 
     * @return \Generator
     */
    protected function _data(){
        $data = [['Tom','M','20'], ['Ann','F','24']];
        foreach ($data as $d) {
            yield $d;
        }
    }


    public function writeFile($filename){

        $headers = ['Name', 'Gender', 'Age'];

        $this->_writer
            ->setup("report.xlsx")
            ->useSheet('Report')
            ->write($this->_data(), $headers)
            ->save();
    }

Write to local file

use Nealyip\Spreadsheet\Writer;
class Sth{
    protected $_writer;

    public function __construct(Writer $writer) {
        $this->_writer = $writer;
    }

    /**    
     * Data source from DB/API etc
     * 
     * @return \Generator
     */
    protected function _data(){
        $data = [['Tom','M','20'], ['Ann','F','24']];
        foreach ($data as $d) {
            yield $d;
        }
    }


    public function writeFile($filename){

        $headers = ['Name', 'Gender', 'Age'];

        $this->_writer
            ->setup("report.xlsx", false)
            ->useSheet('Report')
            ->write($this->_data(), $headers)
            ->save();
    }

Memory limit and execution timeout

If you have encounter memory exhaust problem, you may tune the memory limit by, _(*10)

ini_set('memory_limit', '1000M');

or for execution timeout, _(*11)

ini_set('max_execution_time', 300);