ILIAS  release_5-4 Revision v5.4.26-12-gabc799a52e6
Xls.php
Go to the documentation of this file.
1<?php
2
4
7
8class Xls
9{
20 public static function sizeCol($sheet, $col = 'A')
21 {
22 // default font of the workbook
23 $font = $sheet->getParent()->getDefaultStyle()->getFont();
24
25 $columnDimensions = $sheet->getColumnDimensions();
26
27 // first find the true column width in pixels (uncollapsed and unhidden)
28 if (isset($columnDimensions[$col]) && $columnDimensions[$col]->getWidth() != -1) {
29 // then we have column dimension with explicit width
30 $columnDimension = $columnDimensions[$col];
31 $width = $columnDimension->getWidth();
32 $pixelWidth = Drawing::cellDimensionToPixels($width, $font);
33 } elseif ($sheet->getDefaultColumnDimension()->getWidth() != -1) {
34 // then we have default column dimension with explicit width
35 $defaultColumnDimension = $sheet->getDefaultColumnDimension();
36 $width = $defaultColumnDimension->getWidth();
37 $pixelWidth = Drawing::cellDimensionToPixels($width, $font);
38 } else {
39 // we don't even have any default column dimension. Width depends on default font
40 $pixelWidth = Font::getDefaultColumnWidthByFont($font, true);
41 }
42
43 // now find the effective column width in pixels
44 if (isset($columnDimensions[$col]) && !$columnDimensions[$col]->getVisible()) {
45 $effectivePixelWidth = 0;
46 } else {
47 $effectivePixelWidth = $pixelWidth;
48 }
49
50 return $effectivePixelWidth;
51 }
52
63 public static function sizeRow($sheet, $row = 1)
64 {
65 // default font of the workbook
66 $font = $sheet->getParent()->getDefaultStyle()->getFont();
67
68 $rowDimensions = $sheet->getRowDimensions();
69
70 // first find the true row height in pixels (uncollapsed and unhidden)
71 if (isset($rowDimensions[$row]) && $rowDimensions[$row]->getRowHeight() != -1) {
72 // then we have a row dimension
73 $rowDimension = $rowDimensions[$row];
74 $rowHeight = $rowDimension->getRowHeight();
75 $pixelRowHeight = (int) ceil(4 * $rowHeight / 3); // here we assume Arial 10
76 } elseif ($sheet->getDefaultRowDimension()->getRowHeight() != -1) {
77 // then we have a default row dimension with explicit height
78 $defaultRowDimension = $sheet->getDefaultRowDimension();
79 $rowHeight = $defaultRowDimension->getRowHeight();
80 $pixelRowHeight = Drawing::pointsToPixels($rowHeight);
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($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 $effectivePixelRowHeight;
95 }
96
108 public static function getDistanceX(Worksheet $sheet, $startColumn = 'A', $startOffsetX = 0, $endColumn = 'A', $endOffsetX = 0)
109 {
110 $distanceX = 0;
111
112 // add the widths of the spanning columns
113 $startColumnIndex = Coordinate::columnIndexFromString($startColumn);
114 $endColumnIndex = Coordinate::columnIndexFromString($endColumn);
115 for ($i = $startColumnIndex; $i <= $endColumnIndex; ++$i) {
116 $distanceX += self::sizeCol($sheet, Coordinate::stringFromColumnIndex($i));
117 }
118
119 // correct for offsetX in startcell
120 $distanceX -= (int) floor(self::sizeCol($sheet, $startColumn) * $startOffsetX / 1024);
121
122 // correct for offsetX in endcell
123 $distanceX -= (int) floor(self::sizeCol($sheet, $endColumn) * (1 - $endOffsetX / 1024));
124
125 return $distanceX;
126 }
127
139 public static function getDistanceY(Worksheet $sheet, $startRow = 1, $startOffsetY = 0, $endRow = 1, $endOffsetY = 0)
140 {
141 $distanceY = 0;
142
143 // add the widths of the spanning rows
144 for ($row = $startRow; $row <= $endRow; ++$row) {
145 $distanceY += self::sizeRow($sheet, $row);
146 }
147
148 // correct for offsetX in startcell
149 $distanceY -= (int) floor(self::sizeRow($sheet, $startRow) * $startOffsetY / 256);
150
151 // correct for offsetX in endcell
152 $distanceY -= (int) floor(self::sizeRow($sheet, $endRow) * (1 - $endOffsetY / 256));
153
154 return $distanceY;
155 }
156
210 public static function oneAnchor2twoAnchor($sheet, $coordinates, $offsetX, $offsetY, $width, $height)
211 {
212 [$col_start, $row] = Coordinate::indexesFromString($coordinates);
213 $row_start = $row - 1;
214
215 $x1 = $offsetX;
216 $y1 = $offsetY;
217
218 // Initialise end cell to the same as the start cell
219 $col_end = $col_start; // Col containing lower right corner of object
220 $row_end = $row_start; // Row containing bottom right corner of object
221
222 // Zero the specified offset if greater than the cell dimensions
223 if ($x1 >= self::sizeCol($sheet, Coordinate::stringFromColumnIndex($col_start))) {
224 $x1 = 0;
225 }
226 if ($y1 >= self::sizeRow($sheet, $row_start + 1)) {
227 $y1 = 0;
228 }
229
230 $width = $width + $x1 - 1;
231 $height = $height + $y1 - 1;
232
233 // Subtract the underlying cell widths to find the end cell of the image
234 while ($width >= self::sizeCol($sheet, Coordinate::stringFromColumnIndex($col_end))) {
235 $width -= self::sizeCol($sheet, Coordinate::stringFromColumnIndex($col_end));
236 ++$col_end;
237 }
238
239 // Subtract the underlying cell heights to find the end cell of the image
240 while ($height >= self::sizeRow($sheet, $row_end + 1)) {
241 $height -= self::sizeRow($sheet, $row_end + 1);
242 ++$row_end;
243 }
244
245 // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
246 // with zero height or width.
247 if (self::sizeCol($sheet, Coordinate::stringFromColumnIndex($col_start)) == 0) {
248 return null;
249 }
250 if (self::sizeCol($sheet, Coordinate::stringFromColumnIndex($col_end)) == 0) {
251 return null;
252 }
253 if (self::sizeRow($sheet, $row_start + 1) == 0) {
254 return null;
255 }
256 if (self::sizeRow($sheet, $row_end + 1) == 0) {
257 return null;
258 }
259
260 // Convert the pixel values to the percentage value expected by Excel
261 $x1 = $x1 / self::sizeCol($sheet, Coordinate::stringFromColumnIndex($col_start)) * 1024;
262 $y1 = $y1 / self::sizeRow($sheet, $row_start + 1) * 256;
263 $x2 = ($width + 1) / self::sizeCol($sheet, Coordinate::stringFromColumnIndex($col_end)) * 1024; // Distance to right side of object
264 $y2 = ($height + 1) / self::sizeRow($sheet, $row_end + 1) * 256; // Distance to bottom of object
265
266 $startCoordinates = Coordinate::stringFromColumnIndex($col_start) . ($row_start + 1);
267 $endCoordinates = Coordinate::stringFromColumnIndex($col_end) . ($row_end + 1);
268
269 return [
270 'startCoordinates' => $startCoordinates,
271 'startOffsetX' => $x1,
272 'startOffsetY' => $y1,
273 'endCoordinates' => $endCoordinates,
274 'endOffsetX' => $x2,
275 'endOffsetY' => $y2,
276 ];
277 }
278}
An exception for terminatinating execution or to throw for unit testing.
Helper class to manipulate cell coordinates.
Definition: Coordinate.php:15
static indexesFromString(string $coordinates)
Get indexes from a string coordinates.
Definition: Coordinate.php:52
static columnIndexFromString($pString)
Column index from string.
Definition: Coordinate.php:265
static stringFromColumnIndex($columnIndex)
String from column index.
Definition: Coordinate.php:313
static pointsToPixels($pValue)
Convert points to pixels.
Definition: Drawing.php:113
static cellDimensionToPixels($pValue, \PhpOffice\PhpSpreadsheet\Style\Font $pDefaultFont)
Convert column width from (intrinsic) Excel units to pixels.
Definition: Drawing.php:73
static getDefaultColumnWidthByFont(\PhpOffice\PhpSpreadsheet\Style\Font $font, $pPixels=false)
Get the effective column width for columns without a column dimension or column with width -1 For exa...
Definition: Font.php:554
static getDefaultRowHeightByFont(\PhpOffice\PhpSpreadsheet\Style\Font $font)
Get the effective row height for rows without a row dimension or rows with height -1 For example,...
Definition: Font.php:586
static fontSizeToPixels($fontSizeInPoints)
Calculate an (approximate) pixel size, based on a font points size.
Definition: Font.php:362
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 sp...
Definition: Xls.php:108
static oneAnchor2twoAnchor($sheet, $coordinates, $offsetX, $offsetY, $width, $height)
Convert 1-cell anchor coordinates to 2-cell anchor coordinates This function is ported from PEAR Spre...
Definition: Xls.php:210
static sizeRow($sheet, $row=1)
Convert the height of a cell from user's units to pixels.
Definition: Xls.php:63
static sizeCol($sheet, $col='A')
Get the width of a column in pixels.
Definition: Xls.php:20
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 span...
Definition: Xls.php:139
$i
Definition: disco.tpl.php:19
$row