PHP code example of spatie / simple-excel

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

spatie / simple-excel example snippets

use Spatie\SimpleExcel\SimpleExcelReader;

SimpleExcelReader::create($pathToFile)->getRows()
   ->each(function(array $rowProperties) {
        // process the row
    });

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']
});

SimpleExcelReader::create($pathToCsv)->getRows()
    ->filter(function(array $rowProperties) {
       return strlen($rowProperties['first_name']) > 5;
    })
    ->each(function(array $rowProperties) {
        // processing rows
    });

// $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']
});

// $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']
});

$rows = SimpleExcelReader::create($pathToXlsx)
    ->fromSheet(3)
    ->getRows();

$rows = SimpleExcelReader::create($pathToXlsx)
    ->fromSheetName("sheet1")
    ->getRows();

$hasSheet = SimpleExcelReader::create($pathToXlsx)
    ->hasSheet("sheet1");

$headers = SimpleExcelReader::create($pathToCsv)->getHeaders();

// $headers will contain
// [ 'email', 'first_name' ]

// $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']
});

// $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']
});

$rows = SimpleExcelReader::create($pathToCsv)
    ->headersToSnakeCase()
    ->getRows()
    ->each(function(array $rowProperties) {
        // rowProperties converted to snake_case
        // ['email' => 'john@example', 'first_name' => 'John', 'last_name' => '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']
    });

$reader = SimpleExcelReader::create($pathToCsv)->getReader();

// $rows is an instance of Illuminate\Support\LazyCollection
$rows = SimpleExcelReader::create($pathToCsv)
    ->take(5)
    ->getRows();

$rows = SimpleExcelReader::create($pathToCsv)
    ->skip(10)
    ->take(5)
    ->getRows();

$rows = SimpleExcelReader::create($pathToXlsx)
    ->keepFormulas()
    ->getRows();

$rows = SimpleExcelReader::create($pathToXlsx)
    ->preserveDateTimeFormatting()
    ->getRows();

$rows = SimpleExcelReader::create($pathToXlsx)
    ->preserveEmptyRows()
    ->getRows();

use Spatie\SimpleExcel\SimpleExcelWriter;

$writer = SimpleExcelWriter::create($pathToCsv)
     ->addRow([
        'first_name' => 'John',
        'last_name' => 'Doe',
    ])
    ->addRow([
        'first_name' => 'Jane',
        'last_name' => 'Doe',
    ]);

use Spatie\SimpleExcel\SimpleExcelWriter;

$writer = SimpleExcelWriter::create($pathToCsv)
    ->addHeader(['first_name', 'last_name'])
    ->addRow(['John', 'Doe'])
    ->addRow(['Jane', 'Doe'])

$writer->close();

$writer = SimpleExcelWriter::streamDownload('your-export.xlsx')
     ->addRow([
        'first_name' => 'John',
        'last_name' => 'Doe',
    ])
    ->addRow([
        'first_name' => 'Jane',
        'last_name' => 'Doe',
    ])
    ->toBrowser();

$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();

use Spatie\SimpleExcel\SimpleExcelWriter;
use OpenSpout\Common\Entity\Row;

$writer = SimpleExcelWriter::streamDownload('user-list.xlsx', function ($writerCallback, $downloadName) {

    $writerCallback->openToBrowser($downloadName);

    $writerCallback->addRow(Row::fromValues([
        'first_name' => 'First Name',
        'last_name' => 'Last Name',
    ]));

    $writerCallback->addRow(Row::fromValues([
        'first_name' => 'Rakib',
        'last_name' => 'Hossain',
    ]));

    foreach (range(1, 10_000) as $i) {
        $writerCallback->addRow(Row::fromValues([
            'first_name' => 'Rakib',
            'last_name' => 'Hossain',
        ]));

        if ($i % 1000 === 0) {
            flush();
        }
    }
});

$writer->toBrowser();

$writer = SimpleExcelWriter::streamDownload('your-export.xlsx')
     ->addRows([
        [
            'first_name' => 'John',
            'last_name' => 'Doe',
        ],
        [
            'first_name' => 'Jane',
            'last_name' => 'Doe',
        ],
    ]);

$writer = SimpleExcelWriter::create($pathToCsv)
    ->noHeaderRow()
    ->addRow([
        'first_name' => 'Jane',
        'last_name' => 'Doe',
    ]);

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);

$writer->setHeaderStyle($style);

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);
    }
)

$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();
    }
});

SimpleExcelWriter::create(file: $pathToCsv, delimiter: ';');

$writerWithAutomaticHeader = SimpleExcelWriter::create($this->pathToCsv)
    ->addRow([
        'first_name' => 'John',
        'last_name' => 'Doe',
    ]);

$writerWithAutomaticHeader->getNumberOfRows(); // returns 2

SimpleExcelWriter::createWithoutBom($this->pathToCsv, $type);

$writer = SimpleExcelWriter::create($pathToCsv)->getWriter();