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