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