Cell Access - PhpSpreadsheet Documentation (2023)

Accessing cells in a worksheet should be pretty easy. This topic lists some of the options for accessing a cell.

Set a cell value by coordinate

Defining a cell value by coordinates can be done using the worksheetsetCellValue()Method.

// set a cell A1 with the value of string$spreadsheet->getActiveSheet()->setCellValue('A1', 'PhpSpreadsheet');// set a cell A2 with the value of numeric$spreadsheet->getActiveSheet()- > setCellValue('A2', 12345.6789);// sets a cell A3 with a boolean value $worksheet->getActiveSheet()->setCellValue('A3', TRUE);// sets a cell A4 with a formula $sheet -> getActiveSheet()-> setCellValue( 'A4', '=SE(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))');

Alternatively, you can retrieve the cell object and then call the cell's methoddefinirValor()Method:

$sheet->getActiveSheet() ->getCell('B8') ->setValue('Any value');

Create a new cell

when you callgetCell(), and the cell doesn't exist yet, PhpSpreadsheet will create that cell for you.

ATTENTION: Cells assigned to variables for separate reference

As an "in-memory" model, PHPSpreadsheet can consume a lot of memory, especially when working with large spreadsheets. One technique used to reduce this memory overhead is cell caching, whereby cells are stored in a collection that may or may not be kept in memory while you work with the worksheet. Therefore a call togetCell()(or a similar method) returns the cell data and a pointer to the collection. While this is not usually a problem, it can become significant when assigning the result of a callgetCell()to a variable. All subsequent calls to get other cells disable this pointer, even though the cell object still retains its data values.

What does that mean? Consider the following code:

$worksheet = new spreadsheet(); $worksheet = $worksheet->getActiveSheet();// Define details for the formula we want to evaluate along with any data it depends on $worksheet->fromArray( [1, 2 , 3 ] , null, 'A1');$cellC1 = $workSheet->getCell('C1');echo 'Value: ', $cellC1->getValue(), '; Address: ', $cellC1->getCoordinate(), PHP_EOL;$cellA1 = $workSheet->getCell('A1');echo 'Value: ', $cellA1->getValue(), '; address: ', $cellA1->getCoordinate(), PHP_EOL;echo 'value: ', $cellC1->getValue(), '; Address: ', $cellC1->getCoordinate(), PHP_EOL;

the call togetCell('C1')returns the cell inC1contains its value (3), along with your link to the collection (used to identify your address/coordinatesC1🇧🇷 The subsequent call to access the cell phoneA1change the value of$celdaC1, which highlights your link to the collection.

So if we try to display the value and address a second time, we can display its value, but trying to display its address/coordinate will throw an exception because that binding has been overridden.

Surveillance:There are some built-in methods that get other cells in the collection and this also separates the link to the collection from any cells you have assigned to a variable.

excel data types

MS Excel supports 7 basic data types:

• Fragment
• number
• boleano
• Null
• Formula
• Error
• Encrusted chain (enriched text)

By default when calling the worksheet methodsetCellValue()method or celldefinirValor()method, PhpSpreadsheet uses the appropriate data type for PHP null, boolean, float, or integer; or cast each string data value you pass to the method to the most appropriate data type, so that numeric strings convert to numbers while string values ​​start with=it becomes a formula. Strings that are not numeric or do not start with a leading one=they are treated as actual string values.

Note that a numeric string that begins with a leading zero (not immediately followed by a decimal point) is not converted to a numeric value, so values ​​such as phone numbers (for example, "01615991375" remain as strings).

This "conversion" is handled by a cell "value binding" and you can write custom "value bindings" to change the behavior of these "conversions". conversions, such as B. Convert strings with a fractional format such as "3/4" to a numeric value (0.75 in this case) and specify an appropriate "fractional" number format mask. Similarly, strings like "5%" are converted to a value of 0.05 and a percentage number format mask is applied, and strings containing values ​​that look like dates are converted to serialized timestamp values. Excel and a suitable skin is applied. This is particularly useful when loading data from csv files or setting cell values ​​from a database.

Formats processed by Advanced Value Binder include:

• TRUE or FALSE (depending on locale) are converted to boolean values.
• Number sequences identified as scientific (exponential) format are converted to numbers.
• Fractions and common fractions are converted to numbers and an appropriate number format mask is applied.
• The percentages are converted to numbers, divided by 100, and an appropriate number format mask is applied.
• Dates and times are converted to Excel timestamp values ​​(numbers) and an appropriate number format mask is applied.
• When strings contain a new line character (\norte), the cell style is set to encapsulated.

Basically, it tries to mimic the behavior of the MS Excel GUI.

Read more about Wertbinderlater in this section of the documentation.

Set up a formula in a cell

As mentioned above, if you store a string value with the first character a=in a cell PHPSpreadsheet treats this value as a formula, and you can then evaluate this formula by calling itgetCalculatedValue()against the cell

However, there may be times when you want to store a value starting with=as a string, and you don't want the PHPSpreadsheet to evaluate to a formula.

To do this, you must "escape" the value by setting it to "quoted text".

// Definiere eine Zelle A4 com uma formula$planilha->getActiveSheet()->setCellValue('A4', '=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1)) ' );$planilla->getActiveSheet()->getCell('A4') ->getStyle()->setQuotePrefix(true);

So even if you ask PHPSpreadsheet to return the computed value for the cellA4, To return to=SE(A3, VERTIDO(A1," "", A2), VERTIDO(A2," ", A1))as a string and do not attempt to evaluate the formula.

Set a date and/or time value in a cell

Date or time values ​​are held in Excel as a timestamp (a single floating-point value), and a number format mask is used to indicate how that value should be formatted; So if we want to store a date in a cell, we need to calculate the correct Excel timestamp and define a number format mask.

// Get the current date/time and convert it to an Excel date/time $dateTimeNow = time();$excelDateValue = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel( $dateTimeNow );// Define cell A6 with Excel date/time value $sheet->getActiveSheet()->setCellValue( 'A6', $excelDateValue); // Set number format mask to display Excel timestamp as human readable date/time $sheet-> getActiveSheet()->getStyle('A6') ->getNumberFormat() ->setFormatCode(\ PhpOffice\PhpSpreadsheet\Style\NumberFormat::DATE_FORMAT_DATETIME);

Define a number with leading zeros

By default, PhpSpreadsheet automatically detects the value type and sets it to the appropriate Excel numeric data type. This type conversion is performed by a value archiver, as described in the section of this document titled "Using Value Archivers to Facilitate Data Entry."

Numbers do not have leading zeros. So if you try to specify a numeric value with leading zeros (for example, a phone number), these are usually lost when the value is converted to a number, so "01513789642" appears as 1513789642.

There are two ways to force PhpSpreadsheet to override this behavior.

First, you can explicitly set the data type to a string so it won't be converted to a number.

// Sets cell A8 to a numeric value, but tells PhpSpreadsheet to treat it as string$spreadsheet->getActiveSheet()->setCellValueExplicit( 'A8', "01513789642", \PhpOffice\PhpSpreadsheet\Cell\DataType : :TYPE_STRING will be );

Alternatively, you can use a number format mask to display the value with leading zeros.

// set cell A9 to a numeric value $worksheet->getActiveSheet()->setCellValue('A9', 1513789642); // Set the number format mask to display the value as 11 digits with leading zeros $sheet->getActiveSheet( )->getStyle('A9') ->getNumberFormat() ->setFormatCode('00000000000' );

With number format masking, you can even split digits into groups to make the value more readable.

// set cell A10 to a numeric value $worksheet->getActiveSheet()->setCellValue('A10', 1513789642); // Set the number format mask to display the value as 11 digits with leading zeros $sheet->getActiveSheet( )->getStyle('A10') ->getNumberFormat() ->setFormatCode('0000- 000-0000');

Surveillance:Note that not all complex format masks like this will work when retrieving a value formatted for display or for specific writers like HTML or PDF, but it does work with real spreadsheet programs (Xlsx and Xls).

Defining a range of cells in an array

It is also possible to define a range of cell values ​​in a single call, bypassing an array of values ​​for theofArray()Method.

$arrayData = [[NULL, 2010, 2011, 2012], ['T1', 12, 15, 21], ['T2', 56, 73, 86], ['T3', 52, 61, 69], ['Q4', 30, 32, 0],];$spreadsheet->getActiveSheet() ->fromArray( $arrayData, // The data to be set to NULL, // Array values ​​with this value will not will be set to 'C3' // Top left coordinate of the worksheet area where // we want to put these values ​​(default is A1));

If you pass a 2-D matrix, it will be treated as a series of rows and columns. A one-dimensional array is treated as a single row, which is especially useful when retrieving an array of data from a database.

$rowArray = ['value1', 'value2', 'value3', 'value4'];$spreadsheet->getActiveSheet() ->fromArray( $rowArray, // set the data to NULL, // array values ​​like this value is not set 'C3' // Top left coordinate of the worksheet area where // we want to set these values ​​(default is A1));

If you have a simple 1-D array and want to write it as a column, the following converts it to a properly structured 2-D array that can be entered into theofArray()Method:

$rowArray = ['value1', 'value2', 'value3', 'value4'];$columnArray = array_chunk($rowArray, 1);$spreadsheet->getActiveSheet() ->fromArray( $columnArray, // The data to set NULL, // Array values ​​with this value will not be set to 'C3' // Top left coordinate of the worksheet area where // we want to set these values ​​(default is A1));

Get a cell value by coordinates

To get the value of a cell, the cell must first be retrieved from the worksheet usinggetCell()Method. The value of a cell can also be readget value()Method.

// Get the value of cell A1 $cellValue = $worksheet->getActiveSheet()->getCell('A1')->getValue();

This gets the raw value contained in the cell.

If a cell contains a formula and you need to get the calculated value instead of the formula itself, use the cell's valuegetCalculatedValue()Method. This is explained later inthe calculation engine.

// get the value of cell A4 $cellValue = $sheet->getActiveSheet()->getCell('A4')->getCalculatedValue();

Alternatively, if you want to display the value with whatever cell formatting you like (for example, for a human-readable date or time value), you can use cellgetFormattedValue()Method.

// get the value of cell A6 $cellValue = $worksheet->getActiveSheet()->getCell('A6')->getFormattedValue();

Set one cell value per column and row

Defining a cell value by coordinates can be done using the worksheetsetCellValueByColumnAndRow()Method.

// definir una celda A5 com uma string value$spreadsheet->getActiveSheet()->setCellValueByColumnAndRow(1, 5, 'PhpSpreadsheet');

Surveillance:What column references begin with1for columnA.

Get a cell value per column and row

To get the value of a cell, the cell must first be retrieved from the worksheet usinggetCellByColumnAndRow()Method. The value of a cell can be read with the following line of code:

// get the value of cell B5 $cellValue = $worksheet->getActiveSheet()->getCellByColumnAndRow(2, 5)->getValue();

If you need the calculated value of a cell, use the following code. This is explained later inthe calculation engine.

// get the value of cell A4 $cellValue = $worksheet->getActiveSheet()->getCellByColumnAndRow(1, 4)->getCalculatedValue();

Get a range of cell values ​​for an array

It is also possible to retrieve a range of cell values ​​for an array in a single call withaArray(),rangeToArray()orangoNombradoAArray()methods.

$dataArray = $spreadsheet->getActiveSheet() ->rangeToArray( 'C3:E5', // The spreadsheet range we want to get NULL, // Value to return for empty cells TRUE, // Formulas must be calculated (the equivalent of getCalculatedValue() for each cell) TRUE, // values ​​must be formatted (the equivalent of getFormattedValue() for each cell) TRUE // the array must be indexed by cell row and cell column);

All of these methods return a two-dimensional array of rows and columns. EITHERaArray()the method returns the entire spreadsheet;rangeToArray()returns a range or specific cells; DuringrangoNombradoAArray()returns the cells within a definednamed area.

go through the cells

Iterating through cells with iterators

The easiest way to repeat cells is to use iterators. Using iterators, you can use foreach to iterate over worksheets, rows within a worksheet, and cells within a row.

Below is an example where we read all the values ​​in a worksheet and display them in a table.

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');$reader->setReadDataOnly(TRUE);$spreadsheet = $reader->load("test.xlsx");$worksheet = $spreadsheet-> getActiveSheet(); echo '<table>' . PHP_EOL;foreach ($spreadsheet->getRowIterator() as $row) { echo '<tr>' . PHP_EOL; $cellIterator = $line->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(FALSE); // This will loop through all cells // even if no cell value is defined. // If 'TRUE', we only loop through cells // if its value is set. // If this method is not called, // it defaults to 'false'. foreach ($cellIterator as $cell) { echo '<td>' . $cell->getValue() . '</td>'. PHP_EOL; } echo '</tr>'. PHP_EOL;}echo '</table>' . PHP_EOL;

Notice that we have defined the cell iteratorsetIterateOnlyExistingCells()in FALSE. This causes the iterator to loop through all cells within the worksheet range, even if they are not defined.

The cell iterator returns aNullas a cell value if it is not defined in the spreadsheet. Configuring the cell iteratorsetIterateOnlyExistingCells()ProINCORRECTiterates through all the cells in the worksheet that might be available at the moment. This creates new cells when needed and increases memory usage! Use it only if you intend to iterate through all the cells that might be available.

Traversing cells using indices

One can take advantage of the ability to access cell values ​​by column and row index[1, 1]instead of'A1'Reading and writing cell values ​​in loops.

Surveillance:In PhpSpreadsheet, column index and row index are 1 based. What it means'A1'~[1, 1]

Below is an example where we read all the values ​​in a worksheet and display them in a table.

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');$reader->setReadDataOnly(TRUE);$spreadsheet = $reader->load("test.xlsx");$worksheet = $worksheet->getActiveSheet();// Get the highest row and column numbers referenced in the worksheet $highestRow = $worksheet->getHighestRow(); // for example. 10$largest column = $worksheet->getlargest column(); // for example 'F'$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // for example. 5echo '<table>' . "\n";for ($line = 1; $line <= $highestline; ++$line) { echo '<tr>' . PHP_EOL; for ($col = 1; $col <= $highestColumnIndex; ++$col) { $value = $worksheet->getCellByColumnAndRow($col, $row)->getValue(); echo '<td>'. $value . '</td>'. PHP_EOL; } echo '</tr>'. PHP_EOL;}echo '</table>' . PHP_EOL;

Alternatively, you can use PHP's "Perl-style" character increments to iterate through cells by coordinates:

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');$reader->setReadDataOnly(TRUE);$spreadsheet = $reader->load("test.xlsx");$worksheet = $worksheet->getActiveSheet();// Get the highest row number and highest column letter referenced in the worksheet $highestRow = $worksheet->getHighestRow(); // for example. 10$largest column = $worksheet->getlargest column(); // eg 'F'// increments the letter of the highest column $highestColumn++;echo '<table>' . "\n";for ($line = 1; $line <= $highestline; ++$line) { echo '<tr>' . PHP_EOL; for ($col = 'A'; $col != $highestColumn; ++$col) { echo '<td>' . $spreadsheet->getCell($col. $line) ->getValue() . '</td>'. PHP_EOL; } echo '</tr>'. PHP_EOL;}echo '</table>' . PHP_EOL;

Please note that we cannot use a<=comparison here because'AA'would fit<= 'B', so we increment the highest column letter and then do a while loop$col !=increment the tallest column.

Using value folders to facilitate data entry

PhpSpreadsheet uses a pattern internally\PhpOffice\PhpSpreadsheet\Cell\IValueBinderimplementation(\PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder) to determine the types of data entered using a celldefinirValor()method (thesetValueExplicit()the method skips this check).

Optionally, the default behavior of PhpSpreadsheet can be modified to make data entry easier. for example one\PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinderThe class is available. Automatically convert percentages, scientific numbers, and dates entered as strings to the correct format and set cell style information. The following example shows how to set the value archive in a php spreadsheet:

/** PhpSpreadsheet */require_once 'src/Boostrap.php';// Set Values ​​folder\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder() );// Create one new table object $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();// ...// Add some similar data to some different data types$spreadsheet->getActiveSheet()->setCellValue('A4', 'Percentage : ' ) ;// Converts the string value to 0.1 and sets the cell style to percentage ' , 'Date/Time Value:'); // Converts the string value to an Excel date stamp and sets the cell's date format. style$spreadsheet->getActiveSheet( )->setCellValue('B5', '21 Dec 1983');

Alternatively a\PhpOffice\PhpSpreadsheet\Cell\StringValueBinderThe class is available when you want to keep all content as strings. This can be useful if you are uploading a file that contains values ​​that can be interpreted as numbers (for example, numbers with a leading sign, such as international phone numbers such as+441615579382), but should remain as strings (non-international phone numbers with leading zeros are already preserved as strings).

By default, StringValueBinder converts any data type passed to it into a string. However, there are several settings you can use to specify that certain data types should not be converted to strings, but should be left "as is":

// Wertdefinition $stringValueBinder = new \PhpOffice\PhpSpreadsheet\Cell\StringValueBinder();$stringValueBinder->setNumericConversion(false) ->setBooleanConversion(false) ->setNullConversion(false) ->setFormulaConversion(false);\ PhpOffice\ PhpSpreadsheet\ Celda\Celda::setValueBinder( $stringValueBinder );

Create your own stock folder

Creating your own stock folder is relatively easy. If a more specific value binding is required, you can implement it\PhpOffice\PhpSpreadsheet\Cell\IValueBinderInterface or extension of the existing one\PhpOffice\PhpSpreadsheet\Cell\DefaultValueBindero\PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinderKlassen.