visualizer / vendor / phpoffice / phpexcel / Examples / 08conditionalformatting.php

08conditionalformatting.php in Visualizer – Tables & Charts Manager with Built-in AI Generator 2.2.0, at vendor/phpoffice/phpexcel/Examples/08conditionalformatting.php

190 lines 8.2 KB

Raw Download Zip

1	<?php
2	/**
3	* PHPExcel
4	*
5	* Copyright (C) 2006 - 2014 PHPExcel
6	*
7	* This library is free software; you can redistribute it and/or
8	* modify it under the terms of the GNU Lesser General Public
9	* License as published by the Free Software Foundation; either
10	* version 2.1 of the License, or (at your option) any later version.
11	*
12	* This library is distributed in the hope that it will be useful,
13	* but WITHOUT ANY WARRANTY; without even the implied warranty of
14	* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15	* Lesser General Public License for more details.
16	*
17	* You should have received a copy of the GNU Lesser General Public
18	* License along with this library; if not, write to the Free Software
19	* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
20	*
21	* @category PHPExcel
22	* @package PHPExcel
23	* @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
24	* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25	* @version ##VERSION##, ##DATE##
26	*/
27
28	/** Error reporting */
29	error_reporting(E_ALL);
30	ini_set('display_errors', TRUE);
31	ini_set('display_startup_errors', TRUE);
32
33	define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
34
35	date_default_timezone_set('Europe/London');
36
37	/** Include PHPExcel */
38	require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';
39
40
41	// Create new PHPExcel object
42	echo date('H:i:s') , " Create new PHPExcel object" , EOL;
43	$objPHPExcel = new PHPExcel();
44
45	// Set document properties
46	echo date('H:i:s') , " Set document properties" , EOL;
47	$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
48	->setLastModifiedBy("Maarten Balliauw")
49	->setTitle("Office 2007 XLSX Test Document")
50	->setSubject("Office 2007 XLSX Test Document")
51	->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
52	->setKeywords("office 2007 openxml php")
53	->setCategory("Test result file");
54
55
56	// Create a first sheet, representing sales data
57	echo date('H:i:s') , " Add some data" , EOL;
58	$objPHPExcel->setActiveSheetIndex(0);
59	$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Description')
60	->setCellValue('B1', 'Amount');
61
62	$objPHPExcel->getActiveSheet()->setCellValue('A2', 'Paycheck received')
63	->setCellValue('B2', 100);
64
65	$objPHPExcel->getActiveSheet()->setCellValue('A3', 'Cup of coffee bought')
66	->setCellValue('B3', -1.5);
67
68	$objPHPExcel->getActiveSheet()->setCellValue('A4', 'Cup of coffee bought')
69	->setCellValue('B4', -1.5);
70
71	$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Cup of tea bought')
72	->setCellValue('B5', -1.2);
73
74	$objPHPExcel->getActiveSheet()->setCellValue('A6', 'Found some money')
75	->setCellValue('B6', 8);
76
77	$objPHPExcel->getActiveSheet()->setCellValue('A7', 'Total:')
78	->setCellValue('B7', '=SUM(B2:B6)');
79
80
81	// Set column widths
82	echo date('H:i:s') , " Set column widths" , EOL;
83	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
84	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
85
86
87	// Add conditional formatting
88	echo date('H:i:s') , " Add conditional formatting" , EOL;
89	$objConditional1 = new PHPExcel_Style_Conditional();
90	$objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS)
91	->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_BETWEEN)
92	->addCondition('200')
93	->addCondition('400');
94	$objConditional1->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_YELLOW);
95	$objConditional1->getStyle()->getFont()->setBold(true);
96	$objConditional1->getStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
97
98	$objConditional2 = new PHPExcel_Style_Conditional();
99	$objConditional2->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS)
100	->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN)
101	->addCondition('0');
102	$objConditional2->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
103	$objConditional2->getStyle()->getFont()->setItalic(true);
104	$objConditional2->getStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
105
106	$objConditional3 = new PHPExcel_Style_Conditional();
107	$objConditional3->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS)
108	->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_GREATERTHANOREQUAL)
109	->addCondition('0');
110	$objConditional3->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_GREEN);
111	$objConditional3->getStyle()->getFont()->setItalic(true);
112	$objConditional3->getStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
113
114	$conditionalStyles = $objPHPExcel->getActiveSheet()->getStyle('B2')->getConditionalStyles();
115	array_push($conditionalStyles, $objConditional1);
116	array_push($conditionalStyles, $objConditional2);
117	array_push($conditionalStyles, $objConditional3);
118	$objPHPExcel->getActiveSheet()->getStyle('B2')->setConditionalStyles($conditionalStyles);
119
120
121	// duplicate the conditional styles across a range of cells
122	echo date('H:i:s') , " Duplicate the conditional formatting across a range of cells" , EOL;
123	$objPHPExcel->getActiveSheet()->duplicateConditionalStyle(
124	$objPHPExcel->getActiveSheet()->getStyle('B2')->getConditionalStyles(),
125	'B3:B7'
126	);
127
128
129	// Set fonts
130	echo date('H:i:s') , " Set fonts" , EOL;
131	$objPHPExcel->getActiveSheet()->getStyle('A1:B1')->getFont()->setBold(true);
132	//$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
133	$objPHPExcel->getActiveSheet()->getStyle('A7:B7')->getFont()->setBold(true);
134	//$objPHPExcel->getActiveSheet()->getStyle('B7')->getFont()->setBold(true);
135
136
137	// Set header and footer. When no different headers for odd/even are used, odd header is assumed.
138	echo date('H:i:s') , " Set header/footer" , EOL;
139	$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&BPersonal cash register&RPrinted on &D');
140	$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');
141
142
143	// Set page orientation and size
144	echo date('H:i:s') , " Set page orientation and size" , EOL;
145	$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
146	$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
147
148
149	// Rename worksheet
150	echo date('H:i:s') , " Rename worksheet" , EOL;
151	$objPHPExcel->getActiveSheet()->setTitle('Invoice');
152
153
154	// Set active sheet index to the first sheet, so Excel opens this as the first sheet
155	$objPHPExcel->setActiveSheetIndex(0);
156
157
158	// Save Excel 2007 file
159	echo date('H:i:s') , " Write to Excel2007 format" , EOL;
160	$callStartTime = microtime(true);
161
162	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
163	$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
164	$callEndTime = microtime(true);
165	$callTime = $callEndTime - $callStartTime;
166
167	echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
168	echo 'Call time to write Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;
169	// Save Excel5 file
170	echo date('H:i:s') , " Write to Excel5 format" , EOL;
171	$callStartTime = microtime(true);
172
173	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
174	$objWriter->save(str_replace('.php', '.xls', __FILE__));
175	$callEndTime = microtime(true);
176	$callTime = $callEndTime - $callStartTime;
177
178	echo date('H:i:s') , " File written to " , str_replace('.php', '.xls', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
179	echo 'Call time to write Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;
180	// Echo memory usage
181	echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;
182
183
184	// Echo memory peak usage
185	echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;
186
187	// Echo done
188	echo date('H:i:s') , " Done writing file" , EOL;
189	echo 'File has been created in ' , getcwd() , EOL;
190