PHP code example of saschakliche / phpxlsxreader

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

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

    

saschakliche / phpxlsxreader example snippets

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();
$reader->open(<pathToInputFile>);

foreach ($reader->read() as $worksheetName => $rows) {
    print 'Worksheet ' . $worksheetName . PHP_EOL;
    foreach ($rows as $rowIndex => $row) {
        print "\t" . 'Row ' . $rowIndex . PHP_EOL;
        foreach ($row as $columnAddress => $column) {
            // $column can be int/float/string/DateTime unless a custom formatter is used
            print "\t\t" . 'Cell ' . $columnAddress . ': ' . ($column instanceof DateTime ? $column->format('Y-m-d H:i:s') : $column) . "\n";
        }
    }
}

// opening a file
open(string $filePath): XlsxReader

// reading the actual data
read(): Generator
readAsArray(): array
readWithHeader(int|array $headerRowIndex = 0): array

// retrieving information about the workbook
getHeaders(int|string $worksheet = ''): array
getMetadata(): Metadata
getWorksheetName(): string
getWorksheetNames(): array

XlsxReader::read(): Generator

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();
$reader->open(<pathToInputFile>);

foreach ($reader->read() as $worksheetName => $rows) {
    print $worksheetName . PHP_EOL;
    foreach ($rows as $rowIndex => $row) {
        print "\t" . $rowIndex . PHP_EOL;
        foreach ($row as $columnAddress => $column) {
            print "\t\t" . $columnAddress . ': ' . ($column instanceof DateTime ? $column->format('Y-m-d H:i:s') : $column) . "\n";
        }
    }
}

XlsxReader::readAsArray(): array

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();
$reader->open(<pathToInputFile>);

// using cell address notation
$data = $reader->useCellAddress()->readAsArray();
// $data[<worksheetname (string)>][<rowindex (int)>][<celladdress (string)>]

// using 1-based column index notation 
$data = $reader->useColumnIndex()->readAsArray();
// $data[<worksheetname (string)>][<rowindex (int)>][<columnindex (int)>]

// reading just a single worksheet's data
$sheet1 = $reader->worksheets(['sheet1'])->readAsArray();
// $data['sheet1'][<rowindex (int)>][<celladdress (string)>]

XlsxReader::readWithHeader(int|array $headerRowIndex = 0): array

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$data = $reader
    ->open(<pathToInputFile>)
    ->readWithHeader();

// $data[<worksheetname (string)>][<rowindex (int)>][<header (string)>]

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$data = $reader
    ->open(<pathToInputFile>)
    ->readWithHeader(3);

// $data[<worksheetname (string)>][<rowindex (int)>][<header (string)>]

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$data = $reader
    ->open(<pathToInputFile>)
    ->readWithHeader(['Sheet1' => 1, 'Sheet2' => 3]);
    // other existing worksheets will use the first available row as header

// $data[<worksheetname (string)>][<rowindex (int)>][<header (string)>]

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$data = $reader
    ->open(<pathToInputFile>)
    ->

XlsxReader::getHeaders(int|string $worksheet = ''): array

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();
$reader->open(<pathToInputFile>)->readWithHeader();

// headers for all worksheets
$headers = $reader->getHeaders();
// $headers[<worksheetname (string)>][<columnindex (int)>] = [<header (string)>]

// headers for a specific worksheet identified by the worksheet name
$headersSheet1 = $reader->getHeaders('Sheet1');
// $headersSheet1[<columnindex (int)>] = [<header (string)>]

// headers for a specific worksheet identified by the index number
$headersSheet1 = $reader->getHeaders(3);
// $headersSheet1[<columnindex (int)>] = [<header (string)>]

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$worksheetNames = $reader
    ->open(<pathToInputFile>)
    ->getWorksheetNames();

// e.g. ['sheet1', 'sheet2', 'sheet3']

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$worksheetName = $reader
    ->open(<pathToInputFile>)
    ->getWorksheetName(2);

// e.g. 'sheet2'

use SaschaKliche\PhpXlsxReader\Model\Metadata;
use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$metadata = $reader
    ->open(<pathToInputFile>)
    ->getMetadata();                    // Metadata instance

$metadata->has(<property_name>);        // bool
$metadata->get(<property_name>);        // string|null

Metadata::CREATED            // date/time when the file was created
Metadata::CREATOR            // name of the person who created the file
Metadata::LAST_MODIFIED_BY   // date/time when the file was last modified
Metadata::MODIFIED           // name of the person who last modified the file

use SaschaKliche\PhpXlsxReader\Configuration;
use SaschaKliche\PhpXlsxReader\XlsxReader;

// default configuration
$configuration = [
    Configuration::COLUMNS_TO_LOAD => [],
    Configuration::CUSTOM_FORMATS => [],
    Configuration::RETURN_CELL_OBJECTS => false,
    Configuration::READ_FORMULAS => false, // only if Configuration::RETURN_CELL_OBJECTS set to true
    Configuration::READ_HYPERLINKS => false, // only if Configuration::RETURN_CELL_OBJECTS set to true
    Configuration::ROWS_TO_LOAD => [],
    Configuration::SKIP_MISSING_CELLS => true,
    Configuration::SKIP_MISSING_ROWS => true,
    Configuration::USE_CELL_ADDRESS => true,
    Configuration::USE_DATE_SYSTEM_1900 => false,
    Configuration::WORKSHEETS_TO_LOAD => [],
];

$reader = new XlsxReader(<pathToInputFile>, $configuration);

function (mixed $value, string $rawValue, string $cellAddress, string $worksheetName)

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();
$reader->open(<pathToInputFile>);

$data = $reader->customFormats([
    // <id> => static fn(mixed $value, string $rawValue) => $value,
    '9' => static fn(int $number) => number_format($number * 100, 2) . '%',
    '14' => static fn(DateTime $date) => $date->format('d.m.Y'),
    // format string
    // '0%' => static fn(int $number) => number_format($number * 100, 2) . '%', // same as index '9'
    '#,##0.00' => static fn(float $number) => number_format($number, 2),
])->readAsArray();

// values for cells with number format 14 ('mm-dd-yy')
// will not be returned as DateTime objects but
// as formatted date strings  

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$data = $reader
    ->open(<pathToInputFile>)
    ->

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$data = $reader
    ->open(<pathToInputFile>);
    ->skipMissingCells()
    ->readAsArray();

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$data = $reader
    ->open(<pathToInputFile>)
    ->

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$data = $reader
    ->open(<pathToInputFile>)
    ->skipMissingRows()
    ->readAsArray();

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();
$reader->open(<pathToInputFile>);

// don't read formulas and hyperlinks
$data = $reader->returnCellObjects()->readAsArray();

// read formulas and hyperlinks
$data = $reader->returnCellObjects(readFormulas: true, readHyperlinks: true)->readAsArray();

// $data[<worksheetname (string)>][<rowindex (int)>][<celladdress (string)>][<object (Cell)>]

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$data = $reader
    ->open(<pathToInputFile>)
    ->useCellAddress()
    ->readAsArray();

// $data[<worksheetname (string)>][<rowindex (int)>][<celladdress (string)>][<value (int|float|string|DateTime)>]

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$data = $reader
    ->open(<pathToInputFile>)
    ->useCellAddress()
    ->readAsArray();

// $data[<worksheetname (string)>][<rowindex (int)>][<columnindex (int)>][<value (int|float|string|DateTime)>]

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$data = $reader
    ->open(<pathToInputFile>)
    ->useDateSystem1900()
    ->readAsArray();

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$data = $reader
    ->open(<pathToInputFile>)
    ->useDateSystem1904()
    ->readAsArray();

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$data = $reader
    ->open(<pathToInputFile>)
    ->columns([2, 3, 4, 5])
    ->readAsArray();

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$data = $reader
    ->open(<pathToInputFile>)
    ->columns(['Sheet1' => [2, 3, 4, 5]])
    ->readAsArray();

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();
$reader->open(<pathToInputFile>);

$reader->columns([Configuration::MIN => 3])->readAsArray();
$reader->columns(['Sheet1' => [Configuration::MIN => 3]])->readAsArray();

$reader->columns([Configuration::MIN => 2, Configuration::MAX => 5])->readAsArray();
// same as $reader->columns([2, 3, 4, 5])->readAsArray();
$reader->columns(['Sheet1' => [Configuration::MIN => 2, Configuration::MAX => 5]])->readAsArray();
// same as $reader->columns(['Sheet1' => [2, 3, 4, 5]])->readAsArray();

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$data = $reader
    ->open(<pathToInputFile>)
    ->

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$data = $reader
    ->open(<pathToInputFile>)
    ->rows([2, 3, 4, 5])
    ->readAsArray();

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$data = $reader
    ->open(<pathToInputFile>)
    ->rows(['Sheet1' => [2, 3, 4, 5]])
    ->readAsArray();

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$data = $reader
    ->open(<pathToInputFile>)
    ->

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$sheet1 = $reader
    ->open(<pathToInputFile>)
    ->worksheets(['sheet1', 'sheet2'])
    ->readAsArray();

// $data['sheet1'][<rowindex (int)>][<celladdress (string)>]
// $data['sheet2'][<rowindex (int)>][<celladdress (string)>]

__toString(): string
getAddress(): string
getCellFormatString(): string
getColumnIndex(): int
getDataType(): int
getFormula(): string|null
getHyperlinkTarget(): string|null
getRawValue(): string
getRowIndex(): int
getValue(): string

Cell::DATA_TYPE_UNKNOWN = 0
Cell::DATA_TYPE_DATETIME = 1
Cell::DATA_TYPE_FLOAT = 2
Cell::DATA_TYPE_INTEGER = 3
Cell::DATA_TYPE_STRING = 4

use SaschaKliche\PhpXlsxReader\XlsxReader;

$reader = new XlsxReader();

$data = $reader
    ->open(<pathToInputFile>)
    ->readWithHeader();

$duration = $reader->getDurationInSeconds();   // float
$memory = $reader->getMemoryUsage();           // int
$peak = $reader->getMemoryPeakUsage();         // int

$reader->printPerformanceData();
// example output:
// Duration: 0.000343458 seconds
// Memory: 14.17 MiB
// Peak memory: 14.19 MiB