| 1 |
<?php |
| 2 |
|
| 3 |
namespace TablePress\PhpOffice\PhpSpreadsheet\Shared; |
| 4 |
|
| 5 |
use TablePress\PhpOffice\PhpSpreadsheet\Cell\Coordinate; |
| 6 |
use TablePress\PhpOffice\PhpSpreadsheet\Helper\Dimension; |
| 7 |
use TablePress\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet; |
| 8 |
|
| 9 |
class Xls |
| 10 |
{ |
| 11 |
/** |
| 12 |
* Get the width of a column in pixels. We use the relationship y = ceil(7x) where |
| 13 |
* x is the width in intrinsic Excel units (measuring width in number of normal characters) |
| 14 |
* This holds for Arial 10. |
| 15 |
* |
| 16 |
* @param Worksheet $worksheet The sheet |
| 17 |
* @param string $col The column |
| 18 |
* |
| 19 |
* @return int The width in pixels |
| 20 |
*/ |
| 21 |
public static function sizeCol(Worksheet $worksheet, string $col = 'A'): int |
| 22 |
{ |
| 23 |
// default font of the workbook |
| 24 |
$font = $worksheet->getParentOrThrow()->getDefaultStyle()->getFont(); |
| 25 |
|
| 26 |
$columnDimensions = $worksheet->getColumnDimensions(); |
| 27 |
|
| 28 |
// first find the true column width in pixels (uncollapsed and unhidden) |
| 29 |
if (isset($columnDimensions[$col]) && $columnDimensions[$col]->getWidth() != -1) { |
| 30 |
// then we have column dimension with explicit width |
| 31 |
$columnDimension = $columnDimensions[$col]; |
| 32 |
$width = $columnDimension->getWidth(); |
| 33 |
$pixelWidth = Drawing::cellDimensionToPixels($width, $font); |
| 34 |
} elseif ($worksheet->getDefaultColumnDimension()->getWidth() != -1) { |
| 35 |
// then we have default column dimension with explicit width |
| 36 |
$defaultColumnDimension = $worksheet->getDefaultColumnDimension(); |
| 37 |
$width = $defaultColumnDimension->getWidth(); |
| 38 |
$pixelWidth = Drawing::cellDimensionToPixels($width, $font); |
| 39 |
} else { |
| 40 |
// we don't even have any default column dimension. Width depends on default font |
| 41 |
$pixelWidth = Font::getDefaultColumnWidthByFont($font, true); |
| 42 |
} |
| 43 |
|
| 44 |
// now find the effective column width in pixels |
| 45 |
if (isset($columnDimensions[$col]) && !$columnDimensions[$col]->getVisible()) { |
| 46 |
$effectivePixelWidth = 0; |
| 47 |
} else { |
| 48 |
$effectivePixelWidth = $pixelWidth; |
| 49 |
} |
| 50 |
|
| 51 |
return $effectivePixelWidth; |
| 52 |
} |
| 53 |
|
| 54 |
/** |
| 55 |
* Convert the height of a cell from user's units to pixels. By interpolation |
| 56 |
* the relationship is: y = 4/3x. If the height hasn't been set by the user we |
| 57 |
* use the default value. If the row is hidden we use a value of zero. |
| 58 |
* |
| 59 |
* @param Worksheet $worksheet The sheet |
| 60 |
* @param int $row The row index (1-based) |
| 61 |
* |
| 62 |
* @return int The width in pixels |
| 63 |
*/ |
| 64 |
public static function sizeRow(Worksheet $worksheet, int $row = 1): int |
| 65 |
{ |
| 66 |
// default font of the workbook |
| 67 |
$font = $worksheet->getParentOrThrow()->getDefaultStyle()->getFont(); |
| 68 |
|
| 69 |
$rowDimensions = $worksheet->getRowDimensions(); |
| 70 |
|
| 71 |
// first find the true row height in pixels (uncollapsed and unhidden) |
| 72 |
if (isset($rowDimensions[$row]) && $rowDimensions[$row]->getRowHeight() != -1) { |
| 73 |
// then we have a row dimension |
| 74 |
$rowDimension = $rowDimensions[$row]; |
| 75 |
$rowHeight = $rowDimension->getRowHeight(); |
| 76 |
$pixelRowHeight = (int) ceil(4 * $rowHeight / 3); // here we assume Arial 10 |
| 77 |
} elseif ($worksheet->getDefaultRowDimension()->getRowHeight() != -1) { |
| 78 |
// then we have a default row dimension with explicit height |
| 79 |
$defaultRowDimension = $worksheet->getDefaultRowDimension(); |
| 80 |
$pixelRowHeight = $defaultRowDimension->getRowHeight(Dimension::UOM_PIXELS); |
| 81 |
} else { |
| 82 |
// we don't even have any default row dimension. Height depends on default font |
| 83 |
$pointRowHeight = Font::getDefaultRowHeightByFont($font); |
| 84 |
$pixelRowHeight = Font::fontSizeToPixels((int) $pointRowHeight); |
| 85 |
} |
| 86 |
|
| 87 |
// now find the effective row height in pixels |
| 88 |
if (isset($rowDimensions[$row]) && !$rowDimensions[$row]->getVisible()) { |
| 89 |
$effectivePixelRowHeight = 0; |
| 90 |
} else { |
| 91 |
$effectivePixelRowHeight = $pixelRowHeight; |
| 92 |
} |
| 93 |
|
| 94 |
return (int) $effectivePixelRowHeight; |
| 95 |
} |
| 96 |
|
| 97 |
/** |
| 98 |
* Get the horizontal distance in pixels between two anchors |
| 99 |
* The distanceX is found as sum of all the spanning columns widths minus correction for the two offsets. |
| 100 |
* |
| 101 |
* @param float|int $startOffsetX Offset within start cell measured in 1/1024 of the cell width |
| 102 |
* @param float|int $endOffsetX Offset within end cell measured in 1/1024 of the cell width |
| 103 |
* |
| 104 |
* @return int Horizontal measured in pixels |
| 105 |
*/ |
| 106 |
public static function getDistanceX(Worksheet $worksheet, string $startColumn = 'A', $startOffsetX = 0, string $endColumn = 'A', $endOffsetX = 0): int |
| 107 |
{ |
| 108 |
$distanceX = 0; |
| 109 |
|
| 110 |
// add the widths of the spanning columns |
| 111 |
$startColumnIndex = Coordinate::columnIndexFromString($startColumn); |
| 112 |
$endColumnIndex = Coordinate::columnIndexFromString($endColumn); |
| 113 |
for ($i = $startColumnIndex; $i <= $endColumnIndex; ++$i) { |
| 114 |
$distanceX += self::sizeCol($worksheet, Coordinate::stringFromColumnIndex($i)); |
| 115 |
} |
| 116 |
|
| 117 |
// correct for offsetX in startcell |
| 118 |
$distanceX -= (int) floor(self::sizeCol($worksheet, $startColumn) * $startOffsetX / 1024); |
| 119 |
|
| 120 |
// correct for offsetX in endcell |
| 121 |
$distanceX -= (int) floor(self::sizeCol($worksheet, $endColumn) * (1 - $endOffsetX / 1024)); |
| 122 |
|
| 123 |
return $distanceX; |
| 124 |
} |
| 125 |
|
| 126 |
/** |
| 127 |
* Get the vertical distance in pixels between two anchors |
| 128 |
* The distanceY is found as sum of all the spanning rows minus two offsets. |
| 129 |
* |
| 130 |
* @param int $startRow (1-based) |
| 131 |
* @param float|int $startOffsetY Offset within start cell measured in 1/256 of the cell height |
| 132 |
* @param int $endRow (1-based) |
| 133 |
* @param float|int $endOffsetY Offset within end cell measured in 1/256 of the cell height |
| 134 |
* |
| 135 |
* @return int Vertical distance measured in pixels |
| 136 |
*/ |
| 137 |
public static function getDistanceY(Worksheet $worksheet, int $startRow = 1, $startOffsetY = 0, int $endRow = 1, $endOffsetY = 0): int |
| 138 |
{ |
| 139 |
$distanceY = 0; |
| 140 |
|
| 141 |
// add the widths of the spanning rows |
| 142 |
for ($row = $startRow; $row <= $endRow; ++$row) { |
| 143 |
$distanceY += self::sizeRow($worksheet, $row); |
| 144 |
} |
| 145 |
|
| 146 |
// correct for offsetX in startcell |
| 147 |
$distanceY -= (int) floor(self::sizeRow($worksheet, $startRow) * $startOffsetY / 256); |
| 148 |
|
| 149 |
// correct for offsetX in endcell |
| 150 |
$distanceY -= (int) floor(self::sizeRow($worksheet, $endRow) * (1 - $endOffsetY / 256)); |
| 151 |
|
| 152 |
return $distanceY; |
| 153 |
} |
| 154 |
|
| 155 |
/** |
| 156 |
* Convert 1-cell anchor coordinates to 2-cell anchor coordinates |
| 157 |
* This function is ported from PEAR Spreadsheet_Writer_Excel with small modifications. |
| 158 |
* |
| 159 |
* Calculate the vertices that define the position of the image as required by |
| 160 |
* the OBJ record. |
| 161 |
* |
| 162 |
* +------------+------------+ |
| 163 |
* | A | B | |
| 164 |
* +-----+------------+------------+ |
| 165 |
* | |(x1,y1) | | |
| 166 |
* | 1 |(A1)._______|______ | |
| 167 |
* | | | | | |
| 168 |
* | | | | | |
| 169 |
* +-----+----| BITMAP |-----+ |
| 170 |
* | | | | | |
| 171 |
* | 2 | |______________. | |
| 172 |
* | | | (B2)| |
| 173 |
* | | | (x2,y2)| |
| 174 |
* +---- +------------+------------+ |
| 175 |
* |
| 176 |
* Example of a bitmap that covers some of the area from cell A1 to cell B2. |
| 177 |
* |
| 178 |
* Based on the width and height of the bitmap we need to calculate 8 vars: |
| 179 |
* $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2. |
| 180 |
* The width and height of the cells are also variable and have to be taken into |
| 181 |
* account. |
| 182 |
* The values of $col_start and $row_start are passed in from the calling |
| 183 |
* function. The values of $col_end and $row_end are calculated by subtracting |
| 184 |
* the width and height of the bitmap from the width and height of the |
| 185 |
* underlying cells. |
| 186 |
* The vertices are expressed as a percentage of the underlying cell width as |
| 187 |
* follows (rhs values are in pixels): |
| 188 |
* |
| 189 |
* x1 = X / W *1024 |
| 190 |
* y1 = Y / H *256 |
| 191 |
* x2 = (X-1) / W *1024 |
| 192 |
* y2 = (Y-1) / H *256 |
| 193 |
* |
| 194 |
* Where: X is distance from the left side of the underlying cell |
| 195 |
* Y is distance from the top of the underlying cell |
| 196 |
* W is the width of the cell |
| 197 |
* H is the height of the cell |
| 198 |
* |
| 199 |
* @param string $coordinates E.g. 'A1' |
| 200 |
* @param int $offsetX Horizontal offset in pixels |
| 201 |
* @param int $offsetY Vertical offset in pixels |
| 202 |
* @param int $width Width in pixels |
| 203 |
* @param int $height Height in pixels |
| 204 |
* |
| 205 |
* @return ?array{startCoordinates: string, startOffsetX: float|int, startOffsetY: float|int, endCoordinates: string, endOffsetX: float|int, endOffsetY: float|int} |
| 206 |
*/ |
| 207 |
public static function oneAnchor2twoAnchor(Worksheet $worksheet, string $coordinates, int $offsetX, int $offsetY, int $width, int $height): ?array |
| 208 |
{ |
| 209 |
[$col_start, $row] = Coordinate::indexesFromString($coordinates); |
| 210 |
$row_start = $row - 1; |
| 211 |
|
| 212 |
$x1 = $offsetX; |
| 213 |
$y1 = $offsetY; |
| 214 |
|
| 215 |
// Initialise end cell to the same as the start cell |
| 216 |
$col_end = $col_start; // Col containing lower right corner of object |
| 217 |
$row_end = $row_start; // Row containing bottom right corner of object |
| 218 |
|
| 219 |
// Zero the specified offset if greater than the cell dimensions |
| 220 |
if ($x1 >= self::sizeCol($worksheet, Coordinate::stringFromColumnIndex($col_start))) { |
| 221 |
$x1 = 0; |
| 222 |
} |
| 223 |
if ($y1 >= self::sizeRow($worksheet, $row_start + 1)) { |
| 224 |
$y1 = 0; |
| 225 |
} |
| 226 |
|
| 227 |
$width = $width + $x1 - 1; |
| 228 |
$height = $height + $y1 - 1; |
| 229 |
|
| 230 |
// Subtract the underlying cell widths to find the end cell of the image |
| 231 |
while ($width >= self::sizeCol($worksheet, Coordinate::stringFromColumnIndex($col_end))) { |
| 232 |
$width -= self::sizeCol($worksheet, Coordinate::stringFromColumnIndex($col_end)); |
| 233 |
++$col_end; |
| 234 |
} |
| 235 |
|
| 236 |
// Subtract the underlying cell heights to find the end cell of the image |
| 237 |
while ($height >= self::sizeRow($worksheet, $row_end + 1)) { |
| 238 |
$height -= self::sizeRow($worksheet, $row_end + 1); |
| 239 |
++$row_end; |
| 240 |
} |
| 241 |
|
| 242 |
// Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell |
| 243 |
// with zero height or width. |
| 244 |
if ( |
| 245 |
self::sizeCol($worksheet, Coordinate::stringFromColumnIndex($col_start)) == 0 |
| 246 |
|| self::sizeCol($worksheet, Coordinate::stringFromColumnIndex($col_end)) == 0 |
| 247 |
|| self::sizeRow($worksheet, $row_start + 1) == 0 |
| 248 |
|| self::sizeRow($worksheet, $row_end + 1) == 0 |
| 249 |
) { |
| 250 |
return null; |
| 251 |
} |
| 252 |
|
| 253 |
// Convert the pixel values to the percentage value expected by Excel |
| 254 |
$x1 = $x1 / self::sizeCol($worksheet, Coordinate::stringFromColumnIndex($col_start)) * 1024; |
| 255 |
$y1 = $y1 / self::sizeRow($worksheet, $row_start + 1) * 256; |
| 256 |
$x2 = ($width + 1) / self::sizeCol($worksheet, Coordinate::stringFromColumnIndex($col_end)) * 1024; // Distance to right side of object |
| 257 |
$y2 = ($height + 1) / self::sizeRow($worksheet, $row_end + 1) * 256; // Distance to bottom of object |
| 258 |
|
| 259 |
$startCoordinates = Coordinate::stringFromColumnIndex($col_start) . ($row_start + 1); |
| 260 |
$endCoordinates = Coordinate::stringFromColumnIndex($col_end) . ($row_end + 1); |
| 261 |
|
| 262 |
return [ |
| 263 |
'startCoordinates' => $startCoordinates, |
| 264 |
'startOffsetX' => $x1, |
| 265 |
'startOffsetY' => $y1, |
| 266 |
'endCoordinates' => $endCoordinates, |
| 267 |
'endOffsetX' => $x2, |
| 268 |
'endOffsetY' => $y2, |
| 269 |
]; |
| 270 |
} |
| 271 |
} |
| 272 |
|
