wpdatatables / lib / phpoffice / phpspreadsheet / src / PhpSpreadsheet / Reader / Ods / FormulaTranslator.php

FormulaTranslator.php in wpDataTables – WordPress Data Table, Dynamic Tables & Table Charts Plugin 6.5.1.7, at lib/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Ods/FormulaTranslator.php

98 lines 3.6 KB

Raw Download Zip

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