|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
declare(strict_types=1); |
|
4
|
|
|
|
|
5
|
|
|
namespace PhpOffice\PhpSpreadsheetTests\Worksheet; |
|
6
|
|
|
|
|
7
|
|
|
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader; |
|
8
|
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet; |
|
9
|
|
|
use PhpOffice\PhpSpreadsheet\Style\Alignment; |
|
10
|
|
|
use PhpOffice\PhpSpreadsheet\Style\Fill; |
|
11
|
|
|
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet; |
|
12
|
|
|
use PHPUnit\Framework\TestCase; |
|
13
|
|
|
|
|
14
|
|
|
class MergeCellsDeletedTest extends TestCase |
|
15
|
|
|
{ |
|
16
|
|
|
public function testDeletedColumns(): void |
|
17
|
|
|
{ |
|
18
|
|
|
$infile = 'tests/data/Reader/XLSX/issue.282.xlsx'; |
|
19
|
|
|
$reader = new XlsxReader(); |
|
20
|
|
|
$spreadsheet = $reader->load($infile); |
|
21
|
|
|
$sheet = $spreadsheet->getSheetByNameOrThrow('Sheet1'); |
|
22
|
|
|
|
|
23
|
|
|
$mergeCells = $sheet->getMergeCells(); |
|
24
|
|
|
self::assertSame(['B1:F1', 'G1:I1'], array_values($mergeCells)); |
|
25
|
|
|
|
|
26
|
|
|
// Want to delete column B,C,D,E,F |
|
27
|
|
|
$sheet->removeColumnByIndex(2, 5); |
|
28
|
|
|
$mergeCells2 = $sheet->getMergeCells(); |
|
29
|
|
|
self::assertSame(['B1:D1'], array_values($mergeCells2)); |
|
30
|
|
|
$spreadsheet->disconnectWorksheets(); |
|
31
|
|
|
} |
|
32
|
|
|
|
|
33
|
|
|
public function testDeletedRows(): void |
|
34
|
|
|
{ |
|
35
|
|
|
$infile = 'tests/data/Reader/XLSX/issue.282.xlsx'; |
|
36
|
|
|
$reader = new XlsxReader(); |
|
37
|
|
|
$spreadsheet = $reader->load($infile); |
|
38
|
|
|
$sheet = $spreadsheet->getSheetByNameOrThrow('Sheet2'); |
|
39
|
|
|
|
|
40
|
|
|
$mergeCells = $sheet->getMergeCells(); |
|
41
|
|
|
self::assertSame(['A2:A6', 'A7:A9'], array_values($mergeCells)); |
|
42
|
|
|
|
|
43
|
|
|
// Want to delete rows 2 to 4 |
|
44
|
|
|
$sheet->removeRow(2, 3); |
|
45
|
|
|
$mergeCells2 = $sheet->getMergeCells(); |
|
46
|
|
|
self::assertSame(['A4:A6'], array_values($mergeCells2)); |
|
47
|
|
|
$spreadsheet->disconnectWorksheets(); |
|
48
|
|
|
} |
|
49
|
|
|
|
|
50
|
|
|
private static function yellowBackground(Worksheet $sheet, string $cells, string $color = 'ffffff00'): void |
|
51
|
|
|
{ |
|
52
|
|
|
$sheet->getStyle($cells) |
|
53
|
|
|
->getFill() |
|
54
|
|
|
->setFillType(Fill::FILL_SOLID); |
|
55
|
|
|
$sheet->getStyle($cells) |
|
56
|
|
|
->getFill() |
|
57
|
|
|
->getStartColor() |
|
58
|
|
|
->setArgb($color); |
|
59
|
|
|
$sheet->getStyle($cells) |
|
60
|
|
|
->getAlignment() |
|
61
|
|
|
->setHorizontal(Alignment::HORIZONTAL_CENTER); |
|
62
|
|
|
} |
|
63
|
|
|
|
|
64
|
|
|
public static function testDeletedColumns2(): void |
|
65
|
|
|
{ |
|
66
|
|
|
$spreadsheet = new Spreadsheet(); |
|
67
|
|
|
$sheet = $spreadsheet->getActiveSheet(); |
|
68
|
|
|
$sheet->setTitle('Before'); |
|
69
|
|
|
$sheet->getCell('A1')->setValue('a1'); |
|
70
|
|
|
$sheet->getCell('J1')->setValue('j1'); |
|
71
|
|
|
$sheet->getCell('K1')->setValue('will delete d-f'); |
|
72
|
|
|
$sheet->getCell('C1')->setValue('c1-g1'); |
|
73
|
|
|
$sheet->mergeCells('C1:G1'); |
|
74
|
|
|
self::yellowBackground($sheet, 'C1'); |
|
75
|
|
|
|
|
76
|
|
|
$sheet->getCell('A2')->setValue('a2'); |
|
77
|
|
|
$sheet->getCell('J2')->setValue('j2'); |
|
78
|
|
|
$sheet->getCell('B2')->setValue('b2-c2'); |
|
79
|
|
|
$sheet->mergeCells('B2:C2'); |
|
80
|
|
|
self::yellowBackground($sheet, 'B2'); |
|
81
|
|
|
$sheet->getCell('G2')->setValue('g2-h2'); |
|
82
|
|
|
$sheet->mergeCells('G2:H2'); |
|
83
|
|
|
self::yellowBackground($sheet, 'G2', 'FF00FFFF'); |
|
84
|
|
|
|
|
85
|
|
|
$sheet->getCell('A3')->setValue('a3'); |
|
86
|
|
|
$sheet->getCell('J3')->setValue('j3'); |
|
87
|
|
|
$sheet->getCell('D3')->setValue('d3-g3'); |
|
88
|
|
|
$sheet->mergeCells('D3:G3'); |
|
89
|
|
|
self::yellowBackground($sheet, 'D3'); |
|
90
|
|
|
|
|
91
|
|
|
$sheet->getCell('A4')->setValue('a4'); |
|
92
|
|
|
$sheet->getCell('J4')->setValue('j4'); |
|
93
|
|
|
$sheet->getCell('B4')->setValue('b4-d4'); |
|
94
|
|
|
$sheet->mergeCells('B4:D4'); |
|
95
|
|
|
self::yellowBackground($sheet, 'B4'); |
|
96
|
|
|
|
|
97
|
|
|
$sheet->getCell('A5')->setValue('a5'); |
|
98
|
|
|
$sheet->getCell('J5')->setValue('j5'); |
|
99
|
|
|
$sheet->getCell('D5')->setValue('d5-e5'); |
|
100
|
|
|
$sheet->mergeCells('D5:E5'); |
|
101
|
|
|
self::yellowBackground($sheet, 'D5'); |
|
102
|
|
|
|
|
103
|
|
|
$sheet->removeColumn('D', 3); |
|
104
|
|
|
$expected = [ |
|
105
|
|
|
'C1:D1', // was C1:G1, drop 3 inside cells |
|
106
|
|
|
'B2:C2', // was B2:C2, unaffected |
|
107
|
|
|
'D2:E2', // was G2:H2, move 3 columns left |
|
108
|
|
|
//'D2:E2', // was D3:G3, start in delete range |
|
109
|
|
|
'B4:C4', // was B4:D4, truncated at start of delete range |
|
110
|
|
|
//'D5:E5', // was D5:E5, start in delete range |
|
111
|
|
|
]; |
|
112
|
|
|
self::assertSame($expected, array_keys($sheet->getMergeCells())); |
|
113
|
|
|
|
|
114
|
|
|
$spreadsheet->disconnectWorksheets(); |
|
115
|
|
|
} |
|
116
|
|
|
|
|
117
|
|
|
public static function testDeletedRows2(): void |
|
118
|
|
|
{ |
|
119
|
|
|
$spreadsheet = new Spreadsheet(); |
|
120
|
|
|
$sheet = $spreadsheet->getActiveSheet(); |
|
121
|
|
|
$sheet->setTitle('Before'); |
|
122
|
|
|
$sheet->getCell('A1')->setValue('a1'); |
|
123
|
|
|
$sheet->getCell('A10')->setValue('a10'); |
|
124
|
|
|
$sheet->getCell('A11')->setValue('will delete 4-6'); |
|
125
|
|
|
$sheet->getCell('A3')->setValue('a3-a7'); |
|
126
|
|
|
$sheet->mergeCells('A3:A7'); |
|
127
|
|
|
self::yellowBackground($sheet, 'A3'); |
|
128
|
|
|
|
|
129
|
|
|
$sheet->getCell('B1')->setValue('b1'); |
|
130
|
|
|
$sheet->getCell('B10')->setValue('b10'); |
|
131
|
|
|
$sheet->getCell('B2')->setValue('b2-b3'); |
|
132
|
|
|
$sheet->mergeCells('B2:B3'); |
|
133
|
|
|
self::yellowBackground($sheet, 'B2'); |
|
134
|
|
|
$sheet->getCell('B7')->setValue('b7-b8'); |
|
135
|
|
|
$sheet->mergeCells('B7:B8'); |
|
136
|
|
|
self::yellowBackground($sheet, 'B7', 'FF00FFFF'); |
|
137
|
|
|
|
|
138
|
|
|
$sheet->getCell('C1')->setValue('c1'); |
|
139
|
|
|
$sheet->getCell('C10')->setValue('c10'); |
|
140
|
|
|
$sheet->getCell('C4')->setValue('c4-c7'); |
|
141
|
|
|
$sheet->mergeCells('C4:C7'); |
|
142
|
|
|
self::yellowBackground($sheet, 'C4'); |
|
143
|
|
|
|
|
144
|
|
|
$sheet->getCell('D1')->setValue('d1'); |
|
145
|
|
|
$sheet->getCell('D10')->setValue('d10'); |
|
146
|
|
|
$sheet->getCell('D2')->setValue('d2-d4'); |
|
147
|
|
|
$sheet->mergeCells('D2:D4'); |
|
148
|
|
|
self::yellowBackground($sheet, 'd2'); |
|
149
|
|
|
|
|
150
|
|
|
$sheet->getCell('E1')->setValue('e1'); |
|
151
|
|
|
$sheet->getCell('E10')->setValue('e10'); |
|
152
|
|
|
$sheet->getCell('E4')->setValue('e4-e5'); |
|
153
|
|
|
$sheet->mergeCells('E4:E5'); |
|
154
|
|
|
self::yellowBackground($sheet, 'E4'); |
|
155
|
|
|
|
|
156
|
|
|
$sheet->removeRow(4, 3); |
|
157
|
|
|
$expected = [ |
|
158
|
|
|
'A3:A4', // was A3:A7, drop 3 inside cells |
|
159
|
|
|
'B2:B3', // was B2:B3, unaffected |
|
160
|
|
|
'B4:B5', // was B7:B8, move 3 columns up |
|
161
|
|
|
//'C4:C7', // was C4:C7, start in delete range |
|
162
|
|
|
'D2:D3', // was D2:D4, truncated at start of delete range |
|
163
|
|
|
//'E4:E5', // was E4:E5, start in delete range |
|
164
|
|
|
]; |
|
165
|
|
|
self::assertSame($expected, array_keys($sheet->getMergeCells())); |
|
166
|
|
|
|
|
167
|
|
|
$spreadsheet->disconnectWorksheets(); |
|
168
|
|
|
} |
|
169
|
|
|
} |
|
170
|
|
|
|
PHPOffice /
PhpSpreadsheet
