vendor/phpoffice/phpexcel/Classes/PHPExcel/Worksheet/AutoFilter.php

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