| [ Index ] |
PHP Cross Reference of Unnamed Project |
[Summary view] [Print] [Text view]
1 <?php 2 3 /** 4 * PHPExcel_Worksheet_AutoFilter 5 * 6 * Copyright (c) 2006 - 2015 PHPExcel 7 * 8 * This library is free software; you can redistribute it and/or 9 * modify it under the terms of the GNU Lesser General Public 10 * License as published by the Free Software Foundation; either 11 * version 2.1 of the License, or (at your option) any later version. 12 * 13 * This library is distributed in the hope that it will be useful, 14 * but WITHOUT ANY WARRANTY; without even the implied warranty of 15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 16 * Lesser General Public License for more details. 17 * 18 * You should have received a copy of the GNU Lesser General Public 19 * License along with this library; if not, write to the Free Software 20 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA 21 * 22 * @category PHPExcel 23 * @package PHPExcel_Worksheet 24 * @copyright Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel) 25 * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL 26 * @version ##VERSION##, ##DATE## 27 */ 28 class PHPExcel_Worksheet_AutoFilter 29 { 30 /** 31 * Autofilter Worksheet 32 * 33 * @var PHPExcel_Worksheet 34 */ 35 private $workSheet; 36 37 38 /** 39 * Autofilter Range 40 * 41 * @var string 42 */ 43 private $range = ''; 44 45 46 /** 47 * Autofilter Column Ruleset 48 * 49 * @var array of PHPExcel_Worksheet_AutoFilter_Column 50 */ 51 private $columns = array(); 52 53 54 /** 55 * Create a new PHPExcel_Worksheet_AutoFilter 56 * 57 * @param string $pRange Cell range (i.e. A1:E10) 58 * @param PHPExcel_Worksheet $pSheet 59 */ 60 public function __construct($pRange = '', PHPExcel_Worksheet $pSheet = null) 61 { 62 $this->range = $pRange; 63 $this->workSheet = $pSheet; 64 } 65 66 /** 67 * Get AutoFilter Parent Worksheet 68 * 69 * @return PHPExcel_Worksheet 70 */ 71 public function getParent() 72 { 73 return $this->workSheet; 74 } 75 76 /** 77 * Set AutoFilter Parent Worksheet 78 * 79 * @param PHPExcel_Worksheet $pSheet 80 * @return PHPExcel_Worksheet_AutoFilter 81 */ 82 public function setParent(PHPExcel_Worksheet $pSheet = null) 83 { 84 $this->workSheet = $pSheet; 85 86 return $this; 87 } 88 89 /** 90 * Get AutoFilter Range 91 * 92 * @return string 93 */ 94 public function getRange() 95 { 96 return $this->range; 97 } 98 99 /** 100 * Set AutoFilter Range 101 * 102 * @param string $pRange Cell range (i.e. A1:E10) 103 * @throws PHPExcel_Exception 104 * @return PHPExcel_Worksheet_AutoFilter 105 */ 106 public function setRange($pRange = '') 107 { 108 // Uppercase coordinate 109 $cellAddress = explode('!', strtoupper($pRange)); 110 if (count($cellAddress) > 1) { 111 list($worksheet, $pRange) = $cellAddress; 112 } 113 114 if (strpos($pRange, ':') !== false) { 115 $this->range = $pRange; 116 } elseif (empty($pRange)) { 117 $this->range = ''; 118 } else { 119 throw new PHPExcel_Exception('Autofilter must be set on a range of cells.'); 120 } 121 122 if (empty($pRange)) { 123 // Discard all column rules 124 $this->columns = array(); 125 } else { 126 // Discard any column rules that are no longer valid within this range 127 list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($this->range); 128 foreach ($this->columns as $key => $value) { 129 $colIndex = PHPExcel_Cell::columnIndexFromString($key); 130 if (($rangeStart[0] > $colIndex) || ($rangeEnd[0] < $colIndex)) { 131 unset($this->columns[$key]); 132 } 133 } 134 } 135 136 return $this; 137 } 138 139 /** 140 * Get all AutoFilter Columns 141 * 142 * @throws PHPExcel_Exception 143 * @return array of PHPExcel_Worksheet_AutoFilter_Column 144 */ 145 public function getColumns() 146 { 147 return $this->columns; 148 } 149 150 /** 151 * Validate that the specified column is in the AutoFilter range 152 * 153 * @param string $column Column name (e.g. A) 154 * @throws PHPExcel_Exception 155 * @return integer The column offset within the autofilter range 156 */ 157 public function testColumnInRange($column) 158 { 159 if (empty($this->range)) { 160 throw new PHPExcel_Exception("No autofilter range is defined."); 161 } 162 163 $columnIndex = PHPExcel_Cell::columnIndexFromString($column); 164 list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($this->range); 165 if (($rangeStart[0] > $columnIndex) || ($rangeEnd[0] < $columnIndex)) { 166 throw new PHPExcel_Exception("Column is outside of current autofilter range."); 167 } 168 169 return $columnIndex - $rangeStart[0]; 170 } 171 172 /** 173 * Get a specified AutoFilter Column Offset within the defined AutoFilter range 174 * 175 * @param string $pColumn Column name (e.g. A) 176 * @throws PHPExcel_Exception 177 * @return integer The offset of the specified column within the autofilter range 178 */ 179 public function getColumnOffset($pColumn) 180 { 181 return $this->testColumnInRange($pColumn); 182 } 183 184 /** 185 * Get a specified AutoFilter Column 186 * 187 * @param string $pColumn Column name (e.g. A) 188 * @throws PHPExcel_Exception 189 * @return PHPExcel_Worksheet_AutoFilter_Column 190 */ 191 public function getColumn($pColumn) 192 { 193 $this->testColumnInRange($pColumn); 194 195 if (!isset($this->columns[$pColumn])) { 196 $this->columns[$pColumn] = new PHPExcel_Worksheet_AutoFilter_Column($pColumn, $this); 197 } 198 199 return $this->columns[$pColumn]; 200 } 201 202 /** 203 * Get a specified AutoFilter Column by it's offset 204 * 205 * @param integer $pColumnOffset Column offset within range (starting from 0) 206 * @throws PHPExcel_Exception 207 * @return PHPExcel_Worksheet_AutoFilter_Column 208 */ 209 public function getColumnByOffset($pColumnOffset = 0) 210 { 211 list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($this->range); 212 $pColumn = PHPExcel_Cell::stringFromColumnIndex($rangeStart[0] + $pColumnOffset - 1); 213 214 return $this->getColumn($pColumn); 215 } 216 217 /** 218 * Set AutoFilter 219 * 220 * @param PHPExcel_Worksheet_AutoFilter_Column|string $pColumn 221 * A simple string containing a Column ID like 'A' is permitted 222 * @throws PHPExcel_Exception 223 * @return PHPExcel_Worksheet_AutoFilter 224 */ 225 public function setColumn($pColumn) 226 { 227 if ((is_string($pColumn)) && (!empty($pColumn))) { 228 $column = $pColumn; 229 } elseif (is_object($pColumn) && ($pColumn instanceof PHPExcel_Worksheet_AutoFilter_Column)) { 230 $column = $pColumn->getColumnIndex(); 231 } else { 232 throw new PHPExcel_Exception("Column is not within the autofilter range."); 233 } 234 $this->testColumnInRange($column); 235 236 if (is_string($pColumn)) { 237 $this->columns[$pColumn] = new PHPExcel_Worksheet_AutoFilter_Column($pColumn, $this); 238 } elseif (is_object($pColumn) && ($pColumn instanceof PHPExcel_Worksheet_AutoFilter_Column)) { 239 $pColumn->setParent($this); 240 $this->columns[$column] = $pColumn; 241 } 242 ksort($this->columns); 243 244 return $this; 245 } 246 247 /** 248 * Clear a specified AutoFilter Column 249 * 250 * @param string $pColumn Column name (e.g. A) 251 * @throws PHPExcel_Exception 252 * @return PHPExcel_Worksheet_AutoFilter 253 */ 254 public function clearColumn($pColumn) 255 { 256 $this->testColumnInRange($pColumn); 257 258 if (isset($this->columns[$pColumn])) { 259 unset($this->columns[$pColumn]); 260 } 261 262 return $this; 263 } 264 265 /** 266 * Shift an AutoFilter Column Rule to a different column 267 * 268 * Note: This method bypasses validation of the destination column to ensure it is within this AutoFilter range. 269 * Nor does it verify whether any column rule already exists at $toColumn, but will simply overrideany existing value. 270 * Use with caution. 271 * 272 * @param string $fromColumn Column name (e.g. A) 273 * @param string $toColumn Column name (e.g. B) 274 * @return PHPExcel_Worksheet_AutoFilter 275 */ 276 public function shiftColumn($fromColumn = null, $toColumn = null) 277 { 278 $fromColumn = strtoupper($fromColumn); 279 $toColumn = strtoupper($toColumn); 280 281 if (($fromColumn !== null) && (isset($this->columns[$fromColumn])) && ($toColumn !== null)) { 282 $this->columns[$fromColumn]->setParent(); 283 $this->columns[$fromColumn]->setColumnIndex($toColumn); 284 $this->columns[$toColumn] = $this->columns[$fromColumn]; 285 $this->columns[$toColumn]->setParent($this); 286 unset($this->columns[$fromColumn]); 287 288 ksort($this->columns); 289 } 290 291 return $this; 292 } 293 294 295 /** 296 * Test if cell value is in the defined set of values 297 * 298 * @param mixed $cellValue 299 * @param mixed[] $dataSet 300 * @return boolean 301 */ 302 private static function filterTestInSimpleDataSet($cellValue, $dataSet) 303 { 304 $dataSetValues = $dataSet['filterValues']; 305 $blanks = $dataSet['blanks']; 306 if (($cellValue == '') || ($cellValue === null)) { 307 return $blanks; 308 } 309 return in_array($cellValue, $dataSetValues); 310 } 311 312 /** 313 * Test if cell value is in the defined set of Excel date values 314 * 315 * @param mixed $cellValue 316 * @param mixed[] $dataSet 317 * @return boolean 318 */ 319 private static function filterTestInDateGroupSet($cellValue, $dataSet) 320 { 321 $dateSet = $dataSet['filterValues']; 322 $blanks = $dataSet['blanks']; 323 if (($cellValue == '') || ($cellValue === null)) { 324 return $blanks; 325 } 326 327 if (is_numeric($cellValue)) { 328 $dateValue = PHPExcel_Shared_Date::ExcelToPHP($cellValue); 329 if ($cellValue < 1) { 330 // Just the time part 331 $dtVal = date('His', $dateValue); 332 $dateSet = $dateSet['time']; 333 } elseif ($cellValue == floor($cellValue)) { 334 // Just the date part 335 $dtVal = date('Ymd', $dateValue); 336 $dateSet = $dateSet['date']; 337 } else { 338 // date and time parts 339 $dtVal = date('YmdHis', $dateValue); 340 $dateSet = $dateSet['dateTime']; 341 } 342 foreach ($dateSet as $dateValue) { 343 // Use of substr to extract value at the appropriate group level 344 if (substr($dtVal, 0, strlen($dateValue)) == $dateValue) { 345 return true; 346 } 347 } 348 } 349 return false; 350 } 351 352 /** 353 * Test if cell value is within a set of values defined by a ruleset 354 * 355 * @param mixed $cellValue 356 * @param mixed[] $ruleSet 357 * @return boolean 358 */ 359 private static function filterTestInCustomDataSet($cellValue, $ruleSet) 360 { 361 $dataSet = $ruleSet['filterRules']; 362 $join = $ruleSet['join']; 363 $customRuleForBlanks = isset($ruleSet['customRuleForBlanks']) ? $ruleSet['customRuleForBlanks'] : false; 364 365 if (!$customRuleForBlanks) { 366 // Blank cells are always ignored, so return a FALSE 367 if (($cellValue == '') || ($cellValue === null)) { 368 return false; 369 } 370 } 371 $returnVal = ($join == PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_AND); 372 foreach ($dataSet as $rule) { 373 if (is_numeric($rule['value'])) { 374 // Numeric values are tested using the appropriate operator 375 switch ($rule['operator']) { 376 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_EQUAL: 377 $retVal = ($cellValue == $rule['value']); 378 break; 379 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_NOTEQUAL: 380 $retVal = ($cellValue != $rule['value']); 381 break; 382 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN: 383 $retVal = ($cellValue > $rule['value']); 384 break; 385 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL: 386 $retVal = ($cellValue >= $rule['value']); 387 break; 388 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN: 389 $retVal = ($cellValue < $rule['value']); 390 break; 391 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL: 392 $retVal = ($cellValue <= $rule['value']); 393 break; 394 } 395 } elseif ($rule['value'] == '') { 396 switch ($rule['operator']) { 397 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_EQUAL: 398 $retVal = (($cellValue == '') || ($cellValue === null)); 399 break; 400 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_NOTEQUAL: 401 $retVal = (($cellValue != '') && ($cellValue !== null)); 402 break; 403 default: 404 $retVal = true; 405 break; 406 } 407 } else { 408 // String values are always tested for equality, factoring in for wildcards (hence a regexp test) 409 $retVal = preg_match('/^'.$rule['value'].'$/i', $cellValue); 410 } 411 // If there are multiple conditions, then we need to test both using the appropriate join operator 412 switch ($join) { 413 case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_OR: 414 $returnVal = $returnVal || $retVal; 415 // Break as soon as we have a TRUE match for OR joins, 416 // to avoid unnecessary additional code execution 417 if ($returnVal) { 418 return $returnVal; 419 } 420 break; 421 case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_AND: 422 $returnVal = $returnVal && $retVal; 423 break; 424 } 425 } 426 427 return $returnVal; 428 } 429 430 /** 431 * Test if cell date value is matches a set of values defined by a set of months 432 * 433 * @param mixed $cellValue 434 * @param mixed[] $monthSet 435 * @return boolean 436 */ 437 private static function filterTestInPeriodDateSet($cellValue, $monthSet) 438 { 439 // Blank cells are always ignored, so return a FALSE 440 if (($cellValue == '') || ($cellValue === null)) { 441 return false; 442 } 443 444 if (is_numeric($cellValue)) { 445 $dateValue = date('m', PHPExcel_Shared_Date::ExcelToPHP($cellValue)); 446 if (in_array($dateValue, $monthSet)) { 447 return true; 448 } 449 } 450 451 return false; 452 } 453 454 /** 455 * Search/Replace arrays to convert Excel wildcard syntax to a regexp syntax for preg_matching 456 * 457 * @var array 458 */ 459 private static $fromReplace = array('\*', '\?', '~~', '~.*', '~.?'); 460 private static $toReplace = array('.*', '.', '~', '\*', '\?'); 461 462 463 /** 464 * Convert a dynamic rule daterange to a custom filter range expression for ease of calculation 465 * 466 * @param string $dynamicRuleType 467 * @param PHPExcel_Worksheet_AutoFilter_Column &$filterColumn 468 * @return mixed[] 469 */ 470 private function dynamicFilterDateRange($dynamicRuleType, &$filterColumn) 471 { 472 $rDateType = PHPExcel_Calculation_Functions::getReturnDateType(); 473 PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC); 474 $val = $maxVal = null; 475 476 $ruleValues = array(); 477 $baseDate = PHPExcel_Calculation_DateTime::DATENOW(); 478 // Calculate start/end dates for the required date range based on current date 479 switch ($dynamicRuleType) { 480 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK: 481 $baseDate = strtotime('-7 days', $baseDate); 482 break; 483 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK: 484 $baseDate = strtotime('-7 days', $baseDate); 485 break; 486 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH: 487 $baseDate = strtotime('-1 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); 488 break; 489 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH: 490 $baseDate = strtotime('+1 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); 491 break; 492 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER: 493 $baseDate = strtotime('-3 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); 494 break; 495 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER: 496 $baseDate = strtotime('+3 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); 497 break; 498 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR: 499 $baseDate = strtotime('-1 year', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); 500 break; 501 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR: 502 $baseDate = strtotime('+1 year', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); 503 break; 504 } 505 506 switch ($dynamicRuleType) { 507 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_TODAY: 508 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY: 509 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW: 510 $maxVal = (int) PHPExcel_Shared_Date::PHPtoExcel(strtotime('+1 day', $baseDate)); 511 $val = (int) PHPExcel_Shared_Date::PHPToExcel($baseDate); 512 break; 513 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_YEARTODATE: 514 $maxVal = (int) PHPExcel_Shared_Date::PHPtoExcel(strtotime('+1 day', $baseDate)); 515 $val = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1, date('Y', $baseDate))); 516 break; 517 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISYEAR: 518 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR: 519 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR: 520 $maxVal = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, 31, 12, date('Y', $baseDate))); 521 ++$maxVal; 522 $val = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1, date('Y', $baseDate))); 523 break; 524 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISQUARTER: 525 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER: 526 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER: 527 $thisMonth = date('m', $baseDate); 528 $thisQuarter = floor(--$thisMonth / 3); 529 $maxVal = (int) PHPExcel_Shared_Date::PHPtoExcel(gmmktime(0, 0, 0, date('t', $baseDate), (1+$thisQuarter)*3, date('Y', $baseDate))); 530 ++$maxVal; 531 $val = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1+$thisQuarter*3, date('Y', $baseDate))); 532 break; 533 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISMONTH: 534 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH: 535 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH: 536 $maxVal = (int) PHPExcel_Shared_Date::PHPtoExcel(gmmktime(0, 0, 0, date('t', $baseDate), date('m', $baseDate), date('Y', $baseDate))); 537 ++$maxVal; 538 $val = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate))); 539 break; 540 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISWEEK: 541 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK: 542 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK: 543 $dayOfWeek = date('w', $baseDate); 544 $val = (int) PHPExcel_Shared_Date::PHPToExcel($baseDate) - $dayOfWeek; 545 $maxVal = $val + 7; 546 break; 547 } 548 549 switch ($dynamicRuleType) { 550 // Adjust Today dates for Yesterday and Tomorrow 551 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY: 552 --$maxVal; 553 --$val; 554 break; 555 case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW: 556 ++$maxVal; 557 ++$val; 558 break; 559 } 560 561 // Set the filter column rule attributes ready for writing 562 $filterColumn->setAttributes(array('val' => $val, 'maxVal' => $maxVal)); 563 564 // Set the rules for identifying rows for hide/show 565 $ruleValues[] = array('operator' => PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL, 'value' => $val); 566 $ruleValues[] = array('operator' => PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN, 'value' => $maxVal); 567 PHPExcel_Calculation_Functions::setReturnDateType($rDateType); 568 569 return array('method' => 'filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_AND)); 570 } 571 572 private function calculateTopTenValue($columnID, $startRow, $endRow, $ruleType, $ruleValue) 573 { 574 $range = $columnID.$startRow.':'.$columnID.$endRow; 575 $dataValues = PHPExcel_Calculation_Functions::flattenArray($this->workSheet->rangeToArray($range, null, true, false)); 576 577 $dataValues = array_filter($dataValues); 578 if ($ruleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP) { 579 rsort($dataValues); 580 } else { 581 sort($dataValues); 582 } 583 584 return array_pop(array_slice($dataValues, 0, $ruleValue)); 585 } 586 587 /** 588 * Apply the AutoFilter rules to the AutoFilter Range 589 * 590 * @throws PHPExcel_Exception 591 * @return PHPExcel_Worksheet_AutoFilter 592 */ 593 public function showHideRows() 594 { 595 list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($this->range); 596 597 // The heading row should always be visible 598 // echo 'AutoFilter Heading Row ', $rangeStart[1],' is always SHOWN',PHP_EOL; 599 $this->workSheet->getRowDimension($rangeStart[1])->setVisible(true); 600 601 $columnFilterTests = array(); 602 foreach ($this->columns as $columnID => $filterColumn) { 603 $rules = $filterColumn->getRules(); 604 switch ($filterColumn->getFilterType()) { 605 case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_FILTER: 606 $ruleValues = array(); 607 // Build a list of the filter value selections 608 foreach ($rules as $rule) { 609 $ruleType = $rule->getRuleType(); 610 $ruleValues[] = $rule->getValue(); 611 } 612 // Test if we want to include blanks in our filter criteria 613 $blanks = false; 614 $ruleDataSet = array_filter($ruleValues); 615 if (count($ruleValues) != count($ruleDataSet)) { 616 $blanks = true; 617 } 618 if ($ruleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_FILTER) { 619 // Filter on absolute values 620 $columnFilterTests[$columnID] = array( 621 'method' => 'filterTestInSimpleDataSet', 622 'arguments' => array('filterValues' => $ruleDataSet, 'blanks' => $blanks) 623 ); 624 } else { 625 // Filter on date group values 626 $arguments = array( 627 'date' => array(), 628 'time' => array(), 629 'dateTime' => array(), 630 ); 631 foreach ($ruleDataSet as $ruleValue) { 632 $date = $time = ''; 633 if ((isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR])) && 634 ($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR] !== '')) { 635 $date .= sprintf('%04d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]); 636 } 637 if ((isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH])) && 638 ($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH] != '')) { 639 $date .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]); 640 } 641 if ((isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY])) && 642 ($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY] !== '')) { 643 $date .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]); 644 } 645 if ((isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR])) && 646 ($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR] !== '')) { 647 $time .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]); 648 } 649 if ((isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE])) && 650 ($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE] !== '')) { 651 $time .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]); 652 } 653 if ((isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND])) && 654 ($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND] !== '')) { 655 $time .= sprintf('%02d', $ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]); 656 } 657 $dateTime = $date . $time; 658 $arguments['date'][] = $date; 659 $arguments['time'][] = $time; 660 $arguments['dateTime'][] = $dateTime; 661 } 662 // Remove empty elements 663 $arguments['date'] = array_filter($arguments['date']); 664 $arguments['time'] = array_filter($arguments['time']); 665 $arguments['dateTime'] = array_filter($arguments['dateTime']); 666 $columnFilterTests[$columnID] = array( 667 'method' => 'filterTestInDateGroupSet', 668 'arguments' => array('filterValues' => $arguments, 'blanks' => $blanks) 669 ); 670 } 671 break; 672 case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER: 673 $customRuleForBlanks = false; 674 $ruleValues = array(); 675 // Build a list of the filter value selections 676 foreach ($rules as $rule) { 677 $ruleType = $rule->getRuleType(); 678 $ruleValue = $rule->getValue(); 679 if (!is_numeric($ruleValue)) { 680 // Convert to a regexp allowing for regexp reserved characters, wildcards and escaped wildcards 681 $ruleValue = preg_quote($ruleValue); 682 $ruleValue = str_replace(self::$fromReplace, self::$toReplace, $ruleValue); 683 if (trim($ruleValue) == '') { 684 $customRuleForBlanks = true; 685 $ruleValue = trim($ruleValue); 686 } 687 } 688 $ruleValues[] = array('operator' => $rule->getOperator(), 'value' => $ruleValue); 689 } 690 $join = $filterColumn->getJoin(); 691 $columnFilterTests[$columnID] = array( 692 'method' => 'filterTestInCustomDataSet', 693 'arguments' => array('filterRules' => $ruleValues, 'join' => $join, 'customRuleForBlanks' => $customRuleForBlanks) 694 ); 695 break; 696 case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER: 697 $ruleValues = array(); 698 foreach ($rules as $rule) { 699 // We should only ever have one Dynamic Filter Rule anyway 700 $dynamicRuleType = $rule->getGrouping(); 701 if (($dynamicRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE) || 702 ($dynamicRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_BELOWAVERAGE)) { 703 // Number (Average) based 704 // Calculate the average 705 $averageFormula = '=AVERAGE('.$columnID.($rangeStart[1]+1).':'.$columnID.$rangeEnd[1].')'; 706 $average = PHPExcel_Calculation::getInstance()->calculateFormula($averageFormula, null, $this->workSheet->getCell('A1')); 707 // Set above/below rule based on greaterThan or LessTan 708 $operator = ($dynamicRuleType === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE) 709 ? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN 710 : PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN; 711 $ruleValues[] = array('operator' => $operator, 712 'value' => $average 713 ); 714 $columnFilterTests[$columnID] = array( 715 'method' => 'filterTestInCustomDataSet', 716 'arguments' => array('filterRules' => $ruleValues, 'join' => PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_OR) 717 ); 718 } else { 719 // Date based 720 if ($dynamicRuleType{0} == 'M' || $dynamicRuleType{0} == 'Q') { 721 // Month or Quarter 722 sscanf($dynamicRuleType, '%[A-Z]%d', $periodType, $period); 723 if ($periodType == 'M') { 724 $ruleValues = array($period); 725 } else { 726 --$period; 727 $periodEnd = (1+$period)*3; 728 $periodStart = 1+$period*3; 729 $ruleValues = range($periodStart, $periodEnd); 730 } 731 $columnFilterTests[$columnID] = array( 732 'method' => 'filterTestInPeriodDateSet', 733 'arguments' => $ruleValues 734 ); 735 $filterColumn->setAttributes(array()); 736 } else { 737 // Date Range 738 $columnFilterTests[$columnID] = $this->dynamicFilterDateRange($dynamicRuleType, $filterColumn); 739 break; 740 } 741 } 742 } 743 break; 744 case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER: 745 $ruleValues = array(); 746 $dataRowCount = $rangeEnd[1] - $rangeStart[1]; 747 foreach ($rules as $rule) { 748 // We should only ever have one Dynamic Filter Rule anyway 749 $toptenRuleType = $rule->getGrouping(); 750 $ruleValue = $rule->getValue(); 751 $ruleOperator = $rule->getOperator(); 752 } 753 if ($ruleOperator === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT) { 754 $ruleValue = floor($ruleValue * ($dataRowCount / 100)); 755 } 756 if ($ruleValue < 1) { 757 $ruleValue = 1; 758 } 759 if ($ruleValue > 500) { 760 $ruleValue = 500; 761 } 762 763 $maxVal = $this->calculateTopTenValue($columnID, $rangeStart[1]+1, $rangeEnd[1], $toptenRuleType, $ruleValue); 764 765 $operator = ($toptenRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP) 766 ? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL 767 : PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL; 768 $ruleValues[] = array('operator' => $operator, 'value' => $maxVal); 769 $columnFilterTests[$columnID] = array( 770 'method' => 'filterTestInCustomDataSet', 771 'arguments' => array('filterRules' => $ruleValues, 'join' => PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_OR) 772 ); 773 $filterColumn->setAttributes(array('maxVal' => $maxVal)); 774 break; 775 } 776 } 777 778 // echo 'Column Filter Test CRITERIA',PHP_EOL; 779 // var_dump($columnFilterTests); 780 // 781 // Execute the column tests for each row in the autoFilter range to determine show/hide, 782 for ($row = $rangeStart[1]+1; $row <= $rangeEnd[1]; ++$row) { 783 // echo 'Testing Row = ', $row,PHP_EOL; 784 $result = true; 785 foreach ($columnFilterTests as $columnID => $columnFilterTest) { 786 // echo 'Testing cell ', $columnID.$row,PHP_EOL; 787 $cellValue = $this->workSheet->getCell($columnID.$row)->getCalculatedValue(); 788 // echo 'Value is ', $cellValue,PHP_EOL; 789 // Execute the filter test 790 $result = $result && 791 call_user_func_array( 792 array('PHPExcel_Worksheet_AutoFilter', $columnFilterTest['method']), 793 array($cellValue, $columnFilterTest['arguments']) 794 ); 795 // echo (($result) ? 'VALID' : 'INVALID'),PHP_EOL; 796 // If filter test has resulted in FALSE, exit the loop straightaway rather than running any more tests 797 if (!$result) { 798 break; 799 } 800 } 801 // Set show/hide for the row based on the result of the autoFilter result 802 // echo (($result) ? 'SHOW' : 'HIDE'),PHP_EOL; 803 $this->workSheet->getRowDimension($row)->setVisible($result); 804 } 805 806 return $this; 807 } 808 809 810 /** 811 * Implement PHP __clone to create a deep clone, not just a shallow copy. 812 */ 813 public function __clone() 814 { 815 $vars = get_object_vars($this); 816 foreach ($vars as $key => $value) { 817 if (is_object($value)) { 818 if ($key == 'workSheet') { 819 // Detach from worksheet 820 $this->{$key} = null; 821 } else { 822 $this->{$key} = clone $value; 823 } 824 } elseif ((is_array($value)) && ($key == 'columns')) { 825 // The columns array of PHPExcel_Worksheet_AutoFilter objects 826 $this->{$key} = array(); 827 foreach ($value as $k => $v) { 828 $this->{$key}[$k] = clone $v; 829 // attach the new cloned Column to this new cloned Autofilter object 830 $this->{$key}[$k]->setParent($this); 831 } 832 } else { 833 $this->{$key} = $value; 834 } 835 } 836 } 837 838 /** 839 * toString method replicates previous behavior by returning the range if object is 840 * referenced as a property of its parent. 841 */ 842 public function __toString() 843 { 844 return (string) $this->range; 845 } 846 }
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 |