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