PHP PHPExcel_Cell::splitRange方法代码示例

 /**
  * @param $column
  * @param $row
  *
  * @return mixed
  */
 public function getValue($column, $row)
 {
     $cell = $this->objWorksheet->getCellByColumnAndRow($column, $row);
     // Find if this is cell is merged with others
     foreach ($this->mergedCellsRange as $currMergedRange) {
         if ($cell->isInRange($currMergedRange)) {
             $currMergedCellsArray = \PHPExcel_Cell::splitRange($currMergedRange);
             $cell = $this->objWorksheet->getCell($currMergedCellsArray[0][0]);
             break;
         }
     }
     return $cell->getValue();
 }

//.........这里部分代码省略.........
		    						$sheet->getstyle( $sheet->getCellByColumnAndRow($column, $row)->getCoordinate() )->getNumberFormat()->getFormatCode()
		    					);
		    				}
	    				}
	    			}

	    			// Style information
	    			$style = $sheet->getStyleByColumnAndRow($column, $row);

	    			// Cell width
	    			$columnDimension = $sheet->getColumnDimensionByColumn($column);
	    			if ($columnDimension->getWidth() == -1) {
	    				$columnDimension->setAutoSize(true);
	    				$sheet->calculateColumnWidths(false);
	    			}
	    			$cellWidth = PHPExcel_Shared_Drawing::pixelsToPoints(
	    				PHPExcel_Shared_Drawing::cellDimensionToPixels($columnDimension->getWidth())
	    			);

	    			// Cell height
	    			$rowDimension = $sheet->getRowDimension($row);
	    			$cellHeight = PHPExcel_Shared_Drawing::pixelsToPoints(
	    				PHPExcel_Shared_Drawing::cellDimensionToPixels($rowDimension->getRowHeight())
	    			);
	    			if ($cellHeight <= 0) {
	    				$cellHeight = $style->getFont()->getSize();
	    			}

	    			// Column span? Rowspan?
	    			$singleCellWidth = $cellWidth;
	    			$singleCellHeight = $cellHeight;
					foreach ($sheet->getMergeCells() as $cells) {
						if ($sheet->getCellByColumnAndRow($column, $row)->isInRange($cells)) {
							list($first, ) = PHPExcel_Cell::splitRange($cells);

							if ($first == $sheet->getCellByColumnAndRow($column, $row)->getCoordinate()) {
								list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells);

								$cellWidth = $cellWidth * $colSpan;
								$cellHeight = $cellHeight * $rowSpan;
							}

							break;
						}
					}

					// Cell height OK?
					if ($cellHeight < $lineHeight) {
						$cellHeight = $lineHeight;
						$singleCellHeight = $cellHeight;
					}

	    			// Font formatting
	    			$fontStyle = '';
	    			if ($style->getFont()->getBold()) {
	    				$fontStyle .= 'B';
					}
					if ($style->getFont()->getItalic()) {
						$fontStyle .= 'I';
					}
					if ($style->getFont()->getUnderline() != PHPExcel_Style_Font::UNDERLINE_NONE) {
						$fontStyle .= 'U';
					}
					$pdf->SetFont('Arial', $fontStyle, $style->getFont()->getSize());

					// Text alignment

 /**
  * Writes all the DEFINEDNAME records (BIFF8).
  * So far this is only used for repeating rows/columns (print titles) and print areas
  */
 public function writeAllDefinedNamesBiff8()
 {
     $chunk = '';
     // write the print titles (repeating rows, columns), if any
     $total_worksheets = count($this->_worksheets);
     for ($i = 0; $i < $total_worksheets; ++$i) {
         // repeatColumns / repeatRows
         if ($this->_phpExcel->getSheet($i)->getPageSetup()->isColumnsToRepeatAtLeftSet() || $this->_phpExcel->getSheet($i)->getPageSetup()->isRowsToRepeatAtTopSet()) {
             // Row and column titles have been defined
             // Columns to repeat
             if ($this->_phpExcel->getSheet($i)->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
                 $repeat = $this->_phpExcel->getSheet($i)->getPageSetup()->getColumnsToRepeatAtLeft();
                 $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
                 $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
             } else {
                 $colmin = 0;
                 $colmax = 255;
             }
             // Rows to repeat
             if ($this->_phpExcel->getSheet($i)->getPageSetup()->isRowsToRepeatAtTopSet()) {
                 $repeat = $this->_phpExcel->getSheet($i)->getPageSetup()->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) {
         if ($this->_phpExcel->getSheet($i)->getPageSetup()->isPrintAreaSet()) {
             // Print area
             $printArea = PHPExcel_Cell::splitRange($this->_phpExcel->getSheet($i)->getPageSetup()->getPrintArea());
             $printArea = $printArea[0];
             $printArea[0] = PHPExcel_Cell::coordinateFromString($printArea[0]);
             $printArea[1] = PHPExcel_Cell::coordinateFromString($printArea[1]);
             $print_rowmin = $printArea[0][1] - 1;
             $print_rowmax = $printArea[1][1] - 1;
             $print_colmin = PHPExcel_Cell::columnIndexFromString($printArea[0][0]) - 1;
             $print_colmax = PHPExcel_Cell::columnIndexFromString($printArea[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);
             // store the DEFINEDNAME record
             $chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x6), $formulaData, $i + 1, true));
         }
     }
     return $chunk;
 }

 /**
  * Write the NAME record to define the print area and the repeat rows and cols.
  */
 private function _writeNames()
 {
     // total number of sheets
     $total_worksheets = $this->_phpExcel->getSheetCount();
     // Create the print area NAME records
     for ($i = 0; $i < $total_worksheets; ++$i) {
         $sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup();
         // Write a Name record if the print area has been defined
         if ($sheetSetup->isPrintAreaSet()) {
             // Print area
             $printArea = PHPExcel_Cell::splitRange($sheetSetup->getPrintArea());
             $printArea = $printArea[0];
             $printArea[0] = PHPExcel_Cell::coordinateFromString($printArea[0]);
             $printArea[1] = PHPExcel_Cell::coordinateFromString($printArea[1]);
             $print_rowmin = $printArea[0][1] - 1;
             $print_rowmax = $printArea[1][1] - 1;
             $print_colmin = PHPExcel_Cell::columnIndexFromString($printArea[0][0]) - 1;
             $print_colmax = PHPExcel_Cell::columnIndexFromString($printArea[1][0]) - 1;
             $this->_writeNameShort($i, 0x6, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax);
         }
     }
     // Create the print title NAME records
     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;
             $this->_writeNameLong($i, 0x7, $rowmin, $rowmax, $colmin, $colmax);
             // (exclusive) either repeatColumns or repeatRows
         } else {
             if ($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;
                 }
                 $this->_writeNameShort($i, 0x7, $rowmin, $rowmax, $colmin, $colmax);
             }
         }
     }
 }

 /**
  * Save PHPExcel to file
  *
  * @param 	string 		$pFileName
  * @throws 	Exception
  */
 public function save($pFilename = null)
 {
     $this->_colors = array();
     $phpExcel = $this->_phpExcel;
     $workbook = new PHPExcel_Writer_Excel5_Writer($pFilename);
     $workbook->setVersion(8);
     // Set temp dir
     if ($this->_tempDir != '') {
         $workbook->setTempDir($this->_tempDir);
     }
     // Create empty style
     $emptyStyle = new PHPExcel_Style();
     // Add empty sheets
     foreach ($phpExcel->getSheetNames() as $sheetIndex => $sheetName) {
         $phpSheet = $phpExcel->getSheet($sheetIndex);
         $worksheet = $workbook->addWorksheet($sheetName);
     }
     $allWorksheets = $workbook->worksheets();
     // Add full sheet data
     foreach ($phpExcel->getSheetNames() as $sheetIndex => $sheetName) {
         $phpSheet = $phpExcel->getSheet($sheetIndex);
         $worksheet = $allWorksheets[$sheetIndex];
         $worksheet->setInputEncoding("UTF-8");
         $aStyles = $phpSheet->getStyles();
         $freeze = $phpSheet->getFreezePane();
         if ($freeze) {
             list($column, $row) = PHPExcel_Cell::coordinateFromString($freeze);
             $worksheet->freezePanes(array($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1));
         }
         //if ($sheetIndex == $phpExcel->getActiveSheetIndex()) {
         // $worksheet->select();
         //}
         if ($phpSheet->getProtection()->getSheet()) {
             $worksheet->protect($phpSheet->getProtection()->getPassword(), true);
         }
         if (!$phpSheet->getShowGridlines()) {
             $worksheet->hideGridLines();
         }
         $formats = array();
         foreach ($phpSheet->getCellCollection() as $cell) {
             $row = $cell->getRow() - 1;
             $column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1;
             // Don't break Excel!
             if ($row + 1 >= 65569) {
                 break;
             }
             $style = $emptyStyle;
             if (isset($aStyles[$cell->getCoordinate()])) {
                 $style = $aStyles[$cell->getCoordinate()];
             }
             $styleHash = $style->getHashCode();
             if (!isset($formats[$styleHash])) {
                 $formats[$styleHash] = $workbook->addFormat(array('HAlign' => $style->getAlignment()->getHorizontal(), 'VAlign' => $this->_mapVAlign($style->getAlignment()->getVertical()), 'TextRotation' => $style->getAlignment()->getTextRotation(), 'Bold' => $style->getFont()->getBold(), 'FontFamily' => $style->getFont()->getName(), 'Color' => $this->_addColor($workbook, $style->getFont()->getColor()->getRGB()), 'Underline' => $this->_mapUnderline($style->getFont()->getUnderline()), 'Size' => $style->getFont()->getSize(), 'NumFormat' => iconv("UTF-8", "Windows-1252", $style->getNumberFormat()->getFormatCode()), 'Bottom' => $this->_mapBorderStyle($style->getBorders()->getBottom()->getBorderStyle()), 'Top' => $this->_mapBorderStyle($style->getBorders()->getTop()->getBorderStyle()), 'Left' => $this->_mapBorderStyle($style->getBorders()->getLeft()->getBorderStyle()), 'Right' => $this->_mapBorderStyle($style->getBorders()->getRight()->getBorderStyle()), 'BottomColor' => $this->_addColor($workbook, $style->getBorders()->getBottom()->getColor()->getRGB()), 'TopColor' => $this->_addColor($workbook, $style->getBorders()->getTop()->getColor()->getRGB()), 'RightColor' => $this->_addColor($workbook, $style->getBorders()->getRight()->getColor()->getRGB()), 'LeftColor' => $this->_addColor($workbook, $style->getBorders()->getLeft()->getColor()->getRGB()), 'FgColor' => $this->_addColor($workbook, $style->getFill()->getStartColor()->getRGB()), 'BgColor' => $this->_addColor($workbook, $style->getFill()->getEndColor()->getRGB()), 'Pattern' => $this->_mapFillType($style->getFill()->getFillType())));
                 if ($style->getAlignment()->getWrapText()) {
                     $formats[$styleHash]->setTextWrap();
                 }
                 if ($style->getFont()->getItalic()) {
                     $formats[$styleHash]->setItalic();
                 }
                 if ($style->getFont()->getStriketrough()) {
                     $formats[$styleHash]->setStrikeOut();
                 }
             }
             // Write cell value
             if ($cell->getValue() instanceof PHPExcel_RichText) {
                 $worksheet->write($row, $column, $cell->getValue()->getPlainText(), $formats[$styleHash]);
             } else {
                 // Hyperlink?
                 if ($cell->hasHyperlink()) {
                     $worksheet->writeUrl($row, $column, $cell->getHyperlink()->getUrl(), $cell->getValue(), $formats[$styleHash]);
                 } else {
                     $worksheet->write($row, $column, $cell->getValue(), $formats[$styleHash]);
                 }
             }
         }
         $phpSheet->calculateColumnWidths();
         foreach ($phpSheet->getColumnDimensions() as $columnDimension) {
             $column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1;
             $worksheet->setColumn($column, $column, $columnDimension->getWidth(), null, $columnDimension->getVisible() ? '0' : '1');
         }
         foreach ($phpSheet->getRowDimensions() as $rowDimension) {
             $worksheet->setRow($rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), null, $rowDimension->getVisible() ? '0' : '1');
         }
         foreach ($phpSheet->getMergeCells() as $cells) {
             list($first, $last) = PHPExcel_Cell::splitRange($cells);
             list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($first);
             list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($last);
             $worksheet->mergeCells($firstRow - 1, PHPExcel_Cell::columnIndexFromString($firstColumn) - 1, $lastRow - 1, PHPExcel_Cell::columnIndexFromString($lastColumn) - 1);
         }
         foreach ($phpSheet->getDrawingCollection() as $drawing) {
             if ($drawing instanceof PHPExcel_Worksheet_BaseDrawing) {
                 $filename = $drawing->getPath();
                 $imagesize = getimagesize($filename);
                 switch ($imagesize[2]) {
//.........这里部分代码省略.........

//.........这里部分代码省略.........
					} else {
						$worksheet->write($row, $column, $cell->getValue(), $formats[$styleHash],$style->getNumberFormat()->getFormatCode());
					}
				}
			}

			// Column dimensions
			$phpSheet->calculateColumnWidths();
			$defaultWidth = null;
			if ($phpSheet->getDefaultColumnDimension()->getWidth() >= 0) {
				$defaultWidth = $phpSheet->getDefaultColumnDimension()->getWidth();
				for ($column = 0; $column < PHPExcel_Cell::columnIndexFromString($phpSheet->getHighestColumn()) - 1; $column++) {
					$worksheet->setColumn( $column, $column, $defaultWidth );
				}
			}
			foreach ($phpSheet->getColumnDimensions() as $columnDimension) {
				$column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1;
				$worksheet->setColumn( $column, $column, $columnDimension->getWidth(), null, ($columnDimension->getVisible() ? '0' : '1'), $columnDimension->getOutlineLevel());
			}

			// Row dimensions
			$defaultHeight = null;
			if ($phpSheet->getDefaultRowDimension()->getRowHeight() >= 0) {
				$defaultHeight = $phpSheet->getDefaultRowDimension()->getRowHeight();
				for ($i = 0; $i < $phpSheet->getHighestRow() - 1; $i++) {
					$worksheet->setRow( $i, $defaultHeight );
				}
			}
			foreach ($phpSheet->getRowDimensions() as $rowDimension) {
				$worksheet->setRow( $rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), null, ($rowDimension->getVisible() ? '0' : '1'), $rowDimension->getOutlineLevel() );
			}

			foreach ($phpSheet->getMergeCells() as $cells) {
				list($first, $last) = PHPExcel_Cell::splitRange($cells);
				list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($first);
				list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($last);
				$worksheet->mergeCells($firstRow - 1, PHPExcel_Cell::columnIndexFromString($firstColumn) - 1, $lastRow - 1, PHPExcel_Cell::columnIndexFromString($lastColumn) - 1);
			}

			foreach ($phpSheet->getDrawingCollection() as $drawing) {
				if ($drawing instanceof PHPExcel_Worksheet_BaseDrawing) {
					$filename = $drawing->getPath();
					$imagesize = getimagesize($filename);
					switch ($imagesize[2]) {
						case 1: $image = imagecreatefromgif($filename); break;
						case 2: $image = imagecreatefromjpeg($filename); break;
						case 3: $image = imagecreatefrompng($filename); break;
						default: continue 2;
					}
					list($column, $row) = PHPExcel_Cell::coordinateFromString($drawing->getCoordinates());
					$worksheet->insertBitmap($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1, $image, $drawing->getOffsetX(), $drawing->getOffsetY(), $drawing->getWidth() / $imagesize[0], $drawing->getHeight() / $imagesize[1]);
				}
			}

			// page setup
			if ($phpSheet->getPageSetup()->getOrientation() == PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE) {
				$worksheet->setLandscape();
			}
			$worksheet->setPaper($phpSheet->getPageSetup()->getPaperSize());
			$worksheet->setHeader($phpSheet->getHeaderFooter()->getOddHeader(), $phpSheet->getPageMargins()->getHeader());
			$worksheet->setFooter($phpSheet->getHeaderFooter()->getOddFooter(), $phpSheet->getPageMargins()->getFooter());
			$worksheet->setMarginLeft($phpSheet->getPageMargins()->getLeft());
			$worksheet->setMarginRight($phpSheet->getPageMargins()->getRight());
			$worksheet->setMarginTop($phpSheet->getPageMargins()->getTop());
			$worksheet->setMarginBottom($phpSheet->getPageMargins()->getBottom());

 /**
  * Write Defined Name for PrintTitles
  *
  * @param 	PHPExcel_Shared_XMLWriter	$objWriter 		XML Writer
  * @param 	PHPExcel_Worksheet			$pSheet
  * @param 	int							$pSheetId
  * @throws 	Exception
  */
 private function _writeDefinedNameForPrintArea(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_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 = PHPExcel_Cell::splitRange($pSheet->getPageSetup()->getPrintArea());
         $printArea[0] = PHPExcel_Cell::absoluteCoordinate($printArea[0]);
         $printArea[1] = PHPExcel_Cell::absoluteCoordinate($printArea[1]);
         $objWriter->writeRaw('\'' . $pSheet->getTitle() . '\'!' . implode(':', $printArea));
         $objWriter->endElement();
     }
 }

 /**
  * 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
     $maxCountMergeCellsPerRecord = 1027;
     // 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 = PHPExcel_Cell::splitRange($mergeCell);
         list($first, $last) = $range[0];
         list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($first);
         list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($last);
         $recordData .= pack('vvvv', $firstRow - 1, $lastRow - 1, PHPExcel_Cell::columnIndexFromString($firstColumn) - 1, PHPExcel_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	PHPExcel_Exception
  */
 private function _updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
 {
     if (strpos($pCellRange, ':') !== false || strpos($pCellRange, ',') !== false) {
         // Update range
         $range = PHPExcel_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 = PHPExcel_Cell::coordinateFromString($this->_updateSingleCellReference($range[$i][$j] . '1', $pBefore, $pNumCols, $pNumRows));
                     $range[$i][$j] = $r[0];
                 } elseif (ctype_digit($range[$i][$j])) {
                     $r = PHPExcel_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 PHPExcel_Cell::buildRange($range);
     } else {
         throw new PHPExcel_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) = PHPExcel_Cell::coordinateFromString($explodedSpaces);
             if (strlen($col) <= 2) {
                 $returnValue[] = $explodedSpaces;
             }
             continue;
         }
         // Range...
         $range = PHPExcel_Cell::splitRange($explodedSpaces);
         for ($i = 0; $i < count($range); ++$i) {
             // Single cell?
             if (count($range[$i]) == 1) {
                 $col = 'A';
                 $row = 1;
                 list($col, $row) = PHPExcel_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) = PHPExcel_Cell::coordinateFromString($rangeStart);
             list($endingCol, $endingRow) = PHPExcel_Cell::coordinateFromString($rangeEnd);
             // Conversions...
             $startingCol = PHPExcel_Cell::columnIndexFromString($startingCol);
             $endingCol = PHPExcel_Cell::columnIndexFromString($endingCol);
             // Current data
             $currentCol = --$startingCol;
             $currentRow = $startingRow;
             // Loop cells
             while ($currentCol < $endingCol) {
                 $loopColumn = PHPExcel_Cell::stringFromColumnIndex($currentCol);
                 while ($currentRow <= $endingRow) {
                     $returnValue[] = $loopColumn . $currentRow;
                     ++$currentRow;
                 }
                 ++$currentCol;
                 $currentRow = $startingRow;
             }
         }
     }
     // Return value
     return $returnValue;
 }

 /**
  * Extract range values
  *
  * @param	string				&$pRange	String based range representation
  * @param	PHPExcel_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	PHPExcel_Calculation_Exception
  */
 public function extractNamedRange(&$pRange = 'A1', PHPExcel_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) = PHPExcel_Worksheet::extractSheetTitle($pRange, true);
             //				echo 'New sheet name is '.$pSheetName,PHP_EOL;
             //				echo 'Adjusted Range reference is '.$pRange,PHP_EOL;
             $pSheet = $this->_workbook->getSheetByName($pSheetName);
         }
         // Named range?
         $namedRange = PHPExcel_NamedRange::resolveRange($pRange, $pSheet);
         if ($namedRange !== NULL) {
             $pSheet = $namedRange->getWorksheet();
             //				echo 'Named Range '.$pRange.' (';
             $pRange = $namedRange->getRange();
             $splitRange = PHPExcel_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 PHPExcel_Calculation_Functions::REF();
         }
         // Extract range
         $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
         //			var_dump($aReferences);
         if (!isset($aReferences[1])) {
             //	Single cell (or single column or row) in range
             list($currentCol, $currentRow) = PHPExcel_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) = PHPExcel_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
     return $returnValue;
 }

 /**
  * 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 = '';
     // total number of sheets
     $total_worksheets = count($this->_phpExcel->getAllSheets());
     // write the print titles (repeating rows, columns), if any
     for ($i = 0; $i < $total_worksheets; ++$i) {
         // simultaneous repeatColumns repeatRows
         if ($this->_phpExcel->getSheet($i)->getPageSetup()->isColumnsToRepeatAtLeftSet() && $this->_phpExcel->getSheet($i)->getPageSetup()->isRowsToRepeatAtTopSet()) {
             $repeat = $this->_phpExcel->getSheet($i)->getPageSetup()->getColumnsToRepeatAtLeft();
             $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
             $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
             $repeat = $this->_phpExcel->getSheet($i)->getPageSetup()->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 ($this->_phpExcel->getSheet($i)->getPageSetup()->isColumnsToRepeatAtLeftSet() || $this->_phpExcel->getSheet($i)->getPageSetup()->isRowsToRepeatAtTopSet()) {
                 // Columns to repeat
                 if ($this->_phpExcel->getSheet($i)->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
                     $repeat = $this->_phpExcel->getSheet($i)->getPageSetup()->getColumnsToRepeatAtLeft();
                     $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
                     $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
                 } else {
                     $colmin = 0;
                     $colmax = 255;
                 }
                 // Rows to repeat
                 if ($this->_phpExcel->getSheet($i)->getPageSetup()->isRowsToRepeatAtTopSet()) {
                     $repeat = $this->_phpExcel->getSheet($i)->getPageSetup()->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) {
         if ($this->_phpExcel->getSheet($i)->getPageSetup()->isPrintAreaSet()) {
             // Print area
             $printArea = PHPExcel_Cell::splitRange($this->_phpExcel->getSheet($i)->getPageSetup()->getPrintArea());
             $printArea = $printArea[0];
             $printArea[0] = PHPExcel_Cell::coordinateFromString($printArea[0]);
             $printArea[1] = PHPExcel_Cell::coordinateFromString($printArea[1]);
             $print_rowmin = $printArea[0][1] - 1;
             $print_rowmax = $printArea[1][1] - 1;
             $print_colmin = PHPExcel_Cell::columnIndexFromString($printArea[0][0]) - 1;
             $print_colmax = PHPExcel_Cell::columnIndexFromString($printArea[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);
             // store the DEFINEDNAME record
             $chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x6), $formulaData, $i + 1, true));
         }
     }
     return $chunk;
 }

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

 /**
  * 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, ) = PHPExcel_Cell::splitRange($cells);
             $first = $cells[0];
             $last = $cells[1];
             list($fc, $fr) = PHPExcel_Cell::coordinateFromString($first);
             $fc = PHPExcel_Cell::columnIndexFromString($fc) - 1;
             list($lc, $lr) = PHPExcel_Cell::coordinateFromString($last);
             $lc = PHPExcel_Cell::columnIndexFromString($lc) - 1;
             // loop through the individual cells in the individual merge
             $r = $fr - 1;
             while ($r++ < $lr) {
                 // also, flag this row as a HTML row that is candidate to be omitted
                 $candidateSpannedRow[$r] = $r;
                 $c = $fc - 1;
                 while ($c++ < $lc) {
                     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 = PHPExcel_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();
                 $c = -1;
                 $e = $countColumns - 1;
                 while ($c++ < $e) {
                     $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;
 }

 /**
  * Select a range of cells.
  *
  * @param    string        $pCoordinate    Cell range, examples: 'A1', 'B2:G5', 'A:C', '3:6'
  * @throws    PHPExcel_Exception
  * @return PHPExcel_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, ) = PHPExcel_Cell::splitRange($pCoordinate);
         $this->_activeCell = $first[0];
     } else {
         $this->_activeCell = $pCoordinate;
     }
     $this->_selectedCells = $pCoordinate;
     return $this;
 }