tablepress / libraries / vendor / PhpSpreadsheet / Style / NumberFormat / Formatter.php

Formatter.php in TablePress – Tables in WordPress made easy 3.4, at libraries/vendor/PhpSpreadsheet/Style/NumberFormat/Formatter.php

254 lines 9.1 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Style\NumberFormat;
4
5	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Calculation;
6	use TablePress\PhpOffice\PhpSpreadsheet\Reader\Xls\Color\BIFF8;
7	use TablePress\PhpOffice\PhpSpreadsheet\RichText\RichText;
8	use TablePress\PhpOffice\PhpSpreadsheet\Shared\StringHelper;
9	use TablePress\PhpOffice\PhpSpreadsheet\Style\Color;
10	use TablePress\PhpOffice\PhpSpreadsheet\Style\NumberFormat;
11
12	class Formatter extends BaseFormatter
13	{
14	/**
15	* Matches any @ symbol that isn't enclosed in quotes.
16	*/
17	private const SYMBOL_AT = '/@(?=(?:[^"]"[^"]")[^"]\Z)/miu';
18	private const QUOTE_REPLACEMENT = "\u{fffe}"; // invalid Unicode character
19
20	/**
21	* Matches any ; symbol that isn't enclosed in quotes, for a "section" split.
22	*/
23	private const SECTION_SPLIT = '/;(?=(?:[^"]"[^"]")[^"]\Z)/miu';
24
25	/**
26	* @param mixed $value
27	* @param mixed $comparisonValue
28	* @param mixed $defaultComparisonValue
29	*/
30	private static function splitFormatComparison(
31	$value,
32	?string $condition,
33	$comparisonValue,
34	string $defaultCondition,
35	$defaultComparisonValue
36	): bool {
37	if (!$condition) {
38	$condition = $defaultCondition;
39	$comparisonValue = $defaultComparisonValue;
40	}
41
42	switch ($condition) {
43	case '>':
44	return $value > $comparisonValue;
45	case '<':
46	return $value < $comparisonValue;
47	case '<=':
48	return $value <= $comparisonValue;
49	case '<>':
50	return $value != $comparisonValue;
51	case '=':
52	return $value == $comparisonValue;
53	default:
54	return $value >= $comparisonValue;
55	}
56	}
57
58	/**
59	* @param mixed $value value to be formatted
60	* @param string[] $sections
61	*
62	* @return array{string, string, float\|int\|numeric-string}
63	*/
64	private static function splitFormatForSectionSelection(array $sections, $value): array
65	{
66	// Extract the relevant section depending on whether number is positive, negative, or zero?
67	// Text not supported yet.
68	// Here is how the sections apply to various values in Excel:
69	// 1 section: [POSITIVE/NEGATIVE/ZERO/TEXT]
70	// 2 sections: [POSITIVE/ZERO/TEXT] [NEGATIVE]
71	// 3 sections: [POSITIVE/TEXT] [NEGATIVE] [ZERO]
72	// 4 sections: [POSITIVE] [NEGATIVE] [ZERO] [TEXT]
73	$sectionCount = count($sections);
74	// Colour could be a named colour, or a numeric index entry in the colour-palette
75	$color_regex = '/\[(' . implode('\|', Color::NAMED_COLORS) . '\|color\s*(\d+))\]/mui';
76	$cond_regex = '/\[(>\|>=\|<\|<=\|=\|<>)([+-]?\d+([.]\d+)?)\]/';
77	$colors = ['', '', '', '', ''];
78	$conditionOperations = ['', '', '', '', ''];
79	$conditionComparisonValues = [0, 0, 0, 0, 0];
80	for ($idx = 0; $idx < $sectionCount; ++$idx) {
81	if (preg_match($color_regex, $sections[$idx], $matches)) {
82	if (isset($matches[2])) {
83	$colors[$idx] = '#' . BIFF8::lookup((int) $matches[2] + 7)['rgb'];
84	} else {
85	$colors[$idx] = $matches[0];
86	}
87	$sections[$idx] = (string) preg_replace($color_regex, '', $sections[$idx]);
88	}
89	if (preg_match($cond_regex, $sections[$idx], $matches)) {
90	$conditionOperations[$idx] = $matches[1];
91	$conditionComparisonValues[$idx] = $matches[2];
92	$sections[$idx] = (string) preg_replace($cond_regex, '', $sections[$idx]);
93	}
94	}
95	$color = $colors[0];
96	$format = $sections[0];
97	$absval = $value;
98	switch ($sectionCount) {
99	case 2:
100	$absval = abs($value + 0);
101	if (!self::splitFormatComparison($value, $conditionOperations[0], $conditionComparisonValues[0], '>=', 0)) {
102	$color = $colors[1];
103	$format = $sections[1];
104	}
105
106	break;
107	case 3:
108	case 4:
109	$absval = abs($value + 0);
110	if (!self::splitFormatComparison($value, $conditionOperations[0], $conditionComparisonValues[0], '>', 0)) {
111	if (self::splitFormatComparison($value, $conditionOperations[1], $conditionComparisonValues[1], '<', 0)) {
112	$color = $colors[1];
113	$format = $sections[1];
114	} else {
115	$color = $colors[2];
116	$format = $sections[2];
117	}
118	}
119
120	break;
121	}
122
123	return [$color, $format, $absval];
124	}
125
126	/**
127	* Convert a value in a pre-defined format to a PHP string.
128	*
129	* @param null\|array<mixed>\|bool\|float\|int\|RichText\|string $value Value to format
130	* @param string $format Format code: see = self::FORMAT_* for predefined values;
131	* or can be any valid MS Excel custom format string
132	* @param null\|callable(string, string): string $callBack Callback function for additional formatting of string
133	* @param bool $lessFloatPrecision If true, unstyled floats will be converted to a more human-friendly but less computationally accurate value
134	*
135	* @return string Formatted string
136	*/
137	public static function toFormattedString($value, string $format, ?callable $callBack = null, bool $lessFloatPrecision = false): string
138	{
139	while (is_array($value)) {
140	$value = array_shift($value);
141	}
142	if (is_bool($value)) {
143	return $value ? Calculation::getTRUE() : Calculation::getFALSE();
144	}
145	// For now we do not treat strings in sections, although section 4 of a format code affects strings
146	// Process a single block format code containing @ for text substitution
147	$formatx = str_replace('\"', self::QUOTE_REPLACEMENT, $format);
148	if (preg_match(self::SECTION_SPLIT, $format) === 0 && preg_match(self::SYMBOL_AT, $formatx) === 1) {
149	if (!str_contains($format, '"')) {
150	$temp = str_replace('@', StringHelper::convertToString($value, true, '', false, $lessFloatPrecision), $format);
151	if (is_callable($callBack)) {
152	$temp = $callBack($temp, $format);
153	}
154
155	return $temp;
156	}
157	//escape any dollar signs on the string, so they are not replaced with an empty value
158	$value = str_replace(
159	['$', '"'],
160	['\$', self::QUOTE_REPLACEMENT],
161	StringHelper::convertToString($value, true, '', false, $lessFloatPrecision)
162	);
163	$temp = preg_replace(self::SYMBOL_AT, $value, $formatx) ?? $value;
164	if (is_callable($callBack)) {
165	$temp = $callBack($temp, $formatx);
166	}
167
168	return str_replace(
169	['"', self::QUOTE_REPLACEMENT],
170	['', '"'],
171	$temp
172	);
173	}
174
175	// If we have a text value, return it "as is"
176	if (!is_numeric($value)) {
177	$temp = StringHelper::convertToString($value, true, '', false, $lessFloatPrecision);
178	if (is_callable($callBack)) {
179	$sections = preg_split(self::SECTION_SPLIT, $format) ?: [$format];
180	$temp = $callBack($temp, $sections[3] ?? $sections[0]);
181	}
182
183	return $temp;
184	}
185
186	// For 'General' format code, we just pass the value although this is not entirely the way Excel does it,
187	// it seems to round numbers to a total of 10 digits.
188	if (($format === NumberFormat::FORMAT_GENERAL) \|\| ($format === NumberFormat::FORMAT_TEXT)) {
189	if (is_float($value) && $lessFloatPrecision) {
190	return self::adjustSeparators((string) $value);
191	}
192
193	return self::adjustSeparators(
194	StringHelper::convertToString($value, true, '', false, $lessFloatPrecision)
195	);
196	}
197
198	// Ignore square-$-brackets prefix in format string, like "[$-411]ge.m.d", "[$-010419]0%", etc
199	$format = (string) preg_replace('/^\[\$-[^\]]*\]/', '', $format);
200
201	$format = (string) preg_replace_callback(
202	'/(["])(?:(?=(\\\?))\2.)*?\1/u',
203	fn (array $matches): string => str_replace('.', chr(0x00), $matches[0]),
204	$format
205	);
206
207	// Convert any other escaped characters to quoted strings, e.g. (\T to "T")
208	$format = (string) preg_replace('/(\\\(((.)(?!((AM\/PM)\|(A\/P))))\|([^ ])))(?=(?:[^"]\|"[^"]")$)/ui', '"${2}"', $format);
209
210	// Get the sections, there can be up to four sections, separated with a semicolon (but only if not a quoted literal)
211	$sections = preg_split(self::SECTION_SPLIT, $format) ?: [];
212
213	[$colors, $format, $value] = self::splitFormatForSectionSelection($sections, $value);
214
215	// In Excel formats, "_" is used to add spacing,
216	// The following character indicates the size of the spacing, which we can't do in HTML, so we just use a standard space
217	$temp = $format;
218	$format = (string) preg_replace('/_.?/ui', ' ', $temp);
219
220	// Let's begin inspecting the format and converting the value to a formatted string
221	if (
222	// Check for date/time characters (not inside quotes)
223	(preg_match('/(\[\$[A-Z]-[0-9A-F]\])[hmsdy](?=(?:[^"]\|"[^"]")*$)/miu', $format))
224	// Look out for Currency formats Issue 4124
225	&& !(preg_match('/\[\$[A-Z]{3}\]/miu', $format))
226	// A date/time with a decimal time shouldn't have a digit placeholder before the decimal point
227	&& (preg_match('/[0\?#]\.(?![^\[]*\])/miu', $format) === 0)
228	) {
229	// datetime format
230	/** @var float\|int */
231	$temp = $value;
232	$value = DateFormatter::format($temp, $format);
233	} else {
234	if (str_starts_with($format, '"') && str_ends_with($format, '"') && substr_count($format, '"') === 2) {
235	$value = (string) substr($format, 1, -1);
236	} elseif (preg_match('/[0#, ]%/', $format)) {
237	// % number format - avoid weird '-0' problem
238	$temp = $value;
239	$value = PercentageFormatter::format(0 + (float) $temp, $format);
240	} else {
241	$temp = $value;
242	$value = NumberFormatter::format($temp, $format);
243	}
244	}
245
246	// Additional formatting provided by callback function
247	if (is_callable($callBack)) {
248	$value = $callBack($value, $colors);
249	}
250
251	return str_replace(chr(0x00), '.', $value);
252	}
253	}
254