|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace PhpOffice\PhpSpreadsheetTests\Calculation\Engine; |
|
4
|
|
|
|
|
5
|
|
|
use PhpOffice\PhpSpreadsheet\Calculation\Engine\Operands\StructuredReference; |
|
6
|
|
|
use PhpOffice\PhpSpreadsheet\Calculation\Exception; |
|
7
|
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet; |
|
8
|
|
|
use PhpOffice\PhpSpreadsheet\Worksheet\Table; |
|
9
|
|
|
use PHPUnit\Framework\TestCase; |
|
10
|
|
|
|
|
11
|
|
|
class StructuredReferenceTest extends TestCase |
|
12
|
|
|
{ |
|
13
|
|
|
protected Spreadsheet $spreadSheet; |
|
14
|
|
|
|
|
15
|
|
|
protected const COLUMN_FORMULA = '=[@Sales Amount]*[@[% Commission]]'; |
|
16
|
|
|
|
|
17
|
|
|
// Note that column headings may contain a non-breaking space, while the formula may not; |
|
18
|
|
|
// these still need to match. |
|
19
|
|
|
protected array $tableData = [ |
|
20
|
|
|
["Sales\u{a0}Person", 'Region', "Sales\u{a0}Amount", "%\u{a0}Commission", "Commission\u{a0}Amount"], |
|
21
|
|
|
['Joe', 'North', 260, '10%', self::COLUMN_FORMULA], |
|
22
|
|
|
['Robert', 'South', 660, '15%', self::COLUMN_FORMULA], |
|
23
|
|
|
['Michelle', 'East', 940, '15%', self::COLUMN_FORMULA], |
|
24
|
|
|
['Erich', 'West', 410, '12%', self::COLUMN_FORMULA], |
|
25
|
|
|
['Dafna', 'North', 800, '15%', self::COLUMN_FORMULA], |
|
26
|
|
|
['Rob', 'South', 900, '15%', self::COLUMN_FORMULA], |
|
27
|
|
|
['Total'], |
|
28
|
|
|
]; |
|
29
|
|
|
|
|
30
|
|
|
protected function setUp(): void |
|
31
|
|
|
{ |
|
32
|
|
|
parent::setUp(); |
|
33
|
|
|
|
|
34
|
|
|
$this->spreadSheet = new Spreadsheet(); |
|
35
|
|
|
$workSheet = $this->spreadSheet->getActiveSheet(); |
|
36
|
|
|
$workSheet->fromArray($this->tableData, null, 'A1'); |
|
37
|
|
|
|
|
38
|
|
|
$table = new Table('A1:E8', 'DeptSales'); |
|
39
|
|
|
$table->setShowTotalsRow(true); |
|
40
|
|
|
$table->getColumn('A')->setTotalsRowLabel('Total'); |
|
41
|
|
|
$workSheet->addTable($table); |
|
42
|
|
|
} |
|
43
|
|
|
|
|
44
|
|
|
protected function tearDown(): void |
|
45
|
|
|
{ |
|
46
|
|
|
$this->spreadSheet->disconnectWorksheets(); |
|
47
|
|
|
|
|
48
|
|
|
parent::tearDown(); |
|
49
|
|
|
} |
|
50
|
|
|
|
|
51
|
|
|
public function testStructuredReferenceInvalidTable(): void |
|
52
|
|
|
{ |
|
53
|
|
|
$cell = $this->spreadSheet->getActiveSheet()->getCell('H5'); |
|
54
|
|
|
|
|
55
|
|
|
$this->expectException(Exception::class); |
|
56
|
|
|
$this->expectExceptionMessage('Table SalesResults for Structured Reference cannot be located'); |
|
57
|
|
|
$structuredReferenceObject = new StructuredReference('SalesResults[@[% Commission]]'); |
|
58
|
|
|
$structuredReferenceObject->parse($cell); |
|
59
|
|
|
} |
|
60
|
|
|
|
|
61
|
|
|
public function testStructuredReferenceInvalidCellForTable(): void |
|
62
|
|
|
{ |
|
63
|
|
|
$cell = $this->spreadSheet->getActiveSheet()->getCell('H99'); |
|
64
|
|
|
|
|
65
|
|
|
$this->expectException(Exception::class); |
|
66
|
|
|
$this->expectExceptionMessage('Table for Structured Reference cannot be identified'); |
|
67
|
|
|
$structuredReferenceObject = new StructuredReference('[@[% Commission]]'); |
|
68
|
|
|
$structuredReferenceObject->parse($cell); |
|
69
|
|
|
} |
|
70
|
|
|
|
|
71
|
|
|
/** |
|
72
|
|
|
* @dataProvider structuredReferenceProviderColumnData |
|
73
|
|
|
*/ |
|
74
|
|
|
public function testStructuredReferenceColumns(string $expectedCellRange, string $structuredReference): void |
|
75
|
|
|
{ |
|
76
|
|
|
$cell = $this->spreadSheet->getActiveSheet()->getCell('E5'); |
|
77
|
|
|
|
|
78
|
|
|
$structuredReferenceObject = new StructuredReference($structuredReference); |
|
79
|
|
|
$cellRange = $structuredReferenceObject->parse($cell); |
|
80
|
|
|
self::assertSame($expectedCellRange, $cellRange); |
|
81
|
|
|
} |
|
82
|
|
|
|
|
83
|
|
|
/** |
|
84
|
|
|
* @dataProvider structuredReferenceProviderRowData |
|
85
|
|
|
*/ |
|
86
|
|
|
public function testStructuredReferenceRows(string $expectedCellRange, string $structuredReference): void |
|
87
|
|
|
{ |
|
88
|
|
|
$cell = $this->spreadSheet->getActiveSheet()->getCell('E5'); |
|
89
|
|
|
|
|
90
|
|
|
$structuredReferenceObject = new StructuredReference($structuredReference); |
|
91
|
|
|
$cellRange = $structuredReferenceObject->parse($cell); |
|
92
|
|
|
self::assertSame($expectedCellRange, $cellRange); |
|
93
|
|
|
} |
|
94
|
|
|
|
|
95
|
|
|
public function testInvalidStructuredReferenceRow(): void |
|
96
|
|
|
{ |
|
97
|
|
|
$cell = $this->spreadSheet->getActiveSheet()->getCell('E5'); |
|
98
|
|
|
|
|
99
|
|
|
$this->expectException(Exception::class); |
|
100
|
|
|
$this->expectExceptionCode(1); |
|
101
|
|
|
$this->expectExceptionMessage('Invalid Structured Reference'); |
|
102
|
|
|
$this->expectExceptionCode(Exception::CALCULATION_ENGINE_PUSH_TO_STACK); |
|
103
|
|
|
$structuredReferenceObject = new StructuredReference('DeptSales[@[Sales]:[%age Commission]]'); |
|
104
|
|
|
$structuredReferenceObject->parse($cell); |
|
105
|
|
|
} |
|
106
|
|
|
|
|
107
|
|
|
public function testStructuredReferenceHeadersHidden(): void |
|
108
|
|
|
{ |
|
109
|
|
|
$cell = $this->spreadSheet->getActiveSheet()->getCell('K1'); |
|
110
|
|
|
$table = $this->spreadSheet->getActiveSheet()->getTableByName('DeptSales'); |
|
111
|
|
|
/** @var Table $table */ |
|
112
|
|
|
$structuredReferenceObject = new StructuredReference('DeptSales[[#Headers],[% Commission]]'); |
|
113
|
|
|
$cellRange = $structuredReferenceObject->parse($cell); |
|
114
|
|
|
self::assertSame('D1', $cellRange); |
|
115
|
|
|
|
|
116
|
|
|
$table->setShowHeaderRow(false); |
|
117
|
|
|
|
|
118
|
|
|
$this->expectException(Exception::class); |
|
119
|
|
|
$this->expectExceptionCode(1); |
|
120
|
|
|
$this->expectExceptionMessage('Table Headers are Hidden, and should not be Referenced'); |
|
121
|
|
|
$this->expectExceptionCode(Exception::CALCULATION_ENGINE_PUSH_TO_STACK); |
|
122
|
|
|
$structuredReferenceObject = new StructuredReference('DeptSales[[#Headers],[% Commission]]'); |
|
123
|
|
|
$structuredReferenceObject->parse($cell); |
|
124
|
|
|
} |
|
125
|
|
|
|
|
126
|
|
|
public function structuredReferenceProviderColumnData(): array |
|
127
|
|
|
{ |
|
128
|
|
|
return [ |
|
129
|
|
|
// Full table, with no column specified, means data only, not headers or totals |
|
130
|
|
|
'Full table Unqualified' => ['A2:E7', '[]'], |
|
131
|
|
|
'Full table Qualified' => ['A2:E7', 'DeptSales[]'], |
|
132
|
|
|
// No item identifier, but with a column identifier, means data and header for the column, but no totals |
|
133
|
|
|
'Column with no Item Identifier #1' => ['A2:A7', 'DeptSales[[Sales Person]]'], |
|
134
|
|
|
'Column with no Item Identifier #2' => ['B2:B7', 'DeptSales[Region]'], |
|
135
|
|
|
// Item identifier with no column specified |
|
136
|
|
|
'Item Identifier only #1' => ['A1:E1', 'DeptSales[#Headers]'], |
|
137
|
|
|
'Item Identifier only #2' => ['A1:E1', 'DeptSales[[#Headers]]'], |
|
138
|
|
|
'Item Identifier only #3' => ['A8:E8', 'DeptSales[#Totals]'], |
|
139
|
|
|
'Item Identifier only #4' => ['A2:E7', 'DeptSales[#Data]'], |
|
140
|
|
|
// Item identifiers and column identifiers |
|
141
|
|
|
'Full column' => ['C1:C8', 'DeptSales[[#All],[Sales Amount]]'], |
|
142
|
|
|
'Column Header' => ['D1', 'DeptSales[[#Headers],[% Commission]]'], |
|
143
|
|
|
'Column Total' => ['B8', 'DeptSales[[#Totals],[Region]]'], |
|
144
|
|
|
'Column Range All' => ['C1:D8', 'DeptSales[[#All],[Sales Amount]:[% Commission]]'], |
|
145
|
|
|
'Column Range Data' => ['D2:E7', 'DeptSales[[#Data],[% Commission]:[Commission Amount]]'], |
|
146
|
|
|
'Column Range Headers' => ['B1:E1', 'DeptSales[[#Headers],[Region]:[Commission Amount]]'], |
|
147
|
|
|
'Column Range Totals' => ['C8:E8', 'DeptSales[[#Totals],[Sales Amount]:[Commission Amount]]'], |
|
148
|
|
|
'Column Range Headers and Data' => ['D1:D7', 'DeptSales[[#Headers],[#Data],[% Commission]]'], |
|
149
|
|
|
'Column Range No Item Identifier' => ['A2:B7', 'DeptSales[[Sales Person]:[Region]]'], |
|
150
|
|
|
// ['C2:C7,E2:E7', 'DeptSales[Sales Amount],DeptSales[Commission Amount]'], |
|
151
|
|
|
// ['B2:C7', 'DeptSales[[Sales Person]:[Sales Amount]] DeptSales[[Region]:[% Commission]]'], |
|
152
|
|
|
]; |
|
153
|
|
|
} |
|
154
|
|
|
|
|
155
|
|
|
public function structuredReferenceProviderRowData(): array |
|
156
|
|
|
{ |
|
157
|
|
|
return [ |
|
158
|
|
|
['E5', 'DeptSales[[#This Row], [Commission Amount]]'], |
|
159
|
|
|
['E5', 'DeptSales[@Commission Amount]'], |
|
160
|
|
|
['E5', 'DeptSales[@[Commission Amount]]'], |
|
161
|
|
|
['C5:D5', 'DeptSales[@[Sales Amount]:[% Commission]]'], |
|
162
|
|
|
]; |
|
163
|
|
|
} |
|
164
|
|
|
} |
|
165
|
|
|
|
PHPOffice /
PhpSpreadsheet
