1: <?php
2: /**
3: * PHPExcel
4: *
5: * Copyright (c) 2006 - 2014 PHPExcel
6: *
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.
11: *
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.
16: *
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
20: *
21: * @category PHPExcel
22: * @package PHPExcel_Shared
23: * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
24: * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25: * @version 1.8.0, 2014-03-02
26: */
27:
28: /**
29: * PHPExcel_Shared_Excel5
30: *
31: * @category PHPExcel
32: * @package PHPExcel_Shared
33: * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
34: */
35: class PHPExcel_Shared_Excel5
36: {
37: /**
38: * Get the width of a column in pixels. We use the relationship y = ceil(7x) where
39: * x is the width in intrinsic Excel units (measuring width in number of normal characters)
40: * This holds for Arial 10
41: *
42: * @param PHPExcel_Worksheet $sheet The sheet
43: * @param string $col The column
44: * @return integer The width in pixels
45: */
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:
84: /**
85: * Convert the height of a cell from user's units to pixels. By interpolation
86: * the relationship is: y = 4/3x. If the height hasn't been set by the user we
87: * use the default value. If the row is hidden we use a value of zero.
88: *
89: * @param PHPExcel_Worksheet $sheet The sheet
90: * @param integer $row The row index (1-based)
91: * @return integer The width in pixels
92: */
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:
133: /**
134: * Get the horizontal distance in pixels between two anchors
135: * The distanceX is found as sum of all the spanning columns widths minus correction for the two offsets
136: *
137: * @param PHPExcel_Worksheet $sheet
138: * @param string $startColumn
139: * @param integer $startOffsetX Offset within start cell measured in 1/1024 of the cell width
140: * @param string $endColumn
141: * @param integer $endOffsetX Offset within end cell measured in 1/1024 of the cell width
142: * @return integer Horizontal measured in pixels
143: */
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:
164: /**
165: * Get the vertical distance in pixels between two anchors
166: * The distanceY is found as sum of all the spanning rows minus two offsets
167: *
168: * @param PHPExcel_Worksheet $sheet
169: * @param integer $startRow (1-based)
170: * @param integer $startOffsetY Offset within start cell measured in 1/256 of the cell height
171: * @param integer $endRow (1-based)
172: * @param integer $endOffsetY Offset within end cell measured in 1/256 of the cell height
173: * @return integer Vertical distance measured in pixels
174: */
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:
193: /**
194: * Convert 1-cell anchor coordinates to 2-cell anchor coordinates
195: * This function is ported from PEAR Spreadsheet_Writer_Excel with small modifications
196: *
197: * Calculate the vertices that define the position of the image as required by
198: * the OBJ record.
199: *
200: * +------------+------------+
201: * | A | B |
202: * +-----+------------+------------+
203: * | |(x1,y1) | |
204: * | 1 |(A1)._______|______ |
205: * | | | | |
206: * | | | | |
207: * +-----+----| BITMAP |-----+
208: * | | | | |
209: * | 2 | |______________. |
210: * | | | (B2)|
211: * | | | (x2,y2)|
212: * +---- +------------+------------+
213: *
214: * Example of a bitmap that covers some of the area from cell A1 to cell B2.
215: *
216: * Based on the width and height of the bitmap we need to calculate 8 vars:
217: * $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
218: * The width and height of the cells are also variable and have to be taken into
219: * account.
220: * The values of $col_start and $row_start are passed in from the calling
221: * function. The values of $col_end and $row_end are calculated by subtracting
222: * the width and height of the bitmap from the width and height of the
223: * underlying cells.
224: * The vertices are expressed as a percentage of the underlying cell width as
225: * follows (rhs values are in pixels):
226: *
227: * x1 = X / W *1024
228: * y1 = Y / H *256
229: * x2 = (X-1) / W *1024
230: * y2 = (Y-1) / H *256
231: *
232: * Where: X is distance from the left side of the underlying cell
233: * Y is distance from the top of the underlying cell
234: * W is the width of the cell
235: * H is the height of the cell
236: *
237: * @param PHPExcel_Worksheet $sheet
238: * @param string $coordinates E.g. 'A1'
239: * @param integer $offsetX Horizontal offset in pixels
240: * @param integer $offsetY Vertical offset in pixels
241: * @param integer $width Width in pixels
242: * @param integer $height Height in pixels
243: * @return array
244: */
245: public static function oneAnchor2twoAnchor($sheet, $coordinates, $offsetX, $offsetY, $width, $height)
246: {
247: list($column, $row) = PHPExcel_Cell::coordinateFromString($coordinates);
248: $col_start = PHPExcel_Cell::columnIndexFromString($column) - 1;
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: }
318: