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_Calculation
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 1.8.0, 2014-03-02
26: */
27:
28:
29: /** PHPExcel root directory */
30: if (!defined('PHPEXCEL_ROOT')) {
31: /**
32: * @ignore
33: */
34: define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35: require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36: }
37:
38:
39: /**
40: * PHPExcel_Calculation_Database
41: *
42: * @category PHPExcel
43: * @package PHPExcel_Calculation
44: * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
45: */
46: class PHPExcel_Calculation_Database {
47:
48:
49: /**
50: * __fieldExtract
51: *
52: * Extracts the column ID to use for the data field.
53: *
54: * @access private
55: * @param mixed[] $database The range of cells that makes up the list or database.
56: * A database is a list of related data in which rows of related
57: * information are records, and columns of data are fields. The
58: * first row of the list contains labels for each column.
59: * @param mixed $field Indicates which column is used in the function. Enter the
60: * column label enclosed between double quotation marks, such as
61: * "Age" or "Yield," or a number (without quotation marks) that
62: * represents the position of the column within the list: 1 for
63: * the first column, 2 for the second column, and so on.
64: * @return string|NULL
65: *
66: */
67: private static function __fieldExtract($database,$field) {
68: $field = strtoupper(PHPExcel_Calculation_Functions::flattenSingleValue($field));
69: $fieldNames = array_map('strtoupper',array_shift($database));
70:
71: if (is_numeric($field)) {
72: $keys = array_keys($fieldNames);
73: return $keys[$field-1];
74: }
75: $key = array_search($field,$fieldNames);
76: return ($key) ? $key : NULL;
77: }
78:
79: /**
80: * __filter
81: *
82: * Parses the selection criteria, extracts the database rows that match those criteria, and
83: * returns that subset of rows.
84: *
85: * @access private
86: * @param mixed[] $database The range of cells that makes up the list or database.
87: * A database is a list of related data in which rows of related
88: * information are records, and columns of data are fields. The
89: * first row of the list contains labels for each column.
90: * @param mixed[] $criteria The range of cells that contains the conditions you specify.
91: * You can use any range for the criteria argument, as long as it
92: * includes at least one column label and at least one cell below
93: * the column label in which you specify a condition for the
94: * column.
95: * @return array of mixed
96: *
97: */
98: private static function __filter($database,$criteria) {
99: $fieldNames = array_shift($database);
100: $criteriaNames = array_shift($criteria);
101:
102: // Convert the criteria into a set of AND/OR conditions with [:placeholders]
103: $testConditions = $testValues = array();
104: $testConditionsCount = 0;
105: foreach($criteriaNames as $key => $criteriaName) {
106: $testCondition = array();
107: $testConditionCount = 0;
108: foreach($criteria as $row => $criterion) {
109: if ($criterion[$key] > '') {
110: $testCondition[] = '[:'.$criteriaName.']'.PHPExcel_Calculation_Functions::_ifCondition($criterion[$key]);
111: $testConditionCount++;
112: }
113: }
114: if ($testConditionCount > 1) {
115: $testConditions[] = 'OR('.implode(',',$testCondition).')';
116: $testConditionsCount++;
117: } elseif($testConditionCount == 1) {
118: $testConditions[] = $testCondition[0];
119: $testConditionsCount++;
120: }
121: }
122:
123: if ($testConditionsCount > 1) {
124: $testConditionSet = 'AND('.implode(',',$testConditions).')';
125: } elseif($testConditionsCount == 1) {
126: $testConditionSet = $testConditions[0];
127: }
128:
129: // Loop through each row of the database
130: foreach($database as $dataRow => $dataValues) {
131: // Substitute actual values from the database row for our [:placeholders]
132: $testConditionList = $testConditionSet;
133: foreach($criteriaNames as $key => $criteriaName) {
134: $k = array_search($criteriaName,$fieldNames);
135: if (isset($dataValues[$k])) {
136: $dataValue = $dataValues[$k];
137: $dataValue = (is_string($dataValue)) ? PHPExcel_Calculation::_wrapResult(strtoupper($dataValue)) : $dataValue;
138: $testConditionList = str_replace('[:'.$criteriaName.']',$dataValue,$testConditionList);
139: }
140: }
141: // evaluate the criteria against the row data
142: $result = PHPExcel_Calculation::getInstance()->_calculateFormulaValue('='.$testConditionList);
143: // If the row failed to meet the criteria, remove it from the database
144: if (!$result) {
145: unset($database[$dataRow]);
146: }
147: }
148:
149: return $database;
150: }
151:
152:
153: /**
154: * DAVERAGE
155: *
156: * Averages the values in a column of a list or database that match conditions you specify.
157: *
158: * Excel Function:
159: * DAVERAGE(database,field,criteria)
160: *
161: * @access public
162: * @category Database Functions
163: * @param mixed[] $database The range of cells that makes up the list or database.
164: * A database is a list of related data in which rows of related
165: * information are records, and columns of data are fields. The
166: * first row of the list contains labels for each column.
167: * @param string|integer $field Indicates which column is used in the function. Enter the
168: * column label enclosed between double quotation marks, such as
169: * "Age" or "Yield," or a number (without quotation marks) that
170: * represents the position of the column within the list: 1 for
171: * the first column, 2 for the second column, and so on.
172: * @param mixed[] $criteria The range of cells that contains the conditions you specify.
173: * You can use any range for the criteria argument, as long as it
174: * includes at least one column label and at least one cell below
175: * the column label in which you specify a condition for the
176: * column.
177: * @return float
178: *
179: */
180: public static function DAVERAGE($database,$field,$criteria) {
181: $field = self::__fieldExtract($database,$field);
182: if (is_null($field)) {
183: return NULL;
184: }
185: // reduce the database to a set of rows that match all the criteria
186: $database = self::__filter($database,$criteria);
187: // extract an array of values for the requested column
188: $colData = array();
189: foreach($database as $row) {
190: $colData[] = $row[$field];
191: }
192:
193: // Return
194: return PHPExcel_Calculation_Statistical::AVERAGE($colData);
195: } // function DAVERAGE()
196:
197:
198: /**
199: * DCOUNT
200: *
201: * Counts the cells that contain numbers in a column of a list or database that match conditions
202: * that you specify.
203: *
204: * Excel Function:
205: * DCOUNT(database,[field],criteria)
206: *
207: * Excel Function:
208: * DAVERAGE(database,field,criteria)
209: *
210: * @access public
211: * @category Database Functions
212: * @param mixed[] $database The range of cells that makes up the list or database.
213: * A database is a list of related data in which rows of related
214: * information are records, and columns of data are fields. The
215: * first row of the list contains labels for each column.
216: * @param string|integer $field Indicates which column is used in the function. Enter the
217: * column label enclosed between double quotation marks, such as
218: * "Age" or "Yield," or a number (without quotation marks) that
219: * represents the position of the column within the list: 1 for
220: * the first column, 2 for the second column, and so on.
221: * @param mixed[] $criteria The range of cells that contains the conditions you specify.
222: * You can use any range for the criteria argument, as long as it
223: * includes at least one column label and at least one cell below
224: * the column label in which you specify a condition for the
225: * column.
226: * @return integer
227: *
228: * @TODO The field argument is optional. If field is omitted, DCOUNT counts all records in the
229: * database that match the criteria.
230: *
231: */
232: public static function DCOUNT($database,$field,$criteria) {
233: $field = self::__fieldExtract($database,$field);
234: if (is_null($field)) {
235: return NULL;
236: }
237:
238: // reduce the database to a set of rows that match all the criteria
239: $database = self::__filter($database,$criteria);
240: // extract an array of values for the requested column
241: $colData = array();
242: foreach($database as $row) {
243: $colData[] = $row[$field];
244: }
245:
246: // Return
247: return PHPExcel_Calculation_Statistical::COUNT($colData);
248: } // function DCOUNT()
249:
250:
251: /**
252: * DCOUNTA
253: *
254: * Counts the nonblank cells in a column of a list or database that match conditions that you specify.
255: *
256: * Excel Function:
257: * DCOUNTA(database,[field],criteria)
258: *
259: * @access public
260: * @category Database Functions
261: * @param mixed[] $database The range of cells that makes up the list or database.
262: * A database is a list of related data in which rows of related
263: * information are records, and columns of data are fields. The
264: * first row of the list contains labels for each column.
265: * @param string|integer $field Indicates which column is used in the function. Enter the
266: * column label enclosed between double quotation marks, such as
267: * "Age" or "Yield," or a number (without quotation marks) that
268: * represents the position of the column within the list: 1 for
269: * the first column, 2 for the second column, and so on.
270: * @param mixed[] $criteria The range of cells that contains the conditions you specify.
271: * You can use any range for the criteria argument, as long as it
272: * includes at least one column label and at least one cell below
273: * the column label in which you specify a condition for the
274: * column.
275: * @return integer
276: *
277: * @TODO The field argument is optional. If field is omitted, DCOUNTA counts all records in the
278: * database that match the criteria.
279: *
280: */
281: public static function DCOUNTA($database,$field,$criteria) {
282: $field = self::__fieldExtract($database,$field);
283: if (is_null($field)) {
284: return NULL;
285: }
286:
287: // reduce the database to a set of rows that match all the criteria
288: $database = self::__filter($database,$criteria);
289: // extract an array of values for the requested column
290: $colData = array();
291: foreach($database as $row) {
292: $colData[] = $row[$field];
293: }
294:
295: // Return
296: return PHPExcel_Calculation_Statistical::COUNTA($colData);
297: } // function DCOUNTA()
298:
299:
300: /**
301: * DGET
302: *
303: * Extracts a single value from a column of a list or database that matches conditions that you
304: * specify.
305: *
306: * Excel Function:
307: * DGET(database,field,criteria)
308: *
309: * @access public
310: * @category Database Functions
311: * @param mixed[] $database The range of cells that makes up the list or database.
312: * A database is a list of related data in which rows of related
313: * information are records, and columns of data are fields. The
314: * first row of the list contains labels for each column.
315: * @param string|integer $field Indicates which column is used in the function. Enter the
316: * column label enclosed between double quotation marks, such as
317: * "Age" or "Yield," or a number (without quotation marks) that
318: * represents the position of the column within the list: 1 for
319: * the first column, 2 for the second column, and so on.
320: * @param mixed[] $criteria The range of cells that contains the conditions you specify.
321: * You can use any range for the criteria argument, as long as it
322: * includes at least one column label and at least one cell below
323: * the column label in which you specify a condition for the
324: * column.
325: * @return mixed
326: *
327: */
328: public static function DGET($database,$field,$criteria) {
329: $field = self::__fieldExtract($database,$field);
330: if (is_null($field)) {
331: return NULL;
332: }
333:
334: // reduce the database to a set of rows that match all the criteria
335: $database = self::__filter($database,$criteria);
336: // extract an array of values for the requested column
337: $colData = array();
338: foreach($database as $row) {
339: $colData[] = $row[$field];
340: }
341:
342: // Return
343: if (count($colData) > 1) {
344: return PHPExcel_Calculation_Functions::NaN();
345: }
346:
347: return $colData[0];
348: } // function DGET()
349:
350:
351: /**
352: * DMAX
353: *
354: * Returns the largest number in a column of a list or database that matches conditions you that
355: * specify.
356: *
357: * Excel Function:
358: * DMAX(database,field,criteria)
359: *
360: * @access public
361: * @category Database Functions
362: * @param mixed[] $database The range of cells that makes up the list or database.
363: * A database is a list of related data in which rows of related
364: * information are records, and columns of data are fields. The
365: * first row of the list contains labels for each column.
366: * @param string|integer $field Indicates which column is used in the function. Enter the
367: * column label enclosed between double quotation marks, such as
368: * "Age" or "Yield," or a number (without quotation marks) that
369: * represents the position of the column within the list: 1 for
370: * the first column, 2 for the second column, and so on.
371: * @param mixed[] $criteria The range of cells that contains the conditions you specify.
372: * You can use any range for the criteria argument, as long as it
373: * includes at least one column label and at least one cell below
374: * the column label in which you specify a condition for the
375: * column.
376: * @return float
377: *
378: */
379: public static function DMAX($database,$field,$criteria) {
380: $field = self::__fieldExtract($database,$field);
381: if (is_null($field)) {
382: return NULL;
383: }
384:
385: // reduce the database to a set of rows that match all the criteria
386: $database = self::__filter($database,$criteria);
387: // extract an array of values for the requested column
388: $colData = array();
389: foreach($database as $row) {
390: $colData[] = $row[$field];
391: }
392:
393: // Return
394: return PHPExcel_Calculation_Statistical::MAX($colData);
395: } // function DMAX()
396:
397:
398: /**
399: * DMIN
400: *
401: * Returns the smallest number in a column of a list or database that matches conditions you that
402: * specify.
403: *
404: * Excel Function:
405: * DMIN(database,field,criteria)
406: *
407: * @access public
408: * @category Database Functions
409: * @param mixed[] $database The range of cells that makes up the list or database.
410: * A database is a list of related data in which rows of related
411: * information are records, and columns of data are fields. The
412: * first row of the list contains labels for each column.
413: * @param string|integer $field Indicates which column is used in the function. Enter the
414: * column label enclosed between double quotation marks, such as
415: * "Age" or "Yield," or a number (without quotation marks) that
416: * represents the position of the column within the list: 1 for
417: * the first column, 2 for the second column, and so on.
418: * @param mixed[] $criteria The range of cells that contains the conditions you specify.
419: * You can use any range for the criteria argument, as long as it
420: * includes at least one column label and at least one cell below
421: * the column label in which you specify a condition for the
422: * column.
423: * @return float
424: *
425: */
426: public static function DMIN($database,$field,$criteria) {
427: $field = self::__fieldExtract($database,$field);
428: if (is_null($field)) {
429: return NULL;
430: }
431:
432: // reduce the database to a set of rows that match all the criteria
433: $database = self::__filter($database,$criteria);
434: // extract an array of values for the requested column
435: $colData = array();
436: foreach($database as $row) {
437: $colData[] = $row[$field];
438: }
439:
440: // Return
441: return PHPExcel_Calculation_Statistical::MIN($colData);
442: } // function DMIN()
443:
444:
445: /**
446: * DPRODUCT
447: *
448: * Multiplies the values in a column of a list or database that match conditions that you specify.
449: *
450: * Excel Function:
451: * DPRODUCT(database,field,criteria)
452: *
453: * @access public
454: * @category Database Functions
455: * @param mixed[] $database The range of cells that makes up the list or database.
456: * A database is a list of related data in which rows of related
457: * information are records, and columns of data are fields. The
458: * first row of the list contains labels for each column.
459: * @param string|integer $field Indicates which column is used in the function. Enter the
460: * column label enclosed between double quotation marks, such as
461: * "Age" or "Yield," or a number (without quotation marks) that
462: * represents the position of the column within the list: 1 for
463: * the first column, 2 for the second column, and so on.
464: * @param mixed[] $criteria The range of cells that contains the conditions you specify.
465: * You can use any range for the criteria argument, as long as it
466: * includes at least one column label and at least one cell below
467: * the column label in which you specify a condition for the
468: * column.
469: * @return float
470: *
471: */
472: public static function DPRODUCT($database,$field,$criteria) {
473: $field = self::__fieldExtract($database,$field);
474: if (is_null($field)) {
475: return NULL;
476: }
477:
478: // reduce the database to a set of rows that match all the criteria
479: $database = self::__filter($database,$criteria);
480: // extract an array of values for the requested column
481: $colData = array();
482: foreach($database as $row) {
483: $colData[] = $row[$field];
484: }
485:
486: // Return
487: return PHPExcel_Calculation_MathTrig::PRODUCT($colData);
488: } // function DPRODUCT()
489:
490:
491: /**
492: * DSTDEV
493: *
494: * Estimates the standard deviation of a population based on a sample by using the numbers in a
495: * column of a list or database that match conditions that you specify.
496: *
497: * Excel Function:
498: * DSTDEV(database,field,criteria)
499: *
500: * @access public
501: * @category Database Functions
502: * @param mixed[] $database The range of cells that makes up the list or database.
503: * A database is a list of related data in which rows of related
504: * information are records, and columns of data are fields. The
505: * first row of the list contains labels for each column.
506: * @param string|integer $field Indicates which column is used in the function. Enter the
507: * column label enclosed between double quotation marks, such as
508: * "Age" or "Yield," or a number (without quotation marks) that
509: * represents the position of the column within the list: 1 for
510: * the first column, 2 for the second column, and so on.
511: * @param mixed[] $criteria The range of cells that contains the conditions you specify.
512: * You can use any range for the criteria argument, as long as it
513: * includes at least one column label and at least one cell below
514: * the column label in which you specify a condition for the
515: * column.
516: * @return float
517: *
518: */
519: public static function DSTDEV($database,$field,$criteria) {
520: $field = self::__fieldExtract($database,$field);
521: if (is_null($field)) {
522: return NULL;
523: }
524:
525: // reduce the database to a set of rows that match all the criteria
526: $database = self::__filter($database,$criteria);
527: // extract an array of values for the requested column
528: $colData = array();
529: foreach($database as $row) {
530: $colData[] = $row[$field];
531: }
532:
533: // Return
534: return PHPExcel_Calculation_Statistical::STDEV($colData);
535: } // function DSTDEV()
536:
537:
538: /**
539: * DSTDEVP
540: *
541: * Calculates the standard deviation of a population based on the entire population by using the
542: * numbers in a column of a list or database that match conditions that you specify.
543: *
544: * Excel Function:
545: * DSTDEVP(database,field,criteria)
546: *
547: * @access public
548: * @category Database Functions
549: * @param mixed[] $database The range of cells that makes up the list or database.
550: * A database is a list of related data in which rows of related
551: * information are records, and columns of data are fields. The
552: * first row of the list contains labels for each column.
553: * @param string|integer $field Indicates which column is used in the function. Enter the
554: * column label enclosed between double quotation marks, such as
555: * "Age" or "Yield," or a number (without quotation marks) that
556: * represents the position of the column within the list: 1 for
557: * the first column, 2 for the second column, and so on.
558: * @param mixed[] $criteria The range of cells that contains the conditions you specify.
559: * You can use any range for the criteria argument, as long as it
560: * includes at least one column label and at least one cell below
561: * the column label in which you specify a condition for the
562: * column.
563: * @return float
564: *
565: */
566: public static function DSTDEVP($database,$field,$criteria) {
567: $field = self::__fieldExtract($database,$field);
568: if (is_null($field)) {
569: return NULL;
570: }
571:
572: // reduce the database to a set of rows that match all the criteria
573: $database = self::__filter($database,$criteria);
574: // extract an array of values for the requested column
575: $colData = array();
576: foreach($database as $row) {
577: $colData[] = $row[$field];
578: }
579:
580: // Return
581: return PHPExcel_Calculation_Statistical::STDEVP($colData);
582: } // function DSTDEVP()
583:
584:
585: /**
586: * DSUM
587: *
588: * Adds the numbers in a column of a list or database that match conditions that you specify.
589: *
590: * Excel Function:
591: * DSUM(database,field,criteria)
592: *
593: * @access public
594: * @category Database Functions
595: * @param mixed[] $database The range of cells that makes up the list or database.
596: * A database is a list of related data in which rows of related
597: * information are records, and columns of data are fields. The
598: * first row of the list contains labels for each column.
599: * @param string|integer $field Indicates which column is used in the function. Enter the
600: * column label enclosed between double quotation marks, such as
601: * "Age" or "Yield," or a number (without quotation marks) that
602: * represents the position of the column within the list: 1 for
603: * the first column, 2 for the second column, and so on.
604: * @param mixed[] $criteria The range of cells that contains the conditions you specify.
605: * You can use any range for the criteria argument, as long as it
606: * includes at least one column label and at least one cell below
607: * the column label in which you specify a condition for the
608: * column.
609: * @return float
610: *
611: */
612: public static function DSUM($database,$field,$criteria) {
613: $field = self::__fieldExtract($database,$field);
614: if (is_null($field)) {
615: return NULL;
616: }
617:
618: // reduce the database to a set of rows that match all the criteria
619: $database = self::__filter($database,$criteria);
620: // extract an array of values for the requested column
621: $colData = array();
622: foreach($database as $row) {
623: $colData[] = $row[$field];
624: }
625:
626: // Return
627: return PHPExcel_Calculation_MathTrig::SUM($colData);
628: } // function DSUM()
629:
630:
631: /**
632: * DVAR
633: *
634: * Estimates the variance of a population based on a sample by using the numbers in a column
635: * of a list or database that match conditions that you specify.
636: *
637: * Excel Function:
638: * DVAR(database,field,criteria)
639: *
640: * @access public
641: * @category Database Functions
642: * @param mixed[] $database The range of cells that makes up the list or database.
643: * A database is a list of related data in which rows of related
644: * information are records, and columns of data are fields. The
645: * first row of the list contains labels for each column.
646: * @param string|integer $field Indicates which column is used in the function. Enter the
647: * column label enclosed between double quotation marks, such as
648: * "Age" or "Yield," or a number (without quotation marks) that
649: * represents the position of the column within the list: 1 for
650: * the first column, 2 for the second column, and so on.
651: * @param mixed[] $criteria The range of cells that contains the conditions you specify.
652: * You can use any range for the criteria argument, as long as it
653: * includes at least one column label and at least one cell below
654: * the column label in which you specify a condition for the
655: * column.
656: * @return float
657: *
658: */
659: public static function DVAR($database,$field,$criteria) {
660: $field = self::__fieldExtract($database,$field);
661: if (is_null($field)) {
662: return NULL;
663: }
664:
665: // reduce the database to a set of rows that match all the criteria
666: $database = self::__filter($database,$criteria);
667: // extract an array of values for the requested column
668: $colData = array();
669: foreach($database as $row) {
670: $colData[] = $row[$field];
671: }
672:
673: // Return
674: return PHPExcel_Calculation_Statistical::VARFunc($colData);
675: } // function DVAR()
676:
677:
678: /**
679: * DVARP
680: *
681: * Calculates the variance of a population based on the entire population by using the numbers
682: * in a column of a list or database that match conditions that you specify.
683: *
684: * Excel Function:
685: * DVARP(database,field,criteria)
686: *
687: * @access public
688: * @category Database Functions
689: * @param mixed[] $database The range of cells that makes up the list or database.
690: * A database is a list of related data in which rows of related
691: * information are records, and columns of data are fields. The
692: * first row of the list contains labels for each column.
693: * @param string|integer $field Indicates which column is used in the function. Enter the
694: * column label enclosed between double quotation marks, such as
695: * "Age" or "Yield," or a number (without quotation marks) that
696: * represents the position of the column within the list: 1 for
697: * the first column, 2 for the second column, and so on.
698: * @param mixed[] $criteria The range of cells that contains the conditions you specify.
699: * You can use any range for the criteria argument, as long as it
700: * includes at least one column label and at least one cell below
701: * the column label in which you specify a condition for the
702: * column.
703: * @return float
704: *
705: */
706: public static function DVARP($database,$field,$criteria) {
707: $field = self::__fieldExtract($database,$field);
708: if (is_null($field)) {
709: return NULL;
710: }
711:
712: // reduce the database to a set of rows that match all the criteria
713: $database = self::__filter($database,$criteria);
714: // extract an array of values for the requested column
715: $colData = array();
716: foreach($database as $row) {
717: $colData[] = $row[$field];
718: }
719:
720: // Return
721: return PHPExcel_Calculation_Statistical::VARP($colData);
722: } // function DVARP()
723:
724:
725: } // class PHPExcel_Calculation_Database
726: