PHP Cell::splitRange方法代码示例

 /**
  * Extract range values
  *
  * @param    string       &$pRange    String based range representation
  * @param    Worksheet    $pSheet        Worksheet
  * @return   mixed        Array of values in range if range contains more than one element. Otherwise, a single value is returned.
  * @param    boolean      $resetLog    Flag indicating whether calculation log should be reset or not
  * @throws   Calculation\Exception
  */
 public function extractNamedRange(&$pRange = 'A1', Worksheet $pSheet = null, $resetLog = true)
 {
     // Return value
     $returnValue = array();
     //        echo 'extractNamedRange('.$pRange.')<br />';
     if ($pSheet !== null) {
         $pSheetName = $pSheet->getTitle();
         //            echo 'Current sheet name is '.$pSheetName.'<br />';
         //            echo 'Range reference is '.$pRange.'<br />';
         if (strpos($pRange, '!') !== false) {
             //                echo '$pRange reference includes sheet reference', PHP_EOL;
             list($pSheetName, $pRange) = Worksheet::extractSheetTitle($pRange, true);
             //                echo 'New sheet name is '.$pSheetName, PHP_EOL;
             //                echo 'Adjusted Range reference is '.$pRange, PHP_EOL;
             $pSheet = $this->spreadsheet->getSheetByName($pSheetName);
         }
         // Named range?
         $namedRange = NamedRange::resolveRange($pRange, $pSheet);
         if ($namedRange !== null) {
             $pSheet = $namedRange->getWorksheet();
             //                echo 'Named Range '.$pRange.' (';
             $pRange = $namedRange->getRange();
             $splitRange = Cell::splitRange($pRange);
             //    Convert row and column references
             if (ctype_alpha($splitRange[0][0])) {
                 $pRange = $splitRange[0][0] . '1:' . $splitRange[0][1] . $namedRange->getWorksheet()->getHighestRow();
             } elseif (ctype_digit($splitRange[0][0])) {
                 $pRange = 'A' . $splitRange[0][0] . ':' . $namedRange->getWorksheet()->getHighestColumn() . $splitRange[0][1];
             }
             //                echo $pRange.') is in sheet '.$namedRange->getWorksheet()->getTitle().'<br />';
             //                if ($pSheet->getTitle() != $namedRange->getWorksheet()->getTitle()) {
             //                    if (!$namedRange->getLocalOnly()) {
             //                        $pSheet = $namedRange->getWorksheet();
             //                    } else {
             //                        return $returnValue;
             //                    }
             //                }
         } else {
             return Calculation\Functions::REF();
         }
         // Extract range
         $aReferences = Cell::extractAllCellReferencesInRange($pRange);
         //            var_dump($aReferences);
         if (!isset($aReferences[1])) {
             //    Single cell (or single column or row) in range
             list($currentCol, $currentRow) = Cell::coordinateFromString($aReferences[0]);
             $cellValue = null;
             if ($pSheet->cellExists($aReferences[0])) {
                 $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
             } else {
                 $returnValue[$currentRow][$currentCol] = null;
             }
         } else {
             // Extract cell data for all cells in the range
             foreach ($aReferences as $reference) {
                 // Extract range
                 list($currentCol, $currentRow) = Cell::coordinateFromString($reference);
                 //                    echo 'NAMED RANGE: $currentCol='.$currentCol.' $currentRow='.$currentRow.'<br />';
                 $cellValue = null;
                 if ($pSheet->cellExists($reference)) {
                     $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
                 } else {
                     $returnValue[$currentRow][$currentCol] = null;
                 }
             }
         }
         //                print_r($returnValue);
         //            echo '<br />';
     }
     return $returnValue;
 }

 /**
  * Store the MERGEDCELLS records for all ranges of merged cells
  */
 private function _writeMergedCells()
 {
     $mergeCells = $this->_phpSheet->getMergeCells();
     $countMergeCells = count($mergeCells);
     if ($countMergeCells == 0) {
         return;
     }
     // maximum allowed number of merged cells per record
     if ($this->_BIFF_version == 0x600) {
         $maxCountMergeCellsPerRecord = 1027;
     } else {
         $maxCountMergeCellsPerRecord = 259;
     }
     // record identifier
     $record = 0xe5;
     // counter for total number of merged cells treated so far by the writer
     $i = 0;
     // counter for number of merged cells written in record currently being written
     $j = 0;
     // initialize record data
     $recordData = '';
     // loop through the merged cells
     foreach ($mergeCells as $mergeCell) {
         ++$i;
         ++$j;
         // extract the row and column indexes
         $range = Cell::splitRange($mergeCell);
         list($first, $last) = $range[0];
         list($firstColumn, $firstRow) = Cell::coordinateFromString($first);
         list($lastColumn, $lastRow) = Cell::coordinateFromString($last);
         $recordData .= pack('vvvv', $firstRow - 1, $lastRow - 1, Cell::columnIndexFromString($firstColumn) - 1, Cell::columnIndexFromString($lastColumn) - 1);
         // flush record if we have reached limit for number of merged cells, or reached final merged cell
         if ($j == $maxCountMergeCellsPerRecord or $i == $countMergeCells) {
             $recordData = pack('v', $j) . $recordData;
             $length = strlen($recordData);
             $header = pack('vv', $record, $length);
             $this->_append($header . $recordData);
             // initialize for next record, if any
             $recordData = '';
             $j = 0;
         }
     }
 }

 /**
  * Update cell range
  *
  * @param    string    $pCellRange            Cell range    (e.g. 'B2:D4', 'B:C' or '2:3')
  * @param    int        $pBefore            Insert before this one
  * @param    int        $pNumCols            Number of columns to increment
  * @param    int        $pNumRows            Number of rows to increment
  * @return    string    Updated cell range
  * @throws    Exception
  */
 private function updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
 {
     if (strpos($pCellRange, ':') !== false || strpos($pCellRange, ',') !== false) {
         // Update range
         $range = Cell::splitRange($pCellRange);
         $ic = count($range);
         for ($i = 0; $i < $ic; ++$i) {
             $jc = count($range[$i]);
             for ($j = 0; $j < $jc; ++$j) {
                 if (ctype_alpha($range[$i][$j])) {
                     $r = Cell::coordinateFromString($this->updateSingleCellReference($range[$i][$j] . '1', $pBefore, $pNumCols, $pNumRows));
                     $range[$i][$j] = $r[0];
                 } elseif (ctype_digit($range[$i][$j])) {
                     $r = Cell::coordinateFromString($this->updateSingleCellReference('A' . $range[$i][$j], $pBefore, $pNumCols, $pNumRows));
                     $range[$i][$j] = $r[1];
                 } else {
                     $range[$i][$j] = $this->updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows);
                 }
             }
         }
         // Recreate range string
         return Cell::buildRange($range);
     } else {
         throw new Exception("Only cell ranges may be passed to this method.");
     }
 }

 /**
  * Select a range of cells.
  *
  * @param 	string		$pCoordinate	Cell range, examples: 'A1', 'B2:G5', 'A:C', '3:6'
  * @throws 	Exception
  * @return Worksheet
  */
 public function setSelectedCells($pCoordinate = 'A1')
 {
     // Uppercase coordinate
     $pCoordinate = strtoupper($pCoordinate);
     // Convert 'A' to 'A:A'
     $pCoordinate = preg_replace('/^([A-Z]+)$/', '${1}:${1}', $pCoordinate);
     // Convert '1' to '1:1'
     $pCoordinate = preg_replace('/^([0-9]+)$/', '${1}:${1}', $pCoordinate);
     // Convert 'A:C' to 'A1:C1048576'
     $pCoordinate = preg_replace('/^([A-Z]+):([A-Z]+)$/', '${1}1:${2}1048576', $pCoordinate);
     // Convert '1:3' to 'A1:XFD3'
     $pCoordinate = preg_replace('/^([0-9]+):([0-9]+)$/', 'A${1}:XFD${2}', $pCoordinate);
     if (strpos($pCoordinate, ':') !== false || strpos($pCoordinate, ',') !== false) {
         list($first, ) = Cell::splitRange($pCoordinate);
         $this->_activeCell = $first[0];
     } else {
         $this->_activeCell = $pCoordinate;
     }
     $this->_selectedCells = $pCoordinate;
     return $this;
 }

 /**
  * Update cell range
  *
  * @param	string	$pCellRange			Cell range
  * @param	int		$pBefore			Insert before this one
  * @param	int		$pNumCols			Number of columns to increment
  * @param	int		$pNumRows			Number of rows to increment
  * @return	string	Updated cell range
  * @throws	Exception
  */
 private function _updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
 {
     if (strpos($pCellRange, ':') !== false || strpos($pCellRange, ',') !== false) {
         // Update range
         $range = Cell::splitRange($pCellRange);
         for ($i = 0; $i < count($range); ++$i) {
             for ($j = 0; $j < count($range[$i]); ++$j) {
                 $range[$i][$j] = $this->_updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows);
             }
         }
         // Recreate range string
         return Cell::buildRange($range);
     } else {
         throw new Exception("Only cell ranges may be passed to this method.");
     }
 }

 /**
  * Extract all cell references in range
  *
  * @param 	string 	$pRange		Range (e.g. A1 or A1:A10 or A1:A10 A100:A1000)
  * @return 	array	Array containing single cell references
  */
 public static function extractAllCellReferencesInRange($pRange = 'A1')
 {
     // Returnvalue
     $returnValue = array();
     // Explode spaces
     $aExplodeSpaces = explode(' ', str_replace('$', '', strtoupper($pRange)));
     foreach ($aExplodeSpaces as $explodedSpaces) {
         // Single cell?
         if (strpos($explodedSpaces, ':') === false && strpos($explodedSpaces, ',') === false) {
             $col = 'A';
             $row = 1;
             list($col, $row) = Cell::coordinateFromString($explodedSpaces);
             if (strlen($col) <= 2) {
                 $returnValue[] = $explodedSpaces;
             }
             continue;
         }
         // Range...
         $range = Cell::splitRange($explodedSpaces);
         for ($i = 0; $i < count($range); ++$i) {
             // Single cell?
             if (count($range[$i]) == 1) {
                 $col = 'A';
                 $row = 1;
                 list($col, $row) = Cell::coordinateFromString($range[$i]);
                 if (strlen($col) <= 2) {
                     $returnValue[] = $explodedSpaces;
                 }
             }
             // Range...
             $rangeStart = $rangeEnd = '';
             $startingCol = $startingRow = $endingCol = $endingRow = 0;
             list($rangeStart, $rangeEnd) = $range[$i];
             list($startingCol, $startingRow) = Cell::coordinateFromString($rangeStart);
             list($endingCol, $endingRow) = Cell::coordinateFromString($rangeEnd);
             // Conversions...
             $startingCol = Cell::columnIndexFromString($startingCol);
             $endingCol = Cell::columnIndexFromString($endingCol);
             // Current data
             $currentCol = --$startingCol;
             $currentRow = $startingRow;
             // Loop cells
             while ($currentCol < $endingCol) {
                 $loopColumn = Cell::stringFromColumnIndex($currentCol);
                 while ($currentRow <= $endingRow) {
                     $returnValue[] = $loopColumn . $currentRow;
                     ++$currentRow;
                 }
                 ++$currentCol;
                 $currentRow = $startingRow;
             }
         }
     }
     // Return value
     return $returnValue;
 }

 /**
  * Write Defined Name for PrintTitles
  *
  * @param 	Shared_XMLWriter	$objWriter 		XML Writer
  * @param 	Worksheet			$pSheet
  * @param 	int							$pSheetId
  * @throws 	Exception
  */
 private function _writeDefinedNameForPrintArea(Shared_XMLWriter $objWriter = null, Worksheet $pSheet = null, $pSheetId = 0)
 {
     // definedName for PrintArea
     if ($pSheet->getPageSetup()->isPrintAreaSet()) {
         $objWriter->startElement('definedName');
         $objWriter->writeAttribute('name', '_xlnm.Print_Area');
         $objWriter->writeAttribute('localSheetId', $pSheetId);
         // Setting string
         $settingString = '';
         // Print area
         $printArea = Cell::splitRange($pSheet->getPageSetup()->getPrintArea());
         $chunks = array();
         foreach ($printArea as $printAreaRect) {
             $printAreaRect[0] = Cell::absoluteCoordinate($printAreaRect[0]);
             $printAreaRect[1] = Cell::absoluteCoordinate($printAreaRect[1]);
             $chunks[] = '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . implode(':', $printAreaRect);
         }
         $objWriter->writeRaw(implode(',', $chunks));
         $objWriter->endElement();
     }
 }

 /**
  * Calculate information about HTML colspan and rowspan which is not always the same as Excel's
  */
 private function _calculateSpans()
 {
     // Identify all cells that should be omitted in HTML due to cell merge.
     // In HTML only the upper-left cell should be written and it should have
     //   appropriate rowspan / colspan attribute
     $sheetIndexes = $this->_sheetIndex !== null ? array($this->_sheetIndex) : range(0, $this->_phpExcel->getSheetCount() - 1);
     foreach ($sheetIndexes as $sheetIndex) {
         $sheet = $this->_phpExcel->getSheet($sheetIndex);
         $candidateSpannedRow = array();
         // loop through all Excel merged cells
         foreach ($sheet->getMergeCells() as $cells) {
             list($cells, ) = Cell::splitRange($cells);
             $first = $cells[0];
             $last = $cells[1];
             list($fc, $fr) = Cell::coordinateFromString($first);
             $fc = Cell::columnIndexFromString($fc) - 1;
             list($lc, $lr) = Cell::coordinateFromString($last);
             $lc = Cell::columnIndexFromString($lc) - 1;
             // loop through the individual cells in the individual merge
             for ($r = $fr; $r <= $lr; ++$r) {
                 // also, flag this row as a HTML row that is candidate to be omitted
                 $candidateSpannedRow[$r] = $r;
                 for ($c = $fc; $c <= $lc; ++$c) {
                     if (!($c == $fc && $r == $fr)) {
                         // not the upper-left cell (should not be written in HTML)
                         $this->_isSpannedCell[$sheetIndex][$r][$c] = array('baseCell' => array($fr, $fc));
                     } else {
                         // upper-left is the base cell that should hold the colspan/rowspan attribute
                         $this->_isBaseCell[$sheetIndex][$r][$c] = array('xlrowspan' => $lr - $fr + 1, 'rowspan' => $lr - $fr + 1, 'xlcolspan' => $lc - $fc + 1, 'colspan' => $lc - $fc + 1);
                     }
                 }
             }
         }
         // Identify which rows should be omitted in HTML. These are the rows where all the cells
         //   participate in a merge and the where base cells are somewhere above.
         $countColumns = Cell::columnIndexFromString($sheet->getHighestColumn());
         foreach ($candidateSpannedRow as $rowIndex) {
             if (isset($this->_isSpannedCell[$sheetIndex][$rowIndex])) {
                 if (count($this->_isSpannedCell[$sheetIndex][$rowIndex]) == $countColumns) {
                     $this->_isSpannedRow[$sheetIndex][$rowIndex] = $rowIndex;
                 }
             }
         }
         // For each of the omitted rows we found above, the affected rowspans should be subtracted by 1
         if (isset($this->_isSpannedRow[$sheetIndex])) {
             foreach ($this->_isSpannedRow[$sheetIndex] as $rowIndex) {
                 $adjustedBaseCells = array();
                 for ($c = 0; $c < $countColumns; ++$c) {
                     $baseCell = $this->_isSpannedCell[$sheetIndex][$rowIndex][$c]['baseCell'];
                     if (!in_array($baseCell, $adjustedBaseCells)) {
                         // subtract rowspan by 1
                         --$this->_isBaseCell[$sheetIndex][$baseCell[0]][$baseCell[1]]['rowspan'];
                         $adjustedBaseCells[] = $baseCell;
                     }
                 }
             }
         }
         // TODO: Same for columns
     }
     // We have calculated the spans
     $this->_spansAreCalculated = true;
 }

 /**
  *    Is this cell the master (top left cell) in a merge range (that holds the actual data value)
  *
  *    @return boolean
  */
 public function isMergeRangeValueCell()
 {
     if ($mergeRange = $this->getMergeRange()) {
         $mergeRange = Cell::splitRange($mergeRange);
         list($startCell) = $mergeRange[0];
         if ($this->getCoordinate() === $startCell) {
             return true;
         }
     }
     return false;
 }

 /**
  * 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 = Cell::splitRange($namedRange->getRange());
             for ($i = 0; $i < count($range); $i++) {
                 $range[$i][0] = '\'' . str_replace("'", "''", $namedRange->getWorksheet()->getTitle()) . '\'!' . Cell::absoluteCoordinate($range[$i][0]);
                 if (isset($range[$i][1])) {
                     $range[$i][1] = Cell::absoluteCoordinate($range[$i][1]);
                 }
             }
             $range = 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 (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 = Cell::columnIndexFromString($repeat[0]) - 1;
             $colmax = 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
         } else {
             if ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) {
                 // Columns to repeat
                 if ($sheetSetup->isColumnsToRepeatAtLeftSet()) {
                     $repeat = $sheetSetup->getColumnsToRepeatAtLeft();
                     $colmin = Cell::columnIndexFromString($repeat[0]) - 1;
                     $colmax = 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
//.........这里部分代码省略.........