visualizer / vendor / phpoffice / phpexcel / Examples / 39dropdown.php

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

176 lines 5.8 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()
47	->setCreator("PHPOffice")
48	->setLastModifiedBy("PHPOffice")
49	->setTitle("PHPExcel Test Document")
50	->setSubject("PHPExcel Test Document")
51	->setDescription("Test document for PHPExcel, generated using PHP classes.")
52	->setKeywords("Office PHPExcel php")
53	->setCategory("Test result file");
54
55
56	function transpose($value) {
57	return array($value);
58	}
59
60	// Add some data
61	$continentColumn = 'D';
62	$column = 'F';
63
64	// Set data for dropdowns
65	foreach(glob('./data/continents/*') as $key => $filename) {
66	$continent = pathinfo($filename, PATHINFO_FILENAME);
67	echo "Loading $continent", EOL;
68	$continent = str_replace(' ','_',$continent);
69	$countries = file($filename, FILE_IGNORE_NEW_LINES \| FILE_SKIP_EMPTY_LINES);
70	$countryCount = count($countries);
71
72	// Transpose $countries from a row to a column array
73	$countries = array_map('transpose', $countries);
74	$objPHPExcel->getActiveSheet()
75	->fromArray($countries, null, $column . '1');
76	$objPHPExcel->addNamedRange(
77	new PHPExcel_NamedRange(
78	$continent,
79	$objPHPExcel->getActiveSheet(), $column . '1:' . $column . $countryCount
80	)
81	);
82	$objPHPExcel->getActiveSheet()
83	->getColumnDimension($column)
84	->setVisible(false);
85
86	$objPHPExcel->getActiveSheet()
87	->setCellValue($continentColumn . ($key+1), $continent);
88
89	++$column;
90	}
91
92	// Hide the dropdown data
93	$objPHPExcel->getActiveSheet()
94	->getColumnDimension($continentColumn)
95	->setVisible(false);
96
97	$objPHPExcel->addNamedRange(
98	new PHPExcel_NamedRange(
99	'Continents',
100	$objPHPExcel->getActiveSheet(), $continentColumn . '1:' . $continentColumn . ($key+1)
101	)
102	);
103
104
105	// Set selection cells
106	$objPHPExcel->getActiveSheet()
107	->setCellValue('A1', 'Continent:');
108	$objPHPExcel->getActiveSheet()
109	->setCellValue('B1', 'Select continent');
110	$objPHPExcel->getActiveSheet()
111	->setCellValue('B3', '=' . $column . 1);
112	$objPHPExcel->getActiveSheet()
113	->setCellValue('B3', 'Select country');
114	$objPHPExcel->getActiveSheet()
115	->getStyle('A1:A3')
116	->getFont()->setBold(true);
117
118	// Set linked validators
119	$objValidation = $objPHPExcel->getActiveSheet()
120	->getCell('B1')
121	->getDataValidation();
122	$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST )
123	->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION )
124	->setAllowBlank(false)
125	->setShowInputMessage(true)
126	->setShowErrorMessage(true)
127	->setShowDropDown(true)
128	->setErrorTitle('Input error')
129	->setError('Continent is not in the list.')
130	->setPromptTitle('Pick from the list')
131	->setPrompt('Please pick a continent from the drop-down list.')
132	->setFormula1('=Continents');
133
134	$objPHPExcel->getActiveSheet()
135	->setCellValue('A3', 'Country:');
136	$objPHPExcel->getActiveSheet()
137	->getStyle('A3')
138	->getFont()->setBold(true);
139
140	$objValidation = $objPHPExcel->getActiveSheet()
141	->getCell('B3')
142	->getDataValidation();
143	$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST )
144	->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION )
145	->setAllowBlank(false)
146	->setShowInputMessage(true)
147	->setShowErrorMessage(true)
148	->setShowDropDown(true)
149	->setErrorTitle('Input error')
150	->setError('Country is not in the list.')
151	->setPromptTitle('Pick from the list')
152	->setPrompt('Please pick a country from the drop-down list.')
153	->setFormula1('=INDIRECT($B$1)');
154
155
156	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);
157	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
158
159
160	// Set active sheet index to the first sheet, so Excel opens this as the first sheet
161	$objPHPExcel->setActiveSheetIndex(0);
162
163	// Save Excel 2007 file
164	// This linked validation list method only seems to work for Excel2007, not for Excel5
165	echo date('H:i:s') , " Write to Excel2007 format" , EOL;
166	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
167	$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
168	echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
169
170	// Echo memory peak usage
171	echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;
172
173	// Echo done
174	echo date('H:i:s') , " Done writing files" , EOL;
175	echo 'Files have been created in ' , getcwd() , EOL;
176