/**
* 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 the AUTOFILTERINFO BIFF record. This is used to configure the number of autofilter select used in the sheet.
*/
private function writeAutoFilterInfo()
{
$record = 0x9d;
// Record identifier
$length = 0x2;
// Bytes to follow
$rangeBounds = \PHPExcel\Cell::rangeBoundaries($this->phpSheet->getAutoFilter()->getRange());
$iNumFilters = 1 + $rangeBounds[1][0] - $rangeBounds[0][0];
$header = pack("vv", $record, $length);
$data = pack("v", $iNumFilters);
$this->append($header . $data);
}
/**
* Writes all the DEFINEDNAME records (BIFF8).
* So far this is only used for repeating rows/columns (print titles) and print areas
*/
private function writeAllDefinedNamesBiff8()
{
$chunk = '';
// Named ranges
if (count($this->phpExcel->getNamedRanges()) > 0) {
// Loop named ranges
$namedRanges = $this->phpExcel->getNamedRanges();
foreach ($namedRanges as $namedRange) {
// Create absolute coordinate
$range = \PHPExcel\Cell::splitRange($namedRange->getRange());
for ($i = 0; $i < count($range); $i++) {
$range[$i][0] = '\'' . str_replace("'", "''", $namedRange->getWorksheet()->getTitle()) . '\'!' . \PHPExcel\Cell::absoluteCoordinate($range[$i][0]);
if (isset($range[$i][1])) {
$range[$i][1] = \PHPExcel\Cell::absoluteCoordinate($range[$i][1]);
}
}
$range = \PHPExcel\Cell::buildRange($range);
// e.g. Sheet1!$A$1:$B$2
// parse formula
try {
$error = $this->parser->parse($range);
$formulaData = $this->parser->toReversePolish();
// make sure tRef3d is of type tRef3dR (0x3A)
if (isset($formulaData[0]) and ($formulaData[0] == "z" or $formulaData[0] == "Z")) {
$formulaData = ":" . substr($formulaData, 1);
}
if ($namedRange->getLocalOnly()) {
// local scope
$scope = $this->phpExcel->getIndex($namedRange->getScope()) + 1;
} else {
// global scope
$scope = 0;
}
$chunk .= $this->writeData($this->writeDefinedNameBiff8($namedRange->getName(), $formulaData, $scope, false));
} catch (\PHPExcel\Exception $e) {
// do nothing
}
}
}
// total number of sheets
$total_worksheets = $this->phpExcel->getSheetCount();
// write the print titles (repeating rows, columns), if any
for ($i = 0; $i < $total_worksheets; ++$i) {
$sheetSetup = $this->phpExcel->getSheet($i)->getPageSetup();
// simultaneous repeatColumns repeatRows
if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) {
$repeat = $sheetSetup->getColumnsToRepeatAtLeft();
$colmin = \PHPExcel\Cell::columnIndexFromString($repeat[0]) - 1;
$colmax = \PHPExcel\Cell::columnIndexFromString($repeat[1]) - 1;
$repeat = $sheetSetup->getRowsToRepeatAtTop();
$rowmin = $repeat[0] - 1;
$rowmax = $repeat[1] - 1;
// construct formula data manually
$formulaData = pack('Cv', 0x29, 0x17);
// tMemFunc
$formulaData .= pack('Cvvvvv', 0x3b, $i, 0, 65535, $colmin, $colmax);
// tArea3d
$formulaData .= pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, 0, 255);
// tArea3d
$formulaData .= pack('C', 0x10);
// tList
// store the DEFINEDNAME record
$chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true));
// (exclusive) either repeatColumns or repeatRows
} elseif ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) {
// Columns to repeat
if ($sheetSetup->isColumnsToRepeatAtLeftSet()) {
$repeat = $sheetSetup->getColumnsToRepeatAtLeft();
$colmin = \PHPExcel\Cell::columnIndexFromString($repeat[0]) - 1;
$colmax = \PHPExcel\Cell::columnIndexFromString($repeat[1]) - 1;
} else {
$colmin = 0;
$colmax = 255;
}
// Rows to repeat
if ($sheetSetup->isRowsToRepeatAtTopSet()) {
$repeat = $sheetSetup->getRowsToRepeatAtTop();
$rowmin = $repeat[0] - 1;
$rowmax = $repeat[1] - 1;
} else {
$rowmin = 0;
$rowmax = 65535;
}
// construct formula data manually because parser does not recognize absolute 3d cell references
$formulaData = pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, $colmin, $colmax);
// store the DEFINEDNAME record
$chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true));
}
}
// write the print areas, if any
for ($i = 0; $i < $total_worksheets; ++$i) {
$sheetSetup = $this->phpExcel->getSheet($i)->getPageSetup();
if ($sheetSetup->isPrintAreaSet()) {
// Print area, e.g. A3:J6,H1:X20
$printArea = \PHPExcel\Cell::splitRange($sheetSetup->getPrintArea());
$countPrintArea = count($printArea);
$formulaData = '';
for ($j = 0; $j < $countPrintArea; ++$j) {
$printAreaRect = $printArea[$j];
// e.g. A3:J6
$printAreaRect[0] = \PHPExcel\Cell::coordinateFromString($printAreaRect[0]);
$printAreaRect[1] = \PHPExcel\Cell::coordinateFromString($printAreaRect[1]);
$print_rowmin = $printAreaRect[0][1] - 1;
$print_rowmax = $printAreaRect[1][1] - 1;
$print_colmin = \PHPExcel\Cell::columnIndexFromString($printAreaRect[0][0]) - 1;
$print_colmax = \PHPExcel\Cell::columnIndexFromString($printAreaRect[1][0]) - 1;
// construct formula data manually because parser does not recognize absolute 3d cell references
$formulaData .= pack('Cvvvvv', 0x3b, $i, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax);
if ($j > 0) {
$formulaData .= pack('C', 0x10);
// list operator token ','
}
}
// store the DEFINEDNAME record
$chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x6), $formulaData, $i + 1, true));
}
}
// write autofilters, if any
for ($i = 0; $i < $total_worksheets; ++$i) {
$sheetAutoFilter = $this->phpExcel->getSheet($i)->getAutoFilter();
$autoFilterRange = $sheetAutoFilter->getRange();
if (!empty($autoFilterRange)) {
$rangeBounds = \PHPExcel\Cell::rangeBoundaries($autoFilterRange);
//Autofilter built in name
$name = pack('C', 0xd);
$chunk .= $this->writeData($this->writeShortNameBiff8($name, $i + 1, $rangeBounds, true));
}
}
return $chunk;
}
/**
* Build the Worksheet Escher objects
*
*/
private function buildWorksheetEschers()
{
// 1-based index to BstoreContainer
$blipIndex = 0;
$lastReducedSpId = 0;
$lastSpId = 0;
foreach ($this->phpExcel->getAllsheets() as $sheet) {
// sheet index
$sheetIndex = $sheet->getParent()->getIndex($sheet);
$escher = null;
// check if there are any shapes for this sheet
$filterRange = $sheet->getAutoFilter()->getRange();
if (count($sheet->getDrawingCollection()) == 0 && empty($filterRange)) {
continue;
}
// create intermediate Escher object
$escher = new \PHPExcel\Shared\Escher();
// dgContainer
$dgContainer = new \PHPExcel\Shared\Escher\DgContainer();
// set the drawing index (we use sheet index + 1)
$dgId = $sheet->getParent()->getIndex($sheet) + 1;
$dgContainer->setDgId($dgId);
$escher->setDgContainer($dgContainer);
// spgrContainer
$spgrContainer = new \PHPExcel\Shared\Escher\DgContainer\SpgrContainer();
$dgContainer->setSpgrContainer($spgrContainer);
// add one shape which is the group shape
$spContainer = new \PHPExcel\Shared\Escher\DgContainer\SpgrContainer\SpContainer();
$spContainer->setSpgr(true);
$spContainer->setSpType(0);
$spContainer->setSpId($sheet->getParent()->getIndex($sheet) + 1 << 10);
$spgrContainer->addChild($spContainer);
// add the shapes
$countShapes[$sheetIndex] = 0;
// count number of shapes (minus group shape), in sheet
foreach ($sheet->getDrawingCollection() as $drawing) {
++$blipIndex;
++$countShapes[$sheetIndex];
// add the shape
$spContainer = new \PHPExcel\Shared\Escher\DgContainer\SpgrContainer\SpContainer();
// set the shape type
$spContainer->setSpType(0x4b);
// set the shape flag
$spContainer->setSpFlag(0x2);
// set the shape index (we combine 1-based sheet index and $countShapes to create unique shape index)
$reducedSpId = $countShapes[$sheetIndex];
$spId = $reducedSpId | $sheet->getParent()->getIndex($sheet) + 1 << 10;
$spContainer->setSpId($spId);
// keep track of last reducedSpId
$lastReducedSpId = $reducedSpId;
// keep track of last spId
$lastSpId = $spId;
// set the BLIP index
$spContainer->setOPT(0x4104, $blipIndex);
// set coordinates and offsets, client anchor
$coordinates = $drawing->getCoordinates();
$offsetX = $drawing->getOffsetX();
$offsetY = $drawing->getOffsetY();
$width = $drawing->getWidth();
$height = $drawing->getHeight();
$twoAnchor = \PHPExcel\Shared\Excel5::oneAnchor2twoAnchor($sheet, $coordinates, $offsetX, $offsetY, $width, $height);
$spContainer->setStartCoordinates($twoAnchor['startCoordinates']);
$spContainer->setStartOffsetX($twoAnchor['startOffsetX']);
$spContainer->setStartOffsetY($twoAnchor['startOffsetY']);
$spContainer->setEndCoordinates($twoAnchor['endCoordinates']);
$spContainer->setEndOffsetX($twoAnchor['endOffsetX']);
$spContainer->setEndOffsetY($twoAnchor['endOffsetY']);
$spgrContainer->addChild($spContainer);
}
// AutoFilters
if (!empty($filterRange)) {
$rangeBounds = \PHPExcel\Cell::rangeBoundaries($filterRange);
$iNumColStart = $rangeBounds[0][0];
$iNumColEnd = $rangeBounds[1][0];
$iInc = $iNumColStart;
while ($iInc <= $iNumColEnd) {
++$countShapes[$sheetIndex];
// create an Drawing Object for the dropdown
$oDrawing = new \PHPExcel\Worksheet\BaseDrawing();
// get the coordinates of drawing
$cDrawing = \PHPExcel\Cell::stringFromColumnIndex($iInc - 1) . $rangeBounds[0][1];
$oDrawing->setCoordinates($cDrawing);
$oDrawing->setWorksheet($sheet);
// add the shape
$spContainer = new \PHPExcel\Shared\Escher\DgContainer\SpgrContainer\SpContainer();
// set the shape type
$spContainer->setSpType(0xc9);
// set the shape flag
$spContainer->setSpFlag(0x1);
// set the shape index (we combine 1-based sheet index and $countShapes to create unique shape index)
$reducedSpId = $countShapes[$sheetIndex];
$spId = $reducedSpId | $sheet->getParent()->getIndex($sheet) + 1 << 10;
$spContainer->setSpId($spId);
// keep track of last reducedSpId
$lastReducedSpId = $reducedSpId;
// keep track of last spId
$lastSpId = $spId;
$spContainer->setOPT(0x7f, 0x1040104);
// Protection -> fLockAgainstGrouping
$spContainer->setOPT(0xbf, 0x80008);
// Text -> fFitTextToShape
$spContainer->setOPT(0x1bf, 0x10000);
// Fill Style -> fNoFillHitTest
$spContainer->setOPT(0x1ff, 0x80000);
// Line Style -> fNoLineDrawDash
$spContainer->setOPT(0x3bf, 0xa0000);
// Group Shape -> fPrint
// set coordinates and offsets, client anchor
$endCoordinates = \PHPExcel\Cell::stringFromColumnIndex(\PHPExcel\Cell::stringFromColumnIndex($iInc - 1));
$endCoordinates .= $rangeBounds[0][1] + 1;
$spContainer->setStartCoordinates($cDrawing);
$spContainer->setStartOffsetX(0);
$spContainer->setStartOffsetY(0);
$spContainer->setEndCoordinates($endCoordinates);
$spContainer->setEndOffsetX(0);
$spContainer->setEndOffsetY(0);
$spgrContainer->addChild($spContainer);
$iInc++;
}
}
// identifier clusters, used for workbook Escher object
$this->IDCLs[$dgId] = $lastReducedSpId;
// set last shape index
$dgContainer->setLastSpId($lastSpId);
// set the Escher object
$this->writerWorksheets[$sheetIndex]->setEscher($escher);
}
}
