Sheet Metadata

Retrieve data validation rules#

Every sheet in your XLSX file can contain a set of data validation rules. To retrieve them, you can imply call getDataValidations on your sheet

$excel = Excel::open($file);

$sheet = $excel->sheet();

$validations = $sheet->getDataValidations();
/*
[
  [
    'type' => 'list',
    'sqref' => 'E2:E527',
    'formula1' => '"Berlin,Cape Town,Mexico City,Moscow,Sydney,Tokyo"',
    'formula2' => null, 
  ], [
    'type' => 'decimal',
    'sqref' => 'G2:G527',
    'formula1' => '0.0',
    'formula2' => '999999.0',
  ],
]
*/

Column Widths#

Retrieve the width of a specific column in a sheet:

$excel = Excel::open($file);
$sheet = $excel->selectSheet('SheetName');

// Get the width of column 1 (column 'A')
$columnWidth = $sheet->getColumnWidth(1);

echo $columnWidth; // Example: 11.85

Row Heights#

Retrieve the height of a specific row in a sheet:

$excel = Excel::open($file);
$sheet = $excel->selectSheet('SheetName');

// Get the height of row 1
$rowHeight = $sheet->getRowHeight(1);

echo $rowHeight; // Example: 15

Freeze Pane Info#

Retrieve the freeze pane info for a sheet:

$excel = Excel::open($file);
$sheet = $excel->selectSheet('SheetName');

// Get the freeze pane configuration
$freezePaneConfig = $sheet->getFreezePaneInfo();

print_r($freezePaneConfig);
/*
Example Output:
Array
(
    [xSplit] => 0
    [ySplit] => 1
    [topLeftCell] => 'A2'
)
*/

Tab Color Info#

Retrieve the tab color info for a sheet:

$excel = Excel::open($file);
$sheet = $excel->selectSheet('SheetName');

// Get the tab color configuration
$tabColorConfig = $sheet->getTabColorInfo();

print_r($tabColorConfig);
/*
Example Output:
Array
(
    [theme] => '2'
    [tint] => '-0.499984740745262'
)
*/

Info about merged cells#

You can use the following methods:

• Sheet::getMergedCells() -- Returns all merged ranges
• Sheet::isMerged(string $cellAddress) -- Checks if a cell is merged
• Sheet::mergedRange(string $cellAddress) -- Returns merge range of specified cell

For example

if ($sheet->isMerged('B3')) {
    $range = $sheet->mergedRange('B3');
}

Hyperlinks#

A worksheet can attach hyperlinks to cells. Call Sheet::getHyperlinks() to get them, keyed by the cell (or range) they are anchored to:

$links = $sheet->getHyperlinks();

You will get an array like this:

Array
(
    [A7] => Array
        (
            [link]     => 'https://go.servicetitan.com/'
            [location] => '/new/reports/218?DateType=0&...'
            [display]  => ''
            [tooltip]  => ''
        )
)

• link — the resolved external target (the URL taken from the sheet relationships), or an empty string for a purely internal link;
• location — the in-document location: a cell reference such as Sheet2!A1 for an internal link, or a fragment appended to an external target;
• display, tooltip — the corresponding attributes, or an empty string when absent.

A sheet without hyperlinks returns an empty array.

Count rows and columns#

Each sheet contains the dimension property with the range of the area in which the data is written. If only one cell is filled on the sheet, then there should be an address of only this cell of the form "B2", otherwise it is a range of the form "B2:E10".

There are several methods that get data from this property:

• dimension() -- Returns dimension of default work area from sheet properties
• countRows() -- Count rows from dimension
• countColumns() -- Count columns from dimension
• minRow() -- The minimal row number from sheet properties
• maxRows() -- The maximal row number from sheet properties
• minColumn() -- The minimal column letter from sheet properties
• maxColumn() -- The maximal column letter from sheet properties

But sometimes the dimension property contains incorrect information. For example, it may contain the address of only the first cell of the data range or the address of only the last cell. In such cases, you can use methods that scan the entire sheet and count the actual number of rows and columns with data on the sheet.

IMPORTANT: these methods are slower than methods using the dimension property

• actualDimension() -- Returns dimension of the actual work area
• countActualRows() -- Count actual rows from the sheet
• minActualRow() -- The minimal actual row number
• maxActualRow() -- The maximal actual row number
• countActualColumns() -- Count actual columns from the sheet
• minActualColumn() -- The minimal actual column letter
• maxActualColumn() -- The maximal actual column letter

See also#

• Cell Styles
• Full API Reference — Class Sheet