/** * 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); } }