コード例 #1
0
 /**
  *    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;
 }
コード例 #2
0
ファイル: Worksheet.php プロジェクト: kameshwariv/testexample
 /**
  * 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);
 }
コード例 #3
0
ファイル: Workbook.php プロジェクト: kameshwariv/testexample
 /**
  * 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;
 }
コード例 #4
0
ファイル: Excel5.php プロジェクト: kameshwariv/testexample
 /**
  * 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);
     }
 }