/** * 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 */ public 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); }
/** * 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 $lastSpId = $spId; $spContainer->setOPT(0x7f, 0x1040104); // Protection -> fLockAgainstGrouping $spContainer->setOPT(0xbf, 0x80008); // Text -> fFitTextToShape $spContainer->setOPT(0x1bf, 0x10000); // Fill Style -> fNoFillHitTest $spContainer->setOPT(0x1ff, 0x80000); // Line Style -> fNoLineDrawDash $spContainer->setOPT(0x3bf, 0xa0000); // Group Shape -> fPrint // set coordinates and offsets, client anchor $endCoordinates = \PHPExcel\Cell::stringFromColumnIndex(\PHPExcel\Cell::stringFromColumnIndex($iInc - 1)); $endCoordinates .= $rangeBounds[0][1] + 1; $spContainer->setStartCoordinates($cDrawing); $spContainer->setStartOffsetX(0); $spContainer->setStartOffsetY(0); $spContainer->setEndCoordinates($endCoordinates); $spContainer->setEndOffsetX(0); $spContainer->setEndOffsetY(0); $spgrContainer->addChild($spContainer); $iInc++; } } // 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); } }