New file |
0,0 → 1,858 |
<?php |
/** |
* PHPExcel |
* |
* Copyright (c) 2006 - 2013 PHPExcel |
* |
* This library is free software; you can redistribute it and/or |
* modify it under the terms of the GNU Lesser General Public |
* License as published by the Free Software Foundation; either |
* version 2.1 of the License, or (at your option) any later version. |
* |
* This library is distributed in the hope that it will be useful, |
* but WITHOUT ANY WARRANTY; without even the implied warranty of |
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU |
* Lesser General Public License for more details. |
* |
* You should have received a copy of the GNU Lesser General Public |
* License along with this library; if not, write to the Free Software |
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA |
* |
* @category PHPExcel |
* @package PHPExcel_Worksheet |
* @copyright Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel) |
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL |
* @version ##VERSION##, ##DATE## |
*/ |
|
|
/** |
* PHPExcel_Worksheet_AutoFilter |
* |
* @category PHPExcel |
* @package PHPExcel_Worksheet |
* @copyright Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel) |
*/ |
class PHPExcel_Worksheet_AutoFilter |
{ |
/** |
* Autofilter Worksheet |
* |
* @var PHPExcel_Worksheet |
*/ |
private $_workSheet = NULL; |
|
|
/** |
* Autofilter Range |
* |
* @var string |
*/ |
private $_range = ''; |
|
|
/** |
* Autofilter Column Ruleset |
* |
* @var array of PHPExcel_Worksheet_AutoFilter_Column |
*/ |
private $_columns = array(); |
|
|
/** |
* Create a new PHPExcel_Worksheet_AutoFilter |
* |
* @param string $pRange Cell range (i.e. A1:E10) |
* @param PHPExcel_Worksheet $pSheet |
*/ |
public function __construct($pRange = '', PHPExcel_Worksheet $pSheet = NULL) |
{ |
$this->_range = $pRange; |
$this->_workSheet = $pSheet; |
} |
|
/** |
* Get AutoFilter Parent Worksheet |
* |
* @return PHPExcel_Worksheet |
*/ |
public function getParent() { |
return $this->_workSheet; |
} |
|
/** |
* Set AutoFilter Parent Worksheet |
* |
* @param PHPExcel_Worksheet $pSheet |
* @return PHPExcel_Worksheet_AutoFilter |
*/ |
public function setParent(PHPExcel_Worksheet $pSheet = NULL) { |
$this->_workSheet = $pSheet; |
|
return $this; |
} |
|
/** |
* Get AutoFilter Range |
* |
* @return string |
*/ |
public function getRange() { |
return $this->_range; |
} |
|
/** |
* Set AutoFilter Range |
* |
* @param string $pRange Cell range (i.e. A1:E10) |
* @throws PHPExcel_Exception |
* @return PHPExcel_Worksheet_AutoFilter |
*/ |
public function setRange($pRange = '') { |
// Uppercase coordinate |
$cellAddress = explode('!',strtoupper($pRange)); |
if (count($cellAddress) > 1) { |
list($worksheet,$pRange) = $cellAddress; |
} |
|
if (strpos($pRange,':') !== FALSE) { |
$this->_range = $pRange; |
} elseif(empty($pRange)) { |
$this->_range = ''; |
} else { |
throw new PHPExcel_Exception('Autofilter must be set on a range of cells.'); |
} |
|
if (empty($pRange)) { |
// Discard all column rules |
$this->_columns = array(); |
} else { |
// Discard any column rules that are no longer valid within this range |
list($rangeStart,$rangeEnd) = PHPExcel_Cell::rangeBoundaries($this->_range); |
foreach($this->_columns as $key => $value) { |
$colIndex = PHPExcel_Cell::columnIndexFromString($key); |
if (($rangeStart[0] > $colIndex) || ($rangeEnd[0] < $colIndex)) { |
unset($this->_columns[$key]); |
} |
} |
} |
|
return $this; |
} |
|
/** |
* Get all AutoFilter Columns |
* |
* @throws PHPExcel_Exception |
* @return array of PHPExcel_Worksheet_AutoFilter_Column |
*/ |
public function getColumns() { |
return $this->_columns; |
} |
|
/** |
* Validate that the specified column is in the AutoFilter range |
* |
* @param string $column Column name (e.g. A) |
* @throws PHPExcel_Exception |
* @return integer The column offset within the autofilter range |
*/ |
public function testColumnInRange($column) { |
if (empty($this->_range)) { |
throw new PHPExcel_Exception("No autofilter range is defined."); |
} |
|
$columnIndex = PHPExcel_Cell::columnIndexFromString($column); |
list($rangeStart,$rangeEnd) = PHPExcel_Cell::rangeBoundaries($this->_range); |
if (($rangeStart[0] > $columnIndex) || ($rangeEnd[0] < $columnIndex)) { |
throw new PHPExcel_Exception("Column is outside of current autofilter range."); |
} |
|
return $columnIndex - $rangeStart[0]; |
} |
|
/** |
* Get a specified AutoFilter Column Offset within the defined AutoFilter range |
* |
* @param string $pColumn Column name (e.g. A) |
* @throws PHPExcel_Exception |
* @return integer The offset of the specified column within the autofilter range |
*/ |
public function getColumnOffset($pColumn) { |
return $this->testColumnInRange($pColumn); |
} |
|
/** |
* Get a specified AutoFilter Column |
* |
* @param string $pColumn Column name (e.g. A) |
* @throws PHPExcel_Exception |
* @return PHPExcel_Worksheet_AutoFilter_Column |
*/ |
public function getColumn($pColumn) { |
$this->testColumnInRange($pColumn); |
|
if (!isset($this->_columns[$pColumn])) { |
$this->_columns[$pColumn] = new PHPExcel_Worksheet_AutoFilter_Column($pColumn, $this); |
} |
|
return $this->_columns[$pColumn]; |
} |
|
/** |
* Get a specified AutoFilter Column by it's offset |
* |
* @param integer $pColumnOffset Column offset within range (starting from 0) |
* @throws PHPExcel_Exception |
* @return PHPExcel_Worksheet_AutoFilter_Column |
*/ |
public function getColumnByOffset($pColumnOffset = 0) { |
list($rangeStart,$rangeEnd) = PHPExcel_Cell::rangeBoundaries($this->_range); |
$pColumn = PHPExcel_Cell::stringFromColumnIndex($rangeStart[0] + $pColumnOffset - 1); |
|
return $this->getColumn($pColumn); |
} |
|
/** |
* Set AutoFilter |
* |
* @param PHPExcel_Worksheet_AutoFilter_Column|string $pColumn |
* A simple string containing a Column ID like 'A' is permitted |
* @throws PHPExcel_Exception |
* @return PHPExcel_Worksheet_AutoFilter |
*/ |
public function setColumn($pColumn) |
{ |
if ((is_string($pColumn)) && (!empty($pColumn))) { |
$column = $pColumn; |
} elseif(is_object($pColumn) && ($pColumn instanceof PHPExcel_Worksheet_AutoFilter_Column)) { |
$column = $pColumn->getColumnIndex(); |
} else { |
throw new PHPExcel_Exception("Column is not within the autofilter range."); |
} |
$this->testColumnInRange($column); |
|
if (is_string($pColumn)) { |
$this->_columns[$pColumn] = new PHPExcel_Worksheet_AutoFilter_Column($pColumn, $this); |
} elseif(is_object($pColumn) && ($pColumn instanceof PHPExcel_Worksheet_AutoFilter_Column)) { |
$pColumn->setParent($this); |
$this->_columns[$column] = $pColumn; |
} |
ksort($this->_columns); |
|
return $this; |
} |
|
/** |
* Clear a specified AutoFilter Column |
* |
* @param string $pColumn Column name (e.g. A) |
* @throws PHPExcel_Exception |
* @return PHPExcel_Worksheet_AutoFilter |
*/ |
public function clearColumn($pColumn) { |
$this->testColumnInRange($pColumn); |
|
if (isset($this->_columns[$pColumn])) { |
unset($this->_columns[$pColumn]); |
} |
|
return $this; |
} |
|
/** |
* Shift an AutoFilter Column Rule to a different column |
* |
* Note: This method bypasses validation of the destination column to ensure it is within this AutoFilter range. |
* Nor does it verify whether any column rule already exists at $toColumn, but will simply overrideany existing value. |
* Use with caution. |
* |
* @param string $fromColumn Column name (e.g. A) |
* @param string $toColumn Column name (e.g. B) |
* @return PHPExcel_Worksheet_AutoFilter |
*/ |
public function shiftColumn($fromColumn=NULL,$toColumn=NULL) { |
$fromColumn = strtoupper($fromColumn); |
$toColumn = strtoupper($toColumn); |
|
if (($fromColumn !== NULL) && (isset($this->_columns[$fromColumn])) && ($toColumn !== NULL)) { |
$this->_columns[$fromColumn]->setParent(); |
$this->_columns[$fromColumn]->setColumnIndex($toColumn); |
$this->_columns[$toColumn] = $this->_columns[$fromColumn]; |
$this->_columns[$toColumn]->setParent($this); |
unset($this->_columns[$fromColumn]); |
|
ksort($this->_columns); |
} |
|
return $this; |
} |
|
|
/** |
* Test if cell value is in the defined set of values |
* |
* @param mixed $cellValue |
* @param mixed[] $dataSet |
* @return boolean |
*/ |
private static function _filterTestInSimpleDataSet($cellValue,$dataSet) |
{ |
$dataSetValues = $dataSet['filterValues']; |
$blanks = $dataSet['blanks']; |
if (($cellValue == '') || ($cellValue === NULL)) { |
return $blanks; |
} |
return in_array($cellValue,$dataSetValues); |
} |
|
/** |
* Test if cell value is in the defined set of Excel date values |
* |
* @param mixed $cellValue |
* @param mixed[] $dataSet |
* @return boolean |
*/ |
private static function _filterTestInDateGroupSet($cellValue,$dataSet) |
{ |
$dateSet = $dataSet['filterValues']; |
$blanks = $dataSet['blanks']; |
if (($cellValue == '') || ($cellValue === NULL)) { |
return $blanks; |
} |
|
if (is_numeric($cellValue)) { |
$dateValue = PHPExcel_Shared_Date::ExcelToPHP($cellValue); |
if ($cellValue < 1) { |
// Just the time part |
$dtVal = date('His',$dateValue); |
$dateSet = $dateSet['time']; |
} elseif($cellValue == floor($cellValue)) { |
// Just the date part |
$dtVal = date('Ymd',$dateValue); |
$dateSet = $dateSet['date']; |
} else { |
// date and time parts |
$dtVal = date('YmdHis',$dateValue); |
$dateSet = $dateSet['dateTime']; |
} |
foreach($dateSet as $dateValue) { |
// Use of substr to extract value at the appropriate group level |
if (substr($dtVal,0,strlen($dateValue)) == $dateValue) |
return TRUE; |
} |
} |
|
return FALSE; |
} |
|
/** |
* Test if cell value is within a set of values defined by a ruleset |
* |
* @param mixed $cellValue |
* @param mixed[] $ruleSet |
* @return boolean |
*/ |
private static function _filterTestInCustomDataSet($cellValue, $ruleSet) |
{ |
$dataSet = $ruleSet['filterRules']; |
$join = $ruleSet['join']; |
$customRuleForBlanks = isset($ruleSet['customRuleForBlanks']) ? $ruleSet['customRuleForBlanks'] : FALSE; |
|
if (!$customRuleForBlanks) { |
// Blank cells are always ignored, so return a FALSE |
if (($cellValue == '') || ($cellValue === NULL)) { |
return FALSE; |
} |
} |
$returnVal = ($join == PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_AND); |
foreach($dataSet as $rule) { |
if (is_numeric($rule['value'])) { |
// Numeric values are tested using the appropriate operator |
switch ($rule['operator']) { |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_EQUAL : |
$retVal = ($cellValue == $rule['value']); |
break; |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_NOTEQUAL : |
$retVal = ($cellValue != $rule['value']); |
break; |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN : |
$retVal = ($cellValue > $rule['value']); |
break; |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL : |
$retVal = ($cellValue >= $rule['value']); |
break; |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN : |
$retVal = ($cellValue < $rule['value']); |
break; |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL : |
$retVal = ($cellValue <= $rule['value']); |
break; |
} |
} elseif($rule['value'] == '') { |
switch ($rule['operator']) { |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_EQUAL : |
$retVal = (($cellValue == '') || ($cellValue === NULL)); |
break; |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_NOTEQUAL : |
$retVal = (($cellValue != '') && ($cellValue !== NULL)); |
break; |
default : |
$retVal = TRUE; |
break; |
} |
} else { |
// String values are always tested for equality, factoring in for wildcards (hence a regexp test) |
$retVal = preg_match('/^'.$rule['value'].'$/i',$cellValue); |
} |
// If there are multiple conditions, then we need to test both using the appropriate join operator |
switch ($join) { |
case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_OR : |
$returnVal = $returnVal || $retVal; |
// Break as soon as we have a TRUE match for OR joins, |
// to avoid unnecessary additional code execution |
if ($returnVal) |
return $returnVal; |
break; |
case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_AND : |
$returnVal = $returnVal && $retVal; |
break; |
} |
} |
|
return $returnVal; |
} |
|
/** |
* Test if cell date value is matches a set of values defined by a set of months |
* |
* @param mixed $cellValue |
* @param mixed[] $monthSet |
* @return boolean |
*/ |
private static function _filterTestInPeriodDateSet($cellValue, $monthSet) |
{ |
// Blank cells are always ignored, so return a FALSE |
if (($cellValue == '') || ($cellValue === NULL)) { |
return FALSE; |
} |
|
if (is_numeric($cellValue)) { |
$dateValue = date('m',PHPExcel_Shared_Date::ExcelToPHP($cellValue)); |
if (in_array($dateValue,$monthSet)) { |
return TRUE; |
} |
} |
|
return FALSE; |
} |
|
/** |
* Search/Replace arrays to convert Excel wildcard syntax to a regexp syntax for preg_matching |
* |
* @var array |
*/ |
private static $_fromReplace = array('\*', '\?', '~~', '~.*', '~.?'); |
private static $_toReplace = array('.*', '.', '~', '\*', '\?'); |
|
|
/** |
* Convert a dynamic rule daterange to a custom filter range expression for ease of calculation |
* |
* @param string $dynamicRuleType |
* @param PHPExcel_Worksheet_AutoFilter_Column &$filterColumn |
* @return mixed[] |
*/ |
private function _dynamicFilterDateRange($dynamicRuleType, &$filterColumn) |
{ |
$rDateType = PHPExcel_Calculation_Functions::getReturnDateType(); |
PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC); |
$val = $maxVal = NULL; |
|
$ruleValues = array(); |
$baseDate = PHPExcel_Calculation_DateTime::DATENOW(); |
// Calculate start/end dates for the required date range based on current date |
switch ($dynamicRuleType) { |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK : |
$baseDate = strtotime('-7 days',$baseDate); |
break; |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK : |
$baseDate = strtotime('-7 days',$baseDate); |
break; |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH : |
$baseDate = strtotime('-1 month',gmmktime(0,0,0,1,date('m',$baseDate),date('Y',$baseDate))); |
break; |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH : |
$baseDate = strtotime('+1 month',gmmktime(0,0,0,1,date('m',$baseDate),date('Y',$baseDate))); |
break; |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER : |
$baseDate = strtotime('-3 month',gmmktime(0,0,0,1,date('m',$baseDate),date('Y',$baseDate))); |
break; |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER : |
$baseDate = strtotime('+3 month',gmmktime(0,0,0,1,date('m',$baseDate),date('Y',$baseDate))); |
break; |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR : |
$baseDate = strtotime('-1 year',gmmktime(0,0,0,1,date('m',$baseDate),date('Y',$baseDate))); |
break; |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR : |
$baseDate = strtotime('+1 year',gmmktime(0,0,0,1,date('m',$baseDate),date('Y',$baseDate))); |
break; |
} |
|
switch ($dynamicRuleType) { |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_TODAY : |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY : |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW : |
$maxVal = (int) PHPExcel_Shared_Date::PHPtoExcel(strtotime('+1 day',$baseDate)); |
$val = (int) PHPExcel_Shared_Date::PHPToExcel($baseDate); |
break; |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_YEARTODATE : |
$maxVal = (int) PHPExcel_Shared_Date::PHPtoExcel(strtotime('+1 day',$baseDate)); |
$val = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0,0,0,1,1,date('Y',$baseDate))); |
break; |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISYEAR : |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR : |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR : |
$maxVal = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0,0,0,31,12,date('Y',$baseDate))); |
++$maxVal; |
$val = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0,0,0,1,1,date('Y',$baseDate))); |
break; |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISQUARTER : |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER : |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER : |
$thisMonth = date('m',$baseDate); |
$thisQuarter = floor(--$thisMonth / 3); |
$maxVal = (int) PHPExcel_Shared_Date::PHPtoExcel(gmmktime(0,0,0,date('t',$baseDate),(1+$thisQuarter)*3,date('Y',$baseDate))); |
++$maxVal; |
$val = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0,0,0,1,1+$thisQuarter*3,date('Y',$baseDate))); |
break; |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISMONTH : |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH : |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH : |
$maxVal = (int) PHPExcel_Shared_Date::PHPtoExcel(gmmktime(0,0,0,date('t',$baseDate),date('m',$baseDate),date('Y',$baseDate))); |
++$maxVal; |
$val = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0,0,0,1,date('m',$baseDate),date('Y',$baseDate))); |
break; |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISWEEK : |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK : |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK : |
$dayOfWeek = date('w',$baseDate); |
$val = (int) PHPExcel_Shared_Date::PHPToExcel($baseDate) - $dayOfWeek; |
$maxVal = $val + 7; |
break; |
} |
|
switch ($dynamicRuleType) { |
// Adjust Today dates for Yesterday and Tomorrow |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY : |
--$maxVal; |
--$val; |
break; |
case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW : |
++$maxVal; |
++$val; |
break; |
} |
|
// Set the filter column rule attributes ready for writing |
$filterColumn->setAttributes(array( 'val' => $val, |
'maxVal' => $maxVal |
) |
); |
|
// Set the rules for identifying rows for hide/show |
$ruleValues[] = array( 'operator' => PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL, |
'value' => $val |
); |
$ruleValues[] = array( 'operator' => PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN, |
'value' => $maxVal |
); |
PHPExcel_Calculation_Functions::setReturnDateType($rDateType); |
|
return array( |
'method' => '_filterTestInCustomDataSet', |
'arguments' => array( 'filterRules' => $ruleValues, |
'join' => PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_AND |
) |
); |
} |
|
private function _calculateTopTenValue($columnID,$startRow,$endRow,$ruleType,$ruleValue) { |
$range = $columnID.$startRow.':'.$columnID.$endRow; |
$dataValues = PHPExcel_Calculation_Functions::flattenArray( |
$this->_workSheet->rangeToArray($range,NULL,TRUE,FALSE) |
); |
|
$dataValues = array_filter($dataValues); |
if ($ruleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP) { |
rsort($dataValues); |
} else { |
sort($dataValues); |
} |
|
return array_pop(array_slice($dataValues,0,$ruleValue)); |
} |
|
/** |
* Apply the AutoFilter rules to the AutoFilter Range |
* |
* @throws PHPExcel_Exception |
* @return PHPExcel_Worksheet_AutoFilter |
*/ |
public function showHideRows() |
{ |
list($rangeStart,$rangeEnd) = PHPExcel_Cell::rangeBoundaries($this->_range); |
|
// The heading row should always be visible |
// echo 'AutoFilter Heading Row ',$rangeStart[1],' is always SHOWN',PHP_EOL; |
$this->_workSheet->getRowDimension($rangeStart[1])->setVisible(TRUE); |
|
$columnFilterTests = array(); |
foreach($this->_columns as $columnID => $filterColumn) { |
$rules = $filterColumn->getRules(); |
switch ($filterColumn->getFilterType()) { |
case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_FILTER : |
$ruleValues = array(); |
// Build a list of the filter value selections |
foreach($rules as $rule) { |
$ruleType = $rule->getRuleType(); |
$ruleValues[] = $rule->getValue(); |
} |
// Test if we want to include blanks in our filter criteria |
$blanks = FALSE; |
$ruleDataSet = array_filter($ruleValues); |
if (count($ruleValues) != count($ruleDataSet)) |
$blanks = TRUE; |
if ($ruleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_FILTER) { |
// Filter on absolute values |
$columnFilterTests[$columnID] = array( |
'method' => '_filterTestInSimpleDataSet', |
'arguments' => array( 'filterValues' => $ruleDataSet, |
'blanks' => $blanks |
) |
); |
} else { |
// Filter on date group values |
$arguments = array(); |
foreach($ruleDataSet as $ruleValue) { |
$date = $time = ''; |
if ((isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR])) && |
($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR] !== '')) |
$date .= sprintf('%04d',$ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]); |
if ((isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH])) && |
($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH] != '')) |
$date .= sprintf('%02d',$ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]); |
if ((isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY])) && |
($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY] !== '')) |
$date .= sprintf('%02d',$ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]); |
if ((isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR])) && |
($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR] !== '')) |
$time .= sprintf('%02d',$ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]); |
if ((isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE])) && |
($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE] !== '')) |
$time .= sprintf('%02d',$ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]); |
if ((isset($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND])) && |
($ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND] !== '')) |
$time .= sprintf('%02d',$ruleValue[PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]); |
$dateTime = $date . $time; |
$arguments['date'][] = $date; |
$arguments['time'][] = $time; |
$arguments['dateTime'][] = $dateTime; |
} |
// Remove empty elements |
$arguments['date'] = array_filter($arguments['date']); |
$arguments['time'] = array_filter($arguments['time']); |
$arguments['dateTime'] = array_filter($arguments['dateTime']); |
$columnFilterTests[$columnID] = array( |
'method' => '_filterTestInDateGroupSet', |
'arguments' => array( 'filterValues' => $arguments, |
'blanks' => $blanks |
) |
); |
} |
break; |
case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER : |
$customRuleForBlanks = FALSE; |
$ruleValues = array(); |
// Build a list of the filter value selections |
foreach($rules as $rule) { |
$ruleType = $rule->getRuleType(); |
$ruleValue = $rule->getValue(); |
if (!is_numeric($ruleValue)) { |
// Convert to a regexp allowing for regexp reserved characters, wildcards and escaped wildcards |
$ruleValue = preg_quote($ruleValue); |
$ruleValue = str_replace(self::$_fromReplace,self::$_toReplace,$ruleValue); |
if (trim($ruleValue) == '') { |
$customRuleForBlanks = TRUE; |
$ruleValue = trim($ruleValue); |
} |
} |
$ruleValues[] = array( 'operator' => $rule->getOperator(), |
'value' => $ruleValue |
); |
} |
$join = $filterColumn->getJoin(); |
$columnFilterTests[$columnID] = array( |
'method' => '_filterTestInCustomDataSet', |
'arguments' => array( 'filterRules' => $ruleValues, |
'join' => $join, |
'customRuleForBlanks' => $customRuleForBlanks |
) |
); |
break; |
case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER : |
$ruleValues = array(); |
foreach($rules as $rule) { |
// We should only ever have one Dynamic Filter Rule anyway |
$dynamicRuleType = $rule->getGrouping(); |
if (($dynamicRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE) || |
($dynamicRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_BELOWAVERAGE)) { |
// Number (Average) based |
// Calculate the average |
$averageFormula = '=AVERAGE('.$columnID.($rangeStart[1]+1).':'.$columnID.$rangeEnd[1].')'; |
$average = PHPExcel_Calculation::getInstance()->calculateFormula($averageFormula,NULL,$this->_workSheet->getCell('A1')); |
// Set above/below rule based on greaterThan or LessTan |
$operator = ($dynamicRuleType === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE) |
? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN |
: PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN; |
$ruleValues[] = array( 'operator' => $operator, |
'value' => $average |
); |
$columnFilterTests[$columnID] = array( |
'method' => '_filterTestInCustomDataSet', |
'arguments' => array( 'filterRules' => $ruleValues, |
'join' => PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_OR |
) |
); |
} else { |
// Date based |
if ($dynamicRuleType{0} == 'M' || $dynamicRuleType{0} == 'Q') { |
// Month or Quarter |
sscanf($dynamicRuleType,'%[A-Z]%d', $periodType, $period); |
if ($periodType == 'M') { |
$ruleValues = array($period); |
} else { |
--$period; |
$periodEnd = (1+$period)*3; |
$periodStart = 1+$period*3; |
$ruleValues = range($periodStart,periodEnd); |
} |
$columnFilterTests[$columnID] = array( |
'method' => '_filterTestInPeriodDateSet', |
'arguments' => $ruleValues |
); |
$filterColumn->setAttributes(array()); |
} else { |
// Date Range |
$columnFilterTests[$columnID] = $this->_dynamicFilterDateRange($dynamicRuleType, $filterColumn); |
break; |
} |
} |
} |
break; |
case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER : |
$ruleValues = array(); |
$dataRowCount = $rangeEnd[1] - $rangeStart[1]; |
foreach($rules as $rule) { |
// We should only ever have one Dynamic Filter Rule anyway |
$toptenRuleType = $rule->getGrouping(); |
$ruleValue = $rule->getValue(); |
$ruleOperator = $rule->getOperator(); |
} |
if ($ruleOperator === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT) { |
$ruleValue = floor($ruleValue * ($dataRowCount / 100)); |
} |
if ($ruleValue < 1) $ruleValue = 1; |
if ($ruleValue > 500) $ruleValue = 500; |
|
$maxVal = $this->_calculateTopTenValue($columnID,$rangeStart[1]+1,$rangeEnd[1],$toptenRuleType,$ruleValue); |
|
$operator = ($toptenRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP) |
? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL |
: PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL; |
$ruleValues[] = array( 'operator' => $operator, |
'value' => $maxVal |
); |
$columnFilterTests[$columnID] = array( |
'method' => '_filterTestInCustomDataSet', |
'arguments' => array( 'filterRules' => $ruleValues, |
'join' => PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_OR |
) |
); |
$filterColumn->setAttributes( |
array('maxVal' => $maxVal) |
); |
break; |
} |
} |
|
// echo 'Column Filter Test CRITERIA',PHP_EOL; |
// var_dump($columnFilterTests); |
// |
// Execute the column tests for each row in the autoFilter range to determine show/hide, |
for ($row = $rangeStart[1]+1; $row <= $rangeEnd[1]; ++$row) { |
// echo 'Testing Row = ',$row,PHP_EOL; |
$result = TRUE; |
foreach($columnFilterTests as $columnID => $columnFilterTest) { |
// echo 'Testing cell ',$columnID.$row,PHP_EOL; |
$cellValue = $this->_workSheet->getCell($columnID.$row)->getCalculatedValue(); |
// echo 'Value is ',$cellValue,PHP_EOL; |
// Execute the filter test |
$result = $result && |
call_user_func_array( |
array('PHPExcel_Worksheet_AutoFilter',$columnFilterTest['method']), |
array( |
$cellValue, |
$columnFilterTest['arguments'] |
) |
); |
// echo (($result) ? 'VALID' : 'INVALID'),PHP_EOL; |
// If filter test has resulted in FALSE, exit the loop straightaway rather than running any more tests |
if (!$result) |
break; |
} |
// Set show/hide for the row based on the result of the autoFilter result |
// echo (($result) ? 'SHOW' : 'HIDE'),PHP_EOL; |
$this->_workSheet->getRowDimension($row)->setVisible($result); |
} |
|
return $this; |
} |
|
|
/** |
* Implement PHP __clone to create a deep clone, not just a shallow copy. |
*/ |
public function __clone() { |
$vars = get_object_vars($this); |
foreach ($vars as $key => $value) { |
if (is_object($value)) { |
if ($key == '_workSheet') { |
// Detach from worksheet |
$this->{$key} = NULL; |
} else { |
$this->{$key} = clone $value; |
} |
} elseif ((is_array($value)) && ($key == '_columns')) { |
// The columns array of PHPExcel_Worksheet_AutoFilter objects |
$this->{$key} = array(); |
foreach ($value as $k => $v) { |
$this->{$key}[$k] = clone $v; |
// attach the new cloned Column to this new cloned Autofilter object |
$this->{$key}[$k]->setParent($this); |
} |
} else { |
$this->{$key} = $value; |
} |
} |
} |
|
/** |
* toString method replicates previous behavior by returning the range if object is |
* referenced as a property of its parent. |
*/ |
public function __toString() { |
return (string) $this->_range; |
} |
|
} |