ILIAS  release_5-2 Revision v5.2.25-18-g3f80b82851
Excel5.php
Go to the documentation of this file.
1 <?php
36 {
46  public static function sizeCol($sheet, $col = 'A')
47  {
48  // default font of the workbook
49  $font = $sheet->getParent()->getDefaultStyle()->getFont();
50 
51  $columnDimensions = $sheet->getColumnDimensions();
52 
53  // first find the true column width in pixels (uncollapsed and unhidden)
54  if ( isset($columnDimensions[$col]) and $columnDimensions[$col]->getWidth() != -1 ) {
55 
56  // then we have column dimension with explicit width
57  $columnDimension = $columnDimensions[$col];
58  $width = $columnDimension->getWidth();
59  $pixelWidth = PHPExcel_Shared_Drawing::cellDimensionToPixels($width, $font);
60 
61  } else if ($sheet->getDefaultColumnDimension()->getWidth() != -1) {
62 
63  // then we have default column dimension with explicit width
64  $defaultColumnDimension = $sheet->getDefaultColumnDimension();
65  $width = $defaultColumnDimension->getWidth();
66  $pixelWidth = PHPExcel_Shared_Drawing::cellDimensionToPixels($width, $font);
67 
68  } else {
69 
70  // we don't even have any default column dimension. Width depends on default font
71  $pixelWidth = PHPExcel_Shared_Font::getDefaultColumnWidthByFont($font, true);
72  }
73 
74  // now find the effective column width in pixels
75  if (isset($columnDimensions[$col]) and !$columnDimensions[$col]->getVisible()) {
76  $effectivePixelWidth = 0;
77  } else {
78  $effectivePixelWidth = $pixelWidth;
79  }
80 
81  return $effectivePixelWidth;
82  }
83 
93  public static function sizeRow($sheet, $row = 1)
94  {
95  // default font of the workbook
96  $font = $sheet->getParent()->getDefaultStyle()->getFont();
97 
98  $rowDimensions = $sheet->getRowDimensions();
99 
100  // first find the true row height in pixels (uncollapsed and unhidden)
101  if ( isset($rowDimensions[$row]) and $rowDimensions[$row]->getRowHeight() != -1) {
102 
103  // then we have a row dimension
104  $rowDimension = $rowDimensions[$row];
105  $rowHeight = $rowDimension->getRowHeight();
106  $pixelRowHeight = (int) ceil(4 * $rowHeight / 3); // here we assume Arial 10
107 
108  } else if ($sheet->getDefaultRowDimension()->getRowHeight() != -1) {
109 
110  // then we have a default row dimension with explicit height
111  $defaultRowDimension = $sheet->getDefaultRowDimension();
112  $rowHeight = $defaultRowDimension->getRowHeight();
113  $pixelRowHeight = PHPExcel_Shared_Drawing::pointsToPixels($rowHeight);
114 
115  } else {
116 
117  // we don't even have any default row dimension. Height depends on default font
118  $pointRowHeight = PHPExcel_Shared_Font::getDefaultRowHeightByFont($font);
119  $pixelRowHeight = PHPExcel_Shared_Font::fontSizeToPixels($pointRowHeight);
120 
121  }
122 
123  // now find the effective row height in pixels
124  if ( isset($rowDimensions[$row]) and !$rowDimensions[$row]->getVisible() ) {
125  $effectivePixelRowHeight = 0;
126  } else {
127  $effectivePixelRowHeight = $pixelRowHeight;
128  }
129 
130  return $effectivePixelRowHeight;
131  }
132 
144  public static function getDistanceX(PHPExcel_Worksheet $sheet, $startColumn = 'A', $startOffsetX = 0, $endColumn = 'A', $endOffsetX = 0)
145  {
146  $distanceX = 0;
147 
148  // add the widths of the spanning columns
149  $startColumnIndex = PHPExcel_Cell::columnIndexFromString($startColumn) - 1; // 1-based
150  $endColumnIndex = PHPExcel_Cell::columnIndexFromString($endColumn) - 1; // 1-based
151  for ($i = $startColumnIndex; $i <= $endColumnIndex; ++$i) {
152  $distanceX += self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($i));
153  }
154 
155  // correct for offsetX in startcell
156  $distanceX -= (int) floor(self::sizeCol($sheet, $startColumn) * $startOffsetX / 1024);
157 
158  // correct for offsetX in endcell
159  $distanceX -= (int) floor(self::sizeCol($sheet, $endColumn) * (1 - $endOffsetX / 1024));
160 
161  return $distanceX;
162  }
163 
175  public static function getDistanceY(PHPExcel_Worksheet $sheet, $startRow = 1, $startOffsetY = 0, $endRow = 1, $endOffsetY = 0)
176  {
177  $distanceY = 0;
178 
179  // add the widths of the spanning rows
180  for ($row = $startRow; $row <= $endRow; ++$row) {
181  $distanceY += self::sizeRow($sheet, $row);
182  }
183 
184  // correct for offsetX in startcell
185  $distanceY -= (int) floor(self::sizeRow($sheet, $startRow) * $startOffsetY / 256);
186 
187  // correct for offsetX in endcell
188  $distanceY -= (int) floor(self::sizeRow($sheet, $endRow) * (1 - $endOffsetY / 256));
189 
190  return $distanceY;
191  }
192 
245  public static function oneAnchor2twoAnchor($sheet, $coordinates, $offsetX, $offsetY, $width, $height)
246  {
247  list($column, $row) = PHPExcel_Cell::coordinateFromString($coordinates);
249  $row_start = $row - 1;
250 
251  $x1 = $offsetX;
252  $y1 = $offsetY;
253 
254  // Initialise end cell to the same as the start cell
255  $col_end = $col_start; // Col containing lower right corner of object
256  $row_end = $row_start; // Row containing bottom right corner of object
257 
258  // Zero the specified offset if greater than the cell dimensions
259  if ($x1 >= self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_start))) {
260  $x1 = 0;
261  }
262  if ($y1 >= self::sizeRow($sheet, $row_start + 1)) {
263  $y1 = 0;
264  }
265 
266  $width = $width + $x1 -1;
267  $height = $height + $y1 -1;
268 
269  // Subtract the underlying cell widths to find the end cell of the image
270  while ($width >= self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_end))) {
271  $width -= self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_end));
272  ++$col_end;
273  }
274 
275  // Subtract the underlying cell heights to find the end cell of the image
276  while ($height >= self::sizeRow($sheet, $row_end + 1)) {
277  $height -= self::sizeRow($sheet, $row_end + 1);
278  ++$row_end;
279  }
280 
281  // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
282  // with zero height or width.
283  if (self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_start)) == 0) {
284  return;
285  }
286  if (self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_end)) == 0) {
287  return;
288  }
289  if (self::sizeRow($sheet, $row_start + 1) == 0) {
290  return;
291  }
292  if (self::sizeRow($sheet, $row_end + 1) == 0) {
293  return;
294  }
295 
296  // Convert the pixel values to the percentage value expected by Excel
297  $x1 = $x1 / self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_start)) * 1024;
298  $y1 = $y1 / self::sizeRow($sheet, $row_start + 1) * 256;
299  $x2 = ($width + 1) / self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_end)) * 1024; // Distance to right side of object
300  $y2 = ($height + 1) / self::sizeRow($sheet, $row_end + 1) * 256; // Distance to bottom of object
301 
302  $startCoordinates = PHPExcel_Cell::stringFromColumnIndex($col_start) . ($row_start + 1);
303  $endCoordinates = PHPExcel_Cell::stringFromColumnIndex($col_end) . ($row_end + 1);
304 
305  $twoAnchor = array(
306  'startCoordinates' => $startCoordinates,
307  'startOffsetX' => $x1,
308  'startOffsetY' => $y1,
309  'endCoordinates' => $endCoordinates,
310  'endOffsetX' => $x2,
311  'endOffsetY' => $y2,
312  );
313 
314  return $twoAnchor;
315  }
316 
317 }
static getDefaultRowHeightByFont(PHPExcel_Style_Font $font)
Get the effective row height for rows without a row dimension or rows with height -1 For example...
Definition: Font.php:603
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 sp...
Definition: Excel5.php:144
static coordinateFromString($pCoordinateString='A1')
Coordinate from string.
Definition: Cell.php:580
static pointsToPixels($pValue=0)
Convert points to pixels.
Definition: Drawing.php:140
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: Excel5.php:245
static sizeCol($sheet, $col='A')
Get the width of a column in pixels.
Definition: Excel5.php:46
$column
Definition: 39dropdown.php:62
static fontSizeToPixels($fontSizeInPoints=11)
Calculate an (approximate) pixel size, based on a font points size.
Definition: Font.php:394
Create styles array
The data for the language used.
static sizeRow($sheet, $row=1)
Convert the height of a cell from user&#39;s units to pixels.
Definition: Excel5.php:93
static columnIndexFromString($pString='A')
Column index from string.
Definition: Cell.php:782
static getDefaultColumnWidthByFont(PHPExcel_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:571
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 span...
Definition: Excel5.php:175
static stringFromColumnIndex($pColumnIndex=0)
String from columnindex.
Definition: Cell.php:825
static cellDimensionToPixels($pValue=0, PHPExcel_Style_Font $pDefaultFont)
Convert column width from (intrinsic) Excel units to pixels.
Definition: Drawing.php:99