tablepress / libraries / vendor / PhpSpreadsheet / Reader / Ods / FormulaTranslator.php

FormulaTranslator.php in TablePress – Tables in WordPress made easy 3.4, at libraries/vendor/PhpSpreadsheet/Reader/Ods/FormulaTranslator.php

128 lines 3.3 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Reader\Ods;
4
5	use TablePress\Composer\Pcre\Preg;
6	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Calculation;
7
8	class FormulaTranslator
9	{
10	private static function replaceQuotedPeriod(string $value): string
11	{
12	$value2 = '';
13	$quoted = false;
14	foreach (mb_str_split($value, 1, 'UTF-8') as $char) {
15	if ($char === "'") {
16	$quoted = !$quoted;
17	} elseif ($char === '.' && $quoted) {
18	$char = "\u{fffe}";
19	}
20	$value2 .= $char;
21	}
22
23	return $value2;
24	}
25
26	public static function convertToExcelAddressValue(string $openOfficeAddress): string
27	{
28	// Cell range 3-d reference
29	// As we don't support 3-d ranges, we're just going to take a quick and dirty approach
30	// and assume that the second worksheet reference is the same as the first
31	$excelAddress = Preg::replace(
32	[
33	'/\$?([^\.]+)\.([^\.]+):\$?([^\.]+)\.([^\.]+)/miu',
34	'/\$?([^\.]+)\.([^\.]+):\.([^\.]+)/miu', // Cell range reference in another sheet
35	'/\$?([^\.]+)\.([^\.]+)/miu', // Cell reference in another sheet
36	'/\.([^\.]+):\.([^\.]+)/miu', // Cell range reference
37	'/\.([^\.]+)/miu', // Simple cell reference
38	'/\x{FFFE}/miu', // restore quoted periods
39	],
40	[
41	'$1!$2:$4',
42	'$1!$2:$3',
43	'$1!$2',
44	'$1:$2',
45	'$1',
46	'.',
47	],
48	self::replaceQuotedPeriod($openOfficeAddress)
49	);
50
51	return $excelAddress;
52	}
53
54	public static function convertToExcelFormulaValue(string $openOfficeFormula): string
55	{
56	$temp = explode(Calculation::FORMULA_STRING_QUOTE, $openOfficeFormula);
57	$tKey = false;
58	$inMatrixBracesLevel = 0;
59	$inFunctionBracesLevel = 0;
60	foreach ($temp as &$value) {
61	// @var string $value
62	// Only replace in alternate array entries (i.e. non-quoted blocks)
63	// so that conversion isn't done in string values
64	$tKey = $tKey === false;
65	if ($tKey) {
66	$value = Preg::replace(
67	[
68	'/\[\$?([^\.]+)\.([^\.]+):\.([^\.]+)\]/miu', // Cell range reference in another sheet
69	'/\[\$?([^\.]+)\.([^\.]+)\]/miu', // Cell reference in another sheet
70	'/\[\.([^\.]+):\.([^\.]+)\]/miu', // Cell range reference
71	'/\[\.([^\.]+)\]/miu', // Simple cell reference
72	'/\x{FFFE}/miu', // restore quoted periods
73	],
74	[
75	'$1!$2:$3',
76	'$1!$2',
77	'$1:$2',
78	'$1',
79	'.',
80	],
81	self::replaceQuotedPeriod($value)
82	);
83	// Convert references to defined names/formulae
84	$value = str_replace('$$', '', $value);
85
86	// Convert ODS function argument separators to Excel function argument separators
87	$value = Calculation::translateSeparator(';', ',', $value, $inFunctionBracesLevel);
88
89	// Convert ODS matrix separators to Excel matrix separators
90	$value = Calculation::translateSeparator(
91	';',
92	',',
93	$value,
94	$inMatrixBracesLevel,
95	Calculation::FORMULA_OPEN_MATRIX_BRACE,
96	Calculation::FORMULA_CLOSE_MATRIX_BRACE
97	);
98	$value = Calculation::translateSeparator(
99	'\|',
100	';',
101	$value,
102	$inMatrixBracesLevel,
103	Calculation::FORMULA_OPEN_MATRIX_BRACE,
104	Calculation::FORMULA_CLOSE_MATRIX_BRACE
105	);
106
107	$value = Preg::replace(
108	[
109	'/\b(?<!com[.]microsoft[.])'
110	. '(floor\|ceiling)\s*[(]/ui',
111	'/COM\.MICROSOFT\./ui',
112	],
113	[
114	'$1.ODS(',
115	'',
116	],
117	$value
118	);
119	}
120	}
121
122	// Then rebuild the formula string
123	$excelFormula = implode('"', $temp);
124
125	return $excelFormula;
126	}
127	}
128