File Source for Excel5.php

PHPExcel_Shared

# Source for file Excel5.php

Documentation is available at Excel5.php

1. <?php
2. /**
3. * PHPExcel
4. *
5. * Copyright (c) 2006 - 2011 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 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25. * @version 1.7.6, 2011-02-27
26. */
28. /**
29. * PHPExcel_Shared_Excel5
30. *
31. * @category PHPExcel
32. * @package PHPExcel_Shared
33. * @copyright Copyright (c) 2006 - 2011 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 integer $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();
51. $columnDimensions = $sheet->getColumnDimensions();
53. // first find the true column width in pixels (uncollapsed and unhidden)
54. if ( isset($columnDimensions[$col]) and $columnDimensions[$col]->getWidth() != -1 ) {
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);
61. } else if ($sheet->getDefaultColumnDimension()->getWidth() != -1) {
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);
68. } else {
70. // we don't even have any default column dimension. Width depends on default font
71. $pixelWidth = PHPExcel_Shared_Font:: getDefaultColumnWidthByFont($font, true);
72. }
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. }
81. return $effectivePixelWidth;
82. }
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();
98. $rowDimensions = $sheet->getRowDimensions();
100. // first find the true row height in pixels (uncollapsed and unhidden)
101. if ( isset($rowDimensions[$row]) and $rowDimensions[$row]->getRowHeight() != -1) {
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
108. } else if ($sheet->getDefaultRowDimension()->getRowHeight() != -1) {
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);
115. } else {
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);
121. }
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. }
130. return $effectivePixelRowHeight;
131. }
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 $startOffset Offset within start cell measured in 1/1024 of the cell width
140. * @param string $endColumn
141. * @param integer $endOffset 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;
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. }
155. // correct for offsetX in startcell
156. $distanceX -= (int) floor(self:: sizeCol($sheet, $startColumn) * $startOffsetX / 1024);
158. // correct for offsetX in endcell
159. $distanceX -= (int) floor(self:: sizeCol($sheet, $endColumn) * (1 - $endOffsetX / 1024));
161. return $distanceX;
162. }
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 string $startRow (1-based)
170. * @param integer $startOffset Offset within start cell measured in 1/256 of the cell height
171. * @param string $endRow (1-based)
172. * @param integer $endOffset 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;
179. // add the widths of the spanning rows
180. for ($row = $startRow; $row <= $endRow; ++$row) {
181. $distanceY += self:: sizeRow($sheet, $row);
182. }
184. // correct for offsetX in startcell
185. $distanceY -= (int) floor(self:: sizeRow($sheet, $startRow) * $startOffsetY / 256);
187. // correct for offsetX in endcell
188. $distanceY -= (int) floor(self:: sizeRow($sheet, $endRow) * (1 - $endOffsetY / 256));
190. return $distanceY;
191. }
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;
251. $x1 = $offsetX;
252. $y1 = $offsetY;
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
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. }
266. $width = $width + $x1 -1;
267. $height = $height + $y1 -1;
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. }
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. }
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. }
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
302. $startCoordinates = PHPExcel_Cell:: stringFromColumnIndex($col_start) . ($row_start + 1);
303. $endCoordinates = PHPExcel_Cell:: stringFromColumnIndex($col_end) . ($row_end + 1);
305. $twoAnchor = array(
306. 'startCoordinates' => $startCoordinates,
307. 'startOffsetX' => $x1,
308. 'startOffsetY' => $y1,
309. 'endCoordinates' => $endCoordinates,
310. 'endOffsetX' => $x2,
311. 'endOffsetY' => $y2,
312. );
314. return $twoAnchor;
315. }
317. }

Documentation generated on Sun, 27 Feb 2011 16:31:14 -0800 by phpDocumentor 1.4.3