/** * Create array from a range of cells * * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1") * @param mixed $nullValue Value returned in the array entry if a cell doesn't exist * @param boolean $calculateFormulas Should formulas be calculated? * @param boolean $formatData Should formatting be applied to cell values? * @param boolean $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero * True - Return rows and columns indexed by their actual row and column IDs * @return array */ public function rangeToArray($pRange = 'A1', $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) { // Returnvalue $returnValue = array(); // Identify the range that we need to extract from the worksheet list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($pRange); $minCol = PHPExcel_Cell::stringFromColumnIndex($rangeStart[0] - 1); $minRow = $rangeStart[1]; $maxCol = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0] - 1); $maxRow = $rangeEnd[1]; $maxCol++; // Loop through rows $r = -1; for ($row = $minRow; $row <= $maxRow; ++$row) { $rRef = $returnCellRef ? $row : ++$r; $c = -1; // Loop through columns in the current row for ($col = $minCol; $col != $maxCol; ++$col) { $cRef = $returnCellRef ? $col : ++$c; // Using getCell() will create a new cell if it doesn't already exist. We don't want that to happen // so we test and retrieve directly against _cellCollection if ($this->_cellCollection->isDataSet($col . $row)) { // Cell exists $cell = $this->_cellCollection->getCacheData($col . $row); if ($cell->getValue() !== null) { if ($cell->getValue() instanceof PHPExcel_RichText) { $returnValue[$rRef][$cRef] = $cell->getValue()->getPlainText(); } else { if ($calculateFormulas) { $returnValue[$rRef][$cRef] = $cell->getCalculatedValue(); } else { $returnValue[$rRef][$cRef] = $cell->getValue(); } } if ($formatData) { $style = $this->_parent->getCellXfByIndex($cell->getXfIndex()); $returnValue[$rRef][$cRef] = PHPExcel_Style_NumberFormat::toFormattedString($returnValue[$rRef][$cRef], $style && $style->getNumberFormat() ? $style->getNumberFormat()->getFormatCode() : PHPExcel_Style_NumberFormat::FORMAT_GENERAL); } } else { // Cell holds a NULL $returnValue[$rRef][$cRef] = $nullValue; } } else { // Cell doesn't exist $returnValue[$rRef][$cRef] = $nullValue; } } } // Return return $returnValue; }
/** * 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); }
/** * Helper function for checking subranges of a range */ public function isSubrange($subRange, $range) { list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($range); list($subrangeStart, $subrangeEnd) = PHPExcel_Cell::rangeBoundaries($subRange); return $subrangeStart[0] >= $rangeStart[0] && $subrangeStart[1] >= $rangeStart[1] && $subrangeEnd[0] <= $rangeEnd[0] && $subrangeEnd[1] <= $rangeEnd[1]; }
/** * Insert a new column or row, updating all possible related data * * @param string $pBefore Insert before this cell address (e.g. 'A1') * @param integer $pNumCols Number of columns to insert/delete (negative values indicate deletion) * @param integer $pNumRows Number of rows to insert/delete (negative values indicate deletion) * @param PHPExcel_Worksheet $pSheet The worksheet that we're editing * @throws PHPExcel_Exception */ public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = NULL) { $remove = $pNumCols < 0 || $pNumRows < 0; $aCellCollection = $pSheet->getCellCollection(); // Get coordinates of $pBefore $beforeColumn = 'A'; $beforeRow = 1; list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString($pBefore); $beforeColumnIndex = PHPExcel_Cell::columnIndexFromString($beforeColumn); // Clear cells if we are removing columns or rows $highestColumn = $pSheet->getHighestColumn(); $highestRow = $pSheet->getHighestRow(); // 1. Clear column strips if we are removing columns if ($pNumCols < 0 && $beforeColumnIndex - 2 + $pNumCols > 0) { for ($i = 1; $i <= $highestRow - 1; ++$i) { for ($j = $beforeColumnIndex - 1 + $pNumCols; $j <= $beforeColumnIndex - 2; ++$j) { $coordinate = PHPExcel_Cell::stringFromColumnIndex($j) . $i; $pSheet->removeConditionalStyles($coordinate); if ($pSheet->cellExists($coordinate)) { $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL); $pSheet->getCell($coordinate)->setXfIndex(0); } } } } // 2. Clear row strips if we are removing rows if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) { for ($i = $beforeColumnIndex - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) { for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) { $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . $j; $pSheet->removeConditionalStyles($coordinate); if ($pSheet->cellExists($coordinate)) { $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL); $pSheet->getCell($coordinate)->setXfIndex(0); } } } } // Loop through cells, bottom-up, and change cell coordinates while ($cellID = $remove ? array_shift($aCellCollection) : array_pop($aCellCollection)) { $cell = $pSheet->getCell($cellID); $cellIndex = PHPExcel_Cell::columnIndexFromString($cell->getColumn()); if ($cellIndex - 1 + $pNumCols < 0) { continue; } // New coordinates $newCoordinates = PHPExcel_Cell::stringFromColumnIndex($cellIndex - 1 + $pNumCols) . ($cell->getRow() + $pNumRows); // Should the cell be updated? Move value and cellXf index from one cell to another. if ($cellIndex >= $beforeColumnIndex && $cell->getRow() >= $beforeRow) { // Update cell styles $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex()); // Insert this cell at its new location if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) { // Formula should be adjusted $pSheet->getCell($newCoordinates)->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle())); } else { // Formula should not be adjusted $pSheet->getCell($newCoordinates)->setValue($cell->getValue()); } // Clear the original cell $pSheet->getCellCacheController()->deleteCacheData($cellID); } else { /* We don't need to update styles for rows/columns before our insertion position, but we do still need to adjust any formulae in those cells */ if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) { // Formula should be adjusted $cell->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle())); } } } // Duplicate styles for the newly inserted cells $highestColumn = $pSheet->getHighestColumn(); $highestRow = $pSheet->getHighestRow(); if ($pNumCols > 0 && $beforeColumnIndex - 2 > 0) { for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) { // Style $coordinate = PHPExcel_Cell::stringFromColumnIndex($beforeColumnIndex - 2) . $i; if ($pSheet->cellExists($coordinate)) { $xfIndex = $pSheet->getCell($coordinate)->getXfIndex(); $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false; for ($j = $beforeColumnIndex - 1; $j <= $beforeColumnIndex - 2 + $pNumCols; ++$j) { $pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex); if ($conditionalStyles) { $cloned = array(); foreach ($conditionalStyles as $conditionalStyle) { $cloned[] = clone $conditionalStyle; } $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($j) . $i, $cloned); } } } } } if ($pNumRows > 0 && $beforeRow - 1 > 0) { for ($i = $beforeColumnIndex - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) { // Style $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1); if ($pSheet->cellExists($coordinate)) { $xfIndex = $pSheet->getCell($coordinate)->getXfIndex(); $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false; for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) { $pSheet->getCell(PHPExcel_Cell::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex); if ($conditionalStyles) { $cloned = array(); foreach ($conditionalStyles as $conditionalStyle) { $cloned[] = clone $conditionalStyle; } $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($i) . $j, $cloned); } } } } } // Update worksheet: column dimensions $this->_adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: row dimensions $this->_adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: page breaks $this->_adjustPageBreaks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: comments $this->_adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: hyperlinks $this->_adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: data validations $this->_adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: merge cells $this->_adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: protected cells $this->_adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: autofilter $autoFilter = $pSheet->getAutoFilter(); $autoFilterRange = $autoFilter->getRange(); if (!empty($autoFilterRange)) { if ($pNumCols != 0) { $autoFilterColumns = array_keys($autoFilter->getColumns()); if (count($autoFilterColumns) > 0) { sscanf($pBefore, '%[A-Z]%d', $column, $row); $columnIndex = PHPExcel_Cell::columnIndexFromString($column); list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($autoFilterRange); if ($columnIndex <= $rangeEnd[0]) { if ($pNumCols < 0) { // If we're actually deleting any columns that fall within the autofilter range, // then we delete any rules for those columns $deleteColumn = $columnIndex + $pNumCols - 1; $deleteCount = abs($pNumCols); for ($i = 1; $i <= $deleteCount; ++$i) { if (in_array(PHPExcel_Cell::stringFromColumnIndex($deleteColumn), $autoFilterColumns)) { $autoFilter->clearColumn(PHPExcel_Cell::stringFromColumnIndex($deleteColumn)); } ++$deleteColumn; } } $startCol = $columnIndex > $rangeStart[0] ? $columnIndex : $rangeStart[0]; // Shuffle columns in autofilter range if ($pNumCols > 0) { // For insert, we shuffle from end to beginning to avoid overwriting $startColID = PHPExcel_Cell::stringFromColumnIndex($startCol - 1); $toColID = PHPExcel_Cell::stringFromColumnIndex($startCol + $pNumCols - 1); $endColID = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0]); $startColRef = $startCol; $endColRef = $rangeEnd[0]; $toColRef = $rangeEnd[0] + $pNumCols; do { $autoFilter->shiftColumn(PHPExcel_Cell::stringFromColumnIndex($endColRef - 1), PHPExcel_Cell::stringFromColumnIndex($toColRef - 1)); --$endColRef; --$toColRef; } while ($startColRef <= $endColRef); } else { // For delete, we shuffle from beginning to end to avoid overwriting $startColID = PHPExcel_Cell::stringFromColumnIndex($startCol - 1); $toColID = PHPExcel_Cell::stringFromColumnIndex($startCol + $pNumCols - 1); $endColID = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0]); do { $autoFilter->shiftColumn($startColID, $toColID); ++$startColID; ++$toColID; } while ($startColID != $endColID); } } } } $pSheet->setAutoFilter($this->updateCellReference($autoFilterRange, $pBefore, $pNumCols, $pNumRows)); } // Update worksheet: freeze pane if ($pSheet->getFreezePane() != '') { $pSheet->freezePane($this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows)); } // Page setup if ($pSheet->getPageSetup()->isPrintAreaSet()) { $pSheet->getPageSetup()->setPrintArea($this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows)); } // Update worksheet: drawings $aDrawings = $pSheet->getDrawingCollection(); foreach ($aDrawings as $objDrawing) { $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows); if ($objDrawing->getCoordinates() != $newReference) { $objDrawing->setCoordinates($newReference); } } // Update workbook: named ranges if (count($pSheet->getParent()->getNamedRanges()) > 0) { foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) { if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) { $namedRange->setRange($this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows)); } } } // Garbage collect $pSheet->garbageCollect(); }
/** * 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; }
/** * 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('date' => array(), 'time' => array(), 'dateTime' => 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; }
/** * Calculate range dimension * * @param string $pRange Cell range (e.g. A1:A1) * @return array Range dimension (width, height) */ public static function rangeDimension($pRange = 'A1:A1') { // Calculate range outer borders list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($pRange); return array($rangeEnd[0] - $rangeStart[0] + 1, $rangeEnd[1] - $rangeStart[1] + 1); }
/** * 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); } }
/** * 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: $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); } $ruleValues[] = array('operator' => $rule->getOperator(), 'value' => $ruleValue); } $join = $filterColumn->getAndOr(); $columnFilterTests[$columnID] = array('method' => '_filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => $join)); break; case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER: $ruleValues = array(); //var_dump($rules); foreach ($rules as $rule) { // We should only ever have one Dynamic Filter Rule anyway $dynamicRuleType = $rule->getGrouping(); echo '$dynamicRuleType is ', $dynamicRuleType, PHP_EOL; if ($dynamicRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE || $dynamicRuleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_BELOWAVERAGE) { // Number based $averageFormula = '=AVERAGE(' . $columnID . ($rangeStart[1] + 1) . ':' . $columnID . $rangeEnd[1] . ')'; echo 'Average Formula Result is ', $averageFormula, PHP_EOL; $average = PHPExcel_Calculation::getInstance()->calculateFormula($averageFormula, NULL, $this->_workSheet->getCell('A1')); $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_ANDOR_OR)); } else { // Date based $columnFilterTests[$columnID] = array('method' => '_filterTypeDynamicFilters', 'arguments' => $ruleValues); } } break; case PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER: $ruleValues = array(); var_dump($rules); foreach ($rules as $rule) { // We should only ever have one Dynamic Filter Rule anyway } $columnFilterTests[$columnID] = array('method' => '_filterTypeTopTenFilters', 'arguments' => $ruleValues); break; } } echo 'Column Filter Test CRITERIA', PHP_EOL; var_dump($columnFilterTests); 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; } } echo $result ? 'SHOW' : 'HIDE', PHP_EOL; $this->_workSheet->getRowDimension($row)->setVisible($result); } return $this; }