visualizer / vendor / phpoffice / phpspreadsheet / docs / topics / recipes.md

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

1,507 lines 52.6 KB

Raw Download Zip

1	# Recipes
2
3	The following pages offer you some widely-used PhpSpreadsheet recipes.
4	Please note that these do NOT offer complete documentation on specific
5	PhpSpreadsheet API functions, but just a bump to get you started. If you
6	need specific API functions, please refer to the [](https://phpoffice.github.io/PhpSpreadsheet/masterAPI documentation](https://phpoffice.github.io/PhpSpreadsheet/master](https://phpoffice.github.io/PhpSpreadsheet/master).
7
8	For example, [setting a worksheet's page orientation and size
9	](#setting-a-worksheets-page-orientation-and-size) covers setting a page
10	orientation to A4. Other paper formats, like US Letter, are not covered
11	in this document, but in the PhpSpreadsheet [](https://phpoffice.github.io/PhpSpreadsheet/masterAPI documentation](https://phpoffice.github.io/PhpSpreadsheet/master](https://phpoffice.github.io/PhpSpreadsheet/master).
12
13	## Setting a spreadsheet's metadata
14
15	PhpSpreadsheet allows an easy way to set a spreadsheet's metadata, using
16	document property accessors. Spreadsheet metadata can be useful for
17	finding a specific document in a file repository or a document
18	management system. For example Microsoft Sharepoint uses document
19	metadata to search for a specific document in its document lists.
20
21	Setting spreadsheet metadata is done as follows:
22
23	``` php
24	$spreadsheet->getProperties()
25	->setCreator("Maarten Balliauw")
26	->setLastModifiedBy("Maarten Balliauw")
27	->setTitle("Office 2007 XLSX Test Document")
28	->setSubject("Office 2007 XLSX Test Document")
29	->setDescription(
30	"Test document for Office 2007 XLSX, generated using PHP classes."
31	)
32	->setKeywords("office 2007 openxml php")
33	->setCategory("Test result file");
34	```
35
36	## Setting a spreadsheet's active sheet
37
38	The following line of code sets the active sheet index to the first
39	sheet:
40
41	``` php
42	$spreadsheet->setActiveSheetIndex(0);
43	```
44
45	You can also set the active sheet by its name/title
46
47	``` php
48	$spreadsheet->setActiveSheetIndexByName('DataSheet')
49	```
50
51	will change the currently active sheet to the worksheet called
52	"DataSheet".
53
54	## Write a date or time into a cell
55
56	In Excel, dates and Times are stored as numeric values counting the
57	number of days elapsed since 1900-01-01. For example, the date
58	'2008-12-31' is represented as 39813. You can verify this in Microsoft
59	Office Excel by entering that date in a cell and afterwards changing the
60	number format to 'General' so the true numeric value is revealed.
61	Likewise, '3:15 AM' is represented as 0.135417.
62
63	PhpSpreadsheet works with UST (Universal Standard Time) date and Time
64	values, but does no internal conversions; so it is up to the developer
65	to ensure that values passed to the date/time conversion functions are
66	UST.
67
68	Writing a date value in a cell consists of 2 lines of code. Select the
69	method that suits you the best. Here are some examples:
70
71	``` php
72
73	// MySQL-like timestamp '2008-12-31' or date string
74	\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder() );
75
76	$spreadsheet->getActiveSheet()
77	->setCellValue('D1', '2008-12-31');
78
79	$spreadsheet->getActiveSheet()->getStyle('D1')
80	->getNumberFormat()
81	->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
82
83	// PHP-time (Unix time)
84	$time = gmmktime(0,0,0,12,31,2008); // int(1230681600)
85	$spreadsheet->getActiveSheet()
86	->setCellValue('D1', \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($time));
87	$spreadsheet->getActiveSheet()->getStyle('D1')
88	->getNumberFormat()
89	->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
90
91	// Excel-date/time
92	$spreadsheet->getActiveSheet()->setCellValue('D1', 39813)
93	$spreadsheet->getActiveSheet()->getStyle('D1')
94	->getNumberFormat()
95	->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
96	```
97
98	The above methods for entering a date all yield the same result.
99	`\PhpOffice\PhpSpreadsheet\Style\NumberFormat` provides a lot of
100	pre-defined date formats.
101
102	The `\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel()` method will also
103	work with a PHP DateTime object.
104
105	Similarly, times (or date and time values) can be entered in the same
106	fashion: just remember to use an appropriate format code.
107
108	Note:
109
110	See section "Using value binders to facilitate data entry" to learn more
111	about the AdvancedValueBinder used in the first example. Excel can also
112	operate in a 1904-based calendar (default for workbooks saved on Mac).
113	Normally, you do not have to worry about this when using PhpSpreadsheet.
114
115	## Write a formula into a cell
116
117	Inside the Excel file, formulas are always stored as they would appear
118	in an English version of Microsoft Office Excel, and PhpSpreadsheet
119	handles all formulae internally in this format. This means that the
120	following rules hold:
121
122	- Decimal separator is `.` (period)
123	- Function argument separator is `,` (comma)
124	- Matrix row separator is `;` (semicolon)
125	- English function names must be used
126
127	This is regardless of which language version of Microsoft Office Excel
128	may have been used to create the Excel file.
129
130	When the final workbook is opened by the user, Microsoft Office Excel
131	will take care of displaying the formula according the applications
132	language. Translation is taken care of by the application!
133
134	The following line of code writes the formula
135	`=IF(C4>500,"profit","loss")` into the cell B8. Note that the
136	formula must start with `=` to make PhpSpreadsheet recognise this as a
137	formula.
138
139	``` php
140	$spreadsheet->getActiveSheet()->setCellValue('B8','=IF(C4>500,"profit","loss")');
141	```
142
143	If you want to write a string beginning with an `=` character to a
144	cell, then you should use the `setCellValueExplicit()` method.
145
146	``` php
147	$spreadsheet->getActiveSheet()
148	->setCellValueExplicit(
149	'B8',
150	'=IF(C4>500,"profit","loss")',
151	\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
152	);
153	```
154
155	A cell's formula can be read again using the following line of code:
156
157	``` php
158	$formula = $spreadsheet->getActiveSheet()->getCell('B8')->getValue();
159	```
160
161	If you need the calculated value of a cell, use the following code. This
162	is further explained in [](./calculation-engine.mdthe calculation engine](./calculation-engine.md](./calculation-engine.md).
163
164	``` php
165	$value = $spreadsheet->getActiveSheet()->getCell('B8')->getCalculatedValue();
166	```
167
168	## Locale Settings for Formulae
169
170	Some localisation elements have been included in PhpSpreadsheet. You can
171	set a locale by changing the settings. To set the locale to Russian you
172	would use:
173
174	``` php
175	$locale = 'ru';
176	$validLocale = \PhpOffice\PhpSpreadsheet\Settings::setLocale($locale);
177	if (!$validLocale) {
178	echo 'Unable to set locale to '.$locale." - reverting to en_us<br />\n";
179	}
180	```
181
182	If Russian language files aren't available, the `setLocale()` method
183	will return an error, and English settings will be used throughout.
184
185	Once you have set a locale, you can translate a formula from its
186	internal English coding.
187
188	``` php
189	$formula = $spreadsheet->getActiveSheet()->getCell('B8')->getValue();
190	$translatedFormula = \PhpOffice\PhpSpreadsheet\Calculation\Calculation::getInstance()->_translateFormulaToLocale($formula);
191	```
192
193	You can also create a formula using the function names and argument
194	separators appropriate to the defined locale; then translate it to
195	English before setting the cell value:
196
197	``` php
198	$formula = '=ДНЕЙ360(ДАТА(2010;2;5);ДАТА(2010;12;31);ИСТИНА)';
199	$internalFormula = \PhpOffice\PhpSpreadsheet\Calculation\Calculation::getInstance()->translateFormulaToEnglish($formula);
200	$spreadsheet->getActiveSheet()->setCellValue('B8',$internalFormula);
201	```
202
203	Currently, formula translation only translates the function names, the
204	constants TRUE and FALSE, and the function argument separators.
205
206	At present, the following locale settings are supported:
207
208	Language \| \| Locale Code
209	---------------------\|----------------------\|-------------
210	Czech \| Ceština \| cs
211	Danish \| Dansk \| da
212	German \| Deutsch \| de
213	Spanish \| Español \| es
214	Finnish \| Suomi \| fi
215	French \| Français \| fr
216	Hungarian \| Magyar \| hu
217	Italian \| Italiano \| it
218	Dutch \| Nederlands \| nl
219	Norwegian \| Norsk \| no
220	Polish \| Jezyk polski \| pl
221	Portuguese \| Português \| pt
222	Brazilian Portuguese \| Português Brasileiro \| pt_br
223	Russian \| русский язык \| ru
224	Swedish \| Svenska \| sv
225	Turkish \| Türkçe \| tr
226
227	## Write a newline character "\n" in a cell (ALT+"Enter")
228
229	In Microsoft Office Excel you get a line break in a cell by hitting
230	ALT+"Enter". When you do that, it automatically turns on "wrap text" for
231	the cell.
232
233	Here is how to achieve this in PhpSpreadsheet:
234
235	``` php
236	$spreadsheet->getActiveSheet()->getCell('A1')->setValue("hello\nworld");
237	$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
238	```
239
240	Tip
241
242	Read more about formatting cells using `getStyle()` elsewhere.
243
244	Tip
245
246	AdvancedValuebinder.php automatically turns on "wrap text" for the cell
247	when it sees a newline character in a string that you are inserting in a
248	cell. Just like Microsoft Office Excel. Try this:
249
250	``` php
251	\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder() );
252
253	$spreadsheet->getActiveSheet()->getCell('A1')->setValue("hello\nworld");
254	```
255
256	Read more about AdvancedValueBinder.php elsewhere.
257
258	## Explicitly set a cell's datatype
259
260	You can set a cell's datatype explicitly by using the cell's
261	setValueExplicit method, or the setCellValueExplicit method of a
262	worksheet. Here's an example:
263
264	``` php
265	$spreadsheet->getActiveSheet()->getCell('A1')
266	->setValueExplicit(
267	'25',
268	\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC
269	);
270	```
271
272	## Change a cell into a clickable URL
273
274	You can make a cell a clickable URL by setting its hyperlink property:
275
276	``` php
277	$spreadsheet->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net');
278	$spreadsheet->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('https://www.example.com');
279	```
280
281	If you want to make a hyperlink to another worksheet/cell, use the
282	following code:
283
284	``` php
285	$spreadsheet->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net');
286	$spreadsheet->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl("sheet://'Sheetname'!A1");
287	```
288
289	## Setting Printer Options for Excel files
290
291	### Setting a worksheet's page orientation and size
292
293	Setting a worksheet's page orientation and size can be done using the
294	following lines of code:
295
296	``` php
297	$spreadsheet->getActiveSheet()->getPageSetup()
298	->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);
299	$spreadsheet->getActiveSheet()->getPageSetup()
300	->setPaperSize(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::PAPERSIZE_A4);
301	```
302
303	Note that there are additional page settings available. Please refer to
304	the [](https://phpoffice.github.io/PhpSpreadsheet/masterAPI documentation](https://phpoffice.github.io/PhpSpreadsheet/master](https://phpoffice.github.io/PhpSpreadsheet/master) for all possible options.
305
306	### Page Setup: Scaling options
307
308	The page setup scaling options in PhpSpreadsheet relate directly to the
309	scaling options in the "Page Setup" dialog as shown in the illustration.
310
311	Default values in PhpSpreadsheet correspond to default values in MS
312	Office Excel as shown in illustration
313
314	![08-page-setup-scaling-options.png](./images/08-page-setup-scaling-options.png)
315
316	method \| initial value \| calling method will trigger \| Note
317	--------------------\|:-------------:\|-----------------------------\|------
318	setFitToPage(...) \| FALSE \| - \|
319	setScale(...) \| 100 \| setFitToPage(FALSE) \|
320	setFitToWidth(...) \| 1 \| setFitToPage(TRUE) \| value 0 means do-not-fit-to-width
321	setFitToHeight(...) \| 1 \| setFitToPage(TRUE) \| value 0 means do-not-fit-to-height
322
323	#### Example
324
325	Here is how to fit to 1 page wide by infinite pages tall:
326
327	``` php
328	$spreadsheet->getActiveSheet()->getPageSetup()->setFitToWidth(1);
329	$spreadsheet->getActiveSheet()->getPageSetup()->setFitToHeight(0);
330	```
331
332	As you can see, it is not necessary to call setFitToPage(TRUE) since
333	setFitToWidth(...) and setFitToHeight(...) triggers this.
334
335	If you use `setFitToWidth()` you should in general also specify
336	`setFitToHeight()` explicitly like in the example. Be careful relying on
337	the initial values.
338
339	### Page margins
340
341	To set page margins for a worksheet, use this code:
342
343	``` php
344	$spreadsheet->getActiveSheet()->getPageMargins()->setTop(1);
345	$spreadsheet->getActiveSheet()->getPageMargins()->setRight(0.75);
346	$spreadsheet->getActiveSheet()->getPageMargins()->setLeft(0.75);
347	$spreadsheet->getActiveSheet()->getPageMargins()->setBottom(1);
348	```
349
350	Note that the margin values are specified in inches.
351
352	![08-page-setup-margins.png](./images/08-page-setup-margins.png)
353
354	### Center a page horizontally/vertically
355
356	To center a page horizontally/vertically, you can use the following
357	code:
358
359	``` php
360	$spreadsheet->getActiveSheet()->getPageSetup()->setHorizontalCentered(true);
361	$spreadsheet->getActiveSheet()->getPageSetup()->setVerticalCentered(false);
362	```
363
364	### Setting the print header and footer of a worksheet
365
366	Setting a worksheet's print header and footer can be done using the
367	following lines of code:
368
369	``` php
370	$spreadsheet->getActiveSheet()->getHeaderFooter()
371	->setOddHeader('&C&HPlease treat this document as confidential!');
372	$spreadsheet->getActiveSheet()->getHeaderFooter()
373	->setOddFooter('&L&B' . $spreadsheet->getProperties()->getTitle() . '&RPage &P of &N');
374	```
375
376	Substitution and formatting codes (starting with &) can be used inside
377	headers and footers. There is no required order in which these codes
378	must appear.
379
380	The first occurrence of the following codes turns the formatting ON, the
381	second occurrence turns it OFF again:
382
383	- Strikethrough
384	- Superscript
385	- Subscript
386
387	Superscript and subscript cannot both be ON at same time. Whichever
388	comes first wins and the other is ignored, while the first is ON.
389
390	The following codes are supported by Xlsx:
391
392	Code \| Meaning
393	-------------------------\|-----------
394	`&L` \| Code for "left section" (there are three header / footer locations, "left", "center", and "right"). When two or more occurrences of this section marker exist, the contents from all markers are concatenated, in the order of appearance, and placed into the left section.
395	`&P` \| Code for "current page #"
396	`&N` \| Code for "total pages"
397	`&font size` \| Code for "text font size", where font size is a font size in points.
398	`&K` \| Code for "text font color" - RGB Color is specified as RRGGBB Theme Color is specifed as TTSNN where TT is the theme color Id, S is either "+" or "-" of the tint/shade value, NN is the tint/shade value.
399	`&S` \| Code for "text strikethrough" on / off
400	`&X` \| Code for "text super script" on / off
401	`&Y` \| Code for "text subscript" on / off
402	`&C` \| Code for "center section". When two or more occurrences of this section marker exist, the contents from all markers are concatenated, in the order of appearance, and placed into the center section.
403	`&D` \| Code for "date"
404	`&T` \| Code for "time"
405	`&G` \| Code for "picture as background" - Please make sure to add the image to the header/footer (see Tip for picture)
406	`&U` \| Code for "text single underline"
407	`&E` \| Code for "double underline"
408	`&R` \| Code for "right section". When two or more occurrences of this section marker exist, the contents from all markers are concatenated, in the order of appearance, and placed into the right section.
409	`&Z` \| Code for "this workbook's file path"
410	`&F` \| Code for "this workbook's file name"
411	`&A` \| Code for "sheet tab name"
412	`&+` \| Code for add to page #
413	`&-` \| Code for subtract from page #
414	`&"font name,font type"` \| Code for "text font name" and "text font type", where font name and font type are strings specifying the name and type of the font, separated by a comma. When a hyphen appears in font name, it means "none specified". Both of font name and font type can be localized values.
415	`&"-,Bold"` \| Code for "bold font style"
416	`&B` \| Code for "bold font style"
417	`&"-,Regular"` \| Code for "regular font style"
418	`&"-,Italic"` \| Code for "italic font style"
419	`&I` \| Code for "italic font style"
420	`&"-,Bold Italic"` \| Code for "bold italic font style"
421	`&O` \| Code for "outline style"
422	`&H` \| Code for "shadow style"
423
424	Tip
425
426	The above table of codes may seem overwhelming first time you are trying to
427	figure out how to write some header or footer. Luckily, there is an easier way.
428	Let Microsoft Office Excel do the work for you.For example, create in Microsoft
429	Office Excel an xlsx file where you insert the header and footer as desired
430	using the programs own interface. Save file as test.xlsx. Now, take that file
431	and read off the values using PhpSpreadsheet as follows:
432
433	```php
434	$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('test.xlsx');
435	$worksheet = $spreadsheet->getActiveSheet();
436
437	var_dump($worksheet->getHeaderFooter()->getOddFooter());
438	var_dump($worksheet->getHeaderFooter()->getEvenFooter());
439	var_dump($worksheet->getHeaderFooter()->getOddHeader());
440	var_dump($worksheet->getHeaderFooter()->getEvenHeader());
441	```
442
443	That reveals the codes for the even/odd header and footer. Experienced
444	users may find it easier to rename test.xlsx to test.zip, unzip it, and
445	inspect directly the contents of the relevant xl/worksheets/sheetX.xml
446	to find the codes for header/footer.
447
448	Tip for picture
449
450	```php
451	$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooterDrawing();
452	$drawing->setName('PhpSpreadsheet logo');
453	$drawing->setPath('./images/PhpSpreadsheet_logo.png');
454	$drawing->setHeight(36);
455	$spreadsheet->getActiveSheet()->getHeaderFooter()->addImage($drawing, \PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooter::IMAGE_HEADER_LEFT);
456	```
457
458	### Setting printing breaks on a row or column
459
460	To set a print break, use the following code, which sets a row break on
461	row 10.
462
463	``` php
464	$spreadsheet->getActiveSheet()->setBreak('A10', \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_ROW);
465	```
466
467	The following line of code sets a print break on column D:
468
469	``` php
470	$spreadsheet->getActiveSheet()->setBreak('D10', \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_COLUMN);
471	```
472
473	### Show/hide gridlines when printing
474
475	To show/hide gridlines when printing, use the following code:
476
477	```php
478	$spreadsheet->getActiveSheet()->setShowGridlines(true);
479	```
480
481	### Setting rows/columns to repeat at top/left
482
483	PhpSpreadsheet can repeat specific rows/cells at top/left of a page. The
484	following code is an example of how to repeat row 1 to 5 on each printed
485	page of a specific worksheet:
486
487	``` php
488	$spreadsheet->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 5);
489	```
490
491	### Specify printing area
492
493	To specify a worksheet's printing area, use the following code:
494
495	``` php
496	$spreadsheet->getActiveSheet()->getPageSetup()->setPrintArea('A1:E5');
497	```
498
499	There can also be multiple printing areas in a single worksheet:
500
501	``` php
502	$spreadsheet->getActiveSheet()->getPageSetup()->setPrintArea('A1:E5,G4:M20');
503	```
504
505	## Styles
506
507	### Formatting cells
508
509	A cell can be formatted with font, border, fill, ... style information.
510	For example, one can set the foreground colour of a cell to red, aligned
511	to the right, and the border to black and thick border style. Let's do
512	that on cell B2:
513
514	``` php
515	$spreadsheet->getActiveSheet()->getStyle('B2')
516	->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
517	$spreadsheet->getActiveSheet()->getStyle('B2')
518	->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
519	$spreadsheet->getActiveSheet()->getStyle('B2')
520	->getBorders()->getTop()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);
521	$spreadsheet->getActiveSheet()->getStyle('B2')
522	->getBorders()->getBottom()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);
523	$spreadsheet->getActiveSheet()->getStyle('B2')
524	->getBorders()->getLeft()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);
525	$spreadsheet->getActiveSheet()->getStyle('B2')
526	->getBorders()->getRight()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);
527	$spreadsheet->getActiveSheet()->getStyle('B2')
528	->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
529	$spreadsheet->getActiveSheet()->getStyle('B2')
530	->getFill()->getStartColor()->setARGB('FFFF0000');
531	```
532
533	`getStyle()` also accepts a cell range as a parameter. For example, you
534	can set a red background color on a range of cells:
535
536	``` php
537	$spreadsheet->getActiveSheet()->getStyle('B3:B7')->getFill()
538	->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
539	->getStartColor()->setARGB('FFFF0000');
540	```
541
542	Tip It is recommended to style many cells at once, using e.g.
543	getStyle('A1:M500'), rather than styling the cells individually in a
544	loop. This is much faster compared to looping through cells and styling
545	them individually.
546
547	There is also an alternative manner to set styles. The following code
548	sets a cell's style to font bold, alignment right, top border thin and a
549	gradient fill:
550
551	``` php
552	$styleArray = [
553	'font' => [
554	'bold' => true,
555	],
556	'alignment' => [
557	'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT,
558	],
559	'borders' => [
560	'top' => [
561	'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
562	],
563	],
564	'fill' => [
565	'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
566	'rotation' => 90,
567	'startColor' => [
568	'argb' => 'FFA0A0A0',
569	],
570	'endColor' => [
571	'argb' => 'FFFFFFFF',
572	],
573	],
574	];
575
576	$spreadsheet->getActiveSheet()->getStyle('A3')->applyFromArray($styleArray);
577	```
578
579	Or with a range of cells:
580
581	``` php
582	$spreadsheet->getActiveSheet()->getStyle('B3:B7')->applyFromArray($styleArray);
583	```
584
585	This alternative method using arrays should be faster in terms of
586	execution whenever you are setting more than one style property. But the
587	difference may barely be measurable unless you have many different
588	styles in your workbook.
589
590	### Number formats
591
592	You often want to format numbers in Excel. For example you may want a
593	thousands separator plus a fixed number of decimals after the decimal
594	separator. Or perhaps you want some numbers to be zero-padded.
595
596	In Microsoft Office Excel you may be familiar with selecting a number
597	format from the "Format Cells" dialog. Here there are some predefined
598	number formats available including some for dates. The dialog is
599	designed in a way so you don't have to interact with the underlying raw
600	number format code unless you need a custom number format.
601
602	In PhpSpreadsheet, you can also apply various predefined number formats.
603	Example:
604
605	``` php
606	$spreadsheet->getActiveSheet()->getStyle('A1')->getNumberFormat()
607	->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
608	```
609
610	This will format a number e.g. 1587.2 so it shows up as 1,587.20 when
611	you open the workbook in MS Office Excel. (Depending on settings for
612	decimal and thousands separators in Microsoft Office Excel it may show
613	up as 1.587,20)
614
615	You can achieve exactly the same as the above by using this:
616
617	``` php
618	$spreadsheet->getActiveSheet()->getStyle('A1')->getNumberFormat()
619	->setFormatCode('#,##0.00');
620	```
621
622	In Microsoft Office Excel, as well as in PhpSpreadsheet, you will have
623	to interact with raw number format codes whenever you need some special
624	custom number format. Example:
625
626	``` php
627	$spreadsheet->getActiveSheet()->getStyle('A1')->getNumberFormat()
628	->setFormatCode('[Blue][>=3000]$#,##0;[Red][<0]$#,##0;$#,##0');
629	```
630
631	Another example is when you want numbers zero-padded with leading zeros
632	to a fixed length:
633
634	``` php
635	$spreadsheet->getActiveSheet()->getCell('A1')->setValue(19);
636	$spreadsheet->getActiveSheet()->getStyle('A1')->getNumberFormat()
637	->setFormatCode('0000'); // will show as 0019 in Excel
638	```
639
640	Tip The rules for composing a number format code in Excel can be
641	rather complicated. Sometimes you know how to create some number format
642	in Microsoft Office Excel, but don't know what the underlying number
643	format code looks like. How do you find it?
644
645	The readers shipped with PhpSpreadsheet come to the rescue. Load your
646	template workbook using e.g. Xlsx reader to reveal the number format
647	code. Example how read a number format code for cell A1:
648
649	``` php
650	$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
651	$spreadsheet = $reader->load('template.xlsx');
652	var_dump($spreadsheet->getActiveSheet()->getStyle('A1')->getNumberFormat()->getFormatCode());
653	```
654
655	Advanced users may find it faster to inspect the number format code
656	directly by renaming template.xlsx to template.zip, unzipping, and
657	looking for the relevant piece of XML code holding the number format
658	code in xl/styles.xml.
659
660	### Alignment and wrap text
661
662	Let's set vertical alignment to the top for cells A1:D4
663
664	``` php
665	$spreadsheet->getActiveSheet()->getStyle('A1:D4')
666	->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
667	```
668
669	Here is how to achieve wrap text:
670
671	``` php
672	$spreadsheet->getActiveSheet()->getStyle('A1:D4')
673	->getAlignment()->setWrapText(true);
674	```
675
676	### Setting the default style of a workbook
677
678	It is possible to set the default style of a workbook. Let's set the
679	default font to Arial size 8:
680
681	``` php
682	$spreadsheet->getDefaultStyle()->getFont()->setName('Arial');
683	$spreadsheet->getDefaultStyle()->getFont()->setSize(8);
684	```
685
686	### Styling cell borders
687
688	In PhpSpreadsheet it is easy to apply various borders on a rectangular
689	selection. Here is how to apply a thick red border outline around cells
690	B2:G8.
691
692	``` php
693	$styleArray = [
694	'borders' => [
695	'outline' => [
696	'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
697	'color' => ['argb' => 'FFFF0000'],
698	],
699	],
700	];
701
702	$worksheet->getStyle('B2:G8')->applyFromArray($styleArray);
703	```
704
705	In Microsoft Office Excel, the above operation would correspond to
706	selecting the cells B2:G8, launching the style dialog, choosing a thick
707	red border, and clicking on the "Outline" border component.
708
709	Note that the border outline is applied to the rectangular selection
710	B2:G8 as a whole, not on each cell individually.
711
712	You can achieve any border effect by using just the 5 basic borders and
713	operating on a single cell at a time:
714
715	- left
716	- right
717	- top
718	- bottom
719	- diagonal
720
721	Additional shortcut borders come in handy like in the example above.
722	These are the shortcut borders available:
723
724	- allBorders
725	- outline
726	- inside
727	- vertical
728	- horizontal
729
730	An overview of all border shortcuts can be seen in the following image:
731
732	![08-styling-border-options.png](./images/08-styling-border-options.png)
733
734	If you simultaneously set e.g. allBorders and vertical, then we have
735	"overlapping" borders, and one of the components has to win over the
736	other where there is border overlap. In PhpSpreadsheet, from weakest to
737	strongest borders, the list is as follows: allBorders, outline/inside,
738	vertical/horizontal, left/right/top/bottom/diagonal.
739
740	This border hierarchy can be utilized to achieve various effects in an
741	easy manner.
742
743	### Valid array keys for style `applyFromArray()`
744
745	The following table lists the valid array keys for
746	`\PhpOffice\PhpSpreadsheet\Style\Style::applyFromArray()` classes. If the "Maps
747	to property" column maps a key to a setter, the value provided for that
748	key will be applied directly. If the "Maps to property" column maps a
749	key to a getter, the value provided for that key will be applied as
750	another style array.
751
752	\PhpOffice\PhpSpreadsheet\Style\Style
753
754	Array key \| Maps to property
755	-------------\|-------------------
756	fill \| getFill()
757	font \| getFont()
758	borders \| getBorders()
759	alignment \| getAlignment()
760	numberFormat \| getNumberFormat()
761	protection \| getProtection()
762
763	\PhpOffice\PhpSpreadsheet\Style\Fill
764
765	Array key \| Maps to property
766	-----------\|-------------------
767	fillType \| setFillType()
768	rotation \| setRotation()
769	startColor \| getStartColor()
770	endColor \| getEndColor()
771	color \| getStartColor()
772
773	\PhpOffice\PhpSpreadsheet\Style\Font
774
775	Array key \| Maps to property
776	------------\|-------------------
777	name \| setName()
778	bold \| setBold()
779	italic \| setItalic()
780	underline \| setUnderline()
781	strikethrough \| setStrikethrough()
782	color \| getColor()
783	size \| setSize()
784	superscript \| setSuperscript()
785	subscript \| setSubscript()
786
787	\PhpOffice\PhpSpreadsheet\Style\Borders
788
789	Array key \| Maps to property
790	------------------\|-------------------
791	allBorders \| getLeft(); getRight(); getTop(); getBottom()
792	left \| getLeft()
793	right \| getRight()
794	top \| getTop()
795	bottom \| getBottom()
796	diagonal \| getDiagonal()
797	vertical \| getVertical()
798	horizontal \| getHorizontal()
799	diagonalDirection \| setDiagonalDirection()
800	outline \| setOutline()
801
802	\PhpOffice\PhpSpreadsheet\Style\Border
803
804	Array key \| Maps to property
805	------------\|-------------------
806	borderStyle \| setBorderStyle()
807	color \| getColor()
808
809	\PhpOffice\PhpSpreadsheet\Style\Alignment
810
811	Array key \| Maps to property
812	------------\|-------------------
813	horizontal \| setHorizontal()
814	vertical \| setVertical()
815	textRotation\| setTextRotation()
816	wrapText \| setWrapText()
817	shrinkToFit \| setShrinkToFit()
818	indent \| setIndent()
819
820	\PhpOffice\PhpSpreadsheet\Style\NumberFormat
821
822	Array key \| Maps to property
823	----------\|-------------------
824	formatCode \| setFormatCode()
825
826	\PhpOffice\PhpSpreadsheet\Style\Protection
827
828	Array key \| Maps to property
829	----------\|-------------------
830	locked \| setLocked()
831	hidden \| setHidden()
832
833	## Conditional formatting a cell
834
835	A cell can be formatted conditionally, based on a specific rule. For
836	example, one can set the foreground colour of a cell to red if its value
837	is below zero, and to green if its value is zero or more.
838
839	One can set a conditional style ruleset to a cell using the following
840	code:
841
842	``` php
843	$conditional1 = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
844	$conditional1->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
845	$conditional1->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_LESSTHAN);
846	$conditional1->addCondition('0');
847	$conditional1->getStyle()->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
848	$conditional1->getStyle()->getFont()->setBold(true);
849
850	$conditional2 = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
851	$conditional2->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
852	$conditional2->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_GREATERTHANOREQUAL);
853	$conditional2->addCondition('0');
854	$conditional2->getStyle()->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_GREEN);
855	$conditional2->getStyle()->getFont()->setBold(true);
856
857	$conditionalStyles = $spreadsheet->getActiveSheet()->getStyle('B2')->getConditionalStyles();
858	$conditionalStyles[] = $conditional1;
859	$conditionalStyles[] = $conditional2;
860
861	$spreadsheet->getActiveSheet()->getStyle('B2')->setConditionalStyles($conditionalStyles);
862	```
863
864	If you want to copy the ruleset to other cells, you can duplicate the
865	style object:
866
867	``` php
868	$spreadsheet->getActiveSheet()
869	->duplicateStyle(
870	$spreadsheet->getActiveSheet()->getStyle('B2'),
871	'B3:B7'
872	);
873	```
874
875	## Add a comment to a cell
876
877	To add a comment to a cell, use the following code. The example below
878	adds a comment to cell E11:
879
880	``` php
881	$spreadsheet->getActiveSheet()
882	->getComment('E11')
883	->setAuthor('Mark Baker');
884	$commentRichText = $spreadsheet->getActiveSheet()
885	->getComment('E11')
886	->getText()->createTextRun('PhpSpreadsheet:');
887	$commentRichText->getFont()->setBold(true);
888	$spreadsheet->getActiveSheet()
889	->getComment('E11')
890	->getText()->createTextRun("\r\n");
891	$spreadsheet->getActiveSheet()
892	->getComment('E11')
893	->getText()->createTextRun('Total amount on the current invoice, excluding VAT.');
894	```
895
896	![08-cell-comment.png](./images/08-cell-comment.png)
897
898	## Apply autofilter to a range of cells
899
900	To apply an autofilter to a range of cells, use the following code:
901
902	``` php
903	$spreadsheet->getActiveSheet()->setAutoFilter('A1:C9');
904	```
905
906	Make sure that you always include the complete filter range! Excel
907	does support setting only the captionrow, but that's not a best
908	practice...
909
910	## Setting security on a spreadsheet
911
912	Excel offers 3 levels of "protection":
913
914	- Document: allows you to set a password on a complete
915	spreadsheet, allowing changes to be made only when that password is
916	entered.
917	- Worksheet: offers other security options: you can
918	disallow inserting rows on a specific sheet, disallow sorting, ...
919	- Cell: offers the option to lock/unlock a cell as well as show/hide
920	the internal formula.
921
922	An example on setting document security:
923
924	``` php
925	$spreadsheet->getSecurity()->setLockWindows(true);
926	$spreadsheet->getSecurity()->setLockStructure(true);
927	$spreadsheet->getSecurity()->setWorkbookPassword("PhpSpreadsheet");
928	```
929
930	An example on setting worksheet security:
931
932	``` php
933	$spreadsheet->getActiveSheet()
934	->getProtection()->setPassword('PhpSpreadsheet');
935	$spreadsheet->getActiveSheet()
936	->getProtection()->setSheet(true);
937	$spreadsheet->getActiveSheet()
938	->getProtection()->setSort(true);
939	$spreadsheet->getActiveSheet()
940	->getProtection()->setInsertRows(true);
941	$spreadsheet->getActiveSheet()
942	->getProtection()->setFormatCells(true);
943	```
944
945	An example on setting cell security:
946
947	``` php
948	$spreadsheet->getActiveSheet()->getStyle('B1')
949	->getProtection()
950	->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_UNPROTECTED);
951	```
952
953	**Make sure you enable worksheet protection if you need any of the
954	worksheet protection features!** This can be done using the following
955	code:
956
957	``` php
958	$spreadsheet->getActiveSheet()->getProtection()->setSheet(true);
959	```
960
961	## Setting data validation on a cell
962
963	Data validation is a powerful feature of Xlsx. It allows to specify an
964	input filter on the data that can be inserted in a specific cell. This
965	filter can be a range (i.e. value must be between 0 and 10), a list
966	(i.e. value must be picked from a list), ...
967
968	The following piece of code only allows numbers between 10 and 20 to be
969	entered in cell B3:
970
971	``` php
972	$validation = $spreadsheet->getActiveSheet()->getCell('B3')
973	->getDataValidation();
974	$validation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_WHOLE );
975	$validation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP );
976	$validation->setAllowBlank(true);
977	$validation->setShowInputMessage(true);
978	$validation->setShowErrorMessage(true);
979	$validation->setErrorTitle('Input error');
980	$validation->setError('Number is not allowed!');
981	$validation->setPromptTitle('Allowed input');
982	$validation->setPrompt('Only numbers between 10 and 20 are allowed.');
983	$validation->setFormula1(10);
984	$validation->setFormula2(20);
985	```
986
987	The following piece of code only allows an item picked from a list of
988	data to be entered in cell B5:
989
990	``` php
991	$validation = $spreadsheet->getActiveSheet()->getCell('B5')
992	->getDataValidation();
993	$validation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
994	$validation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
995	$validation->setAllowBlank(false);
996	$validation->setShowInputMessage(true);
997	$validation->setShowErrorMessage(true);
998	$validation->setShowDropDown(true);
999	$validation->setErrorTitle('Input error');
1000	$validation->setError('Value is not in list.');
1001	$validation->setPromptTitle('Pick from list');
1002	$validation->setPrompt('Please pick a value from the drop-down list.');
1003	$validation->setFormula1('"Item A,Item B,Item C"');
1004	```
1005
1006	When using a data validation list like above, make sure you put the list
1007	between `"` and `"` and that you split the items with a comma (`,`).
1008
1009	It is important to remember that any string participating in an Excel
1010	formula is allowed to be maximum 255 characters (not bytes). This sets a
1011	limit on how many items you can have in the string "Item A,Item B,Item
1012	C". Therefore it is normally a better idea to type the item values
1013	directly in some cell range, say A1:A3, and instead use, say,
1014	`$validation->setFormula1('Sheet!$A$1:$A$3')`. Another benefit is that
1015	the item values themselves can contain the comma `,` character itself.
1016
1017	If you need data validation on multiple cells, one can clone the
1018	ruleset:
1019
1020	``` php
1021	$spreadsheet->getActiveSheet()->getCell('B8')->setDataValidation(clone $validation);
1022	```
1023
1024	## Setting a column's width
1025
1026	A column's width can be set using the following code:
1027
1028	``` php
1029	$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(12);
1030	```
1031
1032	If you want PhpSpreadsheet to perform an automatic width calculation,
1033	use the following code. PhpSpreadsheet will approximate the column with
1034	to the width of the widest column value.
1035
1036	``` php
1037	$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
1038	```
1039
1040	![08-column-width.png](./images/08-column-width.png)
1041
1042	The measure for column width in PhpSpreadsheet does not correspond
1043	exactly to the measure you may be used to in Microsoft Office Excel.
1044	Column widths are difficult to deal with in Excel, and there are several
1045	measures for the column width.
1046
1047	1. Inner width in character units
1048	(e.g. 8.43 this is probably what you are familiar with in Excel)
1049	2. Full width in pixels (e.g. 64 pixels)
1050	3. Full width in character units (e.g. 9.140625, value -1 indicates unset width)
1051
1052	**PhpSpreadsheet always
1053	operates with "3. Full width in character units"** which is in fact the
1054	only value that is stored in any Excel file, hence the most reliable
1055	measure. Unfortunately, **Microsoft Office Excel does not present you
1056	with this measure**. Instead measures 1 and 2 are computed by the
1057	application when the file is opened and these values are presented in
1058	various dialogues and tool tips.
1059
1060	The character width unit is the width of
1061	a `0` (zero) glyph in the workbooks default font. Therefore column
1062	widths measured in character units in two different workbooks can only
1063	be compared if they have the same default workbook font.If you have some
1064	Excel file and need to know the column widths in measure 3, you can
1065	read the Excel file with PhpSpreadsheet and echo the retrieved values.
1066
1067	## Show/hide a column
1068
1069	To set a worksheet's column visibility, you can use the following code.
1070	The first line explicitly shows the column C, the second line hides
1071	column D.
1072
1073	``` php
1074	$spreadsheet->getActiveSheet()->getColumnDimension('C')->setVisible(true);
1075	$spreadsheet->getActiveSheet()->getColumnDimension('D')->setVisible(false);
1076	```
1077
1078	## Group/outline a column
1079
1080	To group/outline a column, you can use the following code:
1081
1082	``` php
1083	$spreadsheet->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1);
1084	```
1085
1086	You can also collapse the column. Note that you should also set the
1087	column invisible, otherwise the collapse will not be visible in Excel
1088	2007.
1089
1090	``` php
1091	$spreadsheet->getActiveSheet()->getColumnDimension('E')->setCollapsed(true);
1092	$spreadsheet->getActiveSheet()->getColumnDimension('E')->setVisible(false);
1093	```
1094
1095	Please refer to the section "group/outline a row" for a complete example
1096	on collapsing.
1097
1098	You can instruct PhpSpreadsheet to add a summary to the right (default),
1099	or to the left. The following code adds the summary to the left:
1100
1101	``` php
1102	$spreadsheet->getActiveSheet()->setShowSummaryRight(false);
1103	```
1104
1105	## Setting a row's height
1106
1107	A row's height can be set using the following code:
1108
1109	``` php
1110	$spreadsheet->getActiveSheet()->getRowDimension('10')->setRowHeight(100);
1111	```
1112
1113	Excel measures row height in points, where 1 pt is 1/72 of an inch (or
1114	about 0.35mm). The default value is 12.75 pts; and the permitted range
1115	of values is between 0 and 409 pts, where 0 pts is a hidden row.
1116
1117	## Show/hide a row
1118
1119	To set a worksheet''s row visibility, you can use the following code.
1120	The following example hides row number 10.
1121
1122	``` php
1123	$spreadsheet->getActiveSheet()->getRowDimension('10')->setVisible(false);
1124	```
1125
1126	Note that if you apply active filters using an AutoFilter, then this
1127	will override any rows that you hide or unhide manually within that
1128	AutoFilter range if you save the file.
1129
1130	## Group/outline a row
1131
1132	To group/outline a row, you can use the following code:
1133
1134	``` php
1135	$spreadsheet->getActiveSheet()->getRowDimension('5')->setOutlineLevel(1);
1136	```
1137
1138	You can also collapse the row. Note that you should also set the row
1139	invisible, otherwise the collapse will not be visible in Excel 2007.
1140
1141	``` php
1142	$spreadsheet->getActiveSheet()->getRowDimension('5')->setCollapsed(true);
1143	$spreadsheet->getActiveSheet()->getRowDimension('5')->setVisible(false);
1144	```
1145
1146	Here's an example which collapses rows 50 to 80:
1147
1148	``` php
1149	for ($i = 51; $i <= 80; $i++) {
1150	$spreadsheet->getActiveSheet()->setCellValue('A' . $i, "FName $i");
1151	$spreadsheet->getActiveSheet()->setCellValue('B' . $i, "LName $i");
1152	$spreadsheet->getActiveSheet()->setCellValue('C' . $i, "PhoneNo $i");
1153	$spreadsheet->getActiveSheet()->setCellValue('D' . $i, "FaxNo $i");
1154	$spreadsheet->getActiveSheet()->setCellValue('E' . $i, true);
1155	$spreadsheet->getActiveSheet()->getRowDimension($i)->setOutlineLevel(1);
1156	$spreadsheet->getActiveSheet()->getRowDimension($i)->setVisible(false);
1157	}
1158
1159	$spreadsheet->getActiveSheet()->getRowDimension(81)->setCollapsed(true);
1160	```
1161
1162	You can instruct PhpSpreadsheet to add a summary below the collapsible
1163	rows (default), or above. The following code adds the summary above:
1164
1165	``` php
1166	$spreadsheet->getActiveSheet()->setShowSummaryBelow(false);
1167	```
1168
1169	## Merge/unmerge cells
1170
1171	If you have a big piece of data you want to display in a worksheet, you
1172	can merge two or more cells together, to become one cell. This can be
1173	done using the following code:
1174
1175	``` php
1176	$spreadsheet->getActiveSheet()->mergeCells('A18:E22');
1177	```
1178
1179	Removing a merge can be done using the unmergeCells method:
1180
1181	``` php
1182	$spreadsheet->getActiveSheet()->unmergeCells('A18:E22');
1183	```
1184
1185	## Inserting rows/columns
1186
1187	You can insert/remove rows/columns at a specific position. The following
1188	code inserts 2 new rows, right before row 7:
1189
1190	``` php
1191	$spreadsheet->getActiveSheet()->insertNewRowBefore(7, 2);
1192	```
1193
1194	## Add a drawing to a worksheet
1195
1196	A drawing is always represented as a separate object, which can be added
1197	to a worksheet. Therefore, you must first instantiate a new
1198	`\PhpOffice\PhpSpreadsheet\Worksheet\Drawing`, and assign its properties a
1199	meaningful value:
1200
1201	``` php
1202	$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
1203	$drawing->setName('Logo');
1204	$drawing->setDescription('Logo');
1205	$drawing->setPath('./images/officelogo.jpg');
1206	$drawing->setHeight(36);
1207	```
1208
1209	To add the above drawing to the worksheet, use the following snippet of
1210	code. PhpSpreadsheet creates the link between the drawing and the
1211	worksheet:
1212
1213	``` php
1214	$drawing->setWorksheet($spreadsheet->getActiveSheet());
1215	```
1216
1217	You can set numerous properties on a drawing, here are some examples:
1218
1219	``` php
1220	$drawing->setName('Paid');
1221	$drawing->setDescription('Paid');
1222	$drawing->setPath('./images/paid.png');
1223	$drawing->setCoordinates('B15');
1224	$drawing->setOffsetX(110);
1225	$drawing->setRotation(25);
1226	$drawing->getShadow()->setVisible(true);
1227	$drawing->getShadow()->setDirection(45);
1228	```
1229
1230	You can also add images created using GD functions without needing to
1231	save them to disk first as In-Memory drawings.
1232
1233	``` php
1234	// Use GD to create an in-memory image
1235	$gdImage = @imagecreatetruecolor(120, 20) or die('Cannot Initialize new GD image stream');
1236	$textColor = imagecolorallocate($gdImage, 255, 255, 255);
1237	imagestring($gdImage, 1, 5, 5, 'Created with PhpSpreadsheet', $textColor);
1238
1239	// Add the In-Memory image to a worksheet
1240	$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing();
1241	$drawing->setName('In-Memory image 1');
1242	$drawing->setDescription('In-Memory image 1');
1243	$drawing->setCoordinates('A1');
1244	$drawing->setImageResource($gdImage);
1245	$drawing->setRenderingFunction(
1246	\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::RENDERING_JPEG
1247	);
1248	$drawing->setMimeType(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_DEFAULT);
1249	$drawing->setHeight(36);
1250	$drawing->setWorksheet($spreadsheet->getActiveSheet());
1251	```
1252
1253	## Reading Images from a worksheet
1254
1255	A commonly asked question is how to retrieve the images from a workbook
1256	that has been loaded, and save them as individual image files to disk.
1257
1258	The following code extracts images from the current active worksheet,
1259	and writes each as a separate file.
1260
1261	``` php
1262	$i = 0;
1263	foreach ($spreadsheet->getActiveSheet()->getDrawingCollection() as $drawing) {
1264	if ($drawing instanceof \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing) {
1265	ob_start();
1266	call_user_func(
1267	$drawing->getRenderingFunction(),
1268	$drawing->getImageResource()
1269	);
1270	$imageContents = ob_get_contents();
1271	ob_end_clean();
1272	switch ($drawing->getMimeType()) {
1273	case \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_PNG :
1274	$extension = 'png';
1275	break;
1276	case \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_GIF:
1277	$extension = 'gif';
1278	break;
1279	case \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_JPEG :
1280	$extension = 'jpg';
1281	break;
1282	}
1283	} else {
1284	$zipReader = fopen($drawing->getPath(),'r');
1285	$imageContents = '';
1286	while (!feof($zipReader)) {
1287	$imageContents .= fread($zipReader,1024);
1288	}
1289	fclose($zipReader);
1290	$extension = $drawing->getExtension();
1291	}
1292	$myFileName = '00_Image_'.++$i.'.'.$extension;
1293	file_put_contents($myFileName,$imageContents);
1294	}
1295	```
1296
1297	## Add rich text to a cell
1298
1299	Adding rich text to a cell can be done using
1300	`\PhpOffice\PhpSpreadsheet\RichText\RichText` instances. Here''s an example, which
1301	creates the following rich text string:
1302
1303	> This invoice is ***payable within thirty days after the end of the
1304	> month*** unless specified otherwise on the invoice.
1305
1306	``` php
1307	$richText = new \PhpOffice\PhpSpreadsheet\RichText\RichText();
1308	$richText->createText('This invoice is ');
1309	$payable = $richText->createTextRun('payable within thirty days after the end of the month');
1310	$payable->getFont()->setBold(true);
1311	$payable->getFont()->setItalic(true);
1312	$payable->getFont()->setColor( new \PhpOffice\PhpSpreadsheet\Style\Color( \PhpOffice\PhpSpreadsheet\Style\Color::COLOR_DARKGREEN ) );
1313	$richText->createText(', unless specified otherwise on the invoice.');
1314	$spreadsheet->getActiveSheet()->getCell('A18')->setValue($richText);
1315	```
1316
1317	## Define a named range
1318
1319	PhpSpreadsheet supports the definition of named ranges. These can be
1320	defined using the following code:
1321
1322	``` php
1323	// Add some data
1324	$spreadsheet->setActiveSheetIndex(0);
1325	$spreadsheet->getActiveSheet()->setCellValue('A1', 'Firstname:');
1326	$spreadsheet->getActiveSheet()->setCellValue('A2', 'Lastname:');
1327	$spreadsheet->getActiveSheet()->setCellValue('B1', 'Maarten');
1328	$spreadsheet->getActiveSheet()->setCellValue('B2', 'Balliauw');
1329
1330	// Define named ranges
1331	$spreadsheet->addNamedRange( new \PhpOffice\PhpSpreadsheet\NamedRange('PersonFN', $spreadsheet->getActiveSheet(), 'B1') );
1332	$spreadsheet->addNamedRange( new \PhpOffice\PhpSpreadsheet\NamedRange('PersonLN', $spreadsheet->getActiveSheet(), 'B2') );
1333	```
1334
1335	Optionally, a fourth parameter can be passed defining the named range
1336	local (i.e. only usable on the current worksheet). Named ranges are
1337	global by default.
1338
1339	## Redirect output to a client's web browser
1340
1341	Sometimes, one really wants to output a file to a client''s browser,
1342	especially when creating spreadsheets on-the-fly. There are some easy
1343	steps that can be followed to do this:
1344
1345	1. Create your PhpSpreadsheet spreadsheet
1346	2. Output HTTP headers for the type of document you wish to output
1347	3. Use the `\PhpOffice\PhpSpreadsheet\Writer\*` of your choice, and save
1348	to `'php://output'`
1349
1350	`\PhpOffice\PhpSpreadsheet\Writer\Xlsx` uses temporary storage when
1351	writing to `php://output`. By default, temporary files are stored in the
1352	script's working directory. When there is no access, it falls back to
1353	the operating system's temporary files location.
1354
1355	This may not be safe for unauthorized viewing! Depending on the
1356	configuration of your operating system, temporary storage can be read by
1357	anyone using the same temporary storage folder. When confidentiality of
1358	your document is needed, it is recommended not to use `php://output`.
1359
1360	### HTTP headers
1361
1362	Example of a script redirecting an Excel 2007 file to the client's
1363	browser:
1364
1365	``` php
1366	/* Here there will be some code where you create $spreadsheet */
1367
1368	// redirect output to client browser
1369	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
1370	header('Content-Disposition: attachment;filename="myfile.xlsx"');
1371	header('Cache-Control: max-age=0');
1372
1373	$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
1374	$writer->save('php://output');
1375	```
1376
1377	Example of a script redirecting an Xls file to the client's browser:
1378
1379	``` php
1380	/* Here there will be some code where you create $spreadsheet */
1381
1382	// redirect output to client browser
1383	header('Content-Type: application/vnd.ms-excel');
1384	header('Content-Disposition: attachment;filename="myfile.xls"');
1385	header('Cache-Control: max-age=0');
1386
1387	$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
1388	$writer->save('php://output');
1389	```
1390
1391	Caution:
1392
1393	Make sure not to include any echo statements or output any other
1394	contents than the Excel file. There should be no whitespace before the
1395	opening `<?php` tag and at most one line break after the closing `?>`
1396	tag (which can also be omitted to avoid problems). Make sure that your
1397	script is saved without a BOM (Byte-order mark) because this counts as
1398	echoing output. The same things apply to all included files. Failing to
1399	follow the above guidelines may result in corrupt Excel files arriving
1400	at the client browser, and/or that headers cannot be set by PHP
1401	(resulting in warning messages).
1402
1403	## Setting the default column width
1404
1405	Default column width can be set using the following code:
1406
1407	``` php
1408	$spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(12);
1409	```
1410
1411	## Setting the default row height
1412
1413	Default row height can be set using the following code:
1414
1415	``` php
1416	$spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
1417	```
1418
1419	## Add a GD drawing to a worksheet
1420
1421	There might be a situation where you want to generate an in-memory image
1422	using GD and add it to a `Spreadsheet` without first having to save this
1423	file to a temporary location.
1424
1425	Here''s an example which generates an image in memory and adds it to the
1426	active worksheet:
1427
1428	``` php
1429	// Generate an image
1430	$gdImage = @imagecreatetruecolor(120, 20) or die('Cannot Initialize new GD image stream');
1431	$textColor = imagecolorallocate($gdImage, 255, 255, 255);
1432	imagestring($gdImage, 1, 5, 5, 'Created with PhpSpreadsheet', $textColor);
1433
1434	// Add a drawing to the worksheet
1435	$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing();
1436	$drawing->setName('Sample image');
1437	$drawing->setDescription('Sample image');
1438	$drawing->setImageResource($gdImage);
1439	$drawing->setRenderingFunction(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::RENDERING_JPEG);
1440	$drawing->setMimeType(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_DEFAULT);
1441	$drawing->setHeight(36);
1442	$drawing->setWorksheet($spreadsheet->getActiveSheet());
1443	```
1444
1445	## Setting worksheet zoom level
1446
1447	To set a worksheet's zoom level, the following code can be used:
1448
1449	``` php
1450	$spreadsheet->getActiveSheet()->getSheetView()->setZoomScale(75);
1451	```
1452
1453	Note that zoom level should be in range 10 - 400.
1454
1455	## Sheet tab color
1456
1457	Sometimes you want to set a color for sheet tab. For example you can
1458	have a red sheet tab:
1459
1460	``` php
1461	$worksheet->getTabColor()->setRGB('FF0000');
1462	```
1463
1464	## Creating worksheets in a workbook
1465
1466	If you need to create more worksheets in the workbook, here is how:
1467
1468	``` php
1469	$worksheet1 = $spreadsheet->createSheet();
1470	$worksheet1->setTitle('Another sheet');
1471	```
1472
1473	Think of `createSheet()` as the "Insert sheet" button in Excel. When you
1474	hit that button a new sheet is appended to the existing collection of
1475	worksheets in the workbook.
1476
1477	## Hidden worksheets (Sheet states)
1478
1479	Set a worksheet to be hidden using this code:
1480
1481	``` php
1482	$spreadsheet->getActiveSheet()
1483	->setSheetState(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_HIDDEN);
1484	```
1485
1486	Sometimes you may even want the worksheet to be "very hidden". The
1487	available sheet states are :
1488
1489	- `\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_VISIBLE`
1490	- `\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_HIDDEN`
1491	- `\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_VERYHIDDEN`
1492
1493	In Excel the sheet state "very hidden" can only be set programmatically,
1494	e.g. with Visual Basic Macro. It is not possible to make such a sheet
1495	visible via the user interface.
1496
1497	## Right-to-left worksheet
1498
1499	Worksheets can be set individually whether column `A` should start at
1500	left or right side. Default is left. Here is how to set columns from
1501	right-to-left.
1502
1503	``` php
1504	// right-to-left worksheet
1505	$spreadsheet->getActiveSheet()->setRightToLeft(true);
1506	```
1507