Class \avadim\FastExcelTemplator\SheetTemplate

Source: 3.4.0 · 4de0b13

On this page

__construct()

php
public function __construct(string $sheetName, string $sheetId, string $file, 
                            string $path, $excel)

SheetTemplate constructor

Parameters

  • string $sheetName
  • string $sheetId
  • string $file
  • string $path
  • Excel $excel

actualDimension()

php
public function actualDimension(): string

Get the actual dimension range (e.g. "A1:C10")Note: scans every cell of the sheet (see countActualDimension()); expensive on wide sheets.

Parameters

None

cloneRow()

php
public function cloneRow(?array $cellData = []): $this

Clone current row

Parameters

  • array|null $cellData

countActualColumns()

php
public function countActualColumns(): int

Returns the actual number of columns from the sheet data areaNote: scans every cell of the sheet (see countActualDimension()); expensive on wide sheets.

Parameters

None

countActualDimension()

php
public function countActualDimension(bool $countColumns = true, 
                                     bool $countRows = true, 
                                     int $blockSize = 4096): array

Scan sheet data and returns actual number of rows and columnsNote: this method performs a full streaming pass over the (decompressed) sheet XML,because the ZIP stream of a deflated inner file is not seekable — the tail cannot bereached without reading through the whole entry. On wide sheets requesting columns($countColumns = true) is significantly more expensive than rows only, since everycell tag must be scanned. Prefer dimension() when the declared range is enough.

Parameters

  • bool $countColumns
  • bool $countRows
  • int $blockSize

countActualRows()

php
public function countActualRows(): int

Returns the actual number of rows from the sheet data area

Parameters

None

countCols()

php
public function countCols(?string $range = null): int

Count columns by dimension value, alias of countColumns()

Parameters

  • string|null $range

countColumns()

php
public function countColumns(?string $range = null): int

Count columns by dimension value

Parameters

  • string|null $range

countImages()

php
public function countImages(): int

Count images of the sheet

Parameters

None

countRows()

php
public function countRows(?string $range = null): int

Count rows by dimension value

Parameters

  • string|null $range

dimension()

php
public function dimension(): ?string

Get sheet dimension range (e.g. "A1:C10")

Parameters

None

dimensionArray()

php
public function dimensionArray(): array

Get sheet dimension as an array

Parameters

None

extractConditionalFormatting()

php
public function extractConditionalFormatting(): void

Extracts conditional formatting rules from the sheet

Parameters

None

extractDataValidations()

php
public function extractDataValidations(): void

Extracts data validation rules from the sheet

Parameters

None

extractHyperlinks()

php
public function extractHyperlinks(): void

Extracts the section of the sheet and resolves externaltargets from the sheet relationships file

Parameters

None

fill()

php
public function fill(array $params): SheetTemplate

Replace the entire cell value: applies only when the whole cell equals the key ('{{X}}', not 'text {{X}}').

Parameters

  • array $params – Map of [placeholder => value]; applied to every cell written to the output

Examples

php
$sheet->fill(['{{COMPANY}}' => 'ACME Inc.', '{{NUMBER}}' => 128]);

firstCol()

php
public function firstCol(): string

Get letter of the first column in the read area

Parameters

None

firstRow()

php
public function firstRow(): int

Get number of the first row in the read area

Parameters

None

from()

php
public function from(string $topLeftCell, 
                     ?bool $firstRowKeys = false): avadim\FastExcelReader\AbstractSheet

Set top left of read area. Alias of setReadArea()

Parameters

  • string $topLeftCell
  • bool|null $firstRowKeys

getAllColAttributes()

php
public function getAllColAttributes(): array

Get all column attributes

Parameters

None

getAllRowAttributes()

php
public function getAllRowAttributes(): array

Read all row attributes to the array

Parameters

None

getColumnAttributes()

php
public function getColumnAttributes($col): array|mixed

Get column attributes (width, style, etc)

Parameters

  • int|string $col

getColumnStyle()

php
public function getColumnStyle($col, ?bool $flat = false): array

Get column style

Parameters

  • int|string $col
  • bool|null $flat

getColumnWidth()

php
public function getColumnWidth(int $colNumber): ?float

Get width of the column

Parameters

  • int|string $colNumber

getConditionalFormatting()

php
public function getConditionalFormatting(): array

Get conditional formatting rules

Parameters

None

getDataValidations()

php
public function getDataValidations(): array

Get data validation rules

Parameters

None

getFreezePaneInfo()

php
public function getFreezePaneInfo(): ?array

Get freeze pane info

Parameters

None

getHyperlinks()

php
public function getHyperlinks(): array

Get cell hyperlinks, keyed by the cell (or range) they are anchored toEach entry is['link' => string, 'location' => string, 'display' => string, 'tooltip' => string]where 'link' is the resolved external target (the URL from the sheetrelationships), and 'location' is the in-document location - a cellreference like 'Sheet2!A1' for an internal link, or a fragment appended toan external target. Absent attributes are returned as empty strings.

Parameters

None

getImageBlob()

php
public function getImageBlob(string $cell): ?string

Get image content as binary string

Parameters

  • string $cell

getImageList()

php
public function getImageList(): array

Get image list

Parameters

None

getImageListByRow()

php
public function getImageListByRow($row): array

Get image list by row number

Parameters

  • $row

getImageMimeType()

php
public function getImageMimeType(string $cell): ?string

Get image MIME typeRequires fileinfo extension

Parameters

  • string $cell

getImageName()

php
public function getImageName(string $cell): ?string

Get image name

Parameters

  • string $cell

getMergedCells()

php
public function getMergedCells(): ?array

Get merged cells. Returns an array [min_cell => range]Note: merge definitions live after <sheetData>, so the first call reads the sheet XMLthrough to the end (result is cached). Lazy — only triggered when merged data is requested.

Parameters

None

getReadRowNum()

php
public function getReadRowNum(): int

Get the number of the last row read

Parameters

None

getRowAttributes()

php
public function getRowAttributes(int $row): array

Get row attributes (height, style, etc)

Parameters

  • int $row

getRowHeight()

php
public function getRowHeight(int $rowNumber): ?float

Get height of the row

Parameters

  • int $rowNumber

getRowStyle()

php
public function getRowStyle(int $row, ?bool $flat = false): array

Get row style

Parameters

  • int $row
  • bool|null $flat

getRowTemplate()

php
public function getRowTemplate(int $rowNumber, 
                               ?bool $savePointerPosition = false): RowTemplateCollection

Capture a source row as a reusable template; returns a collection (use it with insertRow()).

Parameters

  • int $rowNumber – Source row number to capture
  • bool|null $savePointerPosition – If false (default), the read cursor is advanced to this row

Examples

php
$tpl = $sheet->getRowTemplate(7);
foreach ($data as $row) { $sheet->insertRow($tpl, ['A' => $row['id']]); }

getRowTemplates()

php
public function getRowTemplates(int $rowNumberMin, int $rowNumberMax, 
                                ?bool $savePointerPosition = false): RowTemplateCollection

Capture source rows as templates; missing rows become empty templates, and insertRow() cycles independently of foreach.

Parameters

  • int $rowNumberMin – First source row of the range
  • int $rowNumberMax – Last source row of the range
  • bool|null $savePointerPosition – If false (default), the read cursor is advanced to $rowNumberMax

Examples

php
$tpls = $sheet->getRowTemplates(7, 8); // two styles, alternated on each insertRow()

getTabColorConfiguration()

php
public function getTabColorConfiguration(): ?array

Get tab color configuration. Alias of getTabColorConfig()

Parameters

None

getTabColorInfo()

php
public function getTabColorInfo(): ?array

Get the tab color info of the sheetContains any of: rgb, theme, tint, indexed

Parameters

None

hasDrawings()

php
public function hasDrawings(): bool

Returns true if the sheet has drawings

Parameters

None

hasImage()

php
public function hasImage(string $cell): bool

Returns TRUE if the cell contains an image

Parameters

  • string $cell

hasStructuralChanges()

php
public function hasStructuralChanges(): bool

Return the conservative structural-operation flag; range remapping is not verified and direct low-level Writer edits are not fully tracked.

Parameters

None

id()

php
public function id(): string

Get sheet ID

Parameters

None

imageEntryFullPath()

php
public function imageEntryFullPath(string $cell): ?string

Get full path to the image in the ZIP archive

Parameters

  • string $cell

insertRow()

php
public function insertRow($row, ?array $cellData = []): SheetTemplate

Insert a template copy filled by column letter; omitted columns retain template values, and the template and prior inserts remain unchanged.

Parameters

  • array|RowTemplateCollection|RowTemplate $row – A template (or collection to cycle through), or a plain [col => value] array for a blank row
  • array|null $cellData – Values keyed by column letter, e.g. ['A' => 1, 'B' => 'name']

Examples

php
$sheet->insertRow($rowTemplate, ['A' => $item['id'], 'B' => $item['name']]);

isActive()

php
public function isActive(): bool

Returns true if the sheet is active

Parameters

None

isHidden()

php
public function isHidden(): bool

Returns true if the sheet is hidden

Parameters

None

isMerged()

php
public function isMerged(string $cellAddress): bool

Returns true if the cell is merged

Parameters

  • string $cellAddress

isName()

php
public function isName(string $name): bool

Case-insensitive name checking

Parameters

  • string $name

isVisible()

php
public function isVisible(): bool

Returns true if the sheet is visible

Parameters

None

lastReadRowNum()

php
public function lastReadRowNum(): int

Last read row number

Parameters

None

lastWrittenRowNum()

php
public function lastWrittenRowNum(): int

Last written row number

Parameters

None

maxActualColumn()

php
public function maxActualColumn(): string

Get the last actual column letter

Parameters

None

maxActualRow()

php
public function maxActualRow(): int

Get the last actual row numberNote: performs a full streaming pass over the sheet (see countActualDimension()).

Parameters

None

maxColumn()

php
public function maxColumn(?string $range = null): string

Max column from dimension value

Parameters

  • string|null $range

maxRow()

php
public function maxRow(?string $range = null): int

Max row number from dimension value

Parameters

  • string|null $range

mergedRange()

php
public function mergedRange(string $cellAddress): ?string

Get merged range for the cell

Parameters

  • string $cellAddress

minActualColumn()

php
public function minActualColumn(): string

Get the first actual column letter

Parameters

None

minActualRow()

php
public function minActualRow(): int

Get the first actual row number

Parameters

None

minColumn()

php
public function minColumn(?string $range = null): string

Min column from dimension value

Parameters

  • string|null $range

minRow()

php
public function minRow(?string $range = null): int

Min row number from dimension value

Parameters

  • string|null $range

name()

php
public function name(): string

Get sheet name

Parameters

None

nextRow()

php
public function nextRow($columnKeys, ?int $resultMode = null, 
                        ?bool $styleIdxInclude = null, 
                        ?int $rowLimit = 0): ?Generator

Read cell values row by row, returns either an array of values or an array of arraysnextRow(..., ...) : <rowNum> => [<colNum1> => , <colNum2> => , ...]nextRow(..., ..., true) : <rowNum> => [<colNum1> => ['v' => , 's' => ], <colNum2> => ['v' => , 's' => ], ...]

Parameters

  • array|bool|int|null $columnKeys
  • int|null $resultMode
  • bool|null $styleIdxInclude
  • int|null $rowLimit

path()

php
public function path(): string

Get path in ZIP

Parameters

None

postRead()

php
public function postRead($xmlReader)

Internal post-read handler

Parameters

  • \XMLReader $xmlReader

preRead()

php
public function preRead($xmlReader)

Internal pre-read handler

Parameters

  • \XMLReader $xmlReader

readCallback()

php
public function readCallback(callable $callback, $columnKeys, 
                             ?int $resultMode = null, 
                             ?bool $styleIdxInclude = null)

Reads cell values and passes them to a callback function

Parameters

  • callback $callback – Callback function($row, $col, $value)
  • array|bool|int|null $columnKeys
  • int|null $resultMode
  • bool|null $styleIdxInclude

readCells()

php
public function readCells(?bool $styleIdxInclude = null): array

Returns values and styles of cells as array

Parameters

  • bool|null $styleIdxInclude

readCellsFrom()

php
public function readCellsFrom(string $areaRange, 
                              ?bool $styleIdxInclude = null): array

Set read area and returns cell values as a one-dimensional array [address => value]

Parameters

  • string $areaRange
  • bool|null $styleIdxInclude

readCellStyles()

php
public function readCellStyles(?bool $flat = false, 
                               ?string $part = null): array

Returns styles of cells as array

Parameters

  • bool|null $flat
  • string|null $part

readCellsWithStyles()

php
public function readCellsWithStyles(?string $styleKey = null): array

Returns cell values and styles as a one-dimensional array [address => value]:'v' => value's' => styles'f' => formula't' => type'o' => original_value

Parameters

  • string|null $styleKey – If specified, only this style property will be returned (e.g. 'fill-color')

readCellsWithStylesFrom()

php
public function readCellsWithStylesFrom(string $areaRange, 
                                        ?string $styleKey = null): array

Set read area and returns cell values and styles as a one-dimensional array [address => value]

Parameters

  • string $areaRange
  • string|null $styleKey – If specified, only this style property will be returned (e.g. 'fill-color')

readColumns()

php
public function readColumns($columnKeys, ?int $resultMode = null, 
                            ?bool $styleIdxInclude = null): array

Returns cell values as a two-dimensional array from default sheet [col][row]['A' => [1 => value_A1], [2 => value_A2]],['B' => [1 => value_B1], [2 => value_B2]]

Parameters

  • array|bool|int|null $columnKeys
  • int|null $resultMode
  • bool|null $styleIdxInclude

readColumnsFrom()

php
public function readColumnsFrom(string $areaRange, $columnKeys, 
                                ?int $resultMode = null, 
                                ?bool $styleIdxInclude = null): array

Set read area and returns cell values as a two-dimensional array from default sheet [col][row]

Parameters

  • string $areaRange
  • array|bool|int|null $columnKeys
  • int|null $resultMode
  • bool|null $styleIdxInclude

readColumnsWithStyles()

php
public function readColumnsWithStyles($columnKeys, 
                                      ?int $resultMode = null): array

Returns cell values and styles as a two-dimensional array [column][row]

Parameters

  • array|bool|int|null $columnKeys
  • int|null $resultMode

readColumnsWithStylesFrom()

php
public function readColumnsWithStylesFrom(string $areaRange, $columnKeys, 
                                          ?int $resultMode = null): array

Set read area and returns cell values and styles as a two-dimensional array [column][row]

Parameters

  • string $areaRange
  • $columnKeys
  • int|null $resultMode

readFirstRow()

php
public function readFirstRow($columnKeys, 
                             ?bool $styleIdxInclude = null): array

Returns values of cells of 1st row as array

Parameters

  • array|bool|int|null $columnKeys
  • bool|null $styleIdxInclude

readFirstRowCells()

php
public function readFirstRowCells(?bool $styleIdxInclude = null): array

Returns values and styles of cells of 1st row as array

Parameters

  • bool|null $styleIdxInclude

readFirstRowCellsFrom()

php
public function readFirstRowCellsFrom(string $areaRange, 
                                      ?bool $styleIdxInclude = null): array

Set read area and returns cell values of 1st row as array [address => value]Like readCellsFrom(), this method takes no column keys, because the result is keyed by cell address and renaming a column would corrupt it.

Parameters

  • string $areaRange
  • bool|null $styleIdxInclude

readFirstRowFrom()

php
public function readFirstRowFrom(string $areaRange, $columnKeys, 
                                 ?bool $styleIdxInclude = null): array

Set read area and returns values of cells of 1st row as array

Parameters

  • string $areaRange
  • array|bool|int|null $columnKeys
  • bool|null $styleIdxInclude

readFirstRowWithStyles()

php
public function readFirstRowWithStyles($columnKeys): array

Returns values and styles of cells of 1st row as array

Parameters

  • array|bool|int|null $columnKeys

readFirstRowWithStylesFrom()

php
public function readFirstRowWithStylesFrom(string $areaRange, 
                                           $columnKeys): array

Set read area and returns values and styles of cells of 1st row as array

Parameters

  • string $areaRange
  • array|bool|int|null $columnKeys

readNextRow()

php
public function readNextRow(): mixed

Read the next row from the generator

Parameters

None

readRow()

php
public function readRow(): ?Generator

Returns the next row from template

Parameters

None

readRows()

php
public function readRows($columnKeys, ?int $resultMode = null, 
                         ?bool $styleIdxInclude = null): array

Returns cell values as a two-dimensional array[1 => ['A' => value_A1], ['B' => value_B1]],[2 => ['A' => value_A2], ['B' => value_B2]]readRows()readRows(true)readRows(false, Excel::KEYS_ZERO_BASED)readRows(Excel::KEYS_ZERO_BASED | Excel::KEYS_RELATIVE)

Parameters

  • array|bool|int|null $columnKeys
  • int|null $resultMode
  • bool|null $styleIdxInclude

readRowsFrom()

php
public function readRowsFrom(string $areaRange, $columnKeys, 
                             ?int $resultMode = null, 
                             ?bool $styleIdxInclude = null): array

Read rows from a given area $areaRange

Parameters

  • string $areaRange
  • array|bool|int|null $columnKeys
  • int|null $resultMode
  • bool|null $styleIdxInclude

readRowsWithStyles()

php
public function readRowsWithStyles($columnKeys, 
                                   ?int $resultMode = null): array

Returns values, styles, and other info of cells as array['v' => value,'s' => styles,'f' => formula,'t' => type,'o' => 'original_value]

Parameters

  • array|bool|int|null $columnKeys
  • int|null $resultMode

readRowsWithStylesFrom()

php
public function readRowsWithStylesFrom(string $areaRange, $columnKeys, 
                                       ?int $resultMode = null): array

Set read area and returns values, styles, and other info of cells as array

Parameters

  • string $areaRange
  • array|bool|int|null $columnKeys
  • int|null $resultMode

replace()

php
public function replace(array $params): SheetTemplate

Replace a substring inside cell values: the key is matched anywhere in the text ('Date: {{DATE}}' works).

Parameters

  • array $params – Map of [search => replacement]; applied to every cell written to the output

Examples

php
$sheet->replace(['{{DATE}}' => date('d.m.Y')]);

replaceRow()

php
public function replaceRow($row, ?array $cellData = []): $this

Replace row with data

Parameters

  • mixed $row
  • array|null $cellData

reset()

php
public function reset($columnKeys, ?int $resultMode = null, 
                      ?bool $styleIdxInclude = null, 
                      ?int $rowLimit = 0): ?Generator

Reset read generator

Parameters

  • array|bool|int|null $columnKeys
  • int|null $resultMode
  • bool|null $styleIdxInclude
  • int|null $rowLimit

rewind()

php
public function rewind($columnKeys, ?int $resultMode = null, 
                       ?bool $styleIdxInclude = null, 
                       ?int $rowLimit = 0): ?Generator

Rewind read generator, alias of reset()

Parameters

  • array|bool|int|null $columnKeys
  • int|null $resultMode
  • bool|null $styleIdxInclude
  • int|null $rowLimit

rows()

php
public function rows($callback): SheetTemplate

Walk all source rows through a callback and write the result (read-modify-write over the whole sheet).

Parameters

  • callable $callback – function($sourceRowNum, $targetRowNum, RowTemplate $row): return $row to write it, null to skip the row, false to stop

Examples

php
$sheet->rows(function ($src, $dst, $row) {
if ($src === 1) return $row;            // keep header
$row->setValue('C', $row->getValue('C') * 1.2);
return $row;
});

saveImage()

php
public function saveImage(string $cell, ?string $filename = null): ?string

Save image to a file

Parameters

  • string $cell
  • string|null $filename

saveImageTo()

php
public function saveImageTo(string $cell, string $dirname): ?string

Save image to a directory

Parameters

  • string $cell
  • string $dirname

saveSheet()

php
public function saveSheet(): void

Empty method for compatibility

Parameters

None

setDateFormat()

php
public function setDateFormat($dateFormat): avadim\FastExcelReader\AbstractSheet

Set date format

Parameters

  • $dateFormat

setDefaultRowHeight()

php
public function setDefaultRowHeight(float $rowHeight): void

Set default row height

Parameters

  • float $rowHeight

setReadArea()

php
public function setReadArea(string $areaRange, 
                            ?bool $firstRowKeys = false): avadim\FastExcelReader\AbstractSheet

Set top left and right bottom of read area

Parameters

  • string $areaRange
  • bool|null $firstRowKeys

Examples

php
setReadArea('C3:AZ28'); // set top left and right bottom of read area
setReadArea('C3'); // set top left only

setReadAreaColumns()

php
public function setReadAreaColumns(string $columnsRange, 
                                   ?bool $firstRowKeys = false): avadim\FastExcelReader\AbstractSheet

setReadArea('C:AZ') - set left and right columns of read areasetReadArea('C') - set left column only

Parameters

  • string $columnsRange
  • bool|null $firstRowKeys

setState()

php
public function setState(string $state): avadim\FastExcelReader\AbstractSheet

Set sheet state (visible, hidden, veryHidden)

Parameters

  • string $state

skipRows()

php
public function skipRows(?int $countRows = null): SheetTemplate

Skip rows from the template

Parameters

  • int|null $countRows – Source row positions to skip; null means to EOF, zero is a no-op, negative counts throw Exception

skipRowsUntil()

php
public function skipRowsUntil(?int $maxRowNum = null): SheetTemplate

Advance through an inclusive source boundary without writing; later existing rows in a sparse sheet remain available.

Parameters

  • int|null $maxRowNum – Inclusive source boundary; null means to EOF, zero is a no-op, negative values throw Exception

stat()

php
public function stat(): array

Returns statistics of the sheet: rows, columns and cell counts['rows' => ['min' => int, 'max' => int, 'count' => int],'cols' => ['min' => string, 'max' => string, 'count' => int],'cells' => ['total' => int, 'filled' => int],]Note: performs a full streaming pass over the sheet XML (cached); memory is O(blockSize),but time grows with the number of cells — expensive on large/wide sheets.

Parameters

None

state()

php
public function state(): string

Get sheet state

Parameters

None

transferRows()

php
public function transferRows(?int $countRows = null, $callback): SheetTemplate

Copy the next $countRows source rows (or all remaining if null) to the output, with an optional callback.

Parameters

  • int|null $countRows – Source row positions to transfer; null means to EOF, zero is a no-op, negative counts throw Exception
  • callable|null $callback – function($sourceRowNum, $targetRowNum, RowTemplate $row): return $row to write it, null to skip the row, false to stop

transferRowsUntil()

php
public function transferRowsUntil(?int $maxRowNum = null, 
                                  $callback): SheetTemplate

Copy source rows to the output up to $maxRowNum; an optional callback can modify, skip or stop each row.

Parameters

  • int|null $maxRowNum – Inclusive source boundary; null means to EOF, zero is a no-op, negative values throw Exception
  • callable|null $callback – function($sourceRowNum, $targetRowNum, RowTemplate $row): return $row to write it, null to skip the row, false to stop

withHeader()

php
public function withHeader(?array $columnNames = null): avadim\FastExcelReader\AbstractSheet

Enables header modeTreats the first row of the read area as a header row and returns subsequent rowsas associative arrays keyed by column names.Pass $columnNames to name the columns yourself: the first row is still skipped,but the names are taken from the list instead of from its values. Names areapplied in column order, starting at the first column of the read area, so theyneed no knowledge of column letters. Columns past the end of the list keep thename from the header row.

Parameters

  • array|null $columnNames – Column names in order, or NULL to use the header row values

writeCell()

php
public function writeCell($value, ?array $styles = null): SheetTemplate

Write value to the current cell and move a pointer to the next cell in the row

Parameters

  • mixed $value
  • array|null $styles

writeRow()

php
public function writeRow(array $rowValues = [], ?array $rowStyle = null, 
                         ?array $cellStyles = null): SheetTemplate

Write values to the current row

Parameters

  • array $rowValues – Values of cells
  • array|null $rowStyle – Style applied to the entire row
  • array|null $cellStyles – Styles of specified cells in the row
Source on GitHub ↗MIT license

FastExcelTemplator 3.4.0 · Search this library and version.

Loading search…

Could not load search. Please try again.

No results. Try another word or method name.