PHP PHPExcel_Cell::rangeBoundaries方法代码示例

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

//.........这里部分代码省略.........
                             $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);

//.........这里部分代码省略.........
                     // 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;
//.........这里部分代码省略.........

 /**
  * 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
//.........这里部分代码省略.........

 /**
  *	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
//.........这里部分代码省略.........