PHP PHPExcel_Worksheet::getDataValidationCollection方法代码示例

//.........这里部分代码省略.........
         }
         $pSheet->refreshColumnDimensions();
     }
     // Update worksheet: row dimensions
     $aRowDimensions = array_reverse($pSheet->getRowDimensions(), true);
     if (count($aRowDimensions) > 0) {
         foreach ($aRowDimensions as $objRowDimension) {
             $newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $pBefore, $pNumCols, $pNumRows);
             list(, $newReference) = PHPExcel_Cell::coordinateFromString($newReference);
             if ($objRowDimension->getRowIndex() != $newReference) {
                 $objRowDimension->setRowIndex($newReference);
             }
         }
         $pSheet->refreshRowDimensions();
         $copyDimension = $pSheet->getRowDimension($beforeRow - 1);
         for ($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; ++$i) {
             $newDimension = $pSheet->getRowDimension($i);
             $newDimension->setRowHeight($copyDimension->getRowHeight());
             $newDimension->setVisible($copyDimension->getVisible());
             $newDimension->setOutlineLevel($copyDimension->getOutlineLevel());
             $newDimension->setCollapsed($copyDimension->getCollapsed());
         }
     }
     // Update worksheet: breaks
     $aBreaks = array_reverse($pSheet->getBreaks(), true);
     foreach ($aBreaks as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setBreak($newReference, $value);
             $pSheet->setBreak($key, PHPExcel_Worksheet::BREAK_NONE);
         }
     }
     // Update worksheet: hyperlinks
     $aHyperlinkCollection = array_reverse($pSheet->getHyperlinkCollection(), true);
     foreach ($aHyperlinkCollection as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setHyperlink($newReference, $value);
             $pSheet->setHyperlink($key, null);
         }
     }
     // Update worksheet: data validations
     $aDataValidationCollection = array_reverse($pSheet->getDataValidationCollection(), true);
     foreach ($aDataValidationCollection as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setDataValidation($newReference, $value);
             $pSheet->setDataValidation($key, null);
         }
     }
     // Update worksheet: merge cells
     $aMergeCells = $pSheet->getMergeCells();
     $aNewMergeCells = array();
     // the new array of all merge cells
     foreach ($aMergeCells as $key => &$value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         $aNewMergeCells[$newReference] = $newReference;
     }
     $pSheet->setMergeCells($aNewMergeCells);
     // replace the merge cells array
     // Update worksheet: protected cells
     $aProtectedCells = array_reverse($pSheet->getProtectedCells(), true);
     foreach ($aProtectedCells as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->protectCells($newReference, $value, true);
             $pSheet->unprotectCells($key);
         }
     }
     // Update worksheet: autofilter
     if ($pSheet->getAutoFilter() != '') {
         $pSheet->setAutoFilter($this->updateCellReference($pSheet->getAutoFilter(), $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();
 }

 /**
  * Store the DATAVALIDATIONS and DATAVALIDATION records.
  */
 private function _writeDataValidity()
 {
     // Datavalidation collection
     $dataValidationCollection = $this->_phpSheet->getDataValidationCollection();
     // Write data validations?
     if (!empty($dataValidationCollection)) {
         // DATAVALIDATIONS record
         $record = 0x1b2;
         // Record identifier
         $length = 0x12;
         // Bytes to follow
         $grbit = 0x0;
         // Prompt box at cell, no cached validity data at DV records
         $horPos = 0x0;
         // Horizontal position of prompt box, if fixed position
         $verPos = 0x0;
         // Vertical position of prompt box, if fixed position
         $objId = 0xffffffff;
         // Object identifier of drop down arrow object, or -1 if not visible
         $header = pack('vv', $record, $length);
         $data = pack('vVVVV', $grbit, $horPos, $verPos, $objId, count($dataValidationCollection));
         $this->_append($header . $data);
         // DATAVALIDATION records
         $record = 0x1be;
         // Record identifier
         foreach ($dataValidationCollection as $cellCoordinate => $dataValidation) {
             // initialize record data
             $data = '';
             // options
             $options = 0x0;
             // data type
             $type = $dataValidation->getType();
             switch ($type) {
                 case PHPExcel_Cell_DataValidation::TYPE_NONE:
                     $type = 0x0;
                     break;
                 case PHPExcel_Cell_DataValidation::TYPE_WHOLE:
                     $type = 0x1;
                     break;
                 case PHPExcel_Cell_DataValidation::TYPE_DECIMAL:
                     $type = 0x2;
                     break;
                 case PHPExcel_Cell_DataValidation::TYPE_LIST:
                     $type = 0x3;
                     break;
                 case PHPExcel_Cell_DataValidation::TYPE_DATE:
                     $type = 0x4;
                     break;
                 case PHPExcel_Cell_DataValidation::TYPE_TIME:
                     $type = 0x5;
                     break;
                 case PHPExcel_Cell_DataValidation::TYPE_TEXTLENGTH:
                     $type = 0x6;
                     break;
                 case PHPExcel_Cell_DataValidation::TYPE_CUSTOM:
                     $type = 0x7;
                     break;
             }
             $options |= $type << 0;
             // error style
             $errorStyle = $dataValidation->getType();
             switch ($errorStyle) {
                 case PHPExcel_Cell_DataValidation::STYLE_STOP:
                     $errorStyle = 0x0;
                     break;
                 case PHPExcel_Cell_DataValidation::STYLE_WARNING:
                     $errorStyle = 0x1;
                     break;
                 case PHPExcel_Cell_DataValidation::STYLE_INFORMATION:
                     $errorStyle = 0x2;
                     break;
             }
             $options |= $errorStyle << 4;
             // explicit formula?
             if ($type == 0x3 && preg_match('/^\\".*\\"$/', $dataValidation->getFormula1())) {
                 $options |= 0x1 << 7;
             }
             // empty cells allowed
             $options |= $dataValidation->getAllowBlank() << 8;
             // show drop down
             $options |= !$dataValidation->getShowDropDown() << 9;
             // show input message
             $options |= $dataValidation->getShowInputMessage() << 18;
             // show error message
             $options |= $dataValidation->getShowErrorMessage() << 19;
             // condition operator
             $operator = $dataValidation->getOperator();
             switch ($operator) {
                 case PHPExcel_Cell_DataValidation::OPERATOR_BETWEEN:
                     $operator = 0x0;
                     break;
                 case PHPExcel_Cell_DataValidation::OPERATOR_NOTBETWEEN:
                     $operator = 0x1;
                     break;
                 case PHPExcel_Cell_DataValidation::OPERATOR_EQUAL:
                     $operator = 0x2;
                     break;
//.........这里部分代码省略.........

 /**
  * Update data validations when inserting/deleting rows/columns
  *
  * @param   PHPExcel_Worksheet  $pSheet             The worksheet that we're editing
  * @param   string              $pBefore            Insert/Delete before this cell address (e.g. 'A1')
  * @param   integer             $beforeColumnIndex  Index number of the column we're inserting/deleting before
  * @param   integer             $pNumCols           Number of columns to insert/delete (negative values indicate deletion)
  * @param   integer             $beforeRow          Number of the row we're inserting/deleting before
  * @param   integer             $pNumRows           Number of rows to insert/delete (negative values indicate deletion)
  */
 protected function _adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
 {
     $aDataValidationCollection = $pSheet->getDataValidationCollection();
     $pNumCols > 0 || $pNumRows > 0 ? uksort($aDataValidationCollection, array('PHPExcel_ReferenceHelper', 'cellReverseSort')) : uksort($aDataValidationCollection, array('PHPExcel_ReferenceHelper', 'cellSort'));
     foreach ($aDataValidationCollection as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setDataValidation($newReference, $value);
             $pSheet->setDataValidation($key, null);
         }
     }
 }

 /**
  * Write DataValidations
  *
  * @param	PHPExcel_Shared_XMLWriter			$objWriter		XML Writer
  * @param	PHPExcel_Worksheet					$pSheet			Worksheet
  * @throws	Exception
  */
 private function _writeDataValidations(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
 {
     // Datavalidation collection
     $dataValidationCollection = $pSheet->getDataValidationCollection();
     // Write data validations?
     if (count($dataValidationCollection) > 0) {
         $objWriter->startElement('dataValidations');
         $objWriter->writeAttribute('count', count($dataValidationCollection));
         foreach ($dataValidationCollection as $coordinate => $dv) {
             $objWriter->startElement('dataValidation');
             if ($dv->getType() != '') {
                 $objWriter->writeAttribute('type', $dv->getType());
             }
             if ($dv->getErrorStyle() != '') {
                 $objWriter->writeAttribute('errorStyle', $dv->getErrorStyle());
             }
             if ($dv->getOperator() != '') {
                 $objWriter->writeAttribute('operator', $dv->getOperator());
             }
             $objWriter->writeAttribute('allowBlank', $dv->getAllowBlank() ? '1' : '0');
             $objWriter->writeAttribute('showDropDown', !$dv->getShowDropDown() ? '1' : '0');
             $objWriter->writeAttribute('showInputMessage', $dv->getShowInputMessage() ? '1' : '0');
             $objWriter->writeAttribute('showErrorMessage', $dv->getShowErrorMessage() ? '1' : '0');
             if ($dv->getErrorTitle() !== '') {
                 $objWriter->writeAttribute('errorTitle', $dv->getErrorTitle());
             }
             if ($dv->getError() !== '') {
                 $objWriter->writeAttribute('error', $dv->getError());
             }
             if ($dv->getPromptTitle() !== '') {
                 $objWriter->writeAttribute('promptTitle', $dv->getPromptTitle());
             }
             if ($dv->getPrompt() !== '') {
                 $objWriter->writeAttribute('prompt', $dv->getPrompt());
             }
             $objWriter->writeAttribute('sqref', $coordinate);
             if ($dv->getFormula1() !== '') {
                 $objWriter->writeElement('formula1', $dv->getFormula1());
             }
             if ($dv->getFormula2() !== '') {
                 $objWriter->writeElement('formula2', $dv->getFormula2());
             }
             $objWriter->endElement();
         }
         $objWriter->endElement();
     }
 }

//.........这里部分代码省略.........
             $newDimension->setOutlineLevel($copyDimension->getOutlineLevel());
             $newDimension->setCollapsed($copyDimension->getCollapsed());
         }
     }
     // Update worksheet: breaks
     $aBreaks = array_reverse($pSheet->getBreaks(), true);
     foreach ($aBreaks as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setBreak($newReference, $value);
             $pSheet->setBreak($key, PHPExcel_Worksheet::BREAK_NONE);
         }
     }
     // Update worksheet: comments
     $aComments = $pSheet->getComments();
     $aNewComments = array();
     // the new array of all comments
     foreach ($aComments as $key => &$value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         $aNewComments[$newReference] = $value;
     }
     $pSheet->setComments($aNewComments);
     // replace the comments array
     // Update worksheet: hyperlinks
     $aHyperlinkCollection = array_reverse($pSheet->getHyperlinkCollection(), true);
     foreach ($aHyperlinkCollection as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setHyperlink($newReference, $value);
             $pSheet->setHyperlink($key, null);
         }
     }
     // Update worksheet: data validations
     $aDataValidationCollection = array_reverse($pSheet->getDataValidationCollection(), true);
     foreach ($aDataValidationCollection as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->setDataValidation($newReference, $value);
             $pSheet->setDataValidation($key, null);
         }
     }
     // Update worksheet: merge cells
     $aMergeCells = $pSheet->getMergeCells();
     $aNewMergeCells = array();
     // the new array of all merge cells
     foreach ($aMergeCells as $key => &$value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         $aNewMergeCells[$newReference] = $newReference;
     }
     $pSheet->setMergeCells($aNewMergeCells);
     // replace the merge cells array
     // Update worksheet: protected cells
     $aProtectedCells = array_reverse($pSheet->getProtectedCells(), true);
     foreach ($aProtectedCells as $key => $value) {
         $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
         if ($key != $newReference) {
             $pSheet->protectCells($newReference, $value, true);
             $pSheet->unprotectCells($key);
         }
     }
     // Update worksheet: autofilter
     $autoFilter = $pSheet->getAutoFilter();
     $autoFilterRange = $autoFilter->getRange();
     if (!empty($autoFilterRange)) {
         if ($pNumCols != 0) {
             $autoFilterColumns = array_keys($autoFilter->getColumns());