visualizer / vendor / phpoffice / phpexcel / Examples / 03formulas.php

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

150 lines 6.4 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	date_default_timezone_set('Europe/London');
33
34	define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
35
36	/** Include PHPExcel */
37	require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';
38
39
40	// Create new PHPExcel object
41	echo date('H:i:s') , " Create new PHPExcel object" , EOL;
42	$objPHPExcel = new PHPExcel();
43
44	// Set document properties
45	echo date('H:i:s') , " Set document properties" , EOL;
46	$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
47	->setLastModifiedBy("Maarten Balliauw")
48	->setTitle("Office 2007 XLSX Test Document")
49	->setSubject("Office 2007 XLSX Test Document")
50	->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
51	->setKeywords("office 2007 openxml php")
52	->setCategory("Test result file");
53
54
55	// Add some data, we will use some formulas here
56	echo date('H:i:s') , " Add some data" , EOL;
57	$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Sum:');
58
59	$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Range #1')
60	->setCellValue('B2', 3)
61	->setCellValue('B3', 7)
62	->setCellValue('B4', 13)
63	->setCellValue('B5', '=SUM(B2:B4)');
64	echo date('H:i:s') , " Sum of Range #1 is " ,
65	$objPHPExcel->getActiveSheet()->getCell('B5')->getCalculatedValue() , EOL;
66
67	$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Range #2')
68	->setCellValue('C2', 5)
69	->setCellValue('C3', 11)
70	->setCellValue('C4', 17)
71	->setCellValue('C5', '=SUM(C2:C4)');
72	echo date('H:i:s') , " Sum of Range #2 is " ,
73	$objPHPExcel->getActiveSheet()->getCell('C5')->getCalculatedValue() , EOL;
74
75	$objPHPExcel->getActiveSheet()->setCellValue('A7', 'Total of both ranges:');
76	$objPHPExcel->getActiveSheet()->setCellValue('B7', '=SUM(B5:C5)');
77	echo date('H:i:s') , " Sum of both Ranges is " ,
78	$objPHPExcel->getActiveSheet()->getCell('B7')->getCalculatedValue() , EOL;
79
80	$objPHPExcel->getActiveSheet()->setCellValue('A8', 'Minimum of both ranges:');
81	$objPHPExcel->getActiveSheet()->setCellValue('B8', '=MIN(B2:C4)');
82	echo date('H:i:s') , " Minimum value in either Range is " ,
83	$objPHPExcel->getActiveSheet()->getCell('B8')->getCalculatedValue() , EOL;
84
85	$objPHPExcel->getActiveSheet()->setCellValue('A9', 'Maximum of both ranges:');
86	$objPHPExcel->getActiveSheet()->setCellValue('B9', '=MAX(B2:C4)');
87	echo date('H:i:s') , " Maximum value in either Range is " ,
88	$objPHPExcel->getActiveSheet()->getCell('B9')->getCalculatedValue() , EOL;
89
90	$objPHPExcel->getActiveSheet()->setCellValue('A10', 'Average of both ranges:');
91	$objPHPExcel->getActiveSheet()->setCellValue('B10', '=AVERAGE(B2:C4)');
92	echo date('H:i:s') , " Average value of both Ranges is " ,
93	$objPHPExcel->getActiveSheet()->getCell('B10')->getCalculatedValue() , EOL;
94
95
96	// Rename worksheet
97	echo date('H:i:s') , " Rename worksheet" , EOL;
98	$objPHPExcel->getActiveSheet()->setTitle('Formulas');
99
100
101	// Set active sheet index to the first sheet, so Excel opens this as the first sheet
102	$objPHPExcel->setActiveSheetIndex(0);
103
104
105	// Save Excel 2007 file
106	echo date('H:i:s') , " Write to Excel2007 format" , EOL;
107	$callStartTime = microtime(true);
108
109	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
110
111	//
112	// If we set Pre Calculated Formulas to true then PHPExcel will calculate all formulae in the
113	// workbook before saving. This adds time and memory overhead, and can cause some problems with formulae
114	// using functions or features (such as array formulae) that aren't yet supported by the calculation engine
115	// If the value is false (the default) for the Excel2007 Writer, then MS Excel (or the application used to
116	// open the file) will need to recalculate values itself to guarantee that the correct results are available.
117	//
118	//$objWriter->setPreCalculateFormulas(true);
119	$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
120	$callEndTime = microtime(true);
121	$callTime = $callEndTime - $callStartTime;
122
123	echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
124	echo 'Call time to write Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;
125	// Echo memory usage
126	echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;
127
128
129	// Save Excel 95 file
130	echo date('H:i:s') , " Write to Excel5 format" , EOL;
131	$callStartTime = microtime(true);
132
133	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
134	$objWriter->save(str_replace('.php', '.xls', __FILE__));
135	$callEndTime = microtime(true);
136	$callTime = $callEndTime - $callStartTime;
137
138	echo date('H:i:s') , " File written to " , str_replace('.php', '.xls', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
139	echo 'Call time to write Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;
140	// Echo memory usage
141	echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;
142
143
144	// Echo memory peak usage
145	echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;
146
147	// Echo done
148	echo date('H:i:s') , " Done writing files" , EOL;
149	echo 'Files have been created in ' , getcwd() , EOL;
150