5
* Copyright (c) 2006 - 2009 PHPExcel
7
* This library is free software; you can redistribute it and/or
8
* modify it under the terms of the GNU Lesser General Public
9
* License as published by the Free Software Foundation; either
10
* version 2.1 of the License, or (at your option) any later version.
12
* This library is distributed in the hope that it will be useful,
13
* but WITHOUT ANY WARRANTY; without even the implied warranty of
14
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15
* Lesser General Public License for more details.
17
* You should have received a copy of the GNU Lesser General Public
18
* License along with this library; if not, write to the Free Software
19
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
22
* @package PHPExcel_Shared
23
* @copyright Copyright (c) 2006 - 2009 PHPExcel (http://www.codeplex.com/PHPExcel)
24
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25
* @version 1.7.0, 2009-08-10
28
/** PHPExcel root directory */
29
if (!defined('PHPEXCEL_ROOT')) {
33
define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
37
require_once PHPEXCEL_ROOT . 'PHPExcel/Cell.php';
39
/** PHPExcel_Shared_Drawing */
40
require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/Drawing.php';
43
* PHPExcel_Shared_Excel5
46
* @package PHPExcel_Shared
47
* @copyright Copyright (c) 2006 - 2009 PHPExcel (http://www.codeplex.com/PHPExcel)
49
class PHPExcel_Shared_Excel5
52
* Get the width of a column in pixels. We use the relationship y = ceil(7x) where
53
* x is the width in intrinsic Excel units (measuring width in number of normal characters)
54
* This holds for Arial 10
56
* @param PHPExcel_Worksheet $sheet The sheet
57
* @param integer $col The column
58
* @return integer The width in pixels
60
public static function sizeCol($sheet, $col = 'A')
62
// default font size of workbook
63
$fontSize = $sheet->getParent()->getDefaultStyle()->getFont()->getSize();
65
$columnDimensions = $sheet->getColumnDimensions();
67
// first find the true column width in pixels (uncollapsed and unhidden)
68
if ( isset($columnDimensions[$col]) and $columnDimensions[$col]->getWidth() != -1 ) {
70
// then we have column dimension with explicit width
71
$columnDimension = $columnDimensions[$col];
72
$width = $columnDimension->getWidth();
73
$pixelWidth = PHPExcel_Shared_Drawing::cellDimensionToPixels($width, $fontSize);
75
} else if ($sheet->getDefaultColumnDimension()->getWidth() != -1) {
77
// then we have default column dimension with explicit width
78
$defaultColumnDimension = $sheet->getDefaultColumnDimension();
79
$width = $defaultColumnDimension->getWidth();
80
$pixelWidth = PHPExcel_Shared_Drawing::cellDimensionToPixels($width, $fontSize);
83
$pixelWidth = (int) 64 * $fontSize / 11; // here we interpolate from Calibri 11
86
// now find the effective column width in pixels
87
if (isset($columnDimensions[$col]) and !$columnDimensions[$col]->getVisible()) {
88
$effectivePixelWidth = 0;
90
$effectivePixelWidth = $pixelWidth;
93
return $effectivePixelWidth;
97
* Convert the height of a cell from user's units to pixels. By interpolation
98
* the relationship is: y = 4/3x. If the height hasn't been set by the user we
99
* use the default value. If the row is hidden we use a value of zero.
101
* @param PHPExcel_Worksheet $sheet The sheet
102
* @param integer $row The row index (1-based)
103
* @return integer The width in pixels
105
public static function sizeRow($sheet, $row = 1)
107
$rowDimensions = $sheet->getRowDimensions();
109
// first find the true row height in pixels (uncollapsed and unhidden)
110
if ( isset($rowDimensions[$row]) and $rowDimensions[$row]->getRowHeight() != -1) {
112
// then we have a row dimension
113
$rowDimension = $rowDimensions[$row];
114
$rowHeight = $rowDimension->getRowHeight();
115
$pixelRowHeight = (int) ceil(4 * $rowHeight / 3); // here we assume Arial 10
117
} else if ($sheet->getDefaultRowDimension()->getRowHeight() != -1) {
119
// then we have a default row dimension with explicit height
120
$defaultRowDimension = $sheet->getDefaultRowDimension();
121
$rowHeight = $defaultRowDimension->getRowHeight();
122
$pixelRowHeight = PHPExcel_Shared_Drawing::pointsToPixels($rowHeight);
125
$pixelRowHeight = 20; // here we assume Calibri 11
128
// now find the effective row height in pixels
129
if ( isset($rowDimensions[$row]) and !$rowDimensions[$row]->getVisible() ) {
130
$effectivePixelRowHeight = 0;
132
$effectivePixelRowHeight = $pixelRowHeight;
135
return $effectivePixelRowHeight;
139
* Get the horizontal distance in pixels between two anchors
140
* The distanceX is found as sum of all the spanning columns widths minus correction for the two offsets
142
* @param PHPExcel_Worksheet $sheet
143
* @param string $startColumn
144
* @param integer $startOffset Offset within start cell measured in 1/1024 of the cell width
145
* @param string $endColumn
146
* @param integer $endOffset Offset within end cell measured in 1/1024 of the cell width
147
* @return integer Horizontal measured in pixels
149
public static function getDistanceX(PHPExcel_Worksheet $sheet, $startColumn = 'A', $startOffsetX = 0, $endColumn = 'A', $endOffsetX = 0)
153
// add the widths of the spanning columns
154
$startColumnIndex = PHPExcel_Cell::columnIndexFromString($startColumn) - 1; // 1-based
155
$endColumnIndex = PHPExcel_Cell::columnIndexFromString($endColumn) - 1; // 1-based
156
for ($i = $startColumnIndex; $i <= $endColumnIndex; ++$i) {
157
$distanceX += self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($i));
160
// correct for offsetX in startcell
161
$distanceX -= (int) floor(self::sizeCol($sheet, $startColumn) * $startOffsetX / 1024);
163
// correct for offsetX in endcell
164
$distanceX -= (int) floor(self::sizeCol($sheet, $endColumn) * (1 - $endOffsetX / 1024));
170
* Get the vertical distance in pixels between two anchors
171
* The distanceY is found as sum of all the spanning rows minus two offsets
173
* @param PHPExcel_Worksheet $sheet
174
* @param string $startRow (1-based)
175
* @param integer $startOffset Offset within start cell measured in 1/256 of the cell height
176
* @param string $endRow (1-based)
177
* @param integer $endOffset Offset within end cell measured in 1/256 of the cell height
178
* @return integer Vertical distance measured in pixels
180
public static function getDistanceY(PHPExcel_Worksheet $sheet, $startRow = 1, $startOffsetY = 0, $endRow = 1, $endOffsetY = 0)
184
// add the widths of the spanning rows
185
for ($row = $startRow; $row <= $endRow; ++$row) {
186
$distanceY += self::sizeRow($sheet, $row);
189
// correct for offsetX in startcell
190
$distanceY -= (int) floor(self::sizeRow($sheet, $startRow) * $startOffsetY / 256);
192
// correct for offsetX in endcell
193
$distanceY -= (int) floor(self::sizeRow($sheet, $endRow) * (1 - $endOffsetY / 256));
199
* Convert 1-cell anchor coordinates to 2-cell anchor coordinates
200
* This function is ported from PEAR Spreadsheet_Writer_Excel with small modifications
202
* Calculate the vertices that define the position of the image as required by
205
* +------------+------------+
207
* +-----+------------+------------+
209
* | 1 |(A1)._______|______ |
212
* +-----+----| BITMAP |-----+
214
* | 2 | |______________. |
217
* +---- +------------+------------+
219
* Example of a bitmap that covers some of the area from cell A1 to cell B2.
221
* Based on the width and height of the bitmap we need to calculate 8 vars:
222
* $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
223
* The width and height of the cells are also variable and have to be taken into
225
* The values of $col_start and $row_start are passed in from the calling
226
* function. The values of $col_end and $row_end are calculated by subtracting
227
* the width and height of the bitmap from the width and height of the
229
* The vertices are expressed as a percentage of the underlying cell width as
230
* follows (rhs values are in pixels):
234
* x2 = (X-1) / W *1024
235
* y2 = (Y-1) / H *256
237
* Where: X is distance from the left side of the underlying cell
238
* Y is distance from the top of the underlying cell
239
* W is the width of the cell
240
* H is the height of the cell
242
* @param PHPExcel_Worksheet $sheet
243
* @param string $coordinates E.g. 'A1'
244
* @param integer $offsetX Horizontal offset in pixels
245
* @param integer $offsetY Vertical offset in pixels
246
* @param integer $width Width in pixels
247
* @param integer $height Height in pixels
250
public static function oneAnchor2twoAnchor($sheet, $coordinates, $offsetX, $offsetY, $width, $height)
252
list($column, $row) = PHPExcel_Cell::coordinateFromString($coordinates);
253
$col_start = PHPExcel_Cell::columnIndexFromString($column) - 1;
254
$row_start = $row - 1;
259
// Initialise end cell to the same as the start cell
260
$col_end = $col_start; // Col containing lower right corner of object
261
$row_end = $row_start; // Row containing bottom right corner of object
263
// Zero the specified offset if greater than the cell dimensions
264
if ($x1 >= self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_start))) {
267
if ($y1 >= self::sizeRow($sheet, $row_start + 1)) {
271
$width = $width + $x1 -1;
272
$height = $height + $y1 -1;
274
// Subtract the underlying cell widths to find the end cell of the image
275
while ($width >= self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_end))) {
276
$width -= self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_end));
280
// Subtract the underlying cell heights to find the end cell of the image
281
while ($height >= self::sizeRow($sheet, $row_end + 1)) {
282
$height -= self::sizeRow($sheet, $row_end + 1);
286
// Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
287
// with zero height or width.
288
if (self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_start)) == 0) {
291
if (self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_end)) == 0) {
294
if (self::sizeRow($sheet, $row_start + 1) == 0) {
297
if (self::sizeRow($sheet, $row_end + 1) == 0) {
301
// Convert the pixel values to the percentage value expected by Excel
302
$x1 = $x1 / self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_start)) * 1024;
303
$y1 = $y1 / self::sizeRow($sheet, $row_start + 1) * 256;
304
$x2 = $width / self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_end)) * 1024; // Distance to right side of object
305
$y2 = $height / self::sizeRow($sheet, $row_end + 1) * 256; // Distance to bottom of object
307
$startCoordinates = PHPExcel_Cell::stringFromColumnIndex($col_start) . ($row_start + 1);
308
$endCoordinates = PHPExcel_Cell::stringFromColumnIndex($col_end) . ($row_end + 1);
311
'startCoordinates' => $startCoordinates,
312
'startOffsetX' => $x1,
313
'startOffsetY' => $y1,
314
'endCoordinates' => $endCoordinates,
added
removed
libraries/PHPExcel/PHPExcel/Shared/Excel5.php
