wpdatatables / lib / phpoffice / phpspreadsheet / src / PhpSpreadsheet / Writer / Ods / Formula.php

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

121 lines 3.9 KB

Raw Download Zip

1	<?php
2
3	namespace PhpOffice\PhpSpreadsheet\Writer\Ods;
4
5	use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
6	use PhpOffice\PhpSpreadsheet\DefinedName;
7
8	class Formula
9	{
10	/** @var array */
11	private $definedNames = [];
12
13	/**
14	* @param DefinedName[] $definedNames
15	*/
16	public function __construct(array $definedNames)
17	{
18	foreach ($definedNames as $definedName) {
19	$this->definedNames[] = $definedName->getName();
20	}
21	}
22
23	public function convertFormula(string $formula, string $worksheetName = ''): string
24	{
25	$formula = $this->convertCellReferences($formula, $worksheetName);
26	$formula = $this->convertDefinedNames($formula);
27
28	if (substr($formula, 0, 1) !== '=') {
29	$formula = '=' . $formula;
30	}
31
32	return 'of:' . $formula;
33	}
34
35	private function convertDefinedNames(string $formula): string
36	{
37	$splitCount = preg_match_all(
38	'/' . Calculation::CALCULATION_REGEXP_DEFINEDNAME . '/mui',
39	$formula,
40	$splitRanges,
41	PREG_OFFSET_CAPTURE
42	);
43
44	$lengths = array_map('strlen', array_column($splitRanges[0], 0));
45	$offsets = array_column($splitRanges[0], 1);
46	$values = array_column($splitRanges[0], 0);
47
48	while ($splitCount > 0) {
49	--$splitCount;
50	$length = $lengths[$splitCount];
51	$offset = $offsets[$splitCount];
52	$value = $values[$splitCount];
53
54	if (in_array($value, $this->definedNames, true)) {
55	$formula = substr($formula, 0, $offset) . '$$' . $value . substr($formula, $offset + $length);
56	}
57	}
58
59	return $formula;
60	}
61
62	private function convertCellReferences(string $formula, string $worksheetName): string
63	{
64	$splitCount = preg_match_all(
65	'/' . Calculation::CALCULATION_REGEXP_CELLREF_RELATIVE . '/mui',
66	$formula,
67	$splitRanges,
68	PREG_OFFSET_CAPTURE
69	);
70
71	$lengths = array_map('strlen', array_column($splitRanges[0], 0));
72	$offsets = array_column($splitRanges[0], 1);
73
74	$worksheets = $splitRanges[2];
75	$columns = $splitRanges[6];
76	$rows = $splitRanges[7];
77
78	// Replace any commas in the formula with semi-colons for Ods
79	// If by chance there are commas in worksheet names, then they will be "fixed" again in the loop
80	// because we've already extracted worksheet names with our preg_match_all()
81	$formula = str_replace(',', ';', $formula);
82	while ($splitCount > 0) {
83	--$splitCount;
84	$length = $lengths[$splitCount];
85	$offset = $offsets[$splitCount];
86	$worksheet = $worksheets[$splitCount][0];
87	$column = $columns[$splitCount][0];
88	$row = $rows[$splitCount][0];
89
90	$newRange = '';
91	if (empty($worksheet)) {
92	if (($offset === 0) \|\| ($formula[$offset - 1] !== ':')) {
93	// We need a worksheet
94	$worksheet = $worksheetName;
95	}
96	} else {
97	$worksheet = str_replace("''", "'", trim($worksheet, "'"));
98	}
99	if (!empty($worksheet)) {
100	$newRange = "['" . str_replace("'", "''", $worksheet) . "'";
101	} elseif (substr($formula, $offset - 1, 1) !== ':') {
102	$newRange = '[';
103	}
104	$newRange .= '.';
105
106	if (!empty($column)) {
107	$newRange .= $column;
108	}
109	if (!empty($row)) {
110	$newRange .= $row;
111	}
112	// close the wrapping [] unless this is the first part of a range
113	$newRange .= substr($formula, $offset + $length, 1) !== ':' ? ']' : '';
114
115	$formula = substr($formula, 0, $offset) . $newRange . substr($formula, $offset + $length);
116	}
117
118	return $formula;
119	}
120	}
121