PHP Cell::coordinateFromString方法代码示例

 /**
  * 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.
  * @throws	Exception
  */
 public function extractNamedRange(&$pRange = 'A1', Worksheet $pSheet = null, $resetLog = true)
 {
     // Return value
     $returnValue = array();
     //		echo 'extractNamedRange('.$pRange.')<br />';
     if (!is_null($pSheet)) {
         //			echo 'Current sheet name is '.$pSheet->getTitle().'<br />';
         //			echo 'Range reference is '.$pRange.'<br />';
         if (strpos($pRange, '!') !== false) {
             //				echo '$pRange reference includes sheet reference<br />';
             $worksheetReference = Worksheet::extractSheetTitle($pRange, true);
             $pSheet = $pSheet->getParent()->getSheetByName($worksheetReference[0]);
             //				echo 'New sheet name is '.$pSheet->getTitle().'<br />';
             $pRange = $worksheetReference[1];
             //				echo 'Adjusted Range reference is '.$pRange.'<br />';
         }
         // Named range?
         $namedRange = NamedRange::resolveRange($pRange, $pSheet);
         if (!is_null($namedRange)) {
             $pSheet = $namedRange->getWorksheet();
             ////			echo 'Named Range '.$pRange.' (';
             $pRange = $namedRange->getRange();
             ////				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);
         if (count($aReferences) == 1) {
             list($currentCol, $currentRow) = Cell::coordinateFromString($aReferences[0]);
             if ($pSheet->cellExists($aReferences[0])) {
                 $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
             } else {
                 $returnValue[$currentRow][$currentCol] = NULL;
             }
         } else {
             // Extract cell data
             foreach ($aReferences as $reference) {
                 // Extract range
                 list($currentCol, $currentRow) = Cell::coordinateFromString($reference);
                 //					echo 'NAMED RANGE: $currentCol='.$currentCol.' $currentRow='.$currentRow.'<br />';
                 if ($pSheet->cellExists($reference)) {
                     $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
                 } else {
                     $returnValue[$currentRow][$currentCol] = NULL;
                 }
             }
         }
         //				print_r($returnValue);
         //			echo '<br />';
     }
     // Return
     return $returnValue;
 }

 /**
  * Update single cell reference
  *
  * @param    string    $pCellReference        Single cell reference
  * @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 reference
  * @throws    Exception
  */
 private function updateSingleCellReference($pCellReference = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
 {
     if (strpos($pCellReference, ':') === false && strpos($pCellReference, ',') === false) {
         // Get coordinates of $pBefore
         list($beforeColumn, $beforeRow) = Cell::coordinateFromString($pBefore);
         // Get coordinates of $pCellReference
         list($newColumn, $newRow) = Cell::coordinateFromString($pCellReference);
         // Verify which parts should be updated
         $updateColumn = $newColumn[0] != '$' && $beforeColumn[0] != '$' && Cell::columnIndexFromString($newColumn) >= Cell::columnIndexFromString($beforeColumn);
         $updateRow = $newRow[0] != '$' && $beforeRow[0] != '$' && $newRow >= $beforeRow;
         // Create new column reference
         if ($updateColumn) {
             $newColumn = Cell::stringFromColumnIndex(Cell::columnIndexFromString($newColumn) - 1 + $pNumCols);
         }
         // Create new row reference
         if ($updateRow) {
             $newRow = $newRow + $pNumRows;
         }
         // Return new reference
         return $newColumn . $newRow;
     } else {
         throw new Exception("Only single cell references may be passed to this method.");
     }
 }

 /**
  * Reads a cell range address in BIFF8 for shared formulas. Uses positive and negative values for row and column
  * to indicate offsets from a base cell
  * section 3.3.4
  *
  * @param string $subData
  * @param string $baseCell Base cell
  * @return string Cell range address
  */
 private function _readBIFF8CellRangeAddressB($subData, $baseCell = 'A1')
 {
     list($baseCol, $baseRow) = Cell::coordinateFromString($baseCell);
     $baseCol = Cell::columnIndexFromString($baseCol) - 1;
     // TODO: if cell range is just a single cell, should this funciton
     // not just return e.g. 'A1' and not 'A1:A1' ?
     // offset: 0; size: 2; first row
     $frIndex = $this->_GetInt2d($subData, 0);
     // adjust below
     // offset: 2; size: 2; relative index to first row (0... 65535) should be treated as offset (-32768... 32767)
     $lrIndex = $this->_GetInt2d($subData, 2);
     // adjust below
     // offset: 4; size: 2; first column with relative/absolute flags
     // bit: 7-0; mask 0x00FF; column index
     $fcIndex = 0xff & $this->_GetInt2d($subData, 4);
     // bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
     if (!(0x4000 & $this->_GetInt2d($subData, 4))) {
         // absolute column index
         $fc = Cell::stringFromColumnIndex($fcIndex);
         $fc = '$' . $fc;
     } else {
         // column offset
         $fcIndex = $fcIndex <= 127 ? $fcIndex : $fcIndex - 256;
         $fc = Cell::stringFromColumnIndex($baseCol + $fcIndex);
     }
     // bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
     if (!(0x8000 & $this->_GetInt2d($subData, 4))) {
         // absolute row index
         $fr = $frIndex + 1;
         $fr = '$' . $fr;
     } else {
         // row offset
         $frIndex = $frIndex <= 32767 ? $frIndex : $frIndex - 65536;
         $fr = $baseRow + $frIndex;
     }
     // offset: 6; size: 2; last column with relative/absolute flags
     // bit: 7-0; mask 0x00FF; column index
     $lcIndex = 0xff & $this->_GetInt2d($subData, 6);
     $lcIndex = $lcIndex <= 127 ? $lcIndex : $lcIndex - 256;
     $lc = Cell::stringFromColumnIndex($baseCol + $lcIndex);
     // bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
     if (!(0x4000 & $this->_GetInt2d($subData, 6))) {
         // absolute column index
         $lc = Cell::stringFromColumnIndex($lcIndex);
         $lc = '$' . $lc;
     } else {
         // column offset
         $lcIndex = $lcIndex <= 127 ? $lcIndex : $lcIndex - 256;
         $lc = Cell::stringFromColumnIndex($baseCol + $lcIndex);
     }
     // bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
     if (!(0x8000 & $this->_GetInt2d($subData, 6))) {
         // absolute row index
         $lr = $lrIndex + 1;
         $lr = '$' . $lr;
     } else {
         // row offset
         $lrIndex = $lrIndex <= 32767 ? $lrIndex : $lrIndex - 65536;
         $lr = $baseRow + $lrIndex;
     }
     return "{$fc}{$fr}:{$lc}{$lr}";
 }

 /**
  * Write the HORIZONTALPAGEBREAKS and VERTICALPAGEBREAKS BIFF records.
  */
 private function _writeBreaks()
 {
     // initialize
     $vbreaks = array();
     $hbreaks = array();
     foreach ($this->_phpSheet->getBreaks() as $cell => $breakType) {
         // Fetch coordinates
         $coordinates = Cell::coordinateFromString($cell);
         // Decide what to do by the type of break
         switch ($breakType) {
             case Worksheet::BREAK_COLUMN:
                 // Add to list of vertical breaks
                 $vbreaks[] = Cell::columnIndexFromString($coordinates[0]) - 1;
                 break;
             case Worksheet::BREAK_ROW:
                 // Add to list of horizontal breaks
                 $hbreaks[] = $coordinates[1];
                 break;
             case Worksheet::BREAK_NONE:
             default:
                 // Nothing to do
                 break;
         }
     }
     //horizontal page breaks
     if (count($hbreaks) > 0) {
         // Sort and filter array of page breaks
         sort($hbreaks, SORT_NUMERIC);
         if ($hbreaks[0] == 0) {
             // don't use first break if it's 0
             array_shift($hbreaks);
         }
         $record = 0x1b;
         // Record identifier
         $cbrk = count($hbreaks);
         // Number of page breaks
         if ($this->_BIFF_version == 0x600) {
             $length = 2 + 6 * $cbrk;
             // Bytes to follow
         } else {
             $length = 2 + 2 * $cbrk;
             // Bytes to follow
         }
         $header = pack("vv", $record, $length);
         $data = pack("v", $cbrk);
         // Append each page break
         foreach ($hbreaks as $hbreak) {
             if ($this->_BIFF_version == 0x600) {
                 $data .= pack("vvv", $hbreak, 0x0, 0xff);
             } else {
                 $data .= pack("v", $hbreak);
             }
         }
         $this->_append($header . $data);
     }
     // vertical page breaks
     if (count($vbreaks) > 0) {
         // 1000 vertical pagebreaks appears to be an internal Excel 5 limit.
         // It is slightly higher in Excel 97/200, approx. 1026
         $vbreaks = array_slice($vbreaks, 0, 1000);
         // Sort and filter array of page breaks
         sort($vbreaks, SORT_NUMERIC);
         if ($vbreaks[0] == 0) {
             // don't use first break if it's 0
             array_shift($vbreaks);
         }
         $record = 0x1a;
         // Record identifier
         $cbrk = count($vbreaks);
         // Number of page breaks
         if ($this->_BIFF_version == 0x600) {
             $length = 2 + 6 * $cbrk;
             // Bytes to follow
         } else {
             $length = 2 + 2 * $cbrk;
             // Bytes to follow
         }
         $header = pack("vv", $record, $length);
         $data = pack("v", $cbrk);
         // Append each page break
         foreach ($vbreaks as $vbreak) {
             if ($this->_BIFF_version == 0x600) {
                 $data .= pack("vvv", $vbreak, 0x0, 0xffff);
             } else {
                 $data .= pack("v", $vbreak);
             }
         }
         $this->_append($header . $data);
     }
 }

 /**
  * Apply styles from array
  *
  * <code>
  * $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray(
  *         array(
  *             'font'    => array(
  *                 'name'      => 'Arial',
  *                 'bold'      => true,
  *                 'italic'    => false,
  *                 'underline' => \PHPExcel\Style\Font::UNDERLINE_DOUBLE,
  *                 'strike'    => false,
  *                 'color'     => array(
  *                     'rgb' => '808080'
  *                 )
  *             ),
  *             'borders' => array(
  *                 'bottom'     => array(
  *                     'style' => \PHPExcel\Style\Border::BORDER_DASHDOT,
  *                     'color' => array(
  *                         'rgb' => '808080'
  *                     )
  *                 ),
  *                 'top'     => array(
  *                     'style' => \PHPExcel\Style\Border::BORDER_DASHDOT,
  *                     'color' => array(
  *                         'rgb' => '808080'
  *                     )
  *                 )
  *             ),
  *             'quotePrefix'    => true
  *         )
  * );
  * </code>
  *
  * @param   array    $pStyles    Array containing style information
  * @param   boolean        $pAdvanced    Advanced mode for setting borders.
  * @throws  Exception
  * @return Style
  */
 public function applyFromArray($pStyles = null, $pAdvanced = true)
 {
     if (is_array($pStyles)) {
         if ($this->isSupervisor) {
             $pRange = $this->getSelectedCells();
             // Uppercase coordinate
             $pRange = strtoupper($pRange);
             // Is it a cell range or a single cell?
             if (strpos($pRange, ':') === false) {
                 $rangeA = $pRange;
                 $rangeB = $pRange;
             } else {
                 list($rangeA, $rangeB) = explode(':', $pRange);
             }
             // Calculate range outer borders
             $rangeStart = Cell::coordinateFromString($rangeA);
             $rangeEnd = Cell::coordinateFromString($rangeB);
             // Translate column into index
             $rangeStart[0] = Cell::columnIndexFromString($rangeStart[0]) - 1;
             $rangeEnd[0] = Cell::columnIndexFromString($rangeEnd[0]) - 1;
             // Make sure we can loop upwards on rows and columns
             if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) {
                 $tmp = $rangeStart;
                 $rangeStart = $rangeEnd;
                 $rangeEnd = $tmp;
             }
             // ADVANCED MODE:
             if ($pAdvanced && isset($pStyles['borders'])) {
                 // 'allborders' is a shorthand property for 'outline' and 'inside' and
                 //        it applies to components that have not been set explicitly
                 if (isset($pStyles['borders']['allborders'])) {
                     foreach (array('outline', 'inside') as $component) {
                         if (!isset($pStyles['borders'][$component])) {
                             $pStyles['borders'][$component] = $pStyles['borders']['allborders'];
                         }
                     }
                     unset($pStyles['borders']['allborders']);
                     // not needed any more
                 }
                 // 'outline' is a shorthand property for 'top', 'right', 'bottom', 'left'
                 //        it applies to components that have not been set explicitly
                 if (isset($pStyles['borders']['outline'])) {
                     foreach (array('top', 'right', 'bottom', 'left') as $component) {
                         if (!isset($pStyles['borders'][$component])) {
                             $pStyles['borders'][$component] = $pStyles['borders']['outline'];
                         }
                     }
                     unset($pStyles['borders']['outline']);
                     // not needed any more
                 }
                 // 'inside' is a shorthand property for 'vertical' and 'horizontal'
                 //        it applies to components that have not been set explicitly
                 if (isset($pStyles['borders']['inside'])) {
                     foreach (array('vertical', 'horizontal') as $component) {
                         if (!isset($pStyles['borders'][$component])) {
                             $pStyles['borders'][$component] = $pStyles['borders']['inside'];
                         }
                     }
                     unset($pStyles['borders']['inside']);
                     // not needed any more
//.........这里部分代码省略.........

//.........这里部分代码省略.........
                 $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
             $printArea = Cell::splitRange($sheetSetup->getPrintArea());
             $countPrintArea = count($printArea);
             $formulaData = '';
             for ($j = 0; $j < $countPrintArea; ++$j) {
                 $printAreaRect = $printArea[$j];
                 // e.g. A3:J6
                 $printAreaRect[0] = Cell::coordinateFromString($printAreaRect[0]);
                 $printAreaRect[1] = Cell::coordinateFromString($printAreaRect[1]);
                 $print_rowmin = $printAreaRect[0][1] - 1;
                 $print_rowmax = $printAreaRect[1][1] - 1;
                 $print_colmin = Cell::columnIndexFromString($printAreaRect[0][0]) - 1;
                 $print_colmax = 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));
         }
     }
     return $chunk;
 }

 /**
  * Write drawings to XML format
  *
  * @param 	Shared_XMLWriter			$objWriter 		XML Writer
  * @param 	Worksheet_BaseDrawing		$pDrawing
  * @param 	int									$pRelationId
  * @throws 	Exception
  */
 public function _writeDrawing(Shared_XMLWriter $objWriter = null, Worksheet_BaseDrawing $pDrawing = null, $pRelationId = -1)
 {
     if ($pRelationId >= 0) {
         // xdr:oneCellAnchor
         $objWriter->startElement('xdr:oneCellAnchor');
         // Image location
         $aCoordinates = Cell::coordinateFromString($pDrawing->getCoordinates());
         $aCoordinates[0] = Cell::columnIndexFromString($aCoordinates[0]);
         // xdr:from
         $objWriter->startElement('xdr:from');
         $objWriter->writeElement('xdr:col', $aCoordinates[0] - 1);
         $objWriter->writeElement('xdr:colOff', Shared_Drawing::pixelsToEMU($pDrawing->getOffsetX()));
         $objWriter->writeElement('xdr:row', $aCoordinates[1] - 1);
         $objWriter->writeElement('xdr:rowOff', Shared_Drawing::pixelsToEMU($pDrawing->getOffsetY()));
         $objWriter->endElement();
         // xdr:ext
         $objWriter->startElement('xdr:ext');
         $objWriter->writeAttribute('cx', Shared_Drawing::pixelsToEMU($pDrawing->getWidth()));
         $objWriter->writeAttribute('cy', Shared_Drawing::pixelsToEMU($pDrawing->getHeight()));
         $objWriter->endElement();
         // xdr:pic
         $objWriter->startElement('xdr:pic');
         // xdr:nvPicPr
         $objWriter->startElement('xdr:nvPicPr');
         // xdr:cNvPr
         $objWriter->startElement('xdr:cNvPr');
         $objWriter->writeAttribute('id', $pRelationId);
         $objWriter->writeAttribute('name', $pDrawing->getName());
         $objWriter->writeAttribute('descr', $pDrawing->getDescription());
         $objWriter->endElement();
         // xdr:cNvPicPr
         $objWriter->startElement('xdr:cNvPicPr');
         // a:picLocks
         $objWriter->startElement('a:picLocks');
         $objWriter->writeAttribute('noChangeAspect', '1');
         $objWriter->endElement();
         $objWriter->endElement();
         $objWriter->endElement();
         // xdr:blipFill
         $objWriter->startElement('xdr:blipFill');
         // a:blip
         $objWriter->startElement('a:blip');
         $objWriter->writeAttribute('xmlns:r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships');
         $objWriter->writeAttribute('r:embed', 'rId' . $pRelationId);
         $objWriter->endElement();
         // a:stretch
         $objWriter->startElement('a:stretch');
         $objWriter->writeElement('a:fillRect', null);
         $objWriter->endElement();
         $objWriter->endElement();
         // xdr:spPr
         $objWriter->startElement('xdr:spPr');
         // a:xfrm
         $objWriter->startElement('a:xfrm');
         $objWriter->writeAttribute('rot', Shared_Drawing::degreesToAngle($pDrawing->getRotation()));
         $objWriter->endElement();
         // a:prstGeom
         $objWriter->startElement('a:prstGeom');
         $objWriter->writeAttribute('prst', 'rect');
         // a:avLst
         $objWriter->writeElement('a:avLst', null);
         $objWriter->endElement();
         //						// a:solidFill
         //						$objWriter->startElement('a:solidFill');
         //							// a:srgbClr
         //							$objWriter->startElement('a:srgbClr');
         //							$objWriter->writeAttribute('val', 'FFFFFF');
         ///* SHADE
         //								// a:shade
         //								$objWriter->startElement('a:shade');
         //								$objWriter->writeAttribute('val', '85000');
         //								$objWriter->endElement();
         //*/
         //							$objWriter->endElement();
         //						$objWriter->endElement();
         /*
         						// a:ln
         						$objWriter->startElement('a:ln');
         						$objWriter->writeAttribute('w', '88900');
         						$objWriter->writeAttribute('cap', 'sq');
         
         							// a:solidFill
         							$objWriter->startElement('a:solidFill');
         
         								// a:srgbClr
         								$objWriter->startElement('a:srgbClr');
         								$objWriter->writeAttribute('val', 'FFFFFF');
         								$objWriter->endElement();
         
         							$objWriter->endElement();
         
         							// a:miter
//.........这里部分代码省略.........

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

 /**
  * Create array from worksheet
  *
  * @param mixed $nullValue Value treated as "null"
  * @param boolean $calculateFormulas Should formulas be calculated?
  * @return array
  */
 public function toArray($nullValue = null, $calculateFormulas = true)
 {
     // Returnvalue
     $returnValue = array();
     // Garbage collect...
     $this->garbageCollect();
     // Get worksheet dimension
     $dimension = explode(':', $this->calculateWorksheetDimension());
     $dimension[0] = Cell::coordinateFromString($dimension[0]);
     $dimension[0][0] = Cell::columnIndexFromString($dimension[0][0]) - 1;
     $dimension[1] = Cell::coordinateFromString($dimension[1]);
     $dimension[1][0] = Cell::columnIndexFromString($dimension[1][0]) - 1;
     // Loop through cells
     for ($row = $dimension[0][1]; $row <= $dimension[1][1]; ++$row) {
         for ($column = $dimension[0][0]; $column <= $dimension[1][0]; ++$column) {
             // Cell exists?
             if ($this->cellExistsByColumnAndRow($column, $row)) {
                 $cell = $this->getCellByColumnAndRow($column, $row);
                 if ($cell->getValue() instanceof RichText) {
                     $returnValue[$row][$column] = $cell->getValue()->getPlainText();
                 } else {
                     if ($calculateFormulas) {
                         $returnValue[$row][$column] = $cell->getCalculatedValue();
                     } else {
                         $returnValue[$row][$column] = $cell->getValue();
                     }
                 }
                 $style = $this->_parent->getCellXfByIndex($cell->getXfIndex());
                 $returnValue[$row][$column] = Style_NumberFormat::toFormattedString($returnValue[$row][$column], $style->getNumberFormat()->getFormatCode());
             } else {
                 $returnValue[$row][$column] = $nullValue;
             }
         }
     }
     // Return
     return $returnValue;
 }

 /**
  * Convert 1-cell anchor coordinates to 2-cell anchor coordinates
  * This function is ported from PEAR Spreadsheet_Writer_Excel with small modifications
  *
  * Calculate the vertices that define the position of the image as required by
  * the OBJ record.
  *
  *		 +------------+------------+
  *		 |	 A	  |	  B	 |
  *   +-----+------------+------------+
  *   |	 |(x1,y1)	 |			|
  *   |  1  |(A1)._______|______	  |
  *   |	 |	|			  |	 |
  *   |	 |	|			  |	 |
  *   +-----+----|	BITMAP	|-----+
  *   |	 |	|			  |	 |
  *   |  2  |	|______________.	 |
  *   |	 |			|		(B2)|
  *   |	 |			|	 (x2,y2)|
  *   +---- +------------+------------+
  *
  * Example of a bitmap that covers some of the area from cell A1 to cell B2.
  *
  * Based on the width and height of the bitmap we need to calculate 8 vars:
  *	 $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
  * The width and height of the cells are also variable and have to be taken into
  * account.
  * The values of $col_start and $row_start are passed in from the calling
  * function. The values of $col_end and $row_end are calculated by subtracting
  * the width and height of the bitmap from the width and height of the
  * underlying cells.
  * The vertices are expressed as a percentage of the underlying cell width as
  * follows (rhs values are in pixels):
  *
  *	   x1 = X / W *1024
  *	   y1 = Y / H *256
  *	   x2 = (X-1) / W *1024
  *	   y2 = (Y-1) / H *256
  *
  *	   Where:  X is distance from the left side of the underlying cell
  *			   Y is distance from the top of the underlying cell
  *			   W is the width of the cell
  *			   H is the height of the cell
  *
  * @param Worksheet $sheet
  * @param string $coordinates E.g. 'A1'
  * @param integer $offsetX Horizontal offset in pixels
  * @param integer $offsetY Vertical offset in pixels
  * @param integer $width Width in pixels
  * @param integer $height Height in pixels
  * @return array
  */
 public static function oneAnchor2twoAnchor($sheet, $coordinates, $offsetX, $offsetY, $width, $height)
 {
     list($column, $row) = Cell::coordinateFromString($coordinates);
     $col_start = Cell::columnIndexFromString($column) - 1;
     $row_start = $row - 1;
     $x1 = $offsetX;
     $y1 = $offsetY;
     // Initialise end cell to the same as the start cell
     $col_end = $col_start;
     // Col containing lower right corner of object
     $row_end = $row_start;
     // Row containing bottom right corner of object
     // Zero the specified offset if greater than the cell dimensions
     if ($x1 >= self::sizeCol($sheet, Cell::stringFromColumnIndex($col_start))) {
         $x1 = 0;
     }
     if ($y1 >= self::sizeRow($sheet, $row_start + 1)) {
         $y1 = 0;
     }
     $width = $width + $x1 - 1;
     $height = $height + $y1 - 1;
     // Subtract the underlying cell widths to find the end cell of the image
     while ($width >= self::sizeCol($sheet, Cell::stringFromColumnIndex($col_end))) {
         $width -= self::sizeCol($sheet, Cell::stringFromColumnIndex($col_end));
         ++$col_end;
     }
     // Subtract the underlying cell heights to find the end cell of the image
     while ($height >= self::sizeRow($sheet, $row_end + 1)) {
         $height -= self::sizeRow($sheet, $row_end + 1);
         ++$row_end;
     }
     // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
     // with zero height or width.
     if (self::sizeCol($sheet, Cell::stringFromColumnIndex($col_start)) == 0) {
         return;
     }
     if (self::sizeCol($sheet, Cell::stringFromColumnIndex($col_end)) == 0) {
         return;
     }
     if (self::sizeRow($sheet, $row_start + 1) == 0) {
         return;
     }
     if (self::sizeRow($sheet, $row_end + 1) == 0) {
         return;
     }
     // Convert the pixel values to the percentage value expected by Excel
     $x1 = $x1 / self::sizeCol($sheet, Cell::stringFromColumnIndex($col_start)) * 1024;
     $y1 = $y1 / self::sizeRow($sheet, $row_start + 1) * 256;
//.........这里部分代码省略.........

 /**
  * Write VML comment to XML format
  *
  * @param 	Shared_XMLWriter		$objWriter 			XML Writer
  * @param	string							$pCellReference		Cell reference
  * @param 	Comment				$pComment			Comment
  * @throws 	Exception
  */
 public function _writeVMLComment(Shared_XMLWriter $objWriter = null, $pCellReference = 'A1', Comment $pComment = null)
 {
     // Metadata
     list($column, $row) = Cell::coordinateFromString($pCellReference);
     $column = Cell::columnIndexFromString($column);
     $id = 1024 + $column + $row;
     $id = substr($id, 0, 4);
     // v:shape
     $objWriter->startElement('v:shape');
     $objWriter->writeAttribute('id', '_x0000_s' . $id);
     $objWriter->writeAttribute('type', '#_x0000_t202');
     $objWriter->writeAttribute('style', 'position:absolute;margin-left:' . $pComment->getMarginLeft() . ';margin-top:' . $pComment->getMarginTop() . ';width:' . $pComment->getWidth() . ';height:' . $pComment->getHeight() . ';z-index:1;visibility:' . ($pComment->getVisible() ? 'visible' : 'hidden'));
     $objWriter->writeAttribute('fillcolor', '#' . $pComment->getFillColor()->getRGB());
     $objWriter->writeAttribute('o:insetmode', 'auto');
     // v:fill
     $objWriter->startElement('v:fill');
     $objWriter->writeAttribute('color2', '#' . $pComment->getFillColor()->getRGB());
     $objWriter->endElement();
     // v:shadow
     $objWriter->startElement('v:shadow');
     $objWriter->writeAttribute('on', 't');
     $objWriter->writeAttribute('color', 'black');
     $objWriter->writeAttribute('obscured', 't');
     $objWriter->endElement();
     // v:path
     $objWriter->startElement('v:path');
     $objWriter->writeAttribute('o:connecttype', 'none');
     $objWriter->endElement();
     // v:textbox
     $objWriter->startElement('v:textbox');
     $objWriter->writeAttribute('style', 'mso-direction-alt:auto');
     // div
     $objWriter->startElement('div');
     $objWriter->writeAttribute('style', 'text-align:left');
     $objWriter->endElement();
     $objWriter->endElement();
     // x:ClientData
     $objWriter->startElement('x:ClientData');
     $objWriter->writeAttribute('ObjectType', 'Note');
     // x:MoveWithCells
     $objWriter->writeElement('x:MoveWithCells', '');
     // x:SizeWithCells
     $objWriter->writeElement('x:SizeWithCells', '');
     // x:Anchor
     //$objWriter->writeElement('x:Anchor', $column . ', 15, ' . ($row - 2) . ', 10, ' . ($column + 4) . ', 15, ' . ($row + 5) . ', 18');
     // x:AutoFill
     $objWriter->writeElement('x:AutoFill', 'False');
     // x:Row
     $objWriter->writeElement('x:Row', $row - 1);
     // x:Column
     $objWriter->writeElement('x:Column', $column - 1);
     $objWriter->endElement();
     $objWriter->endElement();
 }

 /**
  * Fill worksheet from values in array
  *
  * @param array $source Source array
  * @param mixed $nullValue Value in source array that stands for blank cell
  * @param string $startCell Insert array starting from this cell address as the top left coordinate
  * @param boolean $strictNullComparison Apply strict comparison when testing for null values in the array
  * @throws Exception
  * @return Worksheet
  */
 public function fromArray($source = null, $nullValue = null, $startCell = 'A1', $strictNullComparison = false)
 {
     if (is_array($source)) {
         //    Convert a 1-D array to 2-D (for ease of looping)
         if (!is_array(end($source))) {
             $source = array($source);
         }
         // start coordinate
         list($startColumn, $startRow) = Cell::coordinateFromString($startCell);
         // Loop through $source
         foreach ($source as $rowData) {
             $currentColumn = $startColumn;
             foreach ($rowData as $cellValue) {
                 if ($strictNullComparison) {
                     if ($cellValue !== $nullValue) {
                         // Set cell value
                         $this->getCell($currentColumn . $startRow)->setValue($cellValue);
                     }
                 } else {
                     if ($cellValue != $nullValue) {
                         // Set cell value
                         $this->getCell($currentColumn . $startRow)->setValue($cellValue);
                     }
                 }
                 ++$currentColumn;
             }
             ++$startRow;
         }
     } else {
         throw new Exception("Parameter \$source should be an array.");
     }
     return $this;
 }

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

 /**
  *	OFFSET
  *
  *	Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
  *	The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and
  *	the number of columns to be returned.
  *
  *	@param	cellAddress		The reference from which you want to base the offset. Reference must refer to a cell or
  *								range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
  *	@param	rows			The number of rows, up or down, that you want the upper-left cell to refer to.
  *								Using 5 as the rows argument specifies that the upper-left cell in the reference is
  *								five rows below reference. Rows can be positive (which means below the starting reference)
  *								or negative (which means above the starting reference).
  *	@param	cols			The number of columns, to the left or right, that you want the upper-left cell of the result
  *								to refer to. Using 5 as the cols argument specifies that the upper-left cell in the
  *								reference is five columns to the right of reference. Cols can be positive (which means
  *								to the right of the starting reference) or negative (which means to the left of the
  *								starting reference).
  *	@param	height			The height, in number of rows, that you want the returned reference to be. Height must be a positive number.
  *	@param	width			The width, in number of columns, that you want the returned reference to be. Width must be a positive number.
  *	@return	string			A reference to a cell or range of cells
  */
 public static function OFFSET($cellAddress = Null, $rows = 0, $columns = 0, $height = null, $width = null)
 {
     $rows = self::flattenSingleValue($rows);
     $columns = self::flattenSingleValue($columns);
     $height = self::flattenSingleValue($height);
     $width = self::flattenSingleValue($width);
     if ($cellAddress == Null) {
         return 0;
     }
     $args = func_get_args();
     $pCell = array_pop($args);
     if (!is_object($pCell)) {
         return self::$_errorCodes['reference'];
     }
     $sheetName = null;
     if (strpos($cellAddress, "!")) {
         list($sheetName, $cellAddress) = explode("!", $cellAddress);
     }
     if (strpos($cellAddress, ":")) {
         list($startCell, $endCell) = explode(":", $cellAddress);
     } else {
         $startCell = $endCell = $cellAddress;
     }
     list($startCellColumn, $startCellRow) = Cell::coordinateFromString($startCell);
     list($endCellColumn, $endCellRow) = Cell::coordinateFromString($endCell);
     $startCellRow += $rows;
     $startCellColumn = Cell::columnIndexFromString($startCellColumn) - 1;
     $startCellColumn += $columns;
     if ($startCellRow <= 0 || $startCellColumn < 0) {
         return self::$_errorCodes['reference'];
     }
     $endCellColumn = Cell::columnIndexFromString($endCellColumn) - 1;
     if ($width != null && !is_object($width)) {
         $endCellColumn = $startCellColumn + $width - 1;
     } else {
         $endCellColumn += $columns;
     }
     $startCellColumn = Cell::stringFromColumnIndex($startCellColumn);
     if ($height != null && !is_object($height)) {
         $endCellRow = $startCellRow + $height - 1;
     } else {
         $endCellRow += $rows;
     }
     if ($endCellRow <= 0 || $endCellColumn < 0) {
         return self::$_errorCodes['reference'];
     }
     $endCellColumn = Cell::stringFromColumnIndex($endCellColumn);
     $cellAddress = $startCellColumn . $startCellRow;
     if ($startCellColumn != $endCellColumn || $startCellRow != $endCellRow) {
         $cellAddress .= ':' . $endCellColumn . $endCellRow;
     }
     if ($sheetName !== null) {
         $pSheet = $pCell->getParent()->getParent()->getSheetByName($sheetName);
     } else {
         $pSheet = $pCell->getParent();
     }
     return Calculation::getInstance()->extractCellRange($cellAddress, $pSheet, False);
 }

//.........这里部分代码省略.........
                                 $docSheet->setPrintGridlines(true);
                             }
                             if ($xmlSheet->printOptions['horizontalCentered']) {
                                 $docSheet->getPageSetup()->setHorizontalCentered(true);
                             }
                             if ($xmlSheet->printOptions['verticalCentered']) {
                                 $docSheet->getPageSetup()->setVerticalCentered(true);
                             }
                         }
                         if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) {
                             foreach ($xmlSheet->sheetData->row as $row) {
                                 if ($row["ht"] && !$this->_readDataOnly) {
                                     $docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"]));
                                 }
                                 if ($row["hidden"] && !$this->_readDataOnly) {
                                     $docSheet->getRowDimension(intval($row["r"]))->setVisible(false);
                                 }
                                 if ($row["collapsed"]) {
                                     $docSheet->getRowDimension(intval($row["r"]))->setCollapsed(true);
                                 }
                                 if ($row["outlineLevel"] > 0) {
                                     $docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"]));
                                 }
                                 if ($row["s"] && !$this->_readDataOnly) {
                                     $docSheet->getRowDimension(intval($row["r"]))->setXfIndex(intval($row["s"]));
                                 }
                                 foreach ($row->c as $c) {
                                     $r = (string) $c["r"];
                                     $cellDataType = (string) $c["t"];
                                     $value = null;
                                     $calculatedValue = null;
                                     // Read cell?
                                     if (!is_null($this->getReadFilter())) {
                                         $coordinates = Cell::coordinateFromString($r);
                                         if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) {
                                             continue;
                                         }
                                     }
                                     //									echo '<b>Reading cell '.$coordinates[0].$coordinates[1].'</b><br />';
                                     //									print_r($c);
                                     //									echo '<br />';
                                     //									echo 'Cell Data Type is '.$cellDataType.': ';
                                     //
                                     // Read cell!
                                     switch ($cellDataType) {
                                         case "s":
                                             //											echo 'String<br />';
                                             if ((string) $c->v != '') {
                                                 $value = $sharedStrings[intval($c->v)];
                                                 if ($value instanceof RichText) {
                                                     $value = clone $value;
                                                 }
                                             } else {
                                                 $value = '';
                                             }
                                             break;
                                         case "b":
                                             //											echo 'Boolean<br />';
                                             if (!isset($c->f)) {
                                                 $value = $this->_castToBool($c);
                                             } else {
                                                 // Formula
                                                 $this->_castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, '_castToBool');
                                                 //												echo '$calculatedValue = '.$calculatedValue.'<br />';
                                             }
                                             break;