Subversion Repositories eFlore/Applications.cel

Rev

Rev 2388 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
2388 jpm 1
<?php
2
/**
3
 * PHPExcel
4
 *
5
 * Copyright (c) 2006 - 2013 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 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
24
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
25
 * @version    ##VERSION##, ##DATE##
26
 */
27
 
28
/**
29
 * PHPExcel_Shared_Excel5
30
 *
31
 * @category   PHPExcel
32
 * @package    PHPExcel_Shared
33
 * @copyright  Copyright (c) 2006 - 2013 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
}