PHP Cell::columnIndexFromString方法代码示例

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

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

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

 /**
  * Accepts a range, returning it as a range that falls within the current highest row and column of the worksheet
  *
  * @param 	string 	$range
  * @return 	string	Adjusted range value
  */
 public function shrinkRangeToFit($range)
 {
     $maxCol = $this->getHighestColumn();
     $maxRow = $this->getHighestRow();
     $maxCol = Cell::columnIndexFromString($maxCol);
     $rangeBlocks = explode(' ', $range);
     foreach ($rangeBlocks as &$rangeSet) {
         $rangeBoundaries = Cell::getRangeBoundaries($rangeSet);
         if (Cell::columnIndexFromString($rangeBoundaries[0][0]) > $maxCol) {
             $rangeBoundaries[0][0] = Cell::stringFromColumnIndex($maxCol);
         }
         if ($rangeBoundaries[0][1] > $maxRow) {
             $rangeBoundaries[0][1] = $maxRow;
         }
         if (Cell::columnIndexFromString($rangeBoundaries[1][0]) > $maxCol) {
             $rangeBoundaries[1][0] = Cell::stringFromColumnIndex($maxCol);
         }
         if ($rangeBoundaries[1][1] > $maxRow) {
             $rangeBoundaries[1][1] = $maxRow;
         }
         $rangeSet = $rangeBoundaries[0][0] . $rangeBoundaries[0][1] . ':' . $rangeBoundaries[1][0] . $rangeBoundaries[1][1];
     }
     unset($rangeSet);
     $stRange = implode(' ', $rangeBlocks);
     return $stRange;
 }

//.........这里部分代码省略.........
             $recVerInstance = $recVer;
             $recVerInstance |= $recInstance << 4;
             $header = pack('vvV', $recVerInstance, $recType, $length);
             $this->_data = $header . $innerData;
             $this->_spOffsets = $spOffsets;
             break;
         case 'Shared_Escher_DgContainer_SpgrContainer_SpContainer':
             // initialize
             $data = '';
             // build the data
             // write group shape record, if necessary?
             if ($this->_object->getSpgr()) {
                 $recVer = 0x1;
                 $recInstance = 0x0;
                 $recType = 0xf009;
                 $length = 0x10;
                 $recVerInstance = $recVer;
                 $recVerInstance |= $recInstance << 4;
                 $header = pack('vvV', $recVerInstance, $recType, $length);
                 $data .= $header . pack('VVVV', 0, 0, 0, 0);
             }
             // write the shape record
             $recVer = 0x2;
             $recInstance = $this->_object->getSpType();
             // shape type
             $recType = 0xf00a;
             $length = 0x8;
             $recVerInstance = $recVer;
             $recVerInstance |= $recInstance << 4;
             $header = pack('vvV', $recVerInstance, $recType, $length);
             $data .= $header . pack('VV', $this->_object->getSpId(), $this->_object->getSpgr() ? 0x5 : 0xa00);
             // the options
             if ($this->_object->getOPTCollection()) {
                 $optData = '';
                 $recVer = 0x3;
                 $recInstance = count($this->_object->getOPTCollection());
                 $recType = 0xf00b;
                 foreach ($this->_object->getOPTCollection() as $property => $value) {
                     $optData .= pack('vV', $property, $value);
                 }
                 $length = strlen($optData);
                 $recVerInstance = $recVer;
                 $recVerInstance |= $recInstance << 4;
                 $header = pack('vvV', $recVerInstance, $recType, $length);
                 $data .= $header . $optData;
             }
             // the client anchor
             if ($this->_object->getStartCoordinates()) {
                 $clientAnchorData = '';
                 $recVer = 0x0;
                 $recInstance = 0x0;
                 $recType = 0xf010;
                 // start coordinates
                 list($column, $row) = Cell::coordinateFromString($this->_object->getStartCoordinates());
                 $c1 = Cell::columnIndexFromString($column) - 1;
                 $r1 = $row - 1;
                 // start offsetX
                 $startOffsetX = $this->_object->getStartOffsetX();
                 // start offsetY
                 $startOffsetY = $this->_object->getStartOffsetY();
                 // end coordinates
                 list($column, $row) = Cell::coordinateFromString($this->_object->getEndCoordinates());
                 $c2 = Cell::columnIndexFromString($column) - 1;
                 $r2 = $row - 1;
                 // end offsetX
                 $endOffsetX = $this->_object->getEndOffsetX();
                 // end offsetY
                 $endOffsetY = $this->_object->getEndOffsetY();
                 $clientAnchorData = pack('vvvvvvvvv', 0x2, $c1, $startOffsetX, $r1, $startOffsetY, $c2, $endOffsetX, $r2, $endOffsetY);
                 $length = strlen($clientAnchorData);
                 $recVerInstance = $recVer;
                 $recVerInstance |= $recInstance << 4;
                 $header = pack('vvV', $recVerInstance, $recType, $length);
                 $data .= $header . $clientAnchorData;
             }
             // the client data, just empty for now
             if (!$this->_object->getSpgr()) {
                 $clientDataData = '';
                 $recVer = 0x0;
                 $recInstance = 0x0;
                 $recType = 0xf011;
                 $length = strlen($clientDataData);
                 $recVerInstance = $recVer;
                 $recVerInstance |= $recInstance << 4;
                 $header = pack('vvV', $recVerInstance, $recType, $length);
                 $data .= $header . $clientDataData;
             }
             // write the record
             $recVer = 0xf;
             $recInstance = 0x0;
             $recType = 0xf004;
             $length = strlen($data);
             $recVerInstance = $recVer;
             $recVerInstance |= $recInstance << 4;
             $header = pack('vvV', $recVerInstance, $recType, $length);
             $this->_data = $header . $data;
             break;
     }
     return $this->_data;
 }

 private function _castToFormula($c, $r, &$cellDataType, &$value, &$calculatedValue, &$sharedFormulas, $castBaseType)
 {
     //		echo '<font color="darkgreen">Formula</font><br />';
     //		echo '$c->f is '.$c->f.'<br />';
     $cellDataType = 'f';
     $value = "={$c->f}";
     $calculatedValue = $this->{$castBaseType}($c);
     // Shared formula?
     if (isset($c->f['t']) && strtolower((string) $c->f['t']) == 'shared') {
         //			echo '<font color="darkgreen">SHARED FORMULA</font><br />';
         $instance = (string) $c->f['si'];
         //			echo 'Instance ID = '.$instance.'<br />';
         //
         //			echo 'Shared Formula Array:<pre>';
         //			print_r($sharedFormulas);
         //			echo '</pre>';
         if (!isset($sharedFormulas[(string) $c->f['si']])) {
             //				echo '<font color="darkgreen">SETTING NEW SHARED FORMULA</font><br />';
             //				echo 'Master is '.$r.'<br />';
             //				echo 'Formula is '.$value.'<br />';
             $sharedFormulas[$instance] = array('master' => $r, 'formula' => $value);
             //				echo 'New Shared Formula Array:<pre>';
             //				print_r($sharedFormulas);
             //				echo '</pre>';
         } else {
             //				echo '<font color="darkgreen">GETTING SHARED FORMULA</font><br />';
             //				echo 'Master is '.$sharedFormulas[$instance]['master'].'<br />';
             //				echo 'Formula is '.$sharedFormulas[$instance]['formula'].'<br />';
             $master = Cell::coordinateFromString($sharedFormulas[$instance]['master']);
             $current = Cell::coordinateFromString($r);
             $difference = array(0, 0);
             $difference[0] = Cell::columnIndexFromString($current[0]) - Cell::columnIndexFromString($master[0]);
             $difference[1] = $current[1] - $master[1];
             $helper = ReferenceHelper::getInstance();
             $value = $helper->updateFormulaReferences($sharedFormulas[$instance]['formula'], 'A1', $difference[0], $difference[1]);
             //				echo 'Adjusted Formula is '.$value.'<br />';
         }
     }
 }

 /**
  * Write SheetData
  *
  * @param	Shared_XMLWriter		$objWriter		XML Writer
  * @param	Worksheet				$pSheet			Worksheet
  * @param	string[]						$pStringTable	String table
  * @throws	Exception
  */
 private function _writeSheetData(Shared_XMLWriter $objWriter = null, Worksheet $pSheet = null, $pStringTable = null)
 {
     if (is_array($pStringTable)) {
         // Flipped stringtable, for faster index searching
         $aFlippedStringTable = $this->getParentWriter()->getWriterPart('stringtable')->flipStringTable($pStringTable);
         // sheetData
         $objWriter->startElement('sheetData');
         // Get column count
         $colCount = Cell::columnIndexFromString($pSheet->getHighestColumn());
         // Highest row number
         $highestRow = $pSheet->getHighestRow();
         // Loop through cells
         $cellsByRow = array();
         foreach ($pSheet->getCellCollection() as $cellID) {
             $cellAddress = Cell::coordinateFromString($cellID);
             $cellsByRow[$cellAddress[1]][] = $cellID;
         }
         for ($currentRow = 1; $currentRow <= $highestRow; ++$currentRow) {
             // Get row dimension
             $rowDimension = $pSheet->getRowDimension($currentRow);
             // Write current row?
             $writeCurrentRow = isset($cellsByRow[$currentRow]) || $rowDimension->getRowHeight() >= 0 || $rowDimension->getVisible() == false || $rowDimension->getCollapsed() == true || $rowDimension->getOutlineLevel() > 0 || $rowDimension->getXfIndex() !== null;
             if ($writeCurrentRow) {
                 // Start a new row
                 $objWriter->startElement('row');
                 $objWriter->writeAttribute('r', $currentRow);
                 $objWriter->writeAttribute('spans', '1:' . $colCount);
                 // Row dimensions
                 if ($rowDimension->getRowHeight() >= 0) {
                     $objWriter->writeAttribute('customHeight', '1');
                     $objWriter->writeAttribute('ht', Shared_String::FormatNumber($rowDimension->getRowHeight()));
                 }
                 // Row visibility
                 if ($rowDimension->getVisible() == false) {
                     $objWriter->writeAttribute('hidden', 'true');
                 }
                 // Collapsed
                 if ($rowDimension->getCollapsed() == true) {
                     $objWriter->writeAttribute('collapsed', 'true');
                 }
                 // Outline level
                 if ($rowDimension->getOutlineLevel() > 0) {
                     $objWriter->writeAttribute('outlineLevel', $rowDimension->getOutlineLevel());
                 }
                 // Style
                 if ($rowDimension->getXfIndex() !== null) {
                     $objWriter->writeAttribute('s', $rowDimension->getXfIndex());
                     $objWriter->writeAttribute('customFormat', '1');
                 }
                 // Write cells
                 if (isset($cellsByRow[$currentRow])) {
                     foreach ($cellsByRow[$currentRow] as $cellAddress) {
                         // Write cell
                         $this->_writeCell($objWriter, $pSheet, $cellAddress, $pStringTable, $aFlippedStringTable);
                     }
                 }
                 // End row
                 $objWriter->endElement();
             }
         }
         $objWriter->endElement();
     } else {
         throw new Exception("Invalid parameters passed.");
     }
 }

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

 /**
  * More Cell instances available?
  *
  * @return boolean
  */
 public function valid()
 {
     // columnIndexFromString() returns an index based at one,
     // treat it as a count when comparing it to the base zero
     // position.
     $columnCount = Cell::columnIndexFromString($this->_subject->getHighestColumn());
     if ($this->_onlyExistingCells) {
         // If we aren't looking at an existing cell, either
         // because the first column doesn't exist or next() has
         // been called onto a nonexistent cell, then loop until we
         // find one, or pass the last column.
         while ($this->_position < $columnCount && !$this->_subject->cellExistsByColumnAndRow($this->_position, $this->_rowIndex)) {
             ++$this->_position;
         }
     }
     return $this->_position < $columnCount;
 }

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

 function setExcelSchedule($file)
 {
     $this->load->library('PHPExcel');
     $this->load->library('PHPExcel/IOFactory');
     if (substr(strrchr($file['orig_name'], '.'), 1) == 'xlsx') {
         $excel = IOFactory::load($file['full_path']);
         $num = 0;
         foreach ($excel->getWorksheetIterator() as $worksheet) {
             if ($num == 0) {
                 // first worksheet only
                 $letterColumn = $worksheet->getHighestColumn();
                 $numColumn = Cell::columnIndexFromString($letterColumn);
                 $numRow = $worksheet->getHighestRow();
                 $classes = array();
                 for ($row = 2; $row <= $numRow; $row++) {
                     //start at second row
                     $rowData = array();
                     for ($col = 0; $col < $numColumn; $col++) {
                         $cell = $worksheet->getCellByColumnAndRow($col, $row);
                         $val = $cell->getValue();
                         array_push($rowData, $val);
                     }
                     array_push($classes, $rowData);
                 }
             }
             $num++;
         }
         $post = $this->input->post();
         $schedule = array('year' => $post['year'], 'semester' => $post['semester'], 'classes' => $classes);
         $this->setSchedule($schedule);
     }
 }

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

//.........这里部分代码省略.........
             }
             if (isset($columnData_ss['Width'])) {
                 $columnWidth = $columnData_ss['Width'];
                 //					echo '<b>Setting column width for '.$columnID.' to '.$columnWidth.'</b><br />';
                 $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4);
             }
             ++$columnID;
         }
         $rowID = 1;
         foreach ($worksheet->Table->Row as $rowData) {
             $row_ss = $rowData->attributes($namespaces['ss']);
             if (isset($row_ss['Index'])) {
                 $rowID = (int) $row_ss['Index'];
             }
             //				echo '<b>Row '.$rowID.'</b><br />';
             if (isset($row_ss['StyleID'])) {
                 $rowStyle = $row_ss['StyleID'];
             }
             if (isset($row_ss['Height'])) {
                 $rowHeight = $row_ss['Height'];
                 //					echo '<b>Setting row height to '.$rowHeight.'</b><br />';
                 $objPHPExcel->getActiveSheet()->getRowDimension($rowID)->setRowHeight($rowHeight);
             }
             $columnID = 'A';
             foreach ($rowData->Cell as $cell) {
                 $cell_ss = $cell->attributes($namespaces['ss']);
                 if (isset($cell_ss['Index'])) {
                     $columnID = Cell::stringFromColumnIndex($cell_ss['Index'] - 1);
                 }
                 $cellRange = $columnID . $rowID;
                 if (isset($cell_ss['MergeAcross']) || isset($cell_ss['MergeDown'])) {
                     $columnTo = $columnID;
                     if (isset($cell_ss['MergeAcross'])) {
                         $columnTo = Cell::stringFromColumnIndex(Cell::columnIndexFromString($columnID) + $cell_ss['MergeAcross'] - 1);
                     }
                     $rowTo = $rowID;
                     if (isset($cell_ss['MergeDown'])) {
                         $rowTo = $rowTo + $cell_ss['MergeDown'];
                     }
                     $cellRange .= ':' . $columnTo . $rowTo;
                     $objPHPExcel->getActiveSheet()->mergeCells($cellRange);
                 }
                 $hasCalculatedValue = false;
                 $cellDataFormula = '';
                 if (isset($cell_ss['Formula'])) {
                     $cellDataFormula = $cell_ss['Formula'];
                     $hasCalculatedValue = true;
                 }
                 if (isset($cell->Data)) {
                     $cellValue = $cellData = $cell->Data;
                     $type = Cell_DataType::TYPE_NULL;
                     $cellData_ss = $cellData->attributes($namespaces['ss']);
                     if (isset($cellData_ss['Type'])) {
                         $cellDataType = $cellData_ss['Type'];
                         switch ($cellDataType) {
                             /*
                             const TYPE_STRING		= 's';
                             const TYPE_FORMULA		= 'f';
                             const TYPE_NUMERIC		= 'n';
                             const TYPE_BOOL			= 'b';
                             							    const TYPE_NULL			= 's';
                             							    const TYPE_INLINE		= 'inlineStr';
                             const TYPE_ERROR		= 'e';
                             */
                             case 'String':
                                 $type = Cell_DataType::TYPE_STRING;

 /**
  * Writes all the DEFINEDNAME records (BIFF8).
  * So far this is only used for repeating rows/columns (print titles) and print areas
  */
 private function _writeAllDefinedNamesBiff8()
 {
     $chunk = '';
     // Named ranges
     if (count($this->_phpExcel->getNamedRanges()) > 0) {
         // Loop named ranges
         $namedRanges = $this->_phpExcel->getNamedRanges();
         foreach ($namedRanges as $namedRange) {
             // Create absolute coordinate
             $range = Cell::splitRange($namedRange->getRange());
             for ($i = 0; $i < count($range); $i++) {
                 $range[$i][0] = '\'' . str_replace("'", "''", $namedRange->getWorksheet()->getTitle()) . '\'!' . Cell::absoluteCoordinate($range[$i][0]);
                 if (isset($range[$i][1])) {
                     $range[$i][1] = Cell::absoluteCoordinate($range[$i][1]);
                 }
             }
             $range = Cell::buildRange($range);
             // e.g. Sheet1!$A$1:$B$2
             // parse formula
             try {
                 $error = $this->_parser->parse($range);
                 $formulaData = $this->_parser->toReversePolish();
                 // make sure tRef3d is of type tRef3dR (0x3A)
                 if (isset($formulaData[0]) and ($formulaData[0] == "z" or $formulaData[0] == "Z")) {
                     $formulaData = ":" . substr($formulaData, 1);
                 }
                 if ($namedRange->getLocalOnly()) {
                     // local scope
                     $scope = $this->_phpExcel->getIndex($namedRange->getScope()) + 1;
                 } else {
                     // global scope
                     $scope = 0;
                 }
                 $chunk .= $this->writeData($this->_writeDefinedNameBiff8($namedRange->getName(), $formulaData, $scope, false));
             } catch (Exception $e) {
                 // do nothing
             }
         }
     }
     // total number of sheets
     $total_worksheets = $this->_phpExcel->getSheetCount();
     // write the print titles (repeating rows, columns), if any
     for ($i = 0; $i < $total_worksheets; ++$i) {
         $sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup();
         // simultaneous repeatColumns repeatRows
         if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) {
             $repeat = $sheetSetup->getColumnsToRepeatAtLeft();
             $colmin = Cell::columnIndexFromString($repeat[0]) - 1;
             $colmax = Cell::columnIndexFromString($repeat[1]) - 1;
             $repeat = $sheetSetup->getRowsToRepeatAtTop();
             $rowmin = $repeat[0] - 1;
             $rowmax = $repeat[1] - 1;
             // construct formula data manually
             $formulaData = pack('Cv', 0x29, 0x17);
             // tMemFunc
             $formulaData .= pack('Cvvvvv', 0x3b, $i, 0, 65535, $colmin, $colmax);
             // tArea3d
             $formulaData .= pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, 0, 255);
             // tArea3d
             $formulaData .= pack('C', 0x10);
             // tList
             // store the DEFINEDNAME record
             $chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true));
             // (exclusive) either repeatColumns or repeatRows
         } else {
             if ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) {
                 // Columns to repeat
                 if ($sheetSetup->isColumnsToRepeatAtLeftSet()) {
                     $repeat = $sheetSetup->getColumnsToRepeatAtLeft();
                     $colmin = Cell::columnIndexFromString($repeat[0]) - 1;
                     $colmax = Cell::columnIndexFromString($repeat[1]) - 1;
                 } else {
                     $colmin = 0;
                     $colmax = 255;
                 }
                 // Rows to repeat
                 if ($sheetSetup->isRowsToRepeatAtTopSet()) {
                     $repeat = $sheetSetup->getRowsToRepeatAtTop();
                     $rowmin = $repeat[0] - 1;
                     $rowmax = $repeat[1] - 1;
                 } else {
                     $rowmin = 0;
                     $rowmax = 65535;
                 }
                 // construct formula data manually because parser does not recognize absolute 3d cell references
                 $formulaData = pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, $colmin, $colmax);
                 // store the DEFINEDNAME record
                 $chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true));
             }
         }
     }
     // write the print areas, if any
     for ($i = 0; $i < $total_worksheets; ++$i) {
         $sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup();
         if ($sheetSetup->isPrintAreaSet()) {
             // Print area, e.g. A3:J6,H1:X20
//.........这里部分代码省略.........

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