/**
* Apply the AutoFilter rules to the AutoFilter Range
*
* @throws \PHPExcel\Exception
* @return 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 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 == 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('date' => array(), 'time' => array(), 'dateTime' => array());
foreach ($ruleDataSet as $ruleValue) {
$date = $time = '';
if (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]) && $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR] !== '') {
$date .= sprintf('%04d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]);
}
if (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]) && $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH] != '') {
$date .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]);
}
if (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]) && $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY] !== '') {
$date .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]);
}
if (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]) && $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR] !== '') {
$time .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]);
}
if (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]) && $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE] !== '') {
$time .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]);
}
if (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]) && $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND] !== '') {
$time .= sprintf('%02d', $ruleValue[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 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 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 == AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE || $dynamicRuleType == 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 === AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE ? AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN : AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN;
$ruleValues[] = array('operator' => $operator, 'value' => $average);
$columnFilterTests[$columnID] = array('method' => 'filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => 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 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 === 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 == AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP ? AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL : AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL;
$ruleValues[] = array('operator' => $operator, 'value' => $maxVal);
$columnFilterTests[$columnID] = array('method' => 'filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => 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;
}
/**
* Write SheetData
*
* @param \PHPExcel\Shared\XMLWriter $objWriter XML Writer
* @param \PHPExcel\Worksheet $pSheet Worksheet
* @param string[] $pStringTable String table
* @throws \PHPExcel\Writer\Exception
*/
private function writeSheetData(\PHPExcel\Shared\XMLWriter $objWriter = null, \PHPExcel\Worksheet $pSheet = null, $pStringTable = null)
{
if (is_array($pStringTable)) {
// Flipped stringtable, for faster index searching
$aFlippedStringTable = $this->getParentWriter()->getWriterPart('stringtable')->flipStringTable($pStringTable);
// sheetData
$objWriter->startElement('sheetData');
// Get column count
$colCount = \PHPExcel\Cell::columnIndexFromString($pSheet->getHighestColumn());
// Highest row number
$highestRow = $pSheet->getHighestRow();
// Loop through cells
$cellsByRow = array();
foreach ($pSheet->getCellCollection() as $cellID) {
$cellAddress = \PHPExcel\Cell::coordinateFromString($cellID);
$cellsByRow[$cellAddress[1]][] = $cellID;
}
$currentRow = 0;
while ($currentRow++ < $highestRow) {
// Get row dimension
$rowDimension = $pSheet->getRowDimension($currentRow);
// Write current row?
$writeCurrentRow = isset($cellsByRow[$currentRow]) || $rowDimension->getRowHeight() >= 0 || $rowDimension->getVisible() == false || $rowDimension->getCollapsed() == true || $rowDimension->getOutlineLevel() > 0 || $rowDimension->getXfIndex() !== null;
if ($writeCurrentRow) {
// Start a new row
$objWriter->startElement('row');
$objWriter->writeAttribute('r', $currentRow);
$objWriter->writeAttribute('spans', '1:' . $colCount);
// Row dimensions
if ($rowDimension->getRowHeight() >= 0) {
$objWriter->writeAttribute('customHeight', '1');
$objWriter->writeAttribute('ht', \PHPExcel\Shared\StringHelper::formatNumber($rowDimension->getRowHeight()));
}
// Row visibility
if ($rowDimension->getVisible() == false) {
$objWriter->writeAttribute('hidden', 'true');
}
// Collapsed
if ($rowDimension->getCollapsed() == true) {
$objWriter->writeAttribute('collapsed', 'true');
}
// Outline level
if ($rowDimension->getOutlineLevel() > 0) {
$objWriter->writeAttribute('outlineLevel', $rowDimension->getOutlineLevel());
}
// Style
if ($rowDimension->getXfIndex() !== null) {
$objWriter->writeAttribute('s', $rowDimension->getXfIndex());
$objWriter->writeAttribute('customFormat', '1');
}
// Write cells
if (isset($cellsByRow[$currentRow])) {
foreach ($cellsByRow[$currentRow] as $cellAddress) {
// Write cell
$this->writeCell($objWriter, $pSheet, $cellAddress, $pStringTable, $aFlippedStringTable);
}
}
// End row
$objWriter->endElement();
}
}
$objWriter->endElement();
} else {
throw new \PHPExcel\Writer\Exception("Invalid parameters passed.");
}
}
