visualizer / vendor / phpoffice / phpexcel / Documentation / Examples / Calculations / Database / DMAX.php

DMAX.php in Visualizer – Tables & Charts Manager with Built-in AI Generator 3.1.2, at vendor/phpoffice/phpexcel/Documentation/Examples/Calculations/Database/DMAX.php

89 lines 2.5 KB

Raw Download Zip

1	<?php
2
3	error_reporting(E_ALL);
4	set_time_limit(0);
5
6	date_default_timezone_set('Europe/London');
7
8
9	?>
10	<html>
11	<head>
12	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
13
14	<title>PHPExcel Calculation Examples</title>
15
16	</head>
17	<body>
18
19	<h1>DMAX</h1>
20	<h2>Returns the maximum value from selected database entries.</h2>
21	<?php
22
23	/ Include path /
24	set_include_path(get_include_path() . PATH_SEPARATOR . '../../../../Classes/');
25
26	/** Include PHPExcel */
27	include 'PHPExcel.php';
28
29
30	// Create new PHPExcel object
31	$objPHPExcel = new PHPExcel();
32	$worksheet = $objPHPExcel->getActiveSheet();
33
34	// Add some data
35	$database = array( array( 'Tree', 'Height', 'Age', 'Yield', 'Profit' ),
36	array( 'Apple', 18, 20, 14, 105.00 ),
37	array( 'Pear', 12, 12, 10, 96.00 ),
38	array( 'Cherry', 13, 14, 9, 105.00 ),
39	array( 'Apple', 14, 15, 10, 75.00 ),
40	array( 'Pear', 9, 8, 8, 76.80 ),
41	array( 'Apple', 8, 9, 6, 45.00 ),
42	);
43	$criteria = array( array( 'Tree', 'Height', 'Age', 'Yield', 'Profit', 'Height' ),
44	array( '="=Apple"', '>10', NULL, NULL, NULL, '<16' ),
45	array( '="=Pear"', NULL, NULL, NULL, NULL, NULL )
46	);
47
48	$worksheet->fromArray( $criteria, NULL, 'A1' );
49	$worksheet->fromArray( $database, NULL, 'A4' );
50
51	$worksheet->setCellValue('A12', 'The tallest tree in the orchard');
52	$worksheet->setCellValue('B12', '=DMAX(A4:E10,"Height",A4:E10)');
53
54	$worksheet->setCellValue('A13', 'The Oldest apple tree in the orchard');
55	$worksheet->setCellValue('B13', '=DMAX(A4:E10,3,A1:A2)');
56
57
58	echo '<hr />';
59
60
61	echo '<h4>Database</h4>';
62
63	$databaseData = $worksheet->rangeToArray('A4:E10',null,true,true,true);
64	var_dump($databaseData);
65
66
67	echo '<hr />';
68
69
70	// Test the formulae
71	echo '<h4>Criteria</h4>';
72
73	echo 'ALL' . '<br /><br />';
74
75	echo $worksheet->getCell("A12")->getValue() .'<br />';
76	echo 'DMAX() Result is ' . $worksheet->getCell("B12")->getCalculatedValue() .'<br /><br />';
77
78	echo '<h4>Criteria</h4>';
79
80	$criteriaData = $worksheet->rangeToArray('A1:A2',null,true,true,true);
81	var_dump($criteriaData);
82
83	echo $worksheet->getCell("A13")->getValue() .'<br />';
84	echo 'DMAX() Result is ' . $worksheet->getCell("B13")->getCalculatedValue();
85
86
87	?>
88	<body>
89	</html>