PhpOffice\PhpSpreadsheet\Shared\Xls Class Reference

Collaboration diagram for PhpOffice\PhpSpreadsheet\Shared\Xls:

Static Public Member Functions
static	sizeCol ($sheet, $col='A')
	Get the width of a column in pixels. More...
static	sizeRow ($sheet, $row=1)
	Convert the height of a cell from user's units to pixels. More...
static	getDistanceX (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. More...
static	getDistanceY (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. More...
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. More...

Detailed Description

Definition at line 8 of file Xls.php.

Member Function Documentation

getDistanceX()

static PhpOffice\PhpSpreadsheet\Shared\Xls::getDistanceX ( 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

string	$startColumn
int	$startOffsetX	Offset within start cell measured in 1/1024 of the cell width
string	$endColumn
int	$endOffsetX	Offset within end cell measured in 1/1024 of the cell width

Returns

int Horizontal measured in pixels

Definition at line 108 of file Xls.php.

References $i, PhpOffice\PhpSpreadsheet\Cell\Coordinate\columnIndexFromString(), and PhpOffice\PhpSpreadsheet\Cell\Coordinate\stringFromColumnIndex().

Referenced by PhpOffice\PhpSpreadsheet\Reader\Xls\load().

    {
        $distanceX = 0;

        // add the widths of the spanning columns
        $startColumnIndex = Coordinate::columnIndexFromString($startColumn);
        $endColumnIndex = Coordinate::columnIndexFromString($endColumn);
        for ($i = $startColumnIndex; $i <= $endColumnIndex; ++$i) {
            $distanceX += self::sizeCol($sheet, Coordinate::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:

getDistanceY()

static PhpOffice\PhpSpreadsheet\Shared\Xls::getDistanceY ( 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

int	$startRow	(1-based)
int	$startOffsetY	Offset within start cell measured in 1/256 of the cell height
int	$endRow	(1-based)
int	$endOffsetY	Offset within end cell measured in 1/256 of the cell height

Returns

int Vertical distance measured in pixels

Definition at line 139 of file Xls.php.

References $row.

Referenced by PhpOffice\PhpSpreadsheet\Reader\Xls\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 caller graph for this function:

oneAnchor2twoAnchor()

static PhpOffice\PhpSpreadsheet\Shared\Xls::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

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

Returns

null|array

Definition at line 210 of file Xls.php.

References $row, PhpOffice\PhpSpreadsheet\Cell\Coordinate\indexesFromString(), and PhpOffice\PhpSpreadsheet\Cell\Coordinate\stringFromColumnIndex().

Referenced by PhpOffice\PhpSpreadsheet\Writer\Xls\buildWorksheetEschers().

    {
        [$col_start, $row] = Coordinate::indexesFromString($coordinates);
        $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, Coordinate::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, Coordinate::stringFromColumnIndex($col_end))) {
            $width -= self::sizeCol($sheet, Coordinate::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, Coordinate::stringFromColumnIndex($col_start)) == 0) {
            return null;
        }
        if (self::sizeCol($sheet, Coordinate::stringFromColumnIndex($col_end)) == 0) {
            return null;
        }
        if (self::sizeRow($sheet, $row_start + 1) == 0) {
            return null;
        }
        if (self::sizeRow($sheet, $row_end + 1) == 0) {
            return null;
        }

        // Convert the pixel values to the percentage value expected by Excel
        $x1 = $x1 / self::sizeCol($sheet, Coordinate::stringFromColumnIndex($col_start)) * 1024;
        $y1 = $y1 / self::sizeRow($sheet, $row_start + 1) * 256;
        $x2 = ($width + 1) / self::sizeCol($sheet, Coordinate::stringFromColumnIndex($col_end)) * 1024; // Distance to right side of object
        $y2 = ($height + 1) / self::sizeRow($sheet, $row_end + 1) * 256; // Distance to bottom of object

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

        return [
            'startCoordinates' => $startCoordinates,
            'startOffsetX' => $x1,
            'startOffsetY' => $y1,
            'endCoordinates' => $endCoordinates,
            'endOffsetX' => $x2,
            'endOffsetY' => $y2,
        ];
    }

Here is the call graph for this function:

Here is the caller graph for this function:

sizeCol()

static PhpOffice\PhpSpreadsheet\Shared\Xls::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

Worksheet	$sheet	The sheet
string	$col	The column

Returns

int The width in pixels

Definition at line 20 of file Xls.php.

References PhpOffice\PhpSpreadsheet\Shared\Drawing\cellDimensionToPixels(), and PhpOffice\PhpSpreadsheet\Shared\Font\getDefaultColumnWidthByFont().

Referenced by PhpOffice\PhpSpreadsheet\Reader\Xls\load().

    {
        // default font of the workbook
        $font = $sheet->getParent()->getDefaultStyle()->getFont();

        $columnDimensions = $sheet->getColumnDimensions();

        // first find the true column width in pixels (uncollapsed and unhidden)
        if (isset($columnDimensions[$col]) && $columnDimensions[$col]->getWidth() != -1) {
            // then we have column dimension with explicit width
            $columnDimension = $columnDimensions[$col];
            $width = $columnDimension->getWidth();
            $pixelWidth = Drawing::cellDimensionToPixels($width, $font);
        } elseif ($sheet->getDefaultColumnDimension()->getWidth() != -1) {
            // then we have default column dimension with explicit width
            $defaultColumnDimension = $sheet->getDefaultColumnDimension();
            $width = $defaultColumnDimension->getWidth();
            $pixelWidth = Drawing::cellDimensionToPixels($width, $font);
        } else {
            // we don't even have any default column dimension. Width depends on default font
            $pixelWidth = Font::getDefaultColumnWidthByFont($font, true);
        }

        // now find the effective column width in pixels
        if (isset($columnDimensions[$col]) && !$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:

sizeRow()

static PhpOffice\PhpSpreadsheet\Shared\Xls::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

Worksheet	$sheet	The sheet
int	$row	The row index (1-based)

Returns

int The width in pixels

Definition at line 63 of file Xls.php.

References $row, PhpOffice\PhpSpreadsheet\Shared\Font\fontSizeToPixels(), PhpOffice\PhpSpreadsheet\Shared\Font\getDefaultRowHeightByFont(), and PhpOffice\PhpSpreadsheet\Shared\Drawing\pointsToPixels().

Referenced by PhpOffice\PhpSpreadsheet\Reader\Xls\load().

    {
        // default font of the workbook
        $font = $sheet->getParent()->getDefaultStyle()->getFont();

        $rowDimensions = $sheet->getRowDimensions();

        // first find the true row height in pixels (uncollapsed and unhidden)
        if (isset($rowDimensions[$row]) && $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
        } elseif ($sheet->getDefaultRowDimension()->getRowHeight() != -1) {
            // then we have a default row dimension with explicit height
            $defaultRowDimension = $sheet->getDefaultRowDimension();
            $rowHeight = $defaultRowDimension->getRowHeight();
            $pixelRowHeight = Drawing::pointsToPixels($rowHeight);
        } else {
            // we don't even have any default row dimension. Height depends on default font
            $pointRowHeight = Font::getDefaultRowHeightByFont($font);
            $pixelRowHeight = Font::fontSizeToPixels($pointRowHeight);
        }

        // now find the effective row height in pixels
        if (isset($rowDimensions[$row]) && !$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:

• libs/composer/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/Xls.php