|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\DataValidation; |
|
4
|
|
|
use PhpOffice\PhpSpreadsheet\NamedRange; |
|
5
|
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet; |
|
6
|
|
|
|
|
7
|
|
|
require __DIR__ . '/../Header.php'; |
|
8
|
|
|
|
|
9
|
|
|
// Create new Spreadsheet object |
|
10
|
|
|
$helper->log('Create new Spreadsheet object'); |
|
11
|
|
|
$spreadsheet = new Spreadsheet(); |
|
12
|
|
|
|
|
13
|
|
|
// Set document properties |
|
14
|
|
|
$helper->log('Set document properties'); |
|
15
|
|
|
$spreadsheet->getProperties() |
|
16
|
|
|
->setCreator('PHPOffice') |
|
17
|
|
|
->setLastModifiedBy('PHPOffice') |
|
18
|
|
|
->setTitle('PhpSpreadsheet Test Document') |
|
19
|
|
|
->setSubject('PhpSpreadsheet Test Document') |
|
20
|
|
|
->setDescription('Test document for PhpSpreadsheet, generated using PHP classes.') |
|
21
|
|
|
->setKeywords('Office PhpSpreadsheet php') |
|
22
|
|
|
->setCategory('Test result file'); |
|
23
|
|
|
function transpose($value) |
|
24
|
|
|
{ |
|
25
|
|
|
return [$value]; |
|
26
|
|
|
} |
|
27
|
|
|
|
|
28
|
|
|
// Add some data |
|
29
|
|
|
$continentColumn = 'D'; |
|
30
|
|
|
$column = 'F'; |
|
31
|
|
|
|
|
32
|
|
|
// Set data for dropdowns |
|
33
|
|
|
$continents = glob(__DIR__ . '/data/continents/*'); |
|
34
|
|
|
foreach ($continents as $key => $filename) { |
|
35
|
|
|
$continent = pathinfo($filename, PATHINFO_FILENAME); |
|
36
|
|
|
$helper->log("Loading $continent"); |
|
37
|
|
|
$continent = str_replace(' ', '_', $continent); |
|
38
|
|
|
$countries = file($filename, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES); |
|
39
|
|
|
$countryCount = count($countries); |
|
|
|
|
|
|
40
|
|
|
|
|
41
|
|
|
// Transpose $countries from a row to a column array |
|
42
|
|
|
$countries = array_map('transpose', $countries); |
|
|
|
|
|
|
43
|
|
|
$spreadsheet->getActiveSheet() |
|
44
|
|
|
->fromArray($countries, null, $column . '1'); |
|
45
|
|
|
$spreadsheet->addNamedRange( |
|
46
|
|
|
new NamedRange( |
|
47
|
|
|
$continent, |
|
48
|
|
|
$spreadsheet->getActiveSheet(), |
|
49
|
|
|
$column . '1:' . $column . $countryCount |
|
50
|
|
|
) |
|
51
|
|
|
); |
|
52
|
|
|
$spreadsheet->getActiveSheet() |
|
53
|
|
|
->getColumnDimension($column) |
|
54
|
|
|
->setVisible(false); |
|
55
|
|
|
|
|
56
|
|
|
$spreadsheet->getActiveSheet() |
|
57
|
|
|
->setCellValue($continentColumn . ($key + 1), $continent); |
|
58
|
|
|
|
|
59
|
|
|
++$column; |
|
60
|
|
|
} |
|
61
|
|
|
|
|
62
|
|
|
// Hide the dropdown data |
|
63
|
|
|
$spreadsheet->getActiveSheet() |
|
64
|
|
|
->getColumnDimension($continentColumn) |
|
65
|
|
|
->setVisible(false); |
|
66
|
|
|
|
|
67
|
|
|
$spreadsheet->addNamedRange( |
|
68
|
|
|
new NamedRange( |
|
69
|
|
|
'Continents', |
|
70
|
|
|
$spreadsheet->getActiveSheet(), |
|
71
|
|
|
$continentColumn . '1:' . $continentColumn . count($continents) |
|
72
|
|
|
) |
|
73
|
|
|
); |
|
74
|
|
|
|
|
75
|
|
|
// Set selection cells |
|
76
|
|
|
$spreadsheet->getActiveSheet() |
|
77
|
|
|
->setCellValue('A1', 'Continent:'); |
|
78
|
|
|
$spreadsheet->getActiveSheet() |
|
79
|
|
|
->setCellValue('B1', 'Select continent'); |
|
80
|
|
|
$spreadsheet->getActiveSheet() |
|
81
|
|
|
->setCellValue('B3', '=' . $column . 1); |
|
82
|
|
|
$spreadsheet->getActiveSheet() |
|
83
|
|
|
->setCellValue('B3', 'Select country'); |
|
84
|
|
|
$spreadsheet->getActiveSheet() |
|
85
|
|
|
->getStyle('A1:A3') |
|
86
|
|
|
->getFont()->setBold(true); |
|
87
|
|
|
|
|
88
|
|
|
// Set linked validators |
|
89
|
|
|
$validation = $spreadsheet->getActiveSheet() |
|
90
|
|
|
->getCell('B1') |
|
91
|
|
|
->getDataValidation(); |
|
92
|
|
|
$validation->setType(DataValidation::TYPE_LIST) |
|
93
|
|
|
->setErrorStyle(DataValidation::STYLE_INFORMATION) |
|
94
|
|
|
->setAllowBlank(false) |
|
95
|
|
|
->setShowInputMessage(true) |
|
96
|
|
|
->setShowErrorMessage(true) |
|
97
|
|
|
->setShowDropDown(true) |
|
98
|
|
|
->setErrorTitle('Input error') |
|
99
|
|
|
->setError('Continent is not in the list.') |
|
100
|
|
|
->setPromptTitle('Pick from the list') |
|
101
|
|
|
->setPrompt('Please pick a continent from the drop-down list.') |
|
102
|
|
|
->setFormula1('=Continents'); |
|
103
|
|
|
|
|
104
|
|
|
$spreadsheet->getActiveSheet() |
|
105
|
|
|
->setCellValue('A3', 'Country:'); |
|
106
|
|
|
$spreadsheet->getActiveSheet() |
|
107
|
|
|
->getStyle('A3') |
|
108
|
|
|
->getFont()->setBold(true); |
|
109
|
|
|
|
|
110
|
|
|
$validation = $spreadsheet->getActiveSheet() |
|
111
|
|
|
->getCell('B3') |
|
112
|
|
|
->getDataValidation(); |
|
113
|
|
|
$validation->setType(DataValidation::TYPE_LIST) |
|
114
|
|
|
->setErrorStyle(DataValidation::STYLE_INFORMATION) |
|
115
|
|
|
->setAllowBlank(false) |
|
116
|
|
|
->setShowInputMessage(true) |
|
117
|
|
|
->setShowErrorMessage(true) |
|
118
|
|
|
->setShowDropDown(true) |
|
119
|
|
|
->setErrorTitle('Input error') |
|
120
|
|
|
->setError('Country is not in the list.') |
|
121
|
|
|
->setPromptTitle('Pick from the list') |
|
122
|
|
|
->setPrompt('Please pick a country from the drop-down list.') |
|
123
|
|
|
->setFormula1('=INDIRECT($B$1)'); |
|
124
|
|
|
|
|
125
|
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(12); |
|
126
|
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(30); |
|
127
|
|
|
|
|
128
|
|
|
// Save |
|
129
|
|
|
$helper->write($spreadsheet, __FILE__); |
|
130
|
|
|
|
PHPOffice /
PhpSpreadsheet
Loading history...