|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace PhpOffice\PhpSpreadsheet\Calculation\Engine; |
|
4
|
|
|
|
|
5
|
|
|
use PhpOffice\PhpSpreadsheet\Calculation\Calculation; |
|
6
|
|
|
use PhpOffice\PhpSpreadsheet\Shared\StringHelper; |
|
7
|
|
|
|
|
8
|
|
|
class FormattedNumber |
|
9
|
|
|
{ |
|
10
|
|
|
/** Constants */ |
|
11
|
|
|
/** Regular Expressions */ |
|
12
|
|
|
private const STRING_REGEXP_FRACTION = '~^\s*(-?)((\d*)\s+)?(\d+\/\d+)\s*$~'; |
|
13
|
|
|
|
|
14
|
|
|
private const STRING_REGEXP_PERCENT = '~^(?:(?: *(?<PrefixedSign>[-+])? *\% *(?<PrefixedSign2>[-+])? *(?<PrefixedValue>[0-9]+\.?[0-9*]*(?:E[-+]?[0-9]*)?) *)|(?: *(?<PostfixedSign>[-+])? *(?<PostfixedValue>[0-9]+\.?[0-9]*(?:E[-+]?[0-9]*)?) *\% *))$~i'; |
|
15
|
|
|
|
|
16
|
|
|
// preg_quoted string for major currency symbols, with a %s for locale currency |
|
17
|
|
|
private const CURRENCY_CONVERSION_LIST = '\$€£¥%s'; |
|
18
|
|
|
|
|
19
|
|
|
private const STRING_CONVERSION_LIST = [ |
|
20
|
|
|
[self::class, 'convertToNumberIfNumeric'], |
|
21
|
|
|
[self::class, 'convertToNumberIfFraction'], |
|
22
|
|
|
[self::class, 'convertToNumberIfPercent'], |
|
23
|
|
|
[self::class, 'convertToNumberIfCurrency'], |
|
24
|
|
|
]; |
|
25
|
|
|
|
|
26
|
|
|
/** |
|
27
|
|
|
* Identify whether a string contains a formatted numeric value, |
|
28
|
|
|
* and convert it to a numeric if it is. |
|
29
|
|
|
* |
|
30
|
|
|
* @param string $operand string value to test |
|
31
|
|
|
*/ |
|
32
|
13 |
|
public static function convertToNumberIfFormatted(string &$operand): bool |
|
33
|
|
|
{ |
|
34
|
13 |
|
foreach (self::STRING_CONVERSION_LIST as $conversionMethod) { |
|
35
|
13 |
|
if ($conversionMethod($operand) === true) { |
|
36
|
9 |
|
return true; |
|
37
|
|
|
} |
|
38
|
|
|
} |
|
39
|
|
|
|
|
40
|
4 |
|
return false; |
|
41
|
|
|
} |
|
42
|
|
|
|
|
43
|
|
|
/** |
|
44
|
|
|
* Identify whether a string contains a numeric value, |
|
45
|
|
|
* and convert it to a numeric if it is. |
|
46
|
|
|
* |
|
47
|
|
|
* @param string $operand string value to test |
|
48
|
|
|
*/ |
|
49
|
13 |
|
public static function convertToNumberIfNumeric(string &$operand): bool |
|
50
|
|
|
{ |
|
51
|
13 |
|
$thousandsSeparator = preg_quote(StringHelper::getThousandsSeparator()); |
|
52
|
13 |
|
$value = preg_replace(['/(\d)' . $thousandsSeparator . '(\d)/u', '/([+-])\s+(\d)/u'], ['$1$2', '$1$2'], trim($operand)); |
|
53
|
13 |
|
$decimalSeparator = preg_quote(StringHelper::getDecimalSeparator()); |
|
54
|
13 |
|
$value = preg_replace(['/(\d)' . $decimalSeparator . '(\d)/u', '/([+-])\s+(\d)/u'], ['$1.$2', '$1$2'], $value ?? ''); |
|
55
|
|
|
|
|
56
|
13 |
|
if (is_numeric($value)) { |
|
57
|
5 |
|
$operand = (float) $value; |
|
58
|
|
|
|
|
59
|
5 |
|
return true; |
|
60
|
|
|
} |
|
61
|
|
|
|
|
62
|
8 |
|
return false; |
|
63
|
|
|
} |
|
64
|
|
|
|
|
65
|
|
|
/** |
|
66
|
|
|
* Identify whether a string contains a fractional numeric value, |
|
67
|
|
|
* and convert it to a numeric if it is. |
|
68
|
|
|
* |
|
69
|
|
|
* @param string $operand string value to test |
|
70
|
|
|
*/ |
|
71
|
15 |
|
public static function convertToNumberIfFraction(string &$operand): bool |
|
72
|
|
|
{ |
|
73
|
15 |
|
if (preg_match(self::STRING_REGEXP_FRACTION, $operand, $match)) { |
|
74
|
7 |
|
$sign = ($match[1] === '-') ? '-' : '+'; |
|
75
|
7 |
|
$wholePart = ($match[3] === '') ? '' : ($sign . $match[3]); |
|
76
|
7 |
|
$fractionFormula = '=' . $wholePart . $sign . $match[4]; |
|
77
|
7 |
|
$operand = Calculation::getInstance()->_calculateFormulaValue($fractionFormula); |
|
78
|
|
|
|
|
79
|
7 |
|
return true; |
|
80
|
|
|
} |
|
81
|
|
|
|
|
82
|
8 |
|
return false; |
|
83
|
|
|
} |
|
84
|
|
|
|
|
85
|
|
|
/** |
|
86
|
|
|
* Identify whether a string contains a percentage, and if so, |
|
87
|
|
|
* convert it to a numeric. |
|
88
|
|
|
* |
|
89
|
|
|
* @param string $operand string value to test |
|
90
|
|
|
*/ |
|
91
|
110 |
|
public static function convertToNumberIfPercent(string &$operand): bool |
|
92
|
|
|
{ |
|
93
|
110 |
|
$thousandsSeparator = preg_quote(StringHelper::getThousandsSeparator()); |
|
94
|
110 |
|
$value = preg_replace('/(\d)' . $thousandsSeparator . '(\d)/u', '$1$2', trim($operand)); |
|
95
|
110 |
|
$decimalSeparator = preg_quote(StringHelper::getDecimalSeparator()); |
|
96
|
110 |
|
$value = preg_replace(['/(\d)' . $decimalSeparator . '(\d)/u', '/([+-])\s+(\d)/u'], ['$1.$2', '$1$2'], $value ?? ''); |
|
97
|
|
|
|
|
98
|
110 |
|
$match = []; |
|
99
|
110 |
|
if ($value !== null && preg_match(self::STRING_REGEXP_PERCENT, $value, $match, PREG_UNMATCHED_AS_NULL)) { |
|
100
|
|
|
//Calculate the percentage |
|
101
|
86 |
|
$sign = ($match['PrefixedSign'] ?? $match['PrefixedSign2'] ?? $match['PostfixedSign']) ?? ''; |
|
102
|
86 |
|
$operand = (float) ($sign . ($match['PostfixedValue'] ?? $match['PrefixedValue'])) / 100; |
|
103
|
|
|
|
|
104
|
86 |
|
return true; |
|
105
|
|
|
} |
|
106
|
|
|
|
|
107
|
24 |
|
return false; |
|
108
|
|
|
} |
|
109
|
|
|
|
|
110
|
|
|
/** |
|
111
|
|
|
* Identify whether a string contains a currency value, and if so, |
|
112
|
|
|
* convert it to a numeric. |
|
113
|
|
|
* |
|
114
|
|
|
* @param string $operand string value to test |
|
115
|
|
|
*/ |
|
116
|
25 |
|
public static function convertToNumberIfCurrency(string &$operand): bool |
|
117
|
|
|
{ |
|
118
|
25 |
|
$currencyRegexp = self::currencyMatcherRegexp(); |
|
119
|
25 |
|
$thousandsSeparator = preg_quote(StringHelper::getThousandsSeparator()); |
|
120
|
25 |
|
$value = preg_replace('/(\d)' . $thousandsSeparator . '(\d)/u', '$1$2', $operand); |
|
121
|
|
|
|
|
122
|
25 |
|
$match = []; |
|
123
|
25 |
|
if ($value !== null && preg_match($currencyRegexp, $value, $match, PREG_UNMATCHED_AS_NULL)) { |
|
124
|
|
|
//Determine the sign |
|
125
|
21 |
|
$sign = ($match['PrefixedSign'] ?? $match['PrefixedSign2'] ?? $match['PostfixedSign']) ?? ''; |
|
126
|
|
|
//Cast to a float |
|
127
|
21 |
|
$operand = (float) ($sign . ($match['PostfixedValue'] ?? $match['PrefixedValue'])); |
|
128
|
|
|
|
|
129
|
21 |
|
return true; |
|
130
|
|
|
} |
|
131
|
|
|
|
|
132
|
4 |
|
return false; |
|
133
|
|
|
} |
|
134
|
|
|
|
|
135
|
45 |
|
public static function currencyMatcherRegexp(): string |
|
136
|
|
|
{ |
|
137
|
45 |
|
$currencyCodes = sprintf(self::CURRENCY_CONVERSION_LIST, preg_quote(StringHelper::getCurrencyCode())); |
|
138
|
45 |
|
$decimalSeparator = preg_quote(StringHelper::getDecimalSeparator()); |
|
139
|
|
|
|
|
140
|
45 |
|
return '~^(?:(?: *(?<PrefixedSign>[-+])? *(?<PrefixedCurrency>[' . $currencyCodes . ']) *(?<PrefixedSign2>[-+])? *(?<PrefixedValue>[0-9]+[' . $decimalSeparator . ']?[0-9*]*(?:E[-+]?[0-9]*)?) *)|(?: *(?<PostfixedSign>[-+])? *(?<PostfixedValue>[0-9]+' . $decimalSeparator . '?[0-9]*(?:E[-+]?[0-9]*)?) *(?<PostfixedCurrency>[' . $currencyCodes . ']) *))$~ui'; |
|
141
|
|
|
} |
|
142
|
|
|
} |
|
143
|
|
|
|
PHPOffice /
PhpSpreadsheet
