PHP PHPExcel_Shared_Excel5类代码示例

 /**
  * 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
  * The SDK incorrectly states that the height should be expressed as a
  *		percentage of 1024.
  *
  * @access private
  * @param integer $col_start Col containing upper left corner of object
  * @param integer $row_start Row containing top left corner of object
  * @param integer $x1		Distance to left side of object
  * @param integer $y1		Distance to top of object
  * @param integer $width	 Width of image frame
  * @param integer $height	Height of image frame
  */
 function _positionImage($col_start, $row_start, $x1, $y1, $width, $height)
 {
     // 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 >= PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_start))) {
         $x1 = 0;
     }
     if ($y1 >= PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $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 >= PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_end))) {
         $width -= PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_end));
         ++$col_end;
     }
     // Subtract the underlying cell heights to find the end cell of the image
     while ($height >= PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1)) {
         $height -= PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1);
         ++$row_end;
     }
     // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
     // with zero eight or width.
     //
     if (PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_start)) == 0) {
         return;
     }
     if (PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_end)) == 0) {
         return;
     }
     if (PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_start + 1) == 0) {
         return;
     }
     if (PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1) == 0) {
         return;
     }
     // Convert the pixel values to the percentage value expected by Excel
     $x1 = $x1 / PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_start)) * 1024;
     $y1 = $y1 / PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_start + 1) * 256;
     $x2 = $width / PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_end)) * 1024;
     // Distance to right side of object
     $y2 = $height / PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1) * 256;
     // Distance to bottom of object
     $this->_writeObjPicture($col_start, $x1, $row_start, $y1, $col_end, $x2, $row_end, $y2);
//.........这里部分代码省略.........

 /**
  * Write MSODRAWING record
  */
 private function _writeMsoDrawing()
 {
     // check if there are any shapes for this sheet
     if (count($this->_phpSheet->getDrawingCollection()) == 0) {
         return;
     }
     // create intermediate Escher object
     $escher = new PHPExcel_Shared_Escher();
     // dgContainer
     $dgContainer = new PHPExcel_Shared_Escher_DgContainer();
     // set the drawing index (we use sheet index + 1)
     $dgContainer->setDgId($this->_phpSheet->getParent()->getIndex($this->_phpSheet) + 1);
     $escher->setDgContainer($dgContainer);
     // spgrContainer
     $spgrContainer = new PHPExcel_Shared_Escher_DgContainer_SpgrContainer();
     $dgContainer->setSpgrContainer($spgrContainer);
     // add one shape which is the group shape
     $spContainer = new PHPExcel_Shared_Escher_DgContainer_SpgrContainer_SpContainer();
     $spContainer->setSpgr(true);
     $spContainer->setSpType(0);
     $spContainer->setSpId($this->_phpSheet->getParent()->getIndex($this->_phpSheet) + 1 << 10);
     $spgrContainer->addChild($spContainer);
     // add the shapes
     // outer loop is for determining BSE index
     $blipIndex = 0;
     // 1-based index to BstoreContainer
     $countShapes = 0;
     // count number of shapes (minus group shape), in this sheet
     foreach ($this->_phpSheet->getParent()->getAllsheets() as $sheet) {
         foreach ($sheet->getDrawingCollection() as $drawing) {
             ++$blipIndex;
             if ($sheet === $this->_phpSheet) {
                 ++$countShapes;
                 // add the shape
                 $spContainer = new PHPExcel_Shared_Escher_DgContainer_SpgrContainer_SpContainer();
                 // set the shape type
                 $spContainer->setSpType(0x4b);
                 // set the shape index (we combine 1-based sheet index and $countShapes to create unique shape index)
                 $spId = $countShapes | $this->_phpSheet->getParent()->getIndex($this->_phpSheet) + 1 << 10;
                 $spContainer->setSpId($spId);
                 // keep track of last spId
                 $lastSpId = $spId;
                 // set the BLIP index
                 $spContainer->setOPT(0x4104, $blipIndex);
                 // set coordinates and offsets, client anchor
                 $coordinates = $drawing->getCoordinates();
                 $offsetX = $drawing->getOffsetX();
                 $offsetY = $drawing->getOffsetY();
                 $width = $drawing->getWidth();
                 $height = $drawing->getHeight();
                 $twoAnchor = PHPExcel_Shared_Excel5::oneAnchor2twoAnchor($this->_phpSheet, $coordinates, $offsetX, $offsetY, $width, $height);
                 $spContainer->setStartCoordinates($twoAnchor['startCoordinates']);
                 $spContainer->setStartOffsetX($twoAnchor['startOffsetX']);
                 $spContainer->setStartOffsetY($twoAnchor['startOffsetY']);
                 $spContainer->setEndCoordinates($twoAnchor['endCoordinates']);
                 $spContainer->setEndOffsetX($twoAnchor['endOffsetX']);
                 $spContainer->setEndOffsetY($twoAnchor['endOffsetY']);
                 $spgrContainer->addChild($spContainer);
             }
         }
     }
     // set last shape index
     $dgContainer->setLastSpId($lastSpId);
     // write the Escher stream
     $writer = new PHPExcel_Writer_Excel5_Escher($escher);
     $data = $writer->close();
     $spOffsets = $writer->getSpOffsets();
     // write the neccesary MSODRAWING, OBJ records
     // split the Escher stream
     $spOffsets[0] = 0;
     $nm = count($spOffsets) - 1;
     // number of shapes excluding first shape
     for ($i = 1; $i <= $nm; ++$i) {
         // MSODRAWING record
         $record = 0xec;
         // Record identifier
         // chunk of Escher stream for one shape
         $dataChunk = substr($data, $spOffsets[$i - 1], $spOffsets[$i] - $spOffsets[$i - 1]);
         $length = strlen($dataChunk);
         $header = pack("vv", $record, $length);
         $this->_append($header . $dataChunk);
         // OBJ record
         $record = 0x5d;
         // record identifier
         $objData = '';
         // ftCmo
         $objData .= pack('vvvvvVVV', 0x15, 0x12, 0x8, $i, 0x6011, 0, 0, 0);
         // ftEnd
         $objData .= pack('vv', 0x0, 0x0);
         $length = strlen($objData);
         $header = pack('vv', $record, $length);
         $this->_append($header . $objData);
     }
 }

//.........这里部分代码省略.........
                     $this->_readDefault();
                     break 2;
                 default:
                     $this->_readDefault();
                     break;
             }
         }
         // treat MSODRAWING records, sheet-level Escher
         if (!$this->_readDataOnly && $this->_drawingData) {
             $escherWorksheet = new PHPExcel_Shared_Escher();
             $reader = new PHPExcel_Reader_Excel5_Escher($escherWorksheet);
             $escherWorksheet = $reader->load($this->_drawingData);
             // debug Escher stream
             //$debug = new Debug_Escher(new PHPExcel_Shared_Escher());
             //$debug->load($this->_drawingData);
             // get all spContainers in one long array, so they can be mapped to OBJ records
             $allSpContainers = $escherWorksheet->getDgContainer()->getSpgrContainer()->getAllSpContainers();
         }
         // treat OBJ records
         foreach ($this->_objs as $n => $obj) {
             // the first shape container never has a corresponding OBJ record, hence $n + 1
             $spContainer = $allSpContainers[$n + 1];
             // we skip all spContainers that are a part of a group shape since we cannot yet handle those
             if ($spContainer->getNestingLevel() > 1) {
                 continue;
             }
             // calculate the width and height of the shape
             list($startColumn, $startRow) = PHPExcel_Cell::coordinateFromString($spContainer->getStartCoordinates());
             list($endColumn, $endRow) = PHPExcel_Cell::coordinateFromString($spContainer->getEndCoordinates());
             $startOffsetX = $spContainer->getStartOffsetX();
             $startOffsetY = $spContainer->getStartOffsetY();
             $endOffsetX = $spContainer->getEndOffsetX();
             $endOffsetY = $spContainer->getEndOffsetY();
             $width = PHPExcel_Shared_Excel5::getDistanceX($this->_phpSheet, $startColumn, $startOffsetX, $endColumn, $endOffsetX);
             $height = PHPExcel_Shared_Excel5::getDistanceY($this->_phpSheet, $startRow, $startOffsetY, $endRow, $endOffsetY);
             // calculate offsetX and offsetY of the shape
             $offsetX = $startOffsetX * PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, $startColumn) / 1024;
             $offsetY = $startOffsetY * PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $startRow) / 256;
             switch ($obj['type']) {
                 case 0x8:
                     // picture
                     // get index to BSE entry (1-based)
                     $BSEindex = $spContainer->getOPT(0x104);
                     $BSECollection = $escherWorkbook->getDggContainer()->getBstoreContainer()->getBSECollection();
                     $BSE = $BSECollection[$BSEindex - 1];
                     $blipType = $BSE->getBlipType();
                     // need check because some blip types are not supported by Escher reader such as EMF
                     if ($blip = $BSE->getBlip()) {
                         $ih = imagecreatefromstring($blip->getData());
                         $drawing = new PHPExcel_Worksheet_MemoryDrawing();
                         $drawing->setImageResource($ih);
                         // width, height, offsetX, offsetY
                         $drawing->setResizeProportional(false);
                         $drawing->setWidth($width);
                         $drawing->setHeight($height);
                         $drawing->setOffsetX($offsetX);
                         $drawing->setOffsetY($offsetY);
                         switch ($blipType) {
                             case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_JPEG:
                                 $drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
                                 $drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_JPEG);
                                 break;
                             case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG:
                                 $drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG);
                                 $drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_PNG);
                                 break;

 /**
  * Build the Worksheet Escher objects
  *
  */
 private function _buildWorksheetEschers()
 {
     // 1-based index to BstoreContainer
     $blipIndex = 0;
     $lastReducedSpId = 0;
     $lastSpId = 0;
     foreach ($this->_phpExcel->getAllsheets() as $sheet) {
         // sheet index
         $sheetIndex = $sheet->getParent()->getIndex($sheet);
         $escher = null;
         // check if there are any shapes for this sheet
         $filterRange = $sheet->getAutoFilter()->getRange();
         if (count($sheet->getDrawingCollection()) == 0 && empty($filterRange)) {
             continue;
         }
         // create intermediate Escher object
         $escher = new PHPExcel_Shared_Escher();
         // dgContainer
         $dgContainer = new PHPExcel_Shared_Escher_DgContainer();
         // set the drawing index (we use sheet index + 1)
         $dgId = $sheet->getParent()->getIndex($sheet) + 1;
         $dgContainer->setDgId($dgId);
         $escher->setDgContainer($dgContainer);
         // spgrContainer
         $spgrContainer = new PHPExcel_Shared_Escher_DgContainer_SpgrContainer();
         $dgContainer->setSpgrContainer($spgrContainer);
         // add one shape which is the group shape
         $spContainer = new PHPExcel_Shared_Escher_DgContainer_SpgrContainer_SpContainer();
         $spContainer->setSpgr(true);
         $spContainer->setSpType(0);
         $spContainer->setSpId($sheet->getParent()->getIndex($sheet) + 1 << 10);
         $spgrContainer->addChild($spContainer);
         // add the shapes
         $countShapes[$sheetIndex] = 0;
         // count number of shapes (minus group shape), in sheet
         foreach ($sheet->getDrawingCollection() as $drawing) {
             ++$blipIndex;
             ++$countShapes[$sheetIndex];
             // add the shape
             $spContainer = new PHPExcel_Shared_Escher_DgContainer_SpgrContainer_SpContainer();
             // set the shape type
             $spContainer->setSpType(0x4b);
             // set the shape flag
             $spContainer->setSpFlag(0x2);
             // set the shape index (we combine 1-based sheet index and $countShapes to create unique shape index)
             $reducedSpId = $countShapes[$sheetIndex];
             $spId = $reducedSpId | $sheet->getParent()->getIndex($sheet) + 1 << 10;
             $spContainer->setSpId($spId);
             // keep track of last reducedSpId
             $lastReducedSpId = $reducedSpId;
             // keep track of last spId
             $lastSpId = $spId;
             // set the BLIP index
             $spContainer->setOPT(0x4104, $blipIndex);
             // set coordinates and offsets, client anchor
             $coordinates = $drawing->getCoordinates();
             $offsetX = $drawing->getOffsetX();
             $offsetY = $drawing->getOffsetY();
             $width = $drawing->getWidth();
             $height = $drawing->getHeight();
             $twoAnchor = PHPExcel_Shared_Excel5::oneAnchor2twoAnchor($sheet, $coordinates, $offsetX, $offsetY, $width, $height);
             $spContainer->setStartCoordinates($twoAnchor['startCoordinates']);
             $spContainer->setStartOffsetX($twoAnchor['startOffsetX']);
             $spContainer->setStartOffsetY($twoAnchor['startOffsetY']);
             $spContainer->setEndCoordinates($twoAnchor['endCoordinates']);
             $spContainer->setEndOffsetX($twoAnchor['endOffsetX']);
             $spContainer->setEndOffsetY($twoAnchor['endOffsetY']);
             $spgrContainer->addChild($spContainer);
         }
         // AutoFilters
         if (!empty($filterRange)) {
             $rangeBounds = PHPExcel_Cell::rangeBoundaries($filterRange);
             $iNumColStart = $rangeBounds[0][0];
             $iNumColEnd = $rangeBounds[1][0];
             $iInc = $iNumColStart;
             while ($iInc <= $iNumColEnd) {
                 ++$countShapes[$sheetIndex];
                 // create an Drawing Object for the dropdown
                 $oDrawing = new PHPExcel_Worksheet_BaseDrawing();
                 // get the coordinates of drawing
                 $cDrawing = PHPExcel_Cell::stringFromColumnIndex($iInc - 1) . $rangeBounds[0][1];
                 $oDrawing->setCoordinates($cDrawing);
                 $oDrawing->setWorksheet($sheet);
                 // add the shape
                 $spContainer = new PHPExcel_Shared_Escher_DgContainer_SpgrContainer_SpContainer();
                 // set the shape type
                 $spContainer->setSpType(0xc9);
                 // set the shape flag
                 $spContainer->setSpFlag(0x1);
                 // set the shape index (we combine 1-based sheet index and $countShapes to create unique shape index)
                 $reducedSpId = $countShapes[$sheetIndex];
                 $spId = $reducedSpId | $sheet->getParent()->getIndex($sheet) + 1 << 10;
                 $spContainer->setSpId($spId);
                 // keep track of last reducedSpId
                 $lastReducedSpId = $reducedSpId;
                 // keep track of last spId
//.........这里部分代码省略.........

 private function _buildWorksheetEschers()
 {
     // 1-based index to BstoreContainer
     $blipIndex = 0;
     foreach ($this->_phpExcel->getAllsheets() as $sheet) {
         // sheet index
         $sheetIndex = $sheet->getParent()->getIndex($sheet);
         $escher = null;
         // check if there are any shapes for this sheet
         if (count($sheet->getDrawingCollection()) == 0) {
             continue;
         }
         // create intermediate Escher object
         $escher = new PHPExcel_Shared_Escher();
         // dgContainer
         $dgContainer = new PHPExcel_Shared_Escher_DgContainer();
         // set the drawing index (we use sheet index + 1)
         $dgId = $sheet->getParent()->getIndex($sheet) + 1;
         $dgContainer->setDgId($dgId);
         $escher->setDgContainer($dgContainer);
         // spgrContainer
         $spgrContainer = new PHPExcel_Shared_Escher_DgContainer_SpgrContainer();
         $dgContainer->setSpgrContainer($spgrContainer);
         // add one shape which is the group shape
         $spContainer = new PHPExcel_Shared_Escher_DgContainer_SpgrContainer_SpContainer();
         $spContainer->setSpgr(true);
         $spContainer->setSpType(0);
         $spContainer->setSpId($sheet->getParent()->getIndex($sheet) + 1 << 10);
         $spgrContainer->addChild($spContainer);
         // add the shapes
         $countShapes[$sheetIndex] = 0;
         // count number of shapes (minus group shape), in sheet
         foreach ($sheet->getDrawingCollection() as $drawing) {
             ++$blipIndex;
             ++$countShapes[$sheetIndex];
             // add the shape
             $spContainer = new PHPExcel_Shared_Escher_DgContainer_SpgrContainer_SpContainer();
             // set the shape type
             $spContainer->setSpType(0x4b);
             // set the shape index (we combine 1-based sheet index and $countShapes to create unique shape index)
             $reducedSpId = $countShapes[$sheetIndex];
             $spId = $reducedSpId | $sheet->getParent()->getIndex($sheet) + 1 << 10;
             $spContainer->setSpId($spId);
             // keep track of last reducedSpId
             $lastReducedSpId = $reducedSpId;
             // keep track of last spId
             $lastSpId = $spId;
             // set the BLIP index
             $spContainer->setOPT(0x4104, $blipIndex);
             // set coordinates and offsets, client anchor
             $coordinates = $drawing->getCoordinates();
             $offsetX = $drawing->getOffsetX();
             $offsetY = $drawing->getOffsetY();
             $width = $drawing->getWidth();
             $height = $drawing->getHeight();
             $twoAnchor = PHPExcel_Shared_Excel5::oneAnchor2twoAnchor($sheet, $coordinates, $offsetX, $offsetY, $width, $height);
             $spContainer->setStartCoordinates($twoAnchor['startCoordinates']);
             $spContainer->setStartOffsetX($twoAnchor['startOffsetX']);
             $spContainer->setStartOffsetY($twoAnchor['startOffsetY']);
             $spContainer->setEndCoordinates($twoAnchor['endCoordinates']);
             $spContainer->setEndOffsetX($twoAnchor['endOffsetX']);
             $spContainer->setEndOffsetY($twoAnchor['endOffsetY']);
             $spgrContainer->addChild($spContainer);
         }
         // identifier clusters, used for workbook Escher object
         $this->_IDCLs[$dgId] = $lastReducedSpId;
         // set last shape index
         $dgContainer->setLastSpId($lastSpId);
         // set the Escher object
         $this->_writerWorksheets[$sheetIndex]->setEscher($escher);
     }
 }

    /**
	 * Panes are frozen? (in sheet currently being read). See WINDOW2 record.
	 *
	 * @var boolean
	 */
    private $_frozen;
    /**
	 * Fit printout to number of pages? (in sheet currently being read). See SHEETPR record.
	 *
	 * @var boolean
	 */
    private $_isFitToPages;
    /**
	 * Objects. One OBJ record contributes with one entry.
	 *
	 * @var array
	 */
    private $_objs;
    /**
	 * Text Objects. One TXO record corresponds with one entry.
	 *
	 * @var array
	 */
    private $_textObjects;
    /**
	 * Cell Annotations (BIFF8)
	 *
	 * @var array
	 */
    private $_cellNotes;
    /**
	 * The combined MSODRAWINGGROUP data
	 *
	 * @var string
	 */
    private $_drawingGroupData;
    /**
	 * The combined MSODRAWING data (per sheet)
	 *
	 * @var string
	 */
    private $_drawingData;
    /**
	 * Keep track of XF index
	 *
	 * @var int
	 */
    private $_xfIndex;
    /**
	 * Mapping of XF index (that is a cell XF) to final index in cellXf collection
	 *
	 * @var array
	 */
    private $_mapCellXfIndex;
    /**
	 * Mapping of XF index (that is a style XF) to final index in cellStyleXf collection
	 *
	 * @var array
	 */
    private $_mapCellStyleXfIndex;
    /**
	 * The shared formulas in a sheet. One SHAREDFMLA record contributes with one value.
	 *
	 * @var array
	 */
    private $_sharedFormulas;
    /**
	 * The shared formula parts in a sheet. One FORMULA record contributes with one value if it
	 * refers to a shared formula.
	 *
	 * @var array
	 */
    private $_sharedFormulaParts;
    /**
	 *	Read data only?
	 *		If this is true, then the Reader will only read data values for cells, it will not read any formatting information.
	 *		If false (the default) it will read data and formatting.
	 *
	 *	@return	boolean
	 */
    public function getReadDataOnly()
    {
        return $this->_readDataOnly;
    }
    /**
	 *	Set read data only
	 *		Set to true, to advise the Reader only to read data values for cells, and to ignore any formatting information.
	 *		Set to false (the default) to advise the Reader to read both data and formatting for cells.
	 *
	 *	@param	boolean	$pValue
	 *
	 *	@return	PHPExcel_Reader_Excel5
	 */
    public function setReadDataOnly($pValue = false)
    {
        $this->_readDataOnly = $pValue;
        return $this;
    }
    /**
	 *	Get which sheets to load
//.........这里部分代码省略.........