PHP code example of wilsonglasser / spout

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

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

    

wilsonglasser / spout example snippets

use SpoutX\Common\Type;
use SpoutX\Writer\Common\Creator\WriterEntityFactory;

$writer = WriterEntityFactory::createWriter(Type::XLSX);
$writer->openToFile('/path/to/file.xlsx');   // or ->openToBrowser('file.xlsx')

// Add rows from plain arrays…
$writer->addRow(WriterEntityFactory::createRowFromArray(['Name', 'Age', 'Active']));
$writer->addRow(WriterEntityFactory::createRowFromArray(['Alice', 30, true]));

// …or add several at once
$writer->addRows([
    WriterEntityFactory::createRowFromArray(['Bob', 25, false]),
    WriterEntityFactory::createRowFromArray(['Carol', 41, true]),
]);

$writer->close();

use SpoutX\Common\Type;
use SpoutX\Reader\Common\Creator\ReaderEntityFactory;

$reader = ReaderEntityFactory::createReader(Type::XLSX);
// shortcut that infers the type from the extension:
// $reader = ReaderEntityFactory::createReaderFromFile('/path/to/file.xlsx');

$reader->open('/path/to/file.xlsx');

foreach ($reader->getSheetIterator() as $sheet) {
    echo "Sheet: {$sheet->getName()}\n";
    foreach ($sheet->getRowIterator() as $row) {
        $values = $row->toArray();          // array of scalar cell values
        // or iterate cells: foreach ($row->getCells() as $cell) { $cell->getValue(); }
        print_r($values);
    }
}

$reader->close();

$reader->setShouldFormatDates(true);         // return formatted date strings instead of DateTime
$reader->setShouldPreserveEmptyRows(true);   // keep empty rows in the iteration
$reader->setTempFolder('/custom/tmp');       // XLSX only

use SpoutX\Common\Entity\Cell;
use SpoutX\Common\Entity\CellType;

$cell = new Cell(42);
$cell->getType();        // CellType::Numeric
$cell->isNumeric();      // true
$cell->getValue();       // 42

use SpoutX\Writer\Common\Creator\WriterEntityFactory;
use SpoutX\Writer\Common\Creator\Style\StyleBuilder;
use SpoutX\Common\Entity\Style\Color;
use SpoutX\Common\Entity\Style\CellAlignment;
use SpoutX\Common\Entity\Style\CellVerticalAlignment;

$header = (new StyleBuilder())
    ->setFontBold()
    ->setFontSize(14)
    ->setFontName('Calibri')
    ->setFontColor(Color::WHITE)
    ->setBackgroundColor(Color::DARK_RED)
    ->setHorizontalAlign(CellAlignment::Center)
    ->setVerticalAlign(CellVerticalAlignment::Center)
    ->setShouldWrapText()
    ->build();

$writer->addRow(WriterEntityFactory::createRowFromArray(['Report'], $header));

$rgb = Color::rgb(255, 192, 0); // "FFC000"

use SpoutX\Writer\Common\Creator\Style\BorderBuilder;
use SpoutX\Common\Entity\Style\Color;
use SpoutX\Common\Entity\Style\BorderWidth;
use SpoutX\Common\Entity\Style\BorderStyle;

$border = (new BorderBuilder())
    ->setBorderTop(Color::RED, BorderWidth::Thin, BorderStyle::Solid)
    ->setBorderBottom(Color::BLACK, BorderWidth::Medium, BorderStyle::Dashed)
    ->build();

$style = (new StyleBuilder())->setBorder($border)->build();

$style = (new StyleBuilder())->setRowHeight(50)->build();
$writer->addRow(WriterEntityFactory::createRowFromArray(['Tall row'], $style));

use SpoutX\Common\Entity\Style\NumberFormat;

$money = (new StyleBuilder())->setNumberFormat(new NumberFormat('#,##0.00'))->build();
// setFormat() is the shorthand: ->setFormat('#,##0.00')

$writer->getCurrentSheet()->mergeCells('A1:E1');

$writer->getCurrentSheet()->setAutoFilter('A2:E2');

use SpoutX\Common\Entity\ColumnDimension;

$sheet = $writer->getCurrentSheet();
$sheet->addColumnDimension(new ColumnDimension('A', 30));        // fixed width 30
$sheet->addColumnDimension(new ColumnDimension('B', -1, true));  // auto-size
// signature: new ColumnDimension(string|int $columnIndex = 'A', float $width = -1, bool $autoSize = false, bool $visible = true)

use SpoutX\Writer\Common\Entity\Comment;

$sheet->addComment(new Comment('A2', 'A note', 'Author'));       // author is optional

$formula = new Cell('=B4*2');
$formula->setCalculatedValue('84');
$writer->addRow(WriterEntityFactory::createRow([$formula]));

$sheet = $writer->getCurrentSheet();
$sheet->setName('Summary');
$sheet->setIsVisible(true);

$second = $writer->addNewSheetAndMakeItCurrent();  // returns the new Sheet

$writer->setDefaultRowStyle($someStyle);           // applied to rows without an explicit style
$writer->setShouldUseInlineStrings(true);          // XLSX: inline vs shared strings
$writer->setTempFolder('/custom/tmp');

use SpoutX\Writer\XLSX\Entity\PageSetup;
use SpoutX\Writer\XLSX\Entity\PageMargin;
use SpoutX\Writer\XLSX\Entity\HeaderFooter;
use SpoutX\Writer\XLSX\Entity\PageOrientation;
use SpoutX\Writer\XLSX\Entity\PaperSize;

$sheet->setPageSetup(new PageSetup(PageOrientation::Landscape, PaperSize::A4, fitToHeight: 1, fitToWidth: 1));
$sheet->setPageMargin(new PageMargin(top: 1.0, bottom: 1.0));
$sheet->setHeaderFooter(new HeaderFooter(oddHeader: '&CMy report', oddFooter: '&RPage &P of &N'));

use SpoutX\Writer\XLSX\Entity\SheetView;

// Freeze the first (header) row:
$sheet->setSheetView((new SheetView())->setFreezeRow(2));
// Freeze the first column:  ->setFreezeColumn('B')
// Zoom / gridlines:         (new SheetView())->setZoomScale(150)->setShowGridLines(false)

$sheet->addHyperlink('A1', 'https://example.com');
$sheet->addHyperlink('A2', 'mailto:[email protected]');

use SpoutX\Writer\XLSX\Entity\DataValidation;
use SpoutX\Writer\XLSX\Entity\ValidationType;
use SpoutX\Writer\XLSX\Entity\ValidationOperator;

// Dropdown from a fixed list (values must not contain commas):
$sheet->addDataValidation(DataValidation::listFromValues('A2:A100', ['Yes', 'No', 'Maybe']));
// Dropdown backed by a cell range:
$sheet->addDataValidation(DataValidation::listFromRange('B2:B100', 'Lists!$A$1:$A$10'));
// Whole-number constraint with a custom error message:
$sheet->addDataValidation(new DataValidation(
    sqref: 'C2:C100',
    type: ValidationType::Whole,
    formula1: '1',
    formula2: '100',
    operator: ValidationOperator::Between,
    errorTitle: 'Out of range',
    error: 'Enter a number from 1 to 100',
));

use SpoutX\Writer\XLSX\Entity\SheetProtection;
use SpoutX\Writer\XLSX\Entity\WorkbookProtection;

$sheet->setSheetProtection(new SheetProtection(password: 'secret', lockSheet: true, lockSort: true));
$writer->setWorkbookProtection(new WorkbookProtection(password: 'secret', lockStructure: true)); // after openToFile()

use SpoutX\Writer\XLSX\Entity\SheetVisibility;

$sheet->setVisibility(SheetVisibility::Hidden);      // unhideable by the user via the UI? no
$sheet->setVisibility(SheetVisibility::VeryHidden);  // not unhideable from the UI (only via code)
// $sheet->setIsVisible(false) is kept and maps to Hidden

use SpoutX\Writer\XLSX\Entity\DocumentProperties;

$writer->setDocumentProperties(new DocumentProperties(
    title: 'Q1 Report', creator: 'Me', keywords: 'finance,q1', application: 'TBL Manager',
    customProperties: ['Department' => 'Finance', 'Reviewed' => 'yes'],
)); // after openToFile()

use SpoutX\Common\Entity\Cell;
use SpoutX\Common\Entity\RichText;
use SpoutX\Common\Entity\TextRun;
use SpoutX\Common\Entity\Style\Color;

use SpoutX\Common\Entity\TextRunVerticalAlignment;

$cell = new Cell(new RichText(
    new TextRun('Hello ', bold: true, fontColor: Color::RED),
    new TextRun('world', italic: true, fontSize: 14, fontName: 'Calibri'),
    new TextRun('2', verticalAlignment: TextRunVerticalAlignment::Superscript),
));

foreach ($reader->getSheetIterator() as $sheet) {
    $ranges = $sheet->getMergeCells();   // e.g. ['A1:C1', 'A3:A5']
}