Excel5.php

Go to the documentation of this file.

<?php
if (!defined('PHPEXCEL_ROOT')) {
        define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
}

require_once PHPEXCEL_ROOT . 'PHPExcel/Cell.php';

require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/Drawing.php';

class PHPExcel_Shared_Excel5
{
        public static function sizeCol($sheet, $col = 'A')
        {
                // default font size of workbook
                $fontSize = $sheet->getParent()->getDefaultStyle()->getFont()->getSize();

                $columnDimensions = $sheet->getColumnDimensions();

                // first find the true column width in pixels (uncollapsed and unhidden)
                if ( isset($columnDimensions[$col]) and $columnDimensions[$col]->getWidth() != -1 ) {

                        // then we have column dimension with explicit width
                        $columnDimension = $columnDimensions[$col];
                        $width = $columnDimension->getWidth();
                        $pixelWidth = PHPExcel_Shared_Drawing::cellDimensionToPixels($width, $fontSize);

                } else if ($sheet->getDefaultColumnDimension()->getWidth() != -1) {

                        // then we have default column dimension with explicit width
                        $defaultColumnDimension = $sheet->getDefaultColumnDimension();
                        $width = $defaultColumnDimension->getWidth();
                        $pixelWidth = PHPExcel_Shared_Drawing::cellDimensionToPixels($width, $fontSize);

                } else {
                        $pixelWidth = (int) 64 * $fontSize / 11; // here we interpolate from Calibri 11
                }

                // now find the effective column width in pixels
                if (isset($columnDimensions[$col]) and !$columnDimensions[$col]->getVisible()) {
                        $effectivePixelWidth = 0;
                } else {
                        $effectivePixelWidth = $pixelWidth;
                }

                return $effectivePixelWidth;
        }

        public static function sizeRow($sheet, $row = 1)
        {
                $rowDimensions = $sheet->getRowDimensions();

                // first find the true row height in pixels (uncollapsed and unhidden)
                if ( isset($rowDimensions[$row]) and $rowDimensions[$row]->getRowHeight() != -1) {

                        // then we have a row dimension
                        $rowDimension = $rowDimensions[$row];
                        $rowHeight = $rowDimension->getRowHeight();
                        $pixelRowHeight = (int) ceil(4 * $rowHeight / 3); // here we assume Arial 10

                } else if ($sheet->getDefaultRowDimension()->getRowHeight() != -1) {

                        // then we have a default row dimension with explicit height
                        $defaultRowDimension = $sheet->getDefaultRowDimension();
                        $rowHeight = $defaultRowDimension->getRowHeight();
                        $pixelRowHeight = PHPExcel_Shared_Drawing::pointsToPixels($rowHeight);

                } else {
                        $pixelRowHeight = 20; // here we assume Calibri 11
                }

                // now find the effective row height in pixels
                if ( isset($rowDimensions[$row]) and !$rowDimensions[$row]->getVisible() ) {
                        $effectivePixelRowHeight = 0;
                } else {
                        $effectivePixelRowHeight = $pixelRowHeight;
                }

                return $effectivePixelRowHeight;
        }

        public static function getDistanceX(PHPExcel_Worksheet $sheet, $startColumn = 'A', $startOffsetX = 0, $endColumn = 'A', $endOffsetX = 0)
        {
                $distanceX = 0;

                // add the widths of the spanning columns
                $startColumnIndex = PHPExcel_Cell::columnIndexFromString($startColumn) - 1; // 1-based
                $endColumnIndex = PHPExcel_Cell::columnIndexFromString($endColumn) - 1; // 1-based
                for ($i = $startColumnIndex; $i <= $endColumnIndex; ++$i) {
                        $distanceX += self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($i));
                }

                // correct for offsetX in startcell
                $distanceX -= (int) floor(self::sizeCol($sheet, $startColumn) * $startOffsetX / 1024);

                // correct for offsetX in endcell
                $distanceX -= (int) floor(self::sizeCol($sheet, $endColumn) * (1 - $endOffsetX / 1024));

                return $distanceX;
        }

        public static function getDistanceY(PHPExcel_Worksheet $sheet, $startRow = 1, $startOffsetY = 0, $endRow = 1, $endOffsetY = 0)
        {
                $distanceY = 0;

                // add the widths of the spanning rows
                for ($row = $startRow; $row <= $endRow; ++$row) {
                        $distanceY += self::sizeRow($sheet, $row);
                }

                // correct for offsetX in startcell
                $distanceY -= (int) floor(self::sizeRow($sheet, $startRow) * $startOffsetY / 256);

                // correct for offsetX in endcell
                $distanceY -= (int) floor(self::sizeRow($sheet, $endRow) * (1 - $endOffsetY / 256));

                return $distanceY;
        }

        public static function oneAnchor2twoAnchor($sheet, $coordinates, $offsetX, $offsetY, $width, $height)
        {
                list($column, $row) = PHPExcel_Cell::coordinateFromString($coordinates);
                $col_start = PHPExcel_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, PHPExcel_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, PHPExcel_Cell::stringFromColumnIndex($col_end))) {
                        $width -= self::sizeCol($sheet, PHPExcel_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, PHPExcel_Cell::stringFromColumnIndex($col_start)) == 0) {
                        return;
                }
                if (self::sizeCol($sheet, PHPExcel_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, PHPExcel_Cell::stringFromColumnIndex($col_start))   * 1024;
                $y1 = $y1        / self::sizeRow($sheet, $row_start + 1)   *  256;
                $x2 = $width  / self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_end))    * 1024; // Distance to right side of object
                $y2 = $height / self::sizeRow($sheet, $row_end + 1)      *  256; // Distance to bottom of object

                $startCoordinates = PHPExcel_Cell::stringFromColumnIndex($col_start) . ($row_start + 1);
                $endCoordinates = PHPExcel_Cell::stringFromColumnIndex($col_end) . ($row_end + 1);

                $twoAnchor = array(
                        'startCoordinates' => $startCoordinates,
                        'startOffsetX' => $x1,
                        'startOffsetY' => $y1,
                        'endCoordinates' => $endCoordinates,
                        'endOffsetX' => $x2,
                        'endOffsetY' => $y2,
                );

                return  $twoAnchor;
        }

}