1: <?php
2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26:
27:
28:
29: 30: 31: 32: 33: 34: 35:
36: class PHPExcel_Worksheet_AutoFilter
37: {
38: 39: 40: 41: 42:
43: private $_workSheet = NULL;
44:
45:
46: 47: 48: 49: 50:
51: private $_range = '';
52:
53:
54: 55: 56: 57: 58:
59: private $_columns = array();
60:
61:
62: 63: 64: 65: 66: 67:
68: public function __construct($pRange = '', PHPExcel_Worksheet $pSheet = NULL)
69: {
70: $this->_range = $pRange;
71: $this->_workSheet = $pSheet;
72: }
73:
74: 75: 76: 77: 78:
79: public function getParent() {
80: return $this->_workSheet;
81: }
82:
83: 84: 85: 86: 87: 88:
89: public function setParent(PHPExcel_Worksheet $pSheet = NULL) {
90: $this->_workSheet = $pSheet;
91:
92: return $this;
93: }
94:
95: 96: 97: 98: 99:
100: public function getRange() {
101: return $this->_range;
102: }
103:
104: 105: 106: 107: 108: 109: 110:
111: public function setRange($pRange = '') {
112:
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:
128: $this->_columns = array();
129: } else {
130:
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: 145: 146: 147: 148:
149: public function getColumns() {
150: return $this->_columns;
151: }
152:
153: 154: 155: 156: 157: 158: 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: 176: 177: 178: 179: 180:
181: public function getColumnOffset($pColumn) {
182: return $this->testColumnInRange($pColumn);
183: }
184:
185: 186: 187: 188: 189: 190: 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: 204: 205: 206: 207: 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: 218: 219: 220: 221: 222: 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: 248: 249: 250: 251: 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: 265: 266: 267: 268: 269: 270: 271: 272: 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: 294: 295: 296: 297: 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: 311: 312: 313: 314: 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:
328: $dtVal = date('His',$dateValue);
329: $dateSet = $dateSet['time'];
330: } elseif($cellValue == floor($cellValue)) {
331:
332: $dtVal = date('Ymd',$dateValue);
333: $dateSet = $dateSet['date'];
334: } else {
335:
336: $dtVal = date('YmdHis',$dateValue);
337: $dateSet = $dateSet['dateTime'];
338: }
339: foreach($dateSet as $dateValue) {
340:
341: if (substr($dtVal,0,strlen($dateValue)) == $dateValue)
342: return TRUE;
343: }
344: }
345:
346: return FALSE;
347: }
348:
349: 350: 351: 352: 353: 354: 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:
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:
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:
406: $retVal = preg_match('/^'.$rule['value'].'$/i',$cellValue);
407: }
408:
409: switch ($join) {
410: case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_OR :
411: $returnVal = $returnVal || $retVal;
412:
413:
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: 428: 429: 430: 431: 432:
433: private static function _filterTestInPeriodDateSet($cellValue, $monthSet)
434: {
435:
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: 452: 453: 454:
455: private static $_fromReplace = array('\*', '\?', '~~', '~.*', '~.?');
456: private static $_toReplace = array('.*', '.', '~', '\*', '\?');
457:
458:
459: 460: 461: 462: 463: 464: 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:
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:
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:
558: $filterColumn->setAttributes(array( 'val' => $val,
559: 'maxVal' => $maxVal
560: )
561: );
562:
563:
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: 598: 599: 600: 601:
602: public function showHideRows()
603: {
604: list($rangeStart,$rangeEnd) = PHPExcel_Cell::rangeBoundaries($this->_range);
605:
606:
607:
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:
617: foreach($rules as $rule) {
618: $ruleType = $rule->getRuleType();
619: $ruleValues[] = $rule->getValue();
620: }
621:
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:
628: $columnFilterTests[$columnID] = array(
629: 'method' => '_filterTestInSimpleDataSet',
630: 'arguments' => array( 'filterValues' => $ruleDataSet,
631: 'blanks' => $blanks
632: )
633: );
634: } else {
635:
636: $arguments = array();
637: foreach($ruleDataSet as $ruleValue) {
638: $date = $time = '';
639: if ((isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR])) &&
640: ($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR] !== ''))
641: $date .= sprintf('%04d',$ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]);
642: if ((isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH])) &&
643: ($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH] != ''))
644: $date .= sprintf('%02d',$ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]);
645: if ((isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY])) &&
646: ($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY] !== ''))
647: $date .= sprintf('%02d',$ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]);
648: if ((isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR])) &&
649: ($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR] !== ''))
650: $time .= sprintf('%02d',$ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]);
651: if ((isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE])) &&
652: ($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE] !== ''))
653: $time .= sprintf('%02d',$ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]);
654: if ((isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND])) &&
655: ($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND] !== ''))
656: $time .= sprintf('%02d',$ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]);
657: $dateTime = $date . $time;
658: $arguments['date'][] = $date;
659: $arguments['time'][] = $time;
660: $arguments['dateTime'][] = $dateTime;
661: }
662:
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,
669: 'blanks' => $blanks
670: )
671: );
672: }
673: break;
674: case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER :
675: $customRuleForBlanks = FALSE;
676: $ruleValues = array();
677:
678: foreach($rules as $rule) {
679: $ruleType = $rule->getRuleType();
680: $ruleValue = $rule->getValue();
681: if (!is_numeric($ruleValue)) {
682:
683: $ruleValue = preg_quote($ruleValue);
684: $ruleValue = str_replace(self::$_fromReplace,self::$_toReplace,$ruleValue);
685: if (trim($ruleValue) == '') {
686: $customRuleForBlanks = TRUE;
687: $ruleValue = trim($ruleValue);
688: }
689: }
690: $ruleValues[] = array( 'operator' => $rule->getOperator(),
691: 'value' => $ruleValue
692: );
693: }
694: $join = $filterColumn->getJoin();
695: $columnFilterTests[$columnID] = array(
696: 'method' => '_filterTestInCustomDataSet',
697: 'arguments' => array( 'filterRules' => $ruleValues,
698: 'join' => $join,
699: 'customRuleForBlanks' => $customRuleForBlanks
700: )
701: );
702: break;
703: case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER :
704: $ruleValues = array();
705: foreach($rules as $rule) {
706:
707: $dynamicRuleType = $rule->getGrouping();
708: if (($dynamicRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE) ||
709: ($dynamicRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_BELOWAVERAGE)) {
710:
711:
712: $averageFormula = '=AVERAGE('.$columnID.($rangeStart[1]+1).':'.$columnID.$rangeEnd[1].')';
713: $average = PHPExcel_Calculation::getInstance()->calculateFormula($averageFormula,NULL,$this->_workSheet->getCell('A1'));
714:
715: $operator = ($dynamicRuleType === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE)
716: ? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN
717: : PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN;
718: $ruleValues[] = array( 'operator' => $operator,
719: 'value' => $average
720: );
721: $columnFilterTests[$columnID] = array(
722: 'method' => '_filterTestInCustomDataSet',
723: 'arguments' => array( 'filterRules' => $ruleValues,
724: 'join' => PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_OR
725: )
726: );
727: } else {
728:
729: if ($dynamicRuleType{0} == 'M' || $dynamicRuleType{0} == 'Q') {
730:
731: sscanf($dynamicRuleType,'%[A-Z]%d', $periodType, $period);
732: if ($periodType == 'M') {
733: $ruleValues = array($period);
734: } else {
735: --$period;
736: $periodEnd = (1+$period)*3;
737: $periodStart = 1+$period*3;
738: $ruleValues = range($periodStart,periodEnd);
739: }
740: $columnFilterTests[$columnID] = array(
741: 'method' => '_filterTestInPeriodDateSet',
742: 'arguments' => $ruleValues
743: );
744: $filterColumn->setAttributes(array());
745: } else {
746:
747: $columnFilterTests[$columnID] = $this->_dynamicFilterDateRange($dynamicRuleType, $filterColumn);
748: break;
749: }
750: }
751: }
752: break;
753: case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER :
754: $ruleValues = array();
755: $dataRowCount = $rangeEnd[1] - $rangeStart[1];
756: foreach($rules as $rule) {
757:
758: $toptenRuleType = $rule->getGrouping();
759: $ruleValue = $rule->getValue();
760: $ruleOperator = $rule->getOperator();
761: }
762: if ($ruleOperator === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT) {
763: $ruleValue = floor($ruleValue * ($dataRowCount / 100));
764: }
765: if ($ruleValue < 1) $ruleValue = 1;
766: if ($ruleValue > 500) $ruleValue = 500;
767:
768: $maxVal = $this->_calculateTopTenValue($columnID,$rangeStart[1]+1,$rangeEnd[1],$toptenRuleType,$ruleValue);
769:
770: $operator = ($toptenRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP)
771: ? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL
772: : PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL;
773: $ruleValues[] = array( 'operator' => $operator,
774: 'value' => $maxVal
775: );
776: $columnFilterTests[$columnID] = array(
777: 'method' => '_filterTestInCustomDataSet',
778: 'arguments' => array( 'filterRules' => $ruleValues,
779: 'join' => PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_OR
780: )
781: );
782: $filterColumn->setAttributes(
783: array('maxVal' => $maxVal)
784: );
785: break;
786: }
787: }
788:
789:
790:
791:
792:
793: for ($row = $rangeStart[1]+1; $row <= $rangeEnd[1]; ++$row) {
794:
795: $result = TRUE;
796: foreach($columnFilterTests as $columnID => $columnFilterTest) {
797:
798: $cellValue = $this->_workSheet->getCell($columnID.$row)->getCalculatedValue();
799:
800:
801: $result = $result &&
802: call_user_func_array(
803: array('PHPExcel_Worksheet_AutoFilter',$columnFilterTest['method']),
804: array(
805: $cellValue,
806: $columnFilterTest['arguments']
807: )
808: );
809:
810:
811: if (!$result)
812: break;
813: }
814:
815:
816: $this->_workSheet->getRowDimension($row)->setVisible($result);
817: }
818:
819: return $this;
820: }
821:
822:
823: 824: 825:
826: public function __clone() {
827: $vars = get_object_vars($this);
828: foreach ($vars as $key => $value) {
829: if (is_object($value)) {
830: if ($key == '_workSheet') {
831:
832: $this->{$key} = NULL;
833: } else {
834: $this->{$key} = clone $value;
835: }
836: } elseif ((is_array($value)) && ($key == '_columns')) {
837:
838: $this->{$key} = array();
839: foreach ($value as $k => $v) {
840: $this->{$key}[$k] = clone $v;
841:
842: $this->{$key}[$k]->setParent($this);
843: }
844: } else {
845: $this->{$key} = $value;
846: }
847: }
848: }
849:
850: 851: 852: 853:
854: public function __toString() {
855: return (string) $this->_range;
856: }
857:
858: }
859: