72a0f119d5801e1bc737fc6dea8cb189d5379142.svn-base 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317
  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 ##VERSION##, ##DATE##
  26. */
  27. /**
  28. * PHPExcel_Shared_Excel5
  29. *
  30. * @category PHPExcel
  31. * @package PHPExcel_Shared
  32. * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  33. */
  34. class PHPExcel_Shared_Excel5
  35. {
  36. /**
  37. * Get the width of a column in pixels. We use the relationship y = ceil(7x) where
  38. * x is the width in intrinsic Excel units (measuring width in number of normal characters)
  39. * This holds for Arial 10
  40. *
  41. * @param PHPExcel_Worksheet $sheet The sheet
  42. * @param string $col The column
  43. * @return integer The width in pixels
  44. */
  45. public static function sizeCol($sheet, $col = 'A')
  46. {
  47. // default font of the workbook
  48. $font = $sheet->getParent()->getDefaultStyle()->getFont();
  49. $columnDimensions = $sheet->getColumnDimensions();
  50. // first find the true column width in pixels (uncollapsed and unhidden)
  51. if ( isset($columnDimensions[$col]) and $columnDimensions[$col]->getWidth() != -1 ) {
  52. // then we have column dimension with explicit width
  53. $columnDimension = $columnDimensions[$col];
  54. $width = $columnDimension->getWidth();
  55. $pixelWidth = PHPExcel_Shared_Drawing::cellDimensionToPixels($width, $font);
  56. } else if ($sheet->getDefaultColumnDimension()->getWidth() != -1) {
  57. // then we have default column dimension with explicit width
  58. $defaultColumnDimension = $sheet->getDefaultColumnDimension();
  59. $width = $defaultColumnDimension->getWidth();
  60. $pixelWidth = PHPExcel_Shared_Drawing::cellDimensionToPixels($width, $font);
  61. } else {
  62. // we don't even have any default column dimension. Width depends on default font
  63. $pixelWidth = PHPExcel_Shared_Font::getDefaultColumnWidthByFont($font, true);
  64. }
  65. // now find the effective column width in pixels
  66. if (isset($columnDimensions[$col]) and !$columnDimensions[$col]->getVisible()) {
  67. $effectivePixelWidth = 0;
  68. } else {
  69. $effectivePixelWidth = $pixelWidth;
  70. }
  71. return $effectivePixelWidth;
  72. }
  73. /**
  74. * Convert the height of a cell from user's units to pixels. By interpolation
  75. * the relationship is: y = 4/3x. If the height hasn't been set by the user we
  76. * use the default value. If the row is hidden we use a value of zero.
  77. *
  78. * @param PHPExcel_Worksheet $sheet The sheet
  79. * @param integer $row The row index (1-based)
  80. * @return integer The width in pixels
  81. */
  82. public static function sizeRow($sheet, $row = 1)
  83. {
  84. // default font of the workbook
  85. $font = $sheet->getParent()->getDefaultStyle()->getFont();
  86. $rowDimensions = $sheet->getRowDimensions();
  87. // first find the true row height in pixels (uncollapsed and unhidden)
  88. if ( isset($rowDimensions[$row]) and $rowDimensions[$row]->getRowHeight() != -1) {
  89. // then we have a row dimension
  90. $rowDimension = $rowDimensions[$row];
  91. $rowHeight = $rowDimension->getRowHeight();
  92. $pixelRowHeight = (int) ceil(4 * $rowHeight / 3); // here we assume Arial 10
  93. } else if ($sheet->getDefaultRowDimension()->getRowHeight() != -1) {
  94. // then we have a default row dimension with explicit height
  95. $defaultRowDimension = $sheet->getDefaultRowDimension();
  96. $rowHeight = $defaultRowDimension->getRowHeight();
  97. $pixelRowHeight = PHPExcel_Shared_Drawing::pointsToPixels($rowHeight);
  98. } else {
  99. // we don't even have any default row dimension. Height depends on default font
  100. $pointRowHeight = PHPExcel_Shared_Font::getDefaultRowHeightByFont($font);
  101. $pixelRowHeight = PHPExcel_Shared_Font::fontSizeToPixels($pointRowHeight);
  102. }
  103. // now find the effective row height in pixels
  104. if ( isset($rowDimensions[$row]) and !$rowDimensions[$row]->getVisible() ) {
  105. $effectivePixelRowHeight = 0;
  106. } else {
  107. $effectivePixelRowHeight = $pixelRowHeight;
  108. }
  109. return $effectivePixelRowHeight;
  110. }
  111. /**
  112. * Get the horizontal distance in pixels between two anchors
  113. * The distanceX is found as sum of all the spanning columns widths minus correction for the two offsets
  114. *
  115. * @param PHPExcel_Worksheet $sheet
  116. * @param string $startColumn
  117. * @param integer $startOffsetX Offset within start cell measured in 1/1024 of the cell width
  118. * @param string $endColumn
  119. * @param integer $endOffsetX Offset within end cell measured in 1/1024 of the cell width
  120. * @return integer Horizontal measured in pixels
  121. */
  122. public static function getDistanceX(PHPExcel_Worksheet $sheet, $startColumn = 'A', $startOffsetX = 0, $endColumn = 'A', $endOffsetX = 0)
  123. {
  124. $distanceX = 0;
  125. // add the widths of the spanning columns
  126. $startColumnIndex = PHPExcel_Cell::columnIndexFromString($startColumn) - 1; // 1-based
  127. $endColumnIndex = PHPExcel_Cell::columnIndexFromString($endColumn) - 1; // 1-based
  128. for ($i = $startColumnIndex; $i <= $endColumnIndex; ++$i) {
  129. $distanceX += self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($i));
  130. }
  131. // correct for offsetX in startcell
  132. $distanceX -= (int) floor(self::sizeCol($sheet, $startColumn) * $startOffsetX / 1024);
  133. // correct for offsetX in endcell
  134. $distanceX -= (int) floor(self::sizeCol($sheet, $endColumn) * (1 - $endOffsetX / 1024));
  135. return $distanceX;
  136. }
  137. /**
  138. * Get the vertical distance in pixels between two anchors
  139. * The distanceY is found as sum of all the spanning rows minus two offsets
  140. *
  141. * @param PHPExcel_Worksheet $sheet
  142. * @param integer $startRow (1-based)
  143. * @param integer $startOffsetY Offset within start cell measured in 1/256 of the cell height
  144. * @param integer $endRow (1-based)
  145. * @param integer $endOffsetY Offset within end cell measured in 1/256 of the cell height
  146. * @return integer Vertical distance measured in pixels
  147. */
  148. public static function getDistanceY(PHPExcel_Worksheet $sheet, $startRow = 1, $startOffsetY = 0, $endRow = 1, $endOffsetY = 0)
  149. {
  150. $distanceY = 0;
  151. // add the widths of the spanning rows
  152. for ($row = $startRow; $row <= $endRow; ++$row) {
  153. $distanceY += self::sizeRow($sheet, $row);
  154. }
  155. // correct for offsetX in startcell
  156. $distanceY -= (int) floor(self::sizeRow($sheet, $startRow) * $startOffsetY / 256);
  157. // correct for offsetX in endcell
  158. $distanceY -= (int) floor(self::sizeRow($sheet, $endRow) * (1 - $endOffsetY / 256));
  159. return $distanceY;
  160. }
  161. /**
  162. * Convert 1-cell anchor coordinates to 2-cell anchor coordinates
  163. * This function is ported from PEAR Spreadsheet_Writer_Excel with small modifications
  164. *
  165. * Calculate the vertices that define the position of the image as required by
  166. * the OBJ record.
  167. *
  168. * +------------+------------+
  169. * | A | B |
  170. * +-----+------------+------------+
  171. * | |(x1,y1) | |
  172. * | 1 |(A1)._______|______ |
  173. * | | | | |
  174. * | | | | |
  175. * +-----+----| BITMAP |-----+
  176. * | | | | |
  177. * | 2 | |______________. |
  178. * | | | (B2)|
  179. * | | | (x2,y2)|
  180. * +---- +------------+------------+
  181. *
  182. * Example of a bitmap that covers some of the area from cell A1 to cell B2.
  183. *
  184. * Based on the width and height of the bitmap we need to calculate 8 vars:
  185. * $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
  186. * The width and height of the cells are also variable and have to be taken into
  187. * account.
  188. * The values of $col_start and $row_start are passed in from the calling
  189. * function. The values of $col_end and $row_end are calculated by subtracting
  190. * the width and height of the bitmap from the width and height of the
  191. * underlying cells.
  192. * The vertices are expressed as a percentage of the underlying cell width as
  193. * follows (rhs values are in pixels):
  194. *
  195. * x1 = X / W *1024
  196. * y1 = Y / H *256
  197. * x2 = (X-1) / W *1024
  198. * y2 = (Y-1) / H *256
  199. *
  200. * Where: X is distance from the left side of the underlying cell
  201. * Y is distance from the top of the underlying cell
  202. * W is the width of the cell
  203. * H is the height of the cell
  204. *
  205. * @param PHPExcel_Worksheet $sheet
  206. * @param string $coordinates E.g. 'A1'
  207. * @param integer $offsetX Horizontal offset in pixels
  208. * @param integer $offsetY Vertical offset in pixels
  209. * @param integer $width Width in pixels
  210. * @param integer $height Height in pixels
  211. * @return array
  212. */
  213. public static function oneAnchor2twoAnchor($sheet, $coordinates, $offsetX, $offsetY, $width, $height)
  214. {
  215. list($column, $row) = PHPExcel_Cell::coordinateFromString($coordinates);
  216. $col_start = PHPExcel_Cell::columnIndexFromString($column) - 1;
  217. $row_start = $row - 1;
  218. $x1 = $offsetX;
  219. $y1 = $offsetY;
  220. // Initialise end cell to the same as the start cell
  221. $col_end = $col_start; // Col containing lower right corner of object
  222. $row_end = $row_start; // Row containing bottom right corner of object
  223. // Zero the specified offset if greater than the cell dimensions
  224. if ($x1 >= self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_start))) {
  225. $x1 = 0;
  226. }
  227. if ($y1 >= self::sizeRow($sheet, $row_start + 1)) {
  228. $y1 = 0;
  229. }
  230. $width = $width + $x1 -1;
  231. $height = $height + $y1 -1;
  232. // Subtract the underlying cell widths to find the end cell of the image
  233. while ($width >= self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_end))) {
  234. $width -= self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_end));
  235. ++$col_end;
  236. }
  237. // Subtract the underlying cell heights to find the end cell of the image
  238. while ($height >= self::sizeRow($sheet, $row_end + 1)) {
  239. $height -= self::sizeRow($sheet, $row_end + 1);
  240. ++$row_end;
  241. }
  242. // Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
  243. // with zero height or width.
  244. if (self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_start)) == 0) {
  245. return;
  246. }
  247. if (self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_end)) == 0) {
  248. return;
  249. }
  250. if (self::sizeRow($sheet, $row_start + 1) == 0) {
  251. return;
  252. }
  253. if (self::sizeRow($sheet, $row_end + 1) == 0) {
  254. return;
  255. }
  256. // Convert the pixel values to the percentage value expected by Excel
  257. $x1 = $x1 / self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_start)) * 1024;
  258. $y1 = $y1 / self::sizeRow($sheet, $row_start + 1) * 256;
  259. $x2 = ($width + 1) / self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_end)) * 1024; // Distance to right side of object
  260. $y2 = ($height + 1) / self::sizeRow($sheet, $row_end + 1) * 256; // Distance to bottom of object
  261. $startCoordinates = PHPExcel_Cell::stringFromColumnIndex($col_start) . ($row_start + 1);
  262. $endCoordinates = PHPExcel_Cell::stringFromColumnIndex($col_end) . ($row_end + 1);
  263. $twoAnchor = array(
  264. 'startCoordinates' => $startCoordinates,
  265. 'startOffsetX' => $x1,
  266. 'startOffsetY' => $y1,
  267. 'endCoordinates' => $endCoordinates,
  268. 'endOffsetX' => $x2,
  269. 'endOffsetY' => $y2,
  270. );
  271. return $twoAnchor;
  272. }
  273. }