Accessing Cells - PhpSpreadsheet

Overview

Accessing cells in PhpSpreadsheet is straightforward and flexible. You can reference cells using coordinate notation (e.g., B3), array notation with column and row indices (e.g., [2, 3]), or CellAddress objects.

Column indices and rows are 1-based, not 0-based. This matches Excel’s behavior where column A is 1 and the first row is 1.

Setting Cell Values

Basic setValue Methods

You can set cell values using the worksheet’s setCellValue() method:

// Set cell A1 with a string value
$spreadsheet->getActiveSheet()->setCellValue('A1', 'PhpSpreadsheet');

// Set cell A2 with a numeric value using array notation
$spreadsheet->getActiveSheet()->setCellValue([1, 2], 12345.6789);

// Set cell A3 with a boolean value using CellAddress object
$spreadsheet->getActiveSheet()->setCellValue(new CellAddress('A3'), true);

// Set cell A4 with a formula
$spreadsheet->getActiveSheet()->setCellValue(
    'A4',
    '=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))'
);

Alternatively, retrieve the cell object and call setValue() directly:

$spreadsheet->getActiveSheet()
    ->getCell('B8')
    ->setValue('Some value');

Setting Values from Arrays

Set a range of cell values in a single call using fromArray():

$arrayData = [
    [null, 2010, 2011, 2012],
    ['Q1',   12,   15,   21],
    ['Q2',   56,   73,   86],
    ['Q3',   52,   61,   69],
    ['Q4',   30,   32,    0],
];

$spreadsheet->getActiveSheet()->fromArray(
    $arrayData,  // The data to set
    null,        // Array values with this value will not be set
    'C3'         // Top left coordinate (default is A1)
);

For a 1-D array as a column:

$rowArray = ['Value1', 'Value2', 'Value3', 'Value4'];
$columnArray = array_chunk($rowArray, 1);

$spreadsheet->getActiveSheet()->fromArray(
    $columnArray,
    null,
    'C3'
);

Copying Cell Values and Formulas

As of version 5.1.0, you can copy formulas with automatic reference adjustment:

// Copy formula from B1 to B2 with adjusted references
$worksheet->copyFormula($fromCell, $toCell);

// Copy cell style as well
$worksheet->duplicateStyle($fromCell->getStyle(), $toCell);

Data Types

Supported Data Types

PhpSpreadsheet supports 7 Excel data types:

$sheet->setCellValue('A1', 'Hello World');

Explicit Data Types

To set a value with an explicit data type (bypassing the value binder):

use PhpOffice\PhpSpreadsheet\Cell\DataType;

// Set cell A8 as string to preserve leading zeros
$spreadsheet->getActiveSheet()->setCellValueExplicit(
    'A8',
    '01513789642',
    DataType::TYPE_STRING
);

Available type constants: TYPE_STRING, TYPE_NUMERIC, TYPE_BOOL, TYPE_NULL, TYPE_FORMULA, TYPE_ERROR, TYPE_INLINE

Setting Dates and Times

Date and time values are stored as Excel timestamps (floating-point numbers):

use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

// Get current date/time and convert to Excel date/time
$dateTimeNow = time();
$excelDateValue = Date::PHPToExcel($dateTimeNow);

// Set cell A6 with the Excel date/time value
$spreadsheet->getActiveSheet()->setCellValue('A6', $excelDateValue);

// Set the number format to display as human-readable date/time
$spreadsheet->getActiveSheet()->getStyle('A6')
    ->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_DATE_DATETIME);

Numbers with Leading Zeros

Two approaches to preserve leading zeros:

Explicit String Type
Number Format Mask

$spreadsheet->getActiveSheet()->setCellValueExplicit(
    'A8',
    '01513789642',
    \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
);

// Set cell A9 with a numeric value
$spreadsheet->getActiveSheet()->setCellValue('A9', 1513789642);

// Set a number format mask to display with leading zeroes
$spreadsheet->getActiveSheet()->getStyle('A9')
    ->getNumberFormat()
    ->setFormatCode('00000000000');

// Or with formatting for readability
$spreadsheet->getActiveSheet()->getStyle('A10')
    ->getNumberFormat()
    ->setFormatCode('0000-000-0000');

Retrieving Cell Values

Basic getValue Methods

// Get the raw, unformatted value
$cellValue = $spreadsheet->getActiveSheet()
    ->getCell('A1')
    ->getValue();

// Get calculated value for formulas
$cellValue = $spreadsheet->getActiveSheet()
    ->getCell('A4')
    ->getCalculatedValue();

// Get formatted value (with number format applied)
$cellValue = $spreadsheet->getActiveSheet()
    ->getCell('A6')
    ->getFormattedValue();

Retrieving Ranges to Arrays

Retrieve multiple cell values as an array:

$dataArray = $spreadsheet->getActiveSheet()->rangeToArray(
    'C3:E5',  // The worksheet range to retrieve
    null,     // Value returned for empty cells
    true,     // Calculate formulas
    true,     // Format values
    true      // Index by cell row and column
);

// Get entire worksheet
$allData = $spreadsheet->getActiveSheet()->toArray();

// Get named range
$namedData = $spreadsheet->getActiveSheet()->namedRangeToArray('MyRange');

Looping Through Cells

Using Iterators

use PhpOffice\PhpSpreadsheet\IOFactory;

$reader = IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load('test.xlsx');

$worksheet = $spreadsheet->getActiveSheet();

foreach ($worksheet->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
    // Loop through all cells, even if not set
    $cellIterator->setIterateOnlyExistingCells(false);
    
    foreach ($cellIterator as $cell) {
        echo $cell->getValue();
    }
}

Setting setIterateOnlyExistingCells(false) will create empty cells if they don’t exist, increasing memory usage.

Using Index Loops

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

$worksheet = $spreadsheet->getActiveSheet();
$highestRow = $worksheet->getHighestDataRow(); // e.g. 10
$highestColumn = $worksheet->getHighestDataColumn(); // e.g. 'F'
$highestColumnIndex = Coordinate::columnIndexFromString($highestColumn);

for ($row = 1; $row <= $highestRow; ++$row) {
    for ($col = 1; $col <= $highestColumnIndex; ++$col) {
        $value = $worksheet->getCell([$col, $row])->getValue();
        // Process value
    }
}

Or using string coordinates:

$highestRow = $worksheet->getHighestDataRow();
$highestColumn = $worksheet->getHighestDataColumn();
++$highestColumn; // Increment to use in loop condition

for ($row = 1; $row <= $highestRow; ++$row) {
    for ($col = 'A'; $col != $highestColumn; ++$col) {
        $value = $worksheet->getCell($col . $row)->getValue();
        // Process value
    }
}

Use != instead of <= when comparing column letters because 'AA' <= 'B' would match.

Cell References and Coordinates

Coordinate System

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

// Convert column letter to index (A=1, B=2, etc.)
$columnIndex = Coordinate::columnIndexFromString('A'); // Returns 1

// Convert index to column letter
$columnLetter = Coordinate::stringFromColumnIndex(1); // Returns 'A'

// Split coordinate into parts
[$column, $row] = Coordinate::coordinateFromString('A1');

// Get coordinate boundaries for a range
$boundaries = Coordinate::rangeBoundaries('A1:C5');
// Returns [[1, 1], [3, 5]] - [[minCol, minRow], [maxCol, maxRow]]

Cell Address Objects

use PhpOffice\PhpSpreadsheet\Cell\CellAddress;

// Create a cell address
$address = new CellAddress('B3', $worksheet);

// Use in cell operations
$spreadsheet->getActiveSheet()->setCellValue($address, 'Value');

Important Notes

Detached Cell References

Be careful when assigning cell objects to variables. PhpSpreadsheet uses cell caching, and subsequent calls to getCell() will detach previous cell references from the collection.

$cellC1 = $workSheet->getCell('C1');
echo $cellC1->getValue(); // Works fine

$cellA1 = $workSheet->getCell('A1'); // This detaches $cellC1

// $cellC1 still has its value, but coordinate access will fail
echo $cellC1->getValue(); // Still works
echo $cellC1->getCoordinate(); // Throws exception!

Escaping Formula Values

To store a value beginning with = as a string (not a formula):

$spreadsheet->getActiveSheet()->setCellValue('A4', '=This is not a formula');
$spreadsheet->getActiveSheet()->getCell('A4')
    ->getStyle()
    ->setQuotePrefix(true);

Cell Values

Learn about data types and value binders

Formulas

Working with Excel formulas

Data Validation

Setting up cell validation rules

Defined Names

Using named ranges and formulas

​Overview

​Setting Cell Values

​Basic setValue Methods

​Setting Values from Arrays

​Copying Cell Values and Formulas

​Data Types

​Supported Data Types

​Explicit Data Types

​Setting Dates and Times

​Numbers with Leading Zeros

​Retrieving Cell Values

​Basic getValue Methods

​Retrieving Ranges to Arrays

​Looping Through Cells

​Using Iterators

​Using Index Loops

​Cell References and Coordinates

​Coordinate System

​Cell Address Objects

​Important Notes

​Detached Cell References

​Escaping Formula Values

​Related Topics

Cell Values

Formulas

Data Validation

Defined Names

Overview

Setting Cell Values

Basic setValue Methods

Setting Values from Arrays

Copying Cell Values and Formulas

Data Types

Supported Data Types

Explicit Data Types

Setting Dates and Times

Numbers with Leading Zeros

Retrieving Cell Values

Basic getValue Methods

Retrieving Ranges to Arrays

Looping Through Cells

Using Iterators

Using Index Loops

Cell References and Coordinates

Coordinate System

Cell Address Objects

Important Notes

Detached Cell References

Escaping Formula Values

Related Topics