visualizer / vendor / phpoffice / phpspreadsheet / docs / topics / accessing-cells.md

accessing-cells.md in Visualizer – Tables & Charts Manager with Built-in AI Generator 3.7.4, at vendor/phpoffice/phpspreadsheet/docs/topics/accessing-cells.md

557 lines 20.2 KB

Raw Download Zip

1	# Accessing cells
2
3	Accessing cells in a Spreadsheet should be pretty straightforward. This
4	topic lists some of the options to access a cell.
5
6	## Setting a cell value by coordinate
7
8	Setting a cell value by coordinate can be done using the worksheet's
9	`setCellValue()` method.
10
11	``` php
12	// Set cell A1 with a string value
13	$spreadsheet->getActiveSheet()->setCellValue('A1', 'PhpSpreadsheet');
14
15	// Set cell A2 with a numeric value
16	$spreadsheet->getActiveSheet()->setCellValue('A2', 12345.6789);
17
18	// Set cell A3 with a boolean value
19	$spreadsheet->getActiveSheet()->setCellValue('A3', TRUE);
20
21	// Set cell A4 with a formula
22	$spreadsheet->getActiveSheet()->setCellValue(
23	'A4',
24	'=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))'
25	);
26	```
27
28	Alternatively, you can retrieve the cell object, and then call the
29	cell’s `setValue()` method:
30
31	``` php
32	$spreadsheet->getActiveSheet()
33	->getCell('B8')
34	->setValue('Some value');
35	```
36
37	### Creating a new Cell
38
39	If you make a call to `getCell()`, and the cell doesn't already exist, then
40	PhpSpreadsheet will (by default) create the cell for you. If you don't want
41	to create a new cell, then you can pass a second argument of false, and then
42	`getCell()` will return a null if the cell doesn't exist.
43
44	### BEWARE: Cells assigned to variables as a Detached Reference
45
46	As an "in-memory" model, PHPSpreadsheet can be very demanding of memory,
47	particularly when working with large spreadsheets. One technique used to
48	reduce this memory overhead is cell caching, so cells are actually
49	maintained in a collection that may or may not be held in memory while you
50	are working with the spreadsheet. Because of this, a call to `getCell()`
51	(or any similar method) returns the cell data, and a pointer to the collection.
52	While this is not normally an issue, it can become significant
53	if you assign the result of a call to `getCell()` to a variable. Any
54	subsequent calls to retrieve other cells will unset that pointer, although
55	the cell object will still retain its data values.
56
57	What does this mean? Consider the following code:
58
59	```
60	$spreadSheet = new Spreadsheet();
61	$workSheet = $spreadSheet->getActiveSheet();
62
63	// Set details for the formula that we want to evaluate, together with any data on which it depends
64	$workSheet->fromArray(
65	[1, 2, 3],
66	null,
67	'A1'
68	);
69
70	$cellC1 = $workSheet->getCell('C1');
71	echo 'Value: ', $cellC1->getValue(), '; Address: ', $cellC1->getCoordinate(), PHP_EOL;
72
73	$cellA1 = $workSheet->getCell('A1');
74	echo 'Value: ', $cellA1->getValue(), '; Address: ', $cellA1->getCoordinate(), PHP_EOL;
75
76	echo 'Value: ', $cellC1->getValue(), '; Address: ', $cellC1->getCoordinate(), PHP_EOL;
77	```
78
79	The call to `getCell('C1')` returns the cell at `C1` containing its value (`3`),
80	together with its link to the collection (used to identify its
81	address/coordinate `C1`). The subsequent call to access cell `A1`
82	modifies the value of `$cellC1`, detaching its link to the collection.
83
84	So when we try to display the value and address a second time, we can display
85	its value, but trying to display its address/coordinate will throw an
86	exception because that link has been set to null.
87
88	__Note:__ There are some internal methods that will fetch other cells from the
89	collection, and this too will detach the link to the collection from any cell
90	that you might have assigned to a variable.
91
92	## Excel DataTypes
93
94	MS Excel supports 7 basic datatypes:
95
96	- string
97	- number
98	- boolean
99	- null
100	- formula
101	- error
102	- Inline (or rich text) string
103
104	By default, when you call the worksheet's `setCellValue()` method or the
105	cell's `setValue()` method, PhpSpreadsheet will use the appropriate
106	datatype for PHP nulls, booleans, floats or integers; or cast any string
107	data value that you pass to the method into the most appropriate
108	datatype, so numeric strings will be cast to numbers, while string
109	values beginning with `=` will be converted to a formula. Strings that
110	aren't numeric, or that don't begin with a leading `=` will be treated
111	as genuine string values.
112
113	This "conversion" is handled by a cell "value binder", and you can write
114	custom "value binders" to change the behaviour of these "conversions".
115	The standard PhpSpreadsheet package also provides an "advanced value
116	binder" that handles a number of more complex conversions, such as
117	converting strings with a fractional format like "3/4" to a number value
118	(0.75 in this case) and setting an appropriate "fraction" number format
119	mask. Similarly, strings like "5%" will be converted to a value of 0.05,
120	and a percentage number format mask applied, and strings containing
121	values that look like dates will be converted to Excel serialized
122	datetimestamp values, and a corresponding mask applied. This is
123	particularly useful when loading data from csv files, or setting cell
124	values from a database.
125
126	Formats handled by the advanced value binder include:
127
128	- TRUE or FALSE (dependent on locale settings) are converted to booleans.
129	- Numeric strings identified as scientific (exponential) format are
130	converted to numbers.
131	- Fractions and vulgar fractions are converted to numbers, and
132	an appropriate number format mask applied.
133	- Percentages are converted
134	to numbers, divided by 100, and an appropriate number format mask
135	applied.
136	- Dates and times are converted to Excel timestamp values
137	(numbers), and an appropriate number format mask applied.
138	- When strings contain a newline character (`\n`), then the cell styling is
139	set to wrap.
140
141	You can read more about value binders later in this section of the
142	documentation.
143
144	### Setting a formula in a Cell
145
146	As stated above, if you store a string value with the first character an `=`
147	in a cell. PHPSpreadsheet will treat that value as a formula, and then you
148	can evaluate that formula by calling `getCalculatedValue()` against the cell.
149
150	There may be times though, when you wish to store a value beginning with `=`
151	as a string, and that you don't want PHPSpreadsheet to evaluate as though it
152	was a formula.
153
154	To do this, you need to "escape" the value by setting it as "quoted text".
155
156	```
157	// Set cell A4 with a formula
158	$spreadsheet->getActiveSheet()->setCellValue(
159	'A4',
160	'=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))'
161	);
162	$spreadsheet->getActiveSheet()->getCell('A4')
163	->->getStyle()->setQuotePrefix(true);
164	```
165
166	Then, even if you ask PHPSpreadsheet to return the calculated value for cell
167	`A4`, it will return `=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))`
168	as a string, and not try to evaluate the formula.
169
170
171	### Setting a date and/or time value in a cell
172
173	Date or time values are held as timestamp in Excel (a simple floating
174	point value), and a number format mask is used to show how that value
175	should be formatted; so if we want to store a date in a cell, we need to
176	calculate the correct Excel timestamp, and set a number format mask.
177
178	``` php
179	// Get the current date/time and convert to an Excel date/time
180	$dateTimeNow = time();
181	$excelDateValue = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel( $dateTimeNow );
182	// Set cell A6 with the Excel date/time value
183	$spreadsheet->getActiveSheet()->setCellValue(
184	'A6',
185	$excelDateValue
186	);
187	// Set the number format mask so that the excel timestamp will be displayed as a human-readable date/time
188	$spreadsheet->getActiveSheet()->getStyle('A6')
189	->getNumberFormat()
190	->setFormatCode(
191	\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME
192	);
193	```
194
195	### Setting a number with leading zeroes
196
197	By default, PhpSpreadsheet will automatically detect the value type and
198	set it to the appropriate Excel numeric datatype. This type conversion
199	is handled by a value binder, as described in the section of this
200	document entitled "Using value binders to facilitate data entry".
201
202	Numbers don't have leading zeroes, so if you try to set a numeric value
203	that does have leading zeroes (such as a telephone number) then these
204	will be normally be lost as the value is cast to a number, so
205	"01513789642" will be displayed as 1513789642.
206
207	There are two ways you can force PhpSpreadsheet to override this
208	behaviour.
209
210	Firstly, you can set the datatype explicitly as a string so that it is
211	not converted to a number.
212
213	``` php
214	// Set cell A8 with a numeric value, but tell PhpSpreadsheet it should be treated as a string
215	$spreadsheet->getActiveSheet()->setCellValueExplicit(
216	'A8',
217	"01513789642",
218	\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
219	);
220	```
221
222	Alternatively, you can use a number format mask to display the value
223	with leading zeroes.
224
225	``` php
226	// Set cell A9 with a numeric value
227	$spreadsheet->getActiveSheet()->setCellValue('A9', 1513789642);
228	// Set a number format mask to display the value as 11 digits with leading zeroes
229	$spreadsheet->getActiveSheet()->getStyle('A9')
230	->getNumberFormat()
231	->setFormatCode(
232	'00000000000'
233	);
234	```
235
236	With number format masking, you can even break up the digits into groups
237	to make the value more easily readable.
238
239	``` php
240	// Set cell A10 with a numeric value
241	$spreadsheet->getActiveSheet()->setCellValue('A10', 1513789642);
242	// Set a number format mask to display the value as 11 digits with leading zeroes
243	$spreadsheet->getActiveSheet()->getStyle('A10')
244	->getNumberFormat()
245	->setFormatCode(
246	'0000-000-0000'
247	);
248	```
249
250	![07-simple-example-1.png](./images/07-simple-example-1.png)
251
252	Note: that not all complex format masks such as this one will work
253	when retrieving a formatted value to display "on screen", or for certain
254	writers such as HTML or PDF, but it will work with the true spreadsheet
255	writers (Xlsx and Xls).
256
257	## Setting a range of cells from an array
258
259	It is also possible to set a range of cell values in a single call by
260	passing an array of values to the `fromArray()` method.
261
262	``` php
263	$arrayData = [
264	[NULL, 2010, 2011, 2012],
265	['Q1', 12, 15, 21],
266	['Q2', 56, 73, 86],
267	['Q3', 52, 61, 69],
268	['Q4', 30, 32, 0],
269	];
270	$spreadsheet->getActiveSheet()
271	->fromArray(
272	$arrayData, // The data to set
273	NULL, // Array values with this value will not be set
274	'C3' // Top left coordinate of the worksheet range where
275	// we want to set these values (default is A1)
276	);
277	```
278
279	![07-simple-example-2.png](./images/07-simple-example-2.png)
280
281	If you pass a 2-d array, then this will be treated as a series of rows
282	and columns. A 1-d array will be treated as a single row, which is
283	particularly useful if you're fetching an array of data from a database.
284
285	``` php
286	$rowArray = ['Value1', 'Value2', 'Value3', 'Value4'];
287	$spreadsheet->getActiveSheet()
288	->fromArray(
289	$rowArray, // The data to set
290	NULL, // Array values with this value will not be set
291	'C3' // Top left coordinate of the worksheet range where
292	// we want to set these values (default is A1)
293	);
294	```
295
296	![07-simple-example-3.png](./images/07-simple-example-3.png)
297
298	If you have a simple 1-d array, and want to write it as a column, then
299	the following will convert it into an appropriately structured 2-d array
300	that can be fed to the `fromArray()` method:
301
302	``` php
303	$rowArray = ['Value1', 'Value2', 'Value3', 'Value4'];
304	$columnArray = array_chunk($rowArray, 1);
305	$spreadsheet->getActiveSheet()
306	->fromArray(
307	$columnArray, // The data to set
308	NULL, // Array values with this value will not be set
309	'C3' // Top left coordinate of the worksheet range where
310	// we want to set these values (default is A1)
311	);
312	```
313
314	![07-simple-example-4.png](./images/07-simple-example-4.png)
315
316	## Retrieving a cell value by coordinate
317
318	To retrieve the value of a cell, the cell should first be retrieved from
319	the worksheet using the `getCell()` method. A cell's value can be read
320	using the `getValue()` method.
321
322	``` php
323	// Get the value from cell A1
324	$cellValue = $spreadsheet->getActiveSheet()->getCell('A1')->getValue();
325	```
326
327	This will retrieve the raw, unformatted value contained in the cell.
328
329	If a cell contains a formula, and you need to retrieve the calculated
330	value rather than the formula itself, then use the cell's
331	`getCalculatedValue()` method. This is further explained in
332	[](./calculation-engine.mdthe calculation engine](./calculation-engine.md](./calculation-engine.md).
333
334	``` php
335	// Get the value from cell A4
336	$cellValue = $spreadsheet->getActiveSheet()->getCell('A4')->getCalculatedValue();
337	```
338
339	Alternatively, if you want to see the value with any cell formatting
340	applied (e.g. for a human-readable date or time value), then you can use
341	the cell's `getFormattedValue()` method.
342
343	``` php
344	// Get the value from cell A6
345	$cellValue = $spreadsheet->getActiveSheet()->getCell('A6')->getFormattedValue();
346	```
347
348	## Setting a cell value by column and row
349
350	Setting a cell value by coordinate can be done using the worksheet's
351	`setCellValueByColumnAndRow()` method.
352
353	``` php
354	// Set cell A5 with a string value
355	$spreadsheet->getActiveSheet()->setCellValueByColumnAndRow(1, 5, 'PhpSpreadsheet');
356	```
357
358	Note: that column references start with `1` for column `A`.
359
360	## Retrieving a cell value by column and row
361
362	To retrieve the value of a cell, the cell should first be retrieved from
363	the worksheet using the `getCellByColumnAndRow()` method. A cell’s value can
364	be read again using the following line of code:
365
366	``` php
367	// Get the value from cell B5
368	$cellValue = $spreadsheet->getActiveSheet()->getCellByColumnAndRow(2, 5)->getValue();
369	```
370
371	If you need the calculated value of a cell, use the following code. This
372	is further explained in [](./calculation-engine.mdthe calculation engine](./calculation-engine.md](./calculation-engine.md).
373
374	``` php
375	// Get the value from cell A4
376	$cellValue = $spreadsheet->getActiveSheet()->getCellByColumnAndRow(1, 4)->getCalculatedValue();
377	```
378
379	## Retrieving a range of cell values to an array
380
381	It is also possible to retrieve a range of cell values to an array in a
382	single call using the `toArray()`, `rangeToArray()` or
383	`namedRangeToArray()` methods.
384
385	``` php
386	$dataArray = $spreadsheet->getActiveSheet()
387	->rangeToArray(
388	'C3:E5', // The worksheet range that we want to retrieve
389	NULL, // Value that should be returned for empty cells
390	TRUE, // Should formulas be calculated (the equivalent of getCalculatedValue() for each cell)
391	TRUE, // Should values be formatted (the equivalent of getFormattedValue() for each cell)
392	TRUE // Should the array be indexed by cell row and cell column
393	);
394	```
395
396	These methods will all return a 2-d array of rows and columns. The
397	`toArray()` method will return the whole worksheet; `rangeToArray()`
398	will return a specified range or cells; while `namedRangeToArray()` will
399	return the cells within a defined `named range`.
400
401	## Looping through cells
402
403	### Looping through cells using iterators
404
405	The easiest way to loop cells is by using iterators. Using iterators,
406	one can use foreach to loop worksheets, rows within a worksheet, and
407	cells within a row.
408
409	Below is an example where we read all the values in a worksheet and
410	display them in a table.
411
412	``` php
413	$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
414	$reader->setReadDataOnly(TRUE);
415	$spreadsheet = $reader->load("test.xlsx");
416
417	$worksheet = $spreadsheet->getActiveSheet();
418
419	echo '<table>' . PHP_EOL;
420	foreach ($worksheet->getRowIterator() as $row) {
421	echo '<tr>' . PHP_EOL;
422	$cellIterator = $row->getCellIterator();
423	$cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells,
424	// even if a cell value is not set.
425	// By default, only cells that have a value
426	// set will be iterated.
427	foreach ($cellIterator as $cell) {
428	echo '<td>' .
429	$cell->getValue() .
430	'</td>' . PHP_EOL;
431	}
432	echo '</tr>' . PHP_EOL;
433	}
434	echo '</table>' . PHP_EOL;
435	```
436
437	Note that we have set the cell iterator's
438	`setIterateOnlyExistingCells()` to FALSE. This makes the iterator loop
439	all cells within the worksheet range, even if they have not been set.
440
441	The cell iterator will return a `null` as the cell value if it is not
442	set in the worksheet. Setting the cell iterator's
443	`setIterateOnlyExistingCells()` to `false` will loop all cells in the
444	worksheet that can be available at that moment. This will create new
445	cells if required and increase memory usage! Only use it if it is
446	intended to loop all cells that are possibly available.
447
448	### Looping through cells using indexes
449
450	One can use the possibility to access cell values by column and row
451	index like `[1, 1]` instead of `'A1'` for reading and writing cell values in
452	loops.
453
454	Note: In PhpSpreadsheet column index and row index are 1-based. That means `'A1'` ~ `[1, 1]`
455
456	Below is an example where we read all the values in a worksheet and
457	display them in a table.
458
459	``` php
460	$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
461	$reader->setReadDataOnly(TRUE);
462	$spreadsheet = $reader->load("test.xlsx");
463
464	$worksheet = $spreadsheet->getActiveSheet();
465	// Get the highest row and column numbers referenced in the worksheet
466	$highestRow = $worksheet->getHighestRow(); // e.g. 10
467	$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
468	$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5
469
470	echo '<table>' . "\n";
471	for ($row = 1; $row <= $highestRow; ++$row) {
472	echo '<tr>' . PHP_EOL;
473	for ($col = 1; $col <= $highestColumnIndex; ++$col) {
474	$value = $worksheet->getCellByColumnAndRow($col, $row)->getValue();
475	echo '<td>' . $value . '</td>' . PHP_EOL;
476	}
477	echo '</tr>' . PHP_EOL;
478	}
479	echo '</table>' . PHP_EOL;
480	```
481
482	Alternatively, you can take advantage of PHP's "Perl-style" character
483	incrementors to loop through the cells by coordinate:
484
485	``` php
486	$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
487	$reader->setReadDataOnly(TRUE);
488	$spreadsheet = $reader->load("test.xlsx");
489
490	$worksheet = $spreadsheet->getActiveSheet();
491	// Get the highest row number and column letter referenced in the worksheet
492	$highestRow = $worksheet->getHighestRow(); // e.g. 10
493	$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
494	// Increment the highest column letter
495	$highestColumn++;
496
497	echo '<table>' . "\n";
498	for ($row = 1; $row <= $highestRow; ++$row) {
499	echo '<tr>' . PHP_EOL;
500	for ($col = 'A'; $col != $highestColumn; ++$col) {
501	echo '<td>' .
502	$worksheet->getCell($col . $row)
503	->getValue() .
504	'</td>' . PHP_EOL;
505	}
506	echo '</tr>' . PHP_EOL;
507	}
508	echo '</table>' . PHP_EOL;
509	```
510
511	Note that we can't use a `<=` comparison here, because `'AA'` would match
512	as `<= 'B'`, so we increment the highest column letter and then loop
513	while `$col !=` the incremented highest column.
514
515	## Using value binders to facilitate data entry
516
517	Internally, PhpSpreadsheet uses a default
518	`\PhpOffice\PhpSpreadsheet\Cell\IValueBinder` implementation
519	(\PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder) to determine data
520	types of entered data using a cell's `setValue()` method (the
521	`setValueExplicit()` method bypasses this check).
522
523	Optionally, the default behaviour of PhpSpreadsheet can be modified,
524	allowing easier data entry. For example, a
525	`\PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder` class is available.
526	It automatically converts percentages, number in scientific format, and
527	dates entered as strings to the correct format, also setting the cell's
528	style information. The following example demonstrates how to set the
529	value binder in PhpSpreadsheet:
530
531	``` php
532	/** PhpSpreadsheet */
533	require_once 'src/Boostrap.php';
534
535	// Set value binder
536	\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder() );
537
538	// Create new Spreadsheet object
539	$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
540
541	// ...
542	// Add some data, resembling some different data types
543	$spreadsheet->getActiveSheet()->setCellValue('A4', 'Percentage value:');
544	// Converts the string value to 0.1 and sets percentage cell style
545	$spreadsheet->getActiveSheet()->setCellValue('B4', '10%');
546
547	$spreadsheet->getActiveSheet()->setCellValue('A5', 'Date/time value:');
548	// Converts the string value to an Excel datestamp and sets the date format cell style
549	$spreadsheet->getActiveSheet()->setCellValue('B5', '21 December 1983');
550	```
551
552	Creating your own value binder is easy. When advanced value binding
553	is required, you can implement the
554	`\PhpOffice\PhpSpreadsheet\Cell\IValueBinder` interface or extend the
555	`\PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder` or
556	`\PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder` classes.
557