PHPExcel_Shared_Excel5 Class Reference

Collaboration diagram for PHPExcel_Shared_Excel5:

Static Public Member Functions
static	sizeCol ($sheet, $col= 'A')
	Get the width of a column in pixels.
static	sizeRow ($sheet, $row=1)
	Convert the height of a cell from user's units to pixels.
static	getDistanceX (PHPExcel_Worksheet $sheet, $startColumn= 'A', $startOffsetX=0, $endColumn= 'A', $endOffsetX=0)
	Get the horizontal distance in pixels between two anchors The distanceX is found as sum of all the spanning columns widths minus correction for the two offsets.
static	getDistanceY (PHPExcel_Worksheet $sheet, $startRow=1, $startOffsetY=0, $endRow=1, $endOffsetY=0)
	Get the vertical distance in pixels between two anchors The distanceY is found as sum of all the spanning rows minus two offsets.
static	oneAnchor2twoAnchor ($sheet, $coordinates, $offsetX, $offsetY, $width, $height)
	Convert 1-cell anchor coordinates to 2-cell anchor coordinates This function is ported from PEAR Spreadsheet_Writer_Excel with small modifications.

Detailed Description

Definition at line 49 of file Excel5.php.

Member Function Documentation

static PHPExcel_Shared_Excel5::getDistanceX ( PHPExcel_Worksheet  $sheet,   $startColumn = 'A',   $startOffsetX = 0,   $endColumn = 'A',   $endOffsetX = 0  )

static

Get the horizontal distance in pixels between two anchors The distanceX is found as sum of all the spanning columns widths minus correction for the two offsets.

Parameters

PHPExcel_Worksheet	$sheet
string	$startColumn
integer	$startOffset	Offset within start cell measured in 1/1024 of the cell width
string	$endColumn
integer	$endOffset	Offset within end cell measured in 1/1024 of the cell width

Returns

integer Horizontal measured in pixels

Definition at line 149 of file Excel5.php.

References PHPExcel_Cell\columnIndexFromString(), sizeCol(), and PHPExcel_Cell\stringFromColumnIndex().

Referenced by PHPExcel_Reader_Excel5\load().

        {
                $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;
        }

Here is the call graph for this function:

Here is the caller graph for this function:

static PHPExcel_Shared_Excel5::getDistanceY ( PHPExcel_Worksheet  $sheet,   $startRow = 1,   $startOffsetY = 0,   $endRow = 1,   $endOffsetY = 0  )

static

Get the vertical distance in pixels between two anchors The distanceY is found as sum of all the spanning rows minus two offsets.

Parameters

PHPExcel_Worksheet	$sheet
string	$startRow	(1-based)
integer	$startOffset	Offset within start cell measured in 1/256 of the cell height
string	$endRow	(1-based)
integer	$endOffset	Offset within end cell measured in 1/256 of the cell height

Returns

integer Vertical distance measured in pixels

Definition at line 180 of file Excel5.php.

References $row, and sizeRow().

Referenced by PHPExcel_Reader_Excel5\load().

        {
                $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;
        }

Here is the call graph for this function:

Here is the caller graph for this function:

static PHPExcel_Shared_Excel5::oneAnchor2twoAnchor (   $sheet,   $coordinates,   $offsetX,   $offsetY,   $width,   $height  )

static

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

Parameters

PHPExcel_Worksheet	$sheet
string	$coordinates	E.g. 'A1'
integer	$offsetX	Horizontal offset in pixels
integer	$offsetY	Vertical offset in pixels
integer	$width	Width in pixels
integer	$height	Height in pixels

Returns

array

Definition at line 250 of file Excel5.php.

References $row, PHPExcel_Cell\columnIndexFromString(), PHPExcel_Cell\coordinateFromString(), sizeCol(), sizeRow(), and PHPExcel_Cell\stringFromColumnIndex().

Referenced by PHPExcel_Writer_Excel5_Worksheet\_storeMsoDrawing().

        {
                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;
        }

Here is the call graph for this function:

Here is the caller graph for this function:

static PHPExcel_Shared_Excel5::sizeCol (   $sheet,   $col = 'A'  )

static

Get the width of a column in pixels.

We use the relationship y = ceil(7x) where x is the width in intrinsic Excel units (measuring width in number of normal characters) This holds for Arial 10

Parameters

PHPExcel_Worksheet	$sheet	The sheet
integer	$col	The column

Returns

integer The width in pixels

Definition at line 60 of file Excel5.php.

References PHPExcel_Shared_Drawing\cellDimensionToPixels().

Referenced by PHPExcel_Writer_Excel5_Worksheet\_positionImage(), getDistanceX(), PHPExcel_Reader_Excel5\load(), and oneAnchor2twoAnchor().

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

Here is the call graph for this function:

Here is the caller graph for this function:

static PHPExcel_Shared_Excel5::sizeRow (   $sheet,   $row = 1  )

static

Convert the height of a cell from user's units to pixels.

By interpolation the relationship is: y = 4/3x. If the height hasn't been set by the user we use the default value. If the row is hidden we use a value of zero.

Parameters

PHPExcel_Worksheet	$sheet	The sheet
integer	$row	The row index (1-based)

Returns

integer The width in pixels

Definition at line 105 of file Excel5.php.

References $row, and PHPExcel_Shared_Drawing\pointsToPixels().

Referenced by PHPExcel_Writer_Excel5_Worksheet\_positionImage(), getDistanceY(), PHPExcel_Reader_Excel5\load(), and oneAnchor2twoAnchor().

        {
                $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;
        }

Here is the call graph for this function:

Here is the caller graph for this function:

---

The documentation for this class was generated from the following file:

• Services/Excel/classes/PHPExcel/Shared/Excel5.php