|
1
|
|
|
<?php |
|
2
|
|
|
/** |
|
3
|
|
|
* PHPExcel |
|
4
|
|
|
* |
|
5
|
|
|
* Copyright (c) 2006 - 2012 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_Style |
|
23
|
|
|
* @copyright Copyright (c) 2006 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel) |
|
24
|
|
|
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL |
|
25
|
|
|
* @version 1.7.7, 2012-05-19 |
|
26
|
|
|
*/ |
|
27
|
|
|
|
|
28
|
|
|
|
|
29
|
|
|
/** |
|
30
|
|
|
* PHPExcel_Style_NumberFormat |
|
31
|
|
|
* |
|
32
|
|
|
* @category PHPExcel |
|
33
|
|
|
* @package PHPExcel_Style |
|
34
|
|
|
* @copyright Copyright (c) 2006 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel) |
|
35
|
|
|
*/ |
|
36
|
|
|
class PHPExcel_Style_NumberFormat implements PHPExcel_IComparable |
|
37
|
|
|
{ |
|
38
|
|
|
/* Pre-defined formats */ |
|
39
|
|
|
const FORMAT_GENERAL = 'General'; |
|
40
|
|
|
|
|
41
|
|
|
const FORMAT_TEXT = '@'; |
|
42
|
|
|
|
|
43
|
|
|
const FORMAT_NUMBER = '0'; |
|
44
|
|
|
const FORMAT_NUMBER_00 = '0.00'; |
|
45
|
|
|
const FORMAT_NUMBER_COMMA_SEPARATED1 = '#,##0.00'; |
|
46
|
|
|
const FORMAT_NUMBER_COMMA_SEPARATED2 = '#,##0.00_-'; |
|
47
|
|
|
|
|
48
|
|
|
const FORMAT_PERCENTAGE = '0%'; |
|
49
|
|
|
const FORMAT_PERCENTAGE_00 = '0.00%'; |
|
50
|
|
|
|
|
51
|
|
|
const FORMAT_DATE_YYYYMMDD2 = 'yyyy-mm-dd'; |
|
52
|
|
|
const FORMAT_DATE_YYYYMMDD = 'yy-mm-dd'; |
|
53
|
|
|
const FORMAT_DATE_DDMMYYYY = 'dd/mm/yy'; |
|
54
|
|
|
const FORMAT_DATE_DMYSLASH = 'd/m/y'; |
|
55
|
|
|
const FORMAT_DATE_DMYMINUS = 'd-m-y'; |
|
56
|
|
|
const FORMAT_DATE_DMMINUS = 'd-m'; |
|
57
|
|
|
const FORMAT_DATE_MYMINUS = 'm-y'; |
|
58
|
|
|
const FORMAT_DATE_XLSX14 = 'mm-dd-yy'; |
|
59
|
|
|
const FORMAT_DATE_XLSX15 = 'd-mmm-yy'; |
|
60
|
|
|
const FORMAT_DATE_XLSX16 = 'd-mmm'; |
|
61
|
|
|
const FORMAT_DATE_XLSX17 = 'mmm-yy'; |
|
62
|
|
|
const FORMAT_DATE_XLSX22 = 'm/d/yy h:mm'; |
|
63
|
|
|
const FORMAT_DATE_DATETIME = 'd/m/y h:mm'; |
|
64
|
|
|
const FORMAT_DATE_TIME1 = 'h:mm AM/PM'; |
|
65
|
|
|
const FORMAT_DATE_TIME2 = 'h:mm:ss AM/PM'; |
|
66
|
|
|
const FORMAT_DATE_TIME3 = 'h:mm'; |
|
67
|
|
|
const FORMAT_DATE_TIME4 = 'h:mm:ss'; |
|
68
|
|
|
const FORMAT_DATE_TIME5 = 'mm:ss'; |
|
69
|
|
|
const FORMAT_DATE_TIME6 = 'h:mm:ss'; |
|
70
|
|
|
const FORMAT_DATE_TIME7 = 'i:s.S'; |
|
71
|
|
|
const FORMAT_DATE_TIME8 = 'h:mm:ss;@'; |
|
72
|
|
|
const FORMAT_DATE_YYYYMMDDSLASH = 'yy/mm/dd;@'; |
|
73
|
|
|
|
|
74
|
|
|
const FORMAT_CURRENCY_USD_SIMPLE = '"$"#,##0.00_-'; |
|
75
|
|
|
const FORMAT_CURRENCY_USD = '$#,##0_-'; |
|
76
|
|
|
const FORMAT_CURRENCY_EUR_SIMPLE = '[$EUR ]#,##0.00_-'; |
|
77
|
|
|
|
|
78
|
|
|
/** |
|
79
|
|
|
* Excel built-in number formats |
|
80
|
|
|
* |
|
81
|
|
|
* @var array |
|
82
|
|
|
*/ |
|
83
|
|
|
private static $_builtInFormats; |
|
84
|
|
|
|
|
85
|
|
|
/** |
|
86
|
|
|
* Excel built-in number formats (flipped, for faster lookups) |
|
87
|
|
|
* |
|
88
|
|
|
* @var array |
|
89
|
|
|
*/ |
|
90
|
|
|
private static $_flippedBuiltInFormats; |
|
91
|
|
|
|
|
92
|
|
|
/** |
|
93
|
|
|
* Format Code |
|
94
|
|
|
* |
|
95
|
|
|
* @var string |
|
96
|
|
|
*/ |
|
97
|
|
|
private $_formatCode = PHPExcel_Style_NumberFormat::FORMAT_GENERAL; |
|
98
|
|
|
|
|
99
|
|
|
/** |
|
100
|
|
|
* Built-in format Code |
|
101
|
|
|
* |
|
102
|
|
|
* @var string |
|
103
|
|
|
*/ |
|
104
|
|
|
private $_builtInFormatCode = 0; |
|
105
|
|
|
|
|
106
|
|
|
/** |
|
107
|
|
|
* Parent Borders |
|
108
|
|
|
* |
|
109
|
|
|
* @var _parentPropertyName string |
|
110
|
|
|
*/ |
|
111
|
|
|
private $_parentPropertyName; |
|
|
|
|
|
|
112
|
|
|
|
|
113
|
|
|
/** |
|
114
|
|
|
* Supervisor? |
|
115
|
|
|
* |
|
116
|
|
|
* @var boolean |
|
117
|
|
|
*/ |
|
118
|
|
|
private $_isSupervisor; |
|
119
|
|
|
|
|
120
|
|
|
/** |
|
121
|
|
|
* Parent. Only used for supervisor |
|
122
|
|
|
* |
|
123
|
|
|
* @var PHPExcel_Style |
|
124
|
|
|
*/ |
|
125
|
|
|
private $_parent; |
|
126
|
|
|
|
|
127
|
|
|
/** |
|
128
|
|
|
* Create a new PHPExcel_Style_NumberFormat |
|
129
|
|
|
* |
|
130
|
|
|
* @param boolean $isSupervisor Flag indicating if this is a supervisor or not |
|
131
|
|
|
*/ |
|
132
|
|
|
public function __construct($isSupervisor = false) |
|
133
|
|
|
{ |
|
134
|
|
|
// Supervisor? |
|
135
|
|
|
$this->_isSupervisor = $isSupervisor; |
|
136
|
|
|
} |
|
137
|
|
|
|
|
138
|
|
|
/** |
|
139
|
|
|
* Bind parent. Only used for supervisor |
|
140
|
|
|
* |
|
141
|
|
|
* @param PHPExcel_Style $parent |
|
142
|
|
|
* @return PHPExcel_Style_NumberFormat |
|
143
|
|
|
*/ |
|
144
|
|
|
public function bindParent($parent) |
|
145
|
|
|
{ |
|
146
|
|
|
$this->_parent = $parent; |
|
147
|
|
|
} |
|
148
|
|
|
|
|
149
|
|
|
/** |
|
150
|
|
|
* Is this a supervisor or a real style component? |
|
151
|
|
|
* |
|
152
|
|
|
* @return boolean |
|
153
|
|
|
*/ |
|
154
|
|
|
public function getIsSupervisor() |
|
155
|
|
|
{ |
|
156
|
|
|
return $this->_isSupervisor; |
|
157
|
|
|
} |
|
158
|
|
|
|
|
159
|
|
|
/** |
|
160
|
|
|
* Get the shared style component for the currently active cell in currently active sheet. |
|
161
|
|
|
* Only used for style supervisor |
|
162
|
|
|
* |
|
163
|
|
|
* @return PHPExcel_Style_NumberFormat |
|
164
|
|
|
*/ |
|
165
|
|
|
public function getSharedComponent() |
|
166
|
|
|
{ |
|
167
|
|
|
return $this->_parent->getSharedComponent()->getNumberFormat(); |
|
168
|
|
|
} |
|
169
|
|
|
|
|
170
|
|
|
/** |
|
171
|
|
|
* Get the currently active sheet. Only used for supervisor |
|
172
|
|
|
* |
|
173
|
|
|
* @return PHPExcel_Worksheet |
|
174
|
|
|
*/ |
|
175
|
|
|
public function getActiveSheet() |
|
176
|
|
|
{ |
|
177
|
|
|
return $this->_parent->getActiveSheet(); |
|
178
|
|
|
} |
|
179
|
|
|
|
|
180
|
|
|
/** |
|
181
|
|
|
* Get the currently active cell coordinate in currently active sheet. |
|
182
|
|
|
* Only used for supervisor |
|
183
|
|
|
* |
|
184
|
|
|
* @return string E.g. 'A1' |
|
185
|
|
|
*/ |
|
186
|
|
|
public function getSelectedCells() |
|
187
|
|
|
{ |
|
188
|
|
|
return $this->getActiveSheet()->getSelectedCells(); |
|
189
|
|
|
} |
|
190
|
|
|
|
|
191
|
|
|
/** |
|
192
|
|
|
* Get the currently active cell coordinate in currently active sheet. |
|
193
|
|
|
* Only used for supervisor |
|
194
|
|
|
* |
|
195
|
|
|
* @return string E.g. 'A1' |
|
196
|
|
|
*/ |
|
197
|
|
|
public function getActiveCell() |
|
198
|
|
|
{ |
|
199
|
|
|
return $this->getActiveSheet()->getActiveCell(); |
|
200
|
|
|
} |
|
201
|
|
|
|
|
202
|
|
|
/** |
|
203
|
|
|
* Build style array from subcomponents |
|
204
|
|
|
* |
|
205
|
|
|
* @param array $array |
|
206
|
|
|
* @return array |
|
207
|
|
|
*/ |
|
208
|
|
|
public function getStyleArray($array) |
|
209
|
|
|
{ |
|
210
|
|
|
return array('numberformat' => $array); |
|
211
|
|
|
} |
|
212
|
|
|
|
|
213
|
|
|
/** |
|
214
|
|
|
* Apply styles from array |
|
215
|
|
|
* |
|
216
|
|
|
* <code> |
|
217
|
|
|
* $objPHPExcel->getActiveSheet()->getStyle('B2')->getNumberFormat()->applyFromArray( |
|
218
|
|
|
* array( |
|
219
|
|
|
* 'code' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE |
|
220
|
|
|
* ) |
|
221
|
|
|
* ); |
|
222
|
|
|
* </code> |
|
223
|
|
|
* |
|
224
|
|
|
* @param array $pStyles Array containing style information |
|
225
|
|
|
* @throws Exception |
|
226
|
|
|
* @return PHPExcel_Style_NumberFormat |
|
227
|
|
|
*/ |
|
228
|
|
|
public function applyFromArray($pStyles = null) |
|
229
|
|
|
{ |
|
230
|
|
|
if (is_array($pStyles)) { |
|
231
|
|
|
if ($this->_isSupervisor) { |
|
232
|
|
|
$this->getActiveSheet()->getStyle($this->getSelectedCells())->applyFromArray($this->getStyleArray($pStyles)); |
|
233
|
|
|
} else { |
|
234
|
|
|
if (array_key_exists('code', $pStyles)) { |
|
235
|
|
|
$this->setFormatCode($pStyles['code']); |
|
236
|
|
|
} |
|
237
|
|
|
} |
|
238
|
|
|
} else { |
|
239
|
|
|
throw new Exception("Invalid style array passed."); |
|
240
|
|
|
} |
|
241
|
|
|
return $this; |
|
242
|
|
|
} |
|
243
|
|
|
|
|
244
|
|
|
/** |
|
245
|
|
|
* Get Format Code |
|
246
|
|
|
* |
|
247
|
|
|
* @return string |
|
248
|
|
|
*/ |
|
249
|
|
|
public function getFormatCode() |
|
250
|
|
|
{ |
|
251
|
|
|
if ($this->_isSupervisor) { |
|
252
|
|
|
return $this->getSharedComponent()->getFormatCode(); |
|
253
|
|
|
} |
|
254
|
|
|
if ($this->_builtInFormatCode !== false) |
|
255
|
|
|
{ |
|
256
|
|
|
return self::builtInFormatCode($this->_builtInFormatCode); |
|
257
|
|
|
} |
|
258
|
|
|
return $this->_formatCode; |
|
259
|
|
|
} |
|
260
|
|
|
|
|
261
|
|
|
/** |
|
262
|
|
|
* Set Format Code |
|
263
|
|
|
* |
|
264
|
|
|
* @param string $pValue |
|
265
|
|
|
* @return PHPExcel_Style_NumberFormat |
|
266
|
|
|
*/ |
|
267
|
|
|
public function setFormatCode($pValue = PHPExcel_Style_NumberFormat::FORMAT_GENERAL) |
|
268
|
|
|
{ |
|
269
|
|
|
if ($pValue == '') { |
|
270
|
|
|
$pValue = PHPExcel_Style_NumberFormat::FORMAT_GENERAL; |
|
271
|
|
|
} |
|
272
|
|
|
if ($this->_isSupervisor) { |
|
273
|
|
|
$styleArray = $this->getStyleArray(array('code' => $pValue)); |
|
274
|
|
|
$this->getActiveSheet()->getStyle($this->getSelectedCells())->applyFromArray($styleArray); |
|
275
|
|
|
} else { |
|
276
|
|
|
$this->_formatCode = $pValue; |
|
277
|
|
|
$this->_builtInFormatCode = self::builtInFormatCodeIndex($pValue); |
|
|
|
|
|
|
278
|
|
|
} |
|
279
|
|
|
return $this; |
|
280
|
|
|
} |
|
281
|
|
|
|
|
282
|
|
|
/** |
|
283
|
|
|
* Get Built-In Format Code |
|
284
|
|
|
* |
|
285
|
|
|
* @return int |
|
286
|
|
|
*/ |
|
287
|
|
|
public function getBuiltInFormatCode() |
|
288
|
|
|
{ |
|
289
|
|
|
if ($this->_isSupervisor) { |
|
290
|
|
|
return $this->getSharedComponent()->getBuiltInFormatCode(); |
|
291
|
|
|
} |
|
292
|
|
|
return $this->_builtInFormatCode; |
|
293
|
|
|
} |
|
294
|
|
|
|
|
295
|
|
|
/** |
|
296
|
|
|
* Set Built-In Format Code |
|
297
|
|
|
* |
|
298
|
|
|
* @param int $pValue |
|
299
|
|
|
* @return PHPExcel_Style_NumberFormat |
|
300
|
|
|
*/ |
|
301
|
|
|
public function setBuiltInFormatCode($pValue = 0) |
|
302
|
|
|
{ |
|
303
|
|
|
|
|
304
|
|
|
if ($this->_isSupervisor) { |
|
305
|
|
|
$styleArray = $this->getStyleArray(array('code' => self::builtInFormatCode($pValue))); |
|
306
|
|
|
$this->getActiveSheet()->getStyle($this->getSelectedCells())->applyFromArray($styleArray); |
|
307
|
|
|
} else { |
|
308
|
|
|
$this->_builtInFormatCode = $pValue; |
|
|
|
|
|
|
309
|
|
|
$this->_formatCode = self::builtInFormatCode($pValue); |
|
310
|
|
|
} |
|
311
|
|
|
return $this; |
|
312
|
|
|
} |
|
313
|
|
|
|
|
314
|
|
|
/** |
|
315
|
|
|
* Fill built-in format codes |
|
316
|
|
|
*/ |
|
317
|
|
|
private static function fillBuiltInFormatCodes() |
|
318
|
|
|
{ |
|
319
|
|
|
// Built-in format codes |
|
320
|
|
|
if (is_null(self::$_builtInFormats)) { |
|
321
|
|
|
self::$_builtInFormats = array(); |
|
322
|
|
|
|
|
323
|
|
|
// General |
|
324
|
|
|
self::$_builtInFormats[0] = PHPExcel_Style_NumberFormat::FORMAT_GENERAL; |
|
325
|
|
|
self::$_builtInFormats[1] = '0'; |
|
326
|
|
|
self::$_builtInFormats[2] = '0.00'; |
|
327
|
|
|
self::$_builtInFormats[3] = '#,##0'; |
|
328
|
|
|
self::$_builtInFormats[4] = '#,##0.00'; |
|
329
|
|
|
|
|
330
|
|
|
self::$_builtInFormats[9] = '0%'; |
|
331
|
|
|
self::$_builtInFormats[10] = '0.00%'; |
|
332
|
|
|
self::$_builtInFormats[11] = '0.00E+00'; |
|
333
|
|
|
self::$_builtInFormats[12] = '# ?/?'; |
|
334
|
|
|
self::$_builtInFormats[13] = '# ??/??'; |
|
335
|
|
|
self::$_builtInFormats[14] = 'mm-dd-yy'; |
|
336
|
|
|
self::$_builtInFormats[15] = 'd-mmm-yy'; |
|
337
|
|
|
self::$_builtInFormats[16] = 'd-mmm'; |
|
338
|
|
|
self::$_builtInFormats[17] = 'mmm-yy'; |
|
339
|
|
|
self::$_builtInFormats[18] = 'h:mm AM/PM'; |
|
340
|
|
|
self::$_builtInFormats[19] = 'h:mm:ss AM/PM'; |
|
341
|
|
|
self::$_builtInFormats[20] = 'h:mm'; |
|
342
|
|
|
self::$_builtInFormats[21] = 'h:mm:ss'; |
|
343
|
|
|
self::$_builtInFormats[22] = 'm/d/yy h:mm'; |
|
344
|
|
|
|
|
345
|
|
|
self::$_builtInFormats[37] = '#,##0 ;(#,##0)'; |
|
346
|
|
|
self::$_builtInFormats[38] = '#,##0 ;[Red](#,##0)'; |
|
347
|
|
|
self::$_builtInFormats[39] = '#,##0.00;(#,##0.00)'; |
|
348
|
|
|
self::$_builtInFormats[40] = '#,##0.00;[Red](#,##0.00)'; |
|
349
|
|
|
|
|
350
|
|
|
self::$_builtInFormats[44] = '_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)'; |
|
351
|
|
|
self::$_builtInFormats[45] = 'mm:ss'; |
|
352
|
|
|
self::$_builtInFormats[46] = '[h]:mm:ss'; |
|
353
|
|
|
self::$_builtInFormats[47] = 'mmss.0'; |
|
354
|
|
|
self::$_builtInFormats[48] = '##0.0E+0'; |
|
355
|
|
|
self::$_builtInFormats[49] = '@'; |
|
356
|
|
|
|
|
357
|
|
|
// CHT |
|
358
|
|
|
self::$_builtInFormats[27] = '[$-404]e/m/d'; |
|
359
|
|
|
self::$_builtInFormats[30] = 'm/d/yy'; |
|
360
|
|
|
self::$_builtInFormats[36] = '[$-404]e/m/d'; |
|
361
|
|
|
self::$_builtInFormats[50] = '[$-404]e/m/d'; |
|
362
|
|
|
self::$_builtInFormats[57] = '[$-404]e/m/d'; |
|
363
|
|
|
|
|
364
|
|
|
// THA |
|
365
|
|
|
self::$_builtInFormats[59] = 't0'; |
|
366
|
|
|
self::$_builtInFormats[60] = 't0.00'; |
|
367
|
|
|
self::$_builtInFormats[61] = 't#,##0'; |
|
368
|
|
|
self::$_builtInFormats[62] = 't#,##0.00'; |
|
369
|
|
|
self::$_builtInFormats[67] = 't0%'; |
|
370
|
|
|
self::$_builtInFormats[68] = 't0.00%'; |
|
371
|
|
|
self::$_builtInFormats[69] = 't# ?/?'; |
|
372
|
|
|
self::$_builtInFormats[70] = 't# ??/??'; |
|
373
|
|
|
|
|
374
|
|
|
// Flip array (for faster lookups) |
|
375
|
|
|
self::$_flippedBuiltInFormats = array_flip(self::$_builtInFormats); |
|
376
|
|
|
} |
|
377
|
|
|
} |
|
378
|
|
|
|
|
379
|
|
|
/** |
|
380
|
|
|
* Get built-in format code |
|
381
|
|
|
* |
|
382
|
|
|
* @param int $pIndex |
|
383
|
|
|
* @return string |
|
384
|
|
|
*/ |
|
385
|
|
|
public static function builtInFormatCode($pIndex) |
|
386
|
|
|
{ |
|
387
|
|
|
// Clean parameter |
|
388
|
|
|
$pIndex = intval($pIndex); |
|
389
|
|
|
|
|
390
|
|
|
// Ensure built-in format codes are available |
|
391
|
|
|
self::fillBuiltInFormatCodes(); |
|
392
|
|
|
|
|
393
|
|
|
// Lookup format code |
|
394
|
|
|
if (isset(self::$_builtInFormats[$pIndex])) { |
|
395
|
|
|
return self::$_builtInFormats[$pIndex]; |
|
396
|
|
|
} |
|
397
|
|
|
|
|
398
|
|
|
return ''; |
|
399
|
|
|
} |
|
400
|
|
|
|
|
401
|
|
|
/** |
|
402
|
|
|
* Get built-in format code index |
|
403
|
|
|
* |
|
404
|
|
|
* @param string $formatCode |
|
405
|
|
|
* @return int|boolean |
|
406
|
|
|
*/ |
|
407
|
|
|
public static function builtInFormatCodeIndex($formatCode) |
|
408
|
|
|
{ |
|
409
|
|
|
// Ensure built-in format codes are available |
|
410
|
|
|
self::fillBuiltInFormatCodes(); |
|
411
|
|
|
|
|
412
|
|
|
// Lookup format code |
|
413
|
|
|
if (isset(self::$_flippedBuiltInFormats[$formatCode])) { |
|
414
|
|
|
return self::$_flippedBuiltInFormats[$formatCode]; |
|
415
|
|
|
} |
|
416
|
|
|
|
|
417
|
|
|
return false; |
|
418
|
|
|
} |
|
419
|
|
|
|
|
420
|
|
|
/** |
|
421
|
|
|
* Get hash code |
|
422
|
|
|
* |
|
423
|
|
|
* @return string Hash code |
|
424
|
|
|
*/ |
|
425
|
|
|
public function getHashCode() |
|
426
|
|
|
{ |
|
427
|
|
|
if ($this->_isSupervisor) { |
|
428
|
|
|
return $this->getSharedComponent()->getHashCode(); |
|
429
|
|
|
} |
|
430
|
|
|
return md5( |
|
431
|
|
|
$this->_formatCode |
|
432
|
|
|
. $this->_builtInFormatCode |
|
433
|
|
|
. __CLASS__ |
|
434
|
|
|
); |
|
435
|
|
|
} |
|
436
|
|
|
|
|
437
|
|
|
/** |
|
438
|
|
|
* Implement PHP __clone to create a deep clone, not just a shallow copy. |
|
439
|
|
|
*/ |
|
440
|
|
|
public function __clone() |
|
441
|
|
|
{ |
|
442
|
|
|
$vars = get_object_vars($this); |
|
443
|
|
|
foreach ($vars as $key => $value) { |
|
444
|
|
|
if ((is_object($value)) && ($key != '_parent')) { |
|
445
|
|
|
$this->$key = clone $value; |
|
446
|
|
|
} else { |
|
447
|
|
|
$this->$key = $value; |
|
448
|
|
|
} |
|
449
|
|
|
} |
|
450
|
|
|
} |
|
451
|
|
|
|
|
452
|
|
|
/** |
|
453
|
|
|
* Search/replace values to convert Excel date/time format masks to PHP format masks |
|
454
|
|
|
* |
|
455
|
|
|
* @var array |
|
456
|
|
|
*/ |
|
457
|
|
|
private static $_dateFormatReplacements = array( |
|
458
|
|
|
// first remove escapes related to non-format characters |
|
459
|
|
|
'\\' => '', |
|
460
|
|
|
// 12-hour suffix |
|
461
|
|
|
'am/pm' => 'A', |
|
462
|
|
|
// 4-digit year |
|
463
|
|
|
'yyyy' => 'Y', |
|
464
|
|
|
// 2-digit year |
|
465
|
|
|
'yy' => 'y', |
|
466
|
|
|
// first letter of month - no php equivalent |
|
467
|
|
|
'mmmmm' => 'M', |
|
468
|
|
|
// full month name |
|
469
|
|
|
'mmmm' => 'F', |
|
470
|
|
|
// short month name |
|
471
|
|
|
'mmm' => 'M', |
|
472
|
|
|
// mm is minutes if time or month w/leading zero |
|
473
|
|
|
':mm' => ':i', |
|
474
|
|
|
// month leading zero |
|
475
|
|
|
'mm' => 'm', |
|
476
|
|
|
// month no leading zero |
|
477
|
|
|
'm' => 'n', |
|
478
|
|
|
// full day of week name |
|
479
|
|
|
'dddd' => 'l', |
|
480
|
|
|
// short day of week name |
|
481
|
|
|
'ddd' => 'D', |
|
482
|
|
|
// days leading zero |
|
483
|
|
|
'dd' => 'd', |
|
484
|
|
|
// days no leading zero |
|
485
|
|
|
'd' => 'j', |
|
486
|
|
|
// seconds |
|
487
|
|
|
'ss' => 's', |
|
488
|
|
|
// fractional seconds - no php equivalent |
|
489
|
|
|
'.s' => '' |
|
490
|
|
|
); |
|
491
|
|
|
/** |
|
492
|
|
|
* Search/replace values to convert Excel date/time format masks hours to PHP format masks (24 hr clock) |
|
493
|
|
|
* |
|
494
|
|
|
* @var array |
|
495
|
|
|
*/ |
|
496
|
|
|
private static $_dateFormatReplacements24 = array( |
|
497
|
|
|
'hh' => 'H', |
|
498
|
|
|
'h' => 'G' |
|
499
|
|
|
); |
|
500
|
|
|
/** |
|
501
|
|
|
* Search/replace values to convert Excel date/time format masks hours to PHP format masks (12 hr clock) |
|
502
|
|
|
* |
|
503
|
|
|
* @var array |
|
504
|
|
|
*/ |
|
505
|
|
|
private static $_dateFormatReplacements12 = array( |
|
506
|
|
|
'hh' => 'h', |
|
507
|
|
|
'h' => 'g' |
|
508
|
|
|
); |
|
509
|
|
|
|
|
510
|
|
|
/** |
|
511
|
|
|
* Convert a value in a pre-defined format to a PHP string |
|
512
|
|
|
* |
|
513
|
|
|
* @param mixed $value Value to format |
|
514
|
|
|
* @param string $format Format code |
|
515
|
|
|
* @param array $callBack Callback function for additional formatting of string |
|
516
|
|
|
* @return string Formatted string |
|
517
|
|
|
*/ |
|
518
|
|
|
public static function toFormattedString($value = '', $format = '', $callBack = null) |
|
519
|
|
|
{ |
|
520
|
|
|
// For now we do not treat strings although section 4 of a format code affects strings |
|
521
|
|
|
if (!is_numeric($value)) return $value; |
|
|
|
|
|
|
522
|
|
|
|
|
523
|
|
|
// For 'General' format code, we just pass the value although this is not entirely the way Excel does it, |
|
524
|
|
|
// it seems to round numbers to a total of 10 digits. |
|
525
|
|
|
if (($format === PHPExcel_Style_NumberFormat::FORMAT_GENERAL) || ($format === PHPExcel_Style_NumberFormat::FORMAT_TEXT)) { |
|
526
|
|
|
return $value; |
|
527
|
|
|
} |
|
528
|
|
|
|
|
529
|
|
|
// Get the sections, there can be up to four sections |
|
530
|
|
|
$sections = explode(';', $format); |
|
531
|
|
|
|
|
532
|
|
|
// Fetch the relevant section depending on whether number is positive, negative, or zero? |
|
533
|
|
|
// Text not supported yet. |
|
534
|
|
|
// Here is how the sections apply to various values in Excel: |
|
535
|
|
|
// 1 section: [POSITIVE/NEGATIVE/ZERO/TEXT] |
|
536
|
|
|
// 2 sections: [POSITIVE/ZERO/TEXT] [NEGATIVE] |
|
537
|
|
|
// 3 sections: [POSITIVE/TEXT] [NEGATIVE] [ZERO] |
|
538
|
|
|
// 4 sections: [POSITIVE] [NEGATIVE] [ZERO] [TEXT] |
|
539
|
|
|
switch (count($sections)) { |
|
540
|
|
|
case 1: |
|
541
|
|
|
$format = $sections[0]; |
|
542
|
|
|
break; |
|
543
|
|
|
|
|
544
|
|
|
case 2: |
|
545
|
|
|
$format = ($value >= 0) ? $sections[0] : $sections[1]; |
|
546
|
|
|
$value = abs($value); // Use the absolute value |
|
547
|
|
|
break; |
|
548
|
|
|
|
|
549
|
|
View Code Duplication |
case 3: |
|
550
|
|
|
$format = ($value > 0) ? |
|
551
|
|
|
$sections[0] : ( ($value < 0) ? |
|
552
|
|
|
$sections[1] : $sections[2]); |
|
553
|
|
|
$value = abs($value); // Use the absolute value |
|
554
|
|
|
break; |
|
555
|
|
|
|
|
556
|
|
View Code Duplication |
case 4: |
|
557
|
|
|
$format = ($value > 0) ? |
|
558
|
|
|
$sections[0] : ( ($value < 0) ? |
|
559
|
|
|
$sections[1] : $sections[2]); |
|
560
|
|
|
$value = abs($value); // Use the absolute value |
|
561
|
|
|
break; |
|
562
|
|
|
|
|
563
|
|
|
default: |
|
564
|
|
|
// something is wrong, just use first section |
|
565
|
|
|
$format = $sections[0]; |
|
566
|
|
|
break; |
|
567
|
|
|
} |
|
568
|
|
|
|
|
569
|
|
|
// Save format with color information for later use below |
|
570
|
|
|
$formatColor = $format; |
|
571
|
|
|
|
|
572
|
|
|
// Strip color information |
|
573
|
|
|
$color_regex = '/^\\[[a-zA-Z]+\\]/'; |
|
574
|
|
|
$format = preg_replace($color_regex, '', $format); |
|
575
|
|
|
|
|
576
|
|
|
// Let's begin inspecting the format and converting the value to a formatted string |
|
577
|
|
|
if (preg_match('/^(\[\$[A-Z]*-[0-9A-F]*\])*[hmsdy]/i', $format)) { // datetime format |
|
578
|
|
|
// dvc: convert Excel formats to PHP date formats |
|
579
|
|
|
|
|
580
|
|
|
// strip off first part containing e.g. [$-F800] or [$USD-409] |
|
581
|
|
|
// general syntax: [$<Currency string>-<language info>] |
|
582
|
|
|
// language info is in hexadecimal |
|
583
|
|
|
$format = preg_replace('/^(\[\$[A-Z]*-[0-9A-F]*\])/i', '', $format); |
|
584
|
|
|
|
|
585
|
|
|
// OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case |
|
586
|
|
|
$format = strtolower($format); |
|
587
|
|
|
|
|
588
|
|
|
$format = strtr($format,self::$_dateFormatReplacements); |
|
589
|
|
|
if (!strpos($format,'A')) { // 24-hour time format |
|
590
|
|
|
$format = strtr($format,self::$_dateFormatReplacements24); |
|
591
|
|
|
} else { // 12-hour time format |
|
592
|
|
|
$format = strtr($format,self::$_dateFormatReplacements12); |
|
593
|
|
|
} |
|
594
|
|
|
|
|
595
|
|
|
$dateObj = PHPExcel_Shared_Date::ExcelToPHPObject($value); |
|
596
|
|
|
$value = $dateObj->format($format); |
|
597
|
|
|
|
|
598
|
|
|
} else if (preg_match('/%$/', $format)) { // % number format |
|
599
|
|
|
if ($format === self::FORMAT_PERCENTAGE) { |
|
600
|
|
|
$value = round( (100 * $value), 0) . '%'; |
|
601
|
|
|
} else { |
|
602
|
|
|
if (preg_match('/\.[#0]+/i', $format, $m)) { |
|
603
|
|
|
$s = substr($m[0], 0, 1) . (strlen($m[0]) - 1); |
|
604
|
|
|
$format = str_replace($m[0], $s, $format); |
|
605
|
|
|
} |
|
606
|
|
|
if (preg_match('/^[#0]+/', $format, $m)) { |
|
607
|
|
|
$format = str_replace($m[0], strlen($m[0]), $format); |
|
608
|
|
|
} |
|
609
|
|
|
$format = '%' . str_replace('%', 'f%%', $format); |
|
610
|
|
|
|
|
611
|
|
|
$value = sprintf($format, 100 * $value); |
|
612
|
|
|
} |
|
613
|
|
|
|
|
614
|
|
|
} else { |
|
615
|
|
|
if ($format === self::FORMAT_CURRENCY_EUR_SIMPLE) { |
|
616
|
|
|
$value = 'EUR ' . sprintf('%1.2f', $value); |
|
617
|
|
|
|
|
618
|
|
|
} else { |
|
619
|
|
|
// In Excel formats, "_" is used to add spacing, which we can't do in HTML |
|
620
|
|
|
$format = preg_replace('/_./', '', $format); |
|
621
|
|
|
|
|
622
|
|
|
// Some non-number characters are escaped with \, which we don't need |
|
623
|
|
|
$format = preg_replace("/\\\\/", '', $format); |
|
624
|
|
|
|
|
625
|
|
|
// Some non-number strings are quoted, so we'll get rid of the quotes, likewise any positional * symbols |
|
626
|
|
|
$format = str_replace(array('"','*'), '', $format); |
|
627
|
|
|
|
|
628
|
|
|
// Find out if we need thousands separator |
|
629
|
|
|
// This is indicated by a comma enclosed by a digit placeholder: |
|
630
|
|
|
// #,# or 0,0 |
|
631
|
|
|
$useThousands = preg_match('/(#,#|0,0)/', $format); |
|
632
|
|
|
if ($useThousands) { |
|
633
|
|
|
$format = preg_replace('/0,0/', '00', $format); |
|
634
|
|
|
$format = preg_replace('/#,#/', '##', $format); |
|
635
|
|
|
} |
|
636
|
|
|
|
|
637
|
|
|
// Scale thousands, millions,... |
|
638
|
|
|
// This is indicated by a number of commas after a digit placeholder: |
|
639
|
|
|
// #, or 0.0,, |
|
640
|
|
|
$scale = 1; // same as no scale |
|
641
|
|
|
$matches = array(); |
|
642
|
|
|
if (preg_match('/(#|0)(,+)/', $format, $matches)) { |
|
643
|
|
|
$scale = pow(1000, strlen($matches[2])); |
|
644
|
|
|
|
|
645
|
|
|
// strip the commas |
|
646
|
|
|
$format = preg_replace('/0,+/', '0', $format); |
|
647
|
|
|
$format = preg_replace('/#,+/', '#', $format); |
|
648
|
|
|
} |
|
649
|
|
|
|
|
650
|
|
|
if (preg_match('/#?.*\?\/\?/', $format, $m)) { |
|
651
|
|
|
//echo 'Format mask is fractional '.$format.' <br />'; |
|
652
|
|
|
if ($value != (int)$value) { |
|
653
|
|
|
$sign = ($value < 0) ? '-' : ''; |
|
654
|
|
|
|
|
655
|
|
|
$integerPart = floor(abs($value)); |
|
656
|
|
|
$decimalPart = trim(fmod(abs($value),1),'0.'); |
|
657
|
|
|
$decimalLength = strlen($decimalPart); |
|
658
|
|
|
$decimalDivisor = pow(10,$decimalLength); |
|
659
|
|
|
|
|
660
|
|
|
$GCD = PHPExcel_Calculation_MathTrig::GCD($decimalPart,$decimalDivisor); |
|
661
|
|
|
|
|
662
|
|
|
$adjustedDecimalPart = $decimalPart/$GCD; |
|
663
|
|
|
$adjustedDecimalDivisor = $decimalDivisor/$GCD; |
|
664
|
|
|
|
|
665
|
|
|
if ((strpos($format,'0') !== false) || (strpos($format,'#') !== false) || (substr($format,0,3) == '? ?')) { |
|
666
|
|
|
if ($integerPart == 0) { $integerPart = ''; } |
|
667
|
|
|
$value = "$sign$integerPart $adjustedDecimalPart/$adjustedDecimalDivisor"; |
|
668
|
|
|
} else { |
|
669
|
|
|
$adjustedDecimalPart += $integerPart * $adjustedDecimalDivisor; |
|
670
|
|
|
$value = "$sign$adjustedDecimalPart/$adjustedDecimalDivisor"; |
|
671
|
|
|
} |
|
672
|
|
|
} |
|
673
|
|
|
|
|
674
|
|
|
} else { |
|
675
|
|
|
// Handle the number itself |
|
676
|
|
|
|
|
677
|
|
|
// scale number |
|
678
|
|
|
$value = $value / $scale; |
|
679
|
|
|
|
|
680
|
|
|
// Strip # |
|
681
|
|
|
$format = preg_replace('/\\#/', '', $format); |
|
682
|
|
|
|
|
683
|
|
|
$n = "/\[[^\]]+\]/"; |
|
684
|
|
|
$m = preg_replace($n, '', $format); |
|
685
|
|
|
$number_regex = "/(0+)(\.?)(0*)/"; |
|
686
|
|
|
if (preg_match($number_regex, $m, $matches)) { |
|
687
|
|
|
$left = $matches[1]; |
|
688
|
|
|
$dec = $matches[2]; |
|
689
|
|
|
$right = $matches[3]; |
|
690
|
|
|
|
|
691
|
|
|
// minimun width of formatted number (including dot) |
|
692
|
|
|
$minWidth = strlen($left) + strlen($dec) + strlen($right); |
|
693
|
|
|
|
|
694
|
|
|
if ($useThousands) { |
|
695
|
|
|
$value = number_format( |
|
696
|
|
|
$value |
|
697
|
|
|
, strlen($right) |
|
698
|
|
|
, PHPExcel_Shared_String::getDecimalSeparator() |
|
699
|
|
|
, PHPExcel_Shared_String::getThousandsSeparator() |
|
700
|
|
|
); |
|
701
|
|
|
} else { |
|
702
|
|
|
$sprintf_pattern = "%0$minWidth." . strlen($right) . "f"; |
|
703
|
|
|
$value = sprintf($sprintf_pattern, $value); |
|
704
|
|
|
} |
|
705
|
|
|
|
|
706
|
|
|
$value = preg_replace($number_regex, $value, $format); |
|
707
|
|
|
} |
|
708
|
|
|
} |
|
709
|
|
|
if (preg_match('/\[\$(.*)\]/u', $format, $m)) { |
|
710
|
|
|
// Currency or Accounting |
|
711
|
|
|
$currencyFormat = $m[0]; |
|
712
|
|
|
$currencyCode = $m[1]; |
|
713
|
|
|
list($currencyCode) = explode('-',$currencyCode); |
|
714
|
|
|
if ($currencyCode == '') { |
|
715
|
|
|
$currencyCode = PHPExcel_Shared_String::getCurrencyCode(); |
|
716
|
|
|
} |
|
717
|
|
|
$value = preg_replace('/\[\$([^\]]*)\]/u',$currencyCode,$value); |
|
718
|
|
|
} |
|
719
|
|
|
} |
|
720
|
|
|
} |
|
721
|
|
|
|
|
722
|
|
|
// Additional formatting provided by callback function |
|
723
|
|
|
if ($callBack !== null) { |
|
724
|
|
|
list($writerInstance, $function) = $callBack; |
|
725
|
|
|
$value = $writerInstance->$function($value, $formatColor); |
|
726
|
|
|
} |
|
727
|
|
|
|
|
728
|
|
|
return $value; |
|
729
|
|
|
} |
|
730
|
|
|
|
|
731
|
|
|
} |
|
732
|
|
|
|
This check marks private properties in classes that are never used. Those properties can be removed.