"Fossies" - the Fresh Open Source Software Archive 
Member "groupoffice-6.4.215-php-71/vendor/phpoffice/phpexcel/Examples/05featuredemo.inc.php" (22 Nov 2018, 18602 Bytes) of package /linux/www/groupoffice-6.4.215-php-71.tar.gz:
As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) PHP source code syntax highlighting (style:
standard) with prefixed line numbers and
code folding option.
Alternatively you can here
view or
download the uninterpreted source code file.
1 <?php
2 /**
3 * PHPExcel
4 *
5 * Copyright (c) 2006 - 2015 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 - 2015 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
31 /** Include PHPExcel */
32 require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';
33
34
35 // Create new PHPExcel object
36 echo date('H:i:s') , " Create new PHPExcel object" , EOL;
37 $objPHPExcel = new PHPExcel();
38
39 // Set document properties
40 echo date('H:i:s') , " Set document properties" , EOL;
41 $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
42 ->setLastModifiedBy("Maarten Balliauw")
43 ->setTitle("Office 2007 XLSX Test Document")
44 ->setSubject("Office 2007 XLSX Test Document")
45 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
46 ->setKeywords("office 2007 openxml php")
47 ->setCategory("Test result file");
48
49
50 // Create a first sheet, representing sales data
51 echo date('H:i:s') , " Add some data" , EOL;
52 $objPHPExcel->setActiveSheetIndex(0);
53 $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Invoice');
54 $objPHPExcel->getActiveSheet()->setCellValue('D1', PHPExcel_Shared_Date::PHPToExcel( gmmktime(0,0,0,date('m'),date('d'),date('Y')) ));
55 $objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15);
56 $objPHPExcel->getActiveSheet()->setCellValue('E1', '#12566');
57
58 $objPHPExcel->getActiveSheet()->setCellValue('A3', 'Product Id');
59 $objPHPExcel->getActiveSheet()->setCellValue('B3', 'Description');
60 $objPHPExcel->getActiveSheet()->setCellValue('C3', 'Price');
61 $objPHPExcel->getActiveSheet()->setCellValue('D3', 'Amount');
62 $objPHPExcel->getActiveSheet()->setCellValue('E3', 'Total');
63
64 $objPHPExcel->getActiveSheet()->setCellValue('A4', '1001');
65 $objPHPExcel->getActiveSheet()->setCellValue('B4', 'PHP for dummies');
66 $objPHPExcel->getActiveSheet()->setCellValue('C4', '20');
67 $objPHPExcel->getActiveSheet()->setCellValue('D4', '1');
68 $objPHPExcel->getActiveSheet()->setCellValue('E4', '=IF(D4<>"",C4*D4,"")');
69
70 $objPHPExcel->getActiveSheet()->setCellValue('A5', '1012');
71 $objPHPExcel->getActiveSheet()->setCellValue('B5', 'OpenXML for dummies');
72 $objPHPExcel->getActiveSheet()->setCellValue('C5', '22');
73 $objPHPExcel->getActiveSheet()->setCellValue('D5', '2');
74 $objPHPExcel->getActiveSheet()->setCellValue('E5', '=IF(D5<>"",C5*D5,"")');
75
76 $objPHPExcel->getActiveSheet()->setCellValue('E6', '=IF(D6<>"",C6*D6,"")');
77 $objPHPExcel->getActiveSheet()->setCellValue('E7', '=IF(D7<>"",C7*D7,"")');
78 $objPHPExcel->getActiveSheet()->setCellValue('E8', '=IF(D8<>"",C8*D8,"")');
79 $objPHPExcel->getActiveSheet()->setCellValue('E9', '=IF(D9<>"",C9*D9,"")');
80
81 $objPHPExcel->getActiveSheet()->setCellValue('D11', 'Total excl.:');
82 $objPHPExcel->getActiveSheet()->setCellValue('E11', '=SUM(E4:E9)');
83
84 $objPHPExcel->getActiveSheet()->setCellValue('D12', 'VAT:');
85 $objPHPExcel->getActiveSheet()->setCellValue('E12', '=E11*0.21');
86
87 $objPHPExcel->getActiveSheet()->setCellValue('D13', 'Total incl.:');
88 $objPHPExcel->getActiveSheet()->setCellValue('E13', '=E11+E12');
89
90 // Add comment
91 echo date('H:i:s') , " Add comments" , EOL;
92
93 $objPHPExcel->getActiveSheet()->getComment('E11')->setAuthor('PHPExcel');
94 $objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('PHPExcel:');
95 $objCommentRichText->getFont()->setBold(true);
96 $objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun("\r\n");
97 $objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('Total amount on the current invoice, excluding VAT.');
98
99 $objPHPExcel->getActiveSheet()->getComment('E12')->setAuthor('PHPExcel');
100 $objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E12')->getText()->createTextRun('PHPExcel:');
101 $objCommentRichText->getFont()->setBold(true);
102 $objPHPExcel->getActiveSheet()->getComment('E12')->getText()->createTextRun("\r\n");
103 $objPHPExcel->getActiveSheet()->getComment('E12')->getText()->createTextRun('Total amount of VAT on the current invoice.');
104
105 $objPHPExcel->getActiveSheet()->getComment('E13')->setAuthor('PHPExcel');
106 $objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun('PHPExcel:');
107 $objCommentRichText->getFont()->setBold(true);
108 $objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun("\r\n");
109 $objPHPExcel->getActiveSheet()->getComment('E13')->getText()->createTextRun('Total amount on the current invoice, including VAT.');
110 $objPHPExcel->getActiveSheet()->getComment('E13')->setWidth('100pt');
111 $objPHPExcel->getActiveSheet()->getComment('E13')->setHeight('100pt');
112 $objPHPExcel->getActiveSheet()->getComment('E13')->setMarginLeft('150pt');
113 $objPHPExcel->getActiveSheet()->getComment('E13')->getFillColor()->setRGB('EEEEEE');
114
115
116 // Add rich-text string
117 echo date('H:i:s') , " Add rich-text string" , EOL;
118 $objRichText = new PHPExcel_RichText();
119 $objRichText->createText('This invoice is ');
120
121 $objPayable = $objRichText->createTextRun('payable within thirty days after the end of the month');
122 $objPayable->getFont()->setBold(true);
123 $objPayable->getFont()->setItalic(true);
124 $objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );
125
126 $objRichText->createText(', unless specified otherwise on the invoice.');
127
128 $objPHPExcel->getActiveSheet()->getCell('A18')->setValue($objRichText);
129
130 // Merge cells
131 echo date('H:i:s') , " Merge cells" , EOL;
132 $objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
133 $objPHPExcel->getActiveSheet()->mergeCells('A28:B28'); // Just to test...
134 $objPHPExcel->getActiveSheet()->unmergeCells('A28:B28'); // Just to test...
135
136 // Protect cells
137 echo date('H:i:s') , " Protect cells" , EOL;
138 $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!
139 $objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');
140
141 // Set cell number formats
142 echo date('H:i:s') , " Set cell number formats" , EOL;
143 $objPHPExcel->getActiveSheet()->getStyle('E4:E13')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
144
145 // Set column widths
146 echo date('H:i:s') , " Set column widths" , EOL;
147 $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
148 $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
149 $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
150
151 // Set fonts
152 echo date('H:i:s') , " Set fonts" , EOL;
153 $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
154 $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);
155 $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
156 $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
157 $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
158
159 $objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
160 $objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
161
162 $objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setBold(true);
163 $objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setBold(true);
164
165 // Set alignments
166 echo date('H:i:s') , " Set alignments" , EOL;
167 $objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
168 $objPHPExcel->getActiveSheet()->getStyle('D12')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
169 $objPHPExcel->getActiveSheet()->getStyle('D13')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
170
171 $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
172 $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
173
174 $objPHPExcel->getActiveSheet()->getStyle('B5')->getAlignment()->setShrinkToFit(true);
175
176 // Set thin black border outline around column
177 echo date('H:i:s') , " Set thin black border outline around column" , EOL;
178 $styleThinBlackBorderOutline = array(
179 'borders' => array(
180 'outline' => array(
181 'style' => PHPExcel_Style_Border::BORDER_THIN,
182 'color' => array('argb' => 'FF000000'),
183 ),
184 ),
185 );
186 $objPHPExcel->getActiveSheet()->getStyle('A4:E10')->applyFromArray($styleThinBlackBorderOutline);
187
188
189 // Set thick brown border outline around "Total"
190 echo date('H:i:s') , " Set thick brown border outline around Total" , EOL;
191 $styleThickBrownBorderOutline = array(
192 'borders' => array(
193 'outline' => array(
194 'style' => PHPExcel_Style_Border::BORDER_THICK,
195 'color' => array('argb' => 'FF993300'),
196 ),
197 ),
198 );
199 $objPHPExcel->getActiveSheet()->getStyle('D13:E13')->applyFromArray($styleThickBrownBorderOutline);
200
201 // Set fills
202 echo date('H:i:s') , " Set fills" , EOL;
203 $objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
204 $objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getFill()->getStartColor()->setARGB('FF808080');
205
206 // Set style for header row using alternative method
207 echo date('H:i:s') , " Set style for header row using alternative method" , EOL;
208 $objPHPExcel->getActiveSheet()->getStyle('A3:E3')->applyFromArray(
209 array(
210 'font' => array(
211 'bold' => true
212 ),
213 'alignment' => array(
214 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
215 ),
216 'borders' => array(
217 'top' => array(
218 'style' => PHPExcel_Style_Border::BORDER_THIN
219 )
220 ),
221 'fill' => array(
222 'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
223 'rotation' => 90,
224 'startcolor' => array(
225 'argb' => 'FFA0A0A0'
226 ),
227 'endcolor' => array(
228 'argb' => 'FFFFFFFF'
229 )
230 )
231 )
232 );
233
234 $objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray(
235 array(
236 'alignment' => array(
237 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
238 ),
239 'borders' => array(
240 'left' => array(
241 'style' => PHPExcel_Style_Border::BORDER_THIN
242 )
243 )
244 )
245 );
246
247 $objPHPExcel->getActiveSheet()->getStyle('B3')->applyFromArray(
248 array(
249 'alignment' => array(
250 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
251 )
252 )
253 );
254
255 $objPHPExcel->getActiveSheet()->getStyle('E3')->applyFromArray(
256 array(
257 'borders' => array(
258 'right' => array(
259 'style' => PHPExcel_Style_Border::BORDER_THIN
260 )
261 )
262 )
263 );
264
265 // Unprotect a cell
266 echo date('H:i:s') , " Unprotect a cell" , EOL;
267 $objPHPExcel->getActiveSheet()->getStyle('B1')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
268
269 // Add a hyperlink to the sheet
270 echo date('H:i:s') , " Add a hyperlink to an external website" , EOL;
271 $objPHPExcel->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net');
272 $objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('http://www.phpexcel.net');
273 $objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setTooltip('Navigate to website');
274 $objPHPExcel->getActiveSheet()->getStyle('E26')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
275
276 echo date('H:i:s') , " Add a hyperlink to another cell on a different worksheet within the workbook" , EOL;
277 $objPHPExcel->getActiveSheet()->setCellValue('E27', 'Terms and conditions');
278 $objPHPExcel->getActiveSheet()->getCell('E27')->getHyperlink()->setUrl("sheet://'Terms and conditions'!A1");
279 $objPHPExcel->getActiveSheet()->getCell('E27')->getHyperlink()->setTooltip('Review terms and conditions');
280 $objPHPExcel->getActiveSheet()->getStyle('E27')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
281
282 // Add a drawing to the worksheet
283 echo date('H:i:s') , " Add a drawing to the worksheet" , EOL;
284 $objDrawing = new PHPExcel_Worksheet_Drawing();
285 $objDrawing->setName('Logo');
286 $objDrawing->setDescription('Logo');
287 $objDrawing->setPath('./images/officelogo.jpg');
288 $objDrawing->setHeight(36);
289 $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
290
291 // Add a drawing to the worksheet
292 echo date('H:i:s') , " Add a drawing to the worksheet" , EOL;
293 $objDrawing = new PHPExcel_Worksheet_Drawing();
294 $objDrawing->setName('Paid');
295 $objDrawing->setDescription('Paid');
296 $objDrawing->setPath('./images/paid.png');
297 $objDrawing->setCoordinates('B15');
298 $objDrawing->setOffsetX(110);
299 $objDrawing->setRotation(25);
300 $objDrawing->getShadow()->setVisible(true);
301 $objDrawing->getShadow()->setDirection(45);
302 $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
303
304 // Add a drawing to the worksheet
305 echo date('H:i:s') , " Add a drawing to the worksheet" , EOL;
306 $objDrawing = new PHPExcel_Worksheet_Drawing();
307 $objDrawing->setName('PHPExcel logo');
308 $objDrawing->setDescription('PHPExcel logo');
309 $objDrawing->setPath('./images/phpexcel_logo.gif');
310 $objDrawing->setHeight(36);
311 $objDrawing->setCoordinates('D24');
312 $objDrawing->setOffsetX(10);
313 $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
314
315 // Play around with inserting and removing rows and columns
316 echo date('H:i:s') , " Play around with inserting and removing rows and columns" , EOL;
317 $objPHPExcel->getActiveSheet()->insertNewRowBefore(6, 10);
318 $objPHPExcel->getActiveSheet()->removeRow(6, 10);
319 $objPHPExcel->getActiveSheet()->insertNewColumnBefore('E', 5);
320 $objPHPExcel->getActiveSheet()->removeColumn('E', 5);
321
322 // Set header and footer. When no different headers for odd/even are used, odd header is assumed.
323 echo date('H:i:s') , " Set header/footer" , EOL;
324 $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&BInvoice&RPrinted on &D');
325 $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');
326
327 // Set page orientation and size
328 echo date('H:i:s') , " Set page orientation and size" , EOL;
329 $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
330 $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
331
332 // Rename first worksheet
333 echo date('H:i:s') , " Rename first worksheet" , EOL;
334 $objPHPExcel->getActiveSheet()->setTitle('Invoice');
335
336
337 // Create a new worksheet, after the default sheet
338 echo date('H:i:s') , " Create a second Worksheet object" , EOL;
339 $objPHPExcel->createSheet();
340
341 // Llorem ipsum...
342 $sLloremIpsum = 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Vivamus eget ante. Sed cursus nunc semper tortor. Aliquam luctus purus non elit. Fusce vel elit commodo sapien dignissim dignissim. Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. Curabitur accumsan magna sed massa. Nullam bibendum quam ac ipsum. Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Proin augue. Praesent malesuada justo sed orci. Pellentesque lacus ligula, sodales quis, ultricies a, ultricies vitae, elit. Sed luctus consectetuer dolor. Vivamus vel sem ut nisi sodales accumsan. Nunc et felis. Suspendisse semper viverra odio. Morbi at odio. Integer a orci a purus venenatis molestie. Nam mattis. Praesent rhoncus, nisi vel mattis auctor, neque nisi faucibus sem, non dapibus elit pede ac nisl. Cras turpis.';
343
344 // Add some data to the second sheet, resembling some different data types
345 echo date('H:i:s') , " Add some data" , EOL;
346 $objPHPExcel->setActiveSheetIndex(1);
347 $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Terms and conditions');
348 $objPHPExcel->getActiveSheet()->setCellValue('A3', $sLloremIpsum);
349 $objPHPExcel->getActiveSheet()->setCellValue('A4', $sLloremIpsum);
350 $objPHPExcel->getActiveSheet()->setCellValue('A5', $sLloremIpsum);
351 $objPHPExcel->getActiveSheet()->setCellValue('A6', $sLloremIpsum);
352
353 // Set the worksheet tab color
354 echo date('H:i:s') , " Set the worksheet tab color" , EOL;
355 $objPHPExcel->getActiveSheet()->getTabColor()->setARGB('FF0094FF');;
356
357 // Set alignments
358 echo date('H:i:s') , " Set alignments" , EOL;
359 $objPHPExcel->getActiveSheet()->getStyle('A3:A6')->getAlignment()->setWrapText(true);
360
361 // Set column widths
362 echo date('H:i:s') , " Set column widths" , EOL;
363 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(80);
364
365 // Set fonts
366 echo date('H:i:s') , " Set fonts" , EOL;
367 $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('Candara');
368 $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
369 $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
370 $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
371
372 $objPHPExcel->getActiveSheet()->getStyle('A3:A6')->getFont()->setSize(8);
373
374 // Add a drawing to the worksheet
375 echo date('H:i:s') , " Add a drawing to the worksheet" , EOL;
376 $objDrawing = new PHPExcel_Worksheet_Drawing();
377 $objDrawing->setName('Terms and conditions');
378 $objDrawing->setDescription('Terms and conditions');
379 $objDrawing->setPath('./images/termsconditions.jpg');
380 $objDrawing->setCoordinates('B14');
381 $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
382
383 // Set page orientation and size
384 echo date('H:i:s') , " Set page orientation and size" , EOL;
385 $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
386 $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
387
388 // Rename second worksheet
389 echo date('H:i:s') , " Rename second worksheet" , EOL;
390 $objPHPExcel->getActiveSheet()->setTitle('Terms and conditions');
391
392
393 // Set active sheet index to the first sheet, so Excel opens this as the first sheet
394 $objPHPExcel->setActiveSheetIndex(0);