1 | <?php |
||||
2 | |||||
3 | use PhpOffice\PhpSpreadsheet\Shared\Date; |
||||
4 | use PhpOffice\PhpSpreadsheet\Spreadsheet; |
||||
5 | use PhpOffice\PhpSpreadsheet\Style\NumberFormat; |
||||
6 | use PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column; |
||||
7 | use PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule; |
||||
8 | |||||
9 | require __DIR__ . '/../Header.php'; |
||||
10 | |||||
11 | // Create new Spreadsheet object |
||||
12 | $helper->log('Create new Spreadsheet object'); |
||||
13 | $spreadsheet = new Spreadsheet(); |
||||
14 | |||||
15 | // Set document properties |
||||
16 | $helper->log('Set document properties'); |
||||
17 | $spreadsheet->getProperties()->setCreator('Maarten Balliauw') |
||||
18 | ->setLastModifiedBy('Maarten Balliauw') |
||||
19 | ->setTitle('PhpSpreadsheet Test Document') |
||||
20 | ->setSubject('PhpSpreadsheet Test Document') |
||||
21 | ->setDescription('Test document for PhpSpreadsheet, generated using PHP classes.') |
||||
22 | ->setKeywords('office PhpSpreadsheet php') |
||||
23 | ->setCategory('Test result file'); |
||||
24 | |||||
25 | // Create the worksheet |
||||
26 | $helper->log('Add data'); |
||||
27 | $spreadsheet->setActiveSheetIndex(0); |
||||
28 | $spreadsheet->getActiveSheet()->setCellValue('A1', 'Financial Year') |
||||
29 | ->setCellValue('B1', 'Financial Period') |
||||
30 | ->setCellValue('C1', 'Country') |
||||
31 | ->setCellValue('D1', 'Date') |
||||
32 | ->setCellValue('E1', 'Sales Value') |
||||
33 | ->setCellValue('F1', 'Expenditure'); |
||||
34 | $startYear = $endYear = $currentYear = date('Y'); |
||||
35 | --$startYear; |
||||
36 | ++$endYear; |
||||
37 | |||||
38 | $years = range($startYear, $endYear); |
||||
39 | $periods = range(1, 12); |
||||
40 | $countries = [ |
||||
41 | 'United States', |
||||
42 | 'UK', |
||||
43 | 'France', |
||||
44 | 'Germany', |
||||
45 | 'Italy', |
||||
46 | 'Spain', |
||||
47 | 'Portugal', |
||||
48 | 'Japan', |
||||
49 | ]; |
||||
50 | |||||
51 | $row = 2; |
||||
52 | foreach ($years as $year) { |
||||
53 | foreach ($periods as $period) { |
||||
54 | foreach ($countries as $country) { |
||||
55 | $endDays = date('t', mktime(0, 0, 0, $period, 1, $year)); |
||||
56 | for ($i = 1; $i <= $endDays; ++$i) { |
||||
57 | $eDate = Date::formattedPHPToExcel( |
||||
58 | $year, |
||||
59 | $period, |
||||
60 | $i |
||||
61 | ); |
||||
62 | $value = rand(500, 1000) * (1 + rand(-0.25, +0.25)); |
||||
0 ignored issues
–
show
Bug
introduced
by
Loading history...
|
|||||
63 | $salesValue = $invoiceValue = null; |
||||
64 | $incomeOrExpenditure = rand(-1, 1); |
||||
65 | if ($incomeOrExpenditure == -1) { |
||||
66 | $expenditure = rand(-500, -1000) * (1 + rand(-0.25, +0.25)); |
||||
67 | $income = null; |
||||
68 | } elseif ($incomeOrExpenditure == 1) { |
||||
69 | $expenditure = rand(-500, -1000) * (1 + rand(-0.25, +0.25)); |
||||
70 | $income = rand(500, 1000) * (1 + rand(-0.25, +0.25)); |
||||
71 | } else { |
||||
72 | $expenditure = null; |
||||
73 | $income = rand(500, 1000) * (1 + rand(-0.25, +0.25)); |
||||
74 | } |
||||
75 | $dataArray = [$year, |
||||
76 | $period, |
||||
77 | $country, |
||||
78 | $eDate, |
||||
79 | $income, |
||||
80 | $expenditure, |
||||
81 | ]; |
||||
82 | $spreadsheet->getActiveSheet()->fromArray($dataArray, null, 'A' . $row++); |
||||
83 | } |
||||
84 | } |
||||
85 | } |
||||
86 | } |
||||
87 | --$row; |
||||
88 | |||||
89 | // Set styling |
||||
90 | $helper->log('Set styling'); |
||||
91 | $spreadsheet->getActiveSheet()->getStyle('A1:F1')->getFont()->setBold(true); |
||||
92 | $spreadsheet->getActiveSheet()->getStyle('A1:F1')->getAlignment()->setWrapText(true); |
||||
93 | $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(12.5); |
||||
94 | $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(10.5); |
||||
95 | $spreadsheet->getActiveSheet()->getStyle('D2:D' . $row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_YYYYMMDD2); |
||||
96 | $spreadsheet->getActiveSheet()->getStyle('E2:F' . $row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_CURRENCY_USD_SIMPLE); |
||||
97 | $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(14); |
||||
98 | $spreadsheet->getActiveSheet()->freezePane('A2'); |
||||
99 | |||||
100 | // Set autofilter range |
||||
101 | $helper->log('Set autofilter range'); |
||||
102 | // Always include the complete filter range! |
||||
103 | // Excel does support setting only the caption |
||||
104 | // row, but that's not a best practise... |
||||
105 | $spreadsheet->getActiveSheet()->setAutoFilter($spreadsheet->getActiveSheet()->calculateWorksheetDimension()); |
||||
106 | |||||
107 | // Set active filters |
||||
108 | $autoFilter = $spreadsheet->getActiveSheet()->getAutoFilter(); |
||||
109 | $helper->log('Set active filters'); |
||||
110 | // Filter the Country column on a filter value of countries beginning with the letter U (or Japan) |
||||
111 | // We use * as a wildcard, so specify as U* and using a wildcard requires customFilter |
||||
112 | $autoFilter->getColumn('C') |
||||
113 | ->setFilterType(Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER) |
||||
114 | ->createRule() |
||||
115 | ->setRule( |
||||
116 | Rule::AUTOFILTER_COLUMN_RULE_EQUAL, |
||||
117 | 'u*' |
||||
118 | ) |
||||
119 | ->setRuleType(Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER); |
||||
120 | $autoFilter->getColumn('C') |
||||
121 | ->createRule() |
||||
122 | ->setRule( |
||||
123 | Rule::AUTOFILTER_COLUMN_RULE_EQUAL, |
||||
124 | 'japan' |
||||
125 | ) |
||||
126 | ->setRuleType(Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER); |
||||
127 | // Filter the Date column on a filter value of the first day of every period of the current year |
||||
128 | // We us a dateGroup ruletype for this, although it is still a standard filter |
||||
129 | foreach ($periods as $period) { |
||||
130 | $endDate = date('t', mktime(0, 0, 0, $period, 1, $currentYear)); |
||||
0 ignored issues
–
show
$currentYear of type string is incompatible with the type integer expected by parameter $year of mktime() .
(
Ignorable by Annotation
)
If this is a false-positive, you can also ignore this issue in your code via the
Loading history...
|
|||||
131 | |||||
132 | $autoFilter->getColumn('D') |
||||
133 | ->setFilterType(Column::AUTOFILTER_FILTERTYPE_FILTER) |
||||
134 | ->createRule() |
||||
135 | ->setRule( |
||||
136 | Rule::AUTOFILTER_COLUMN_RULE_EQUAL, |
||||
137 | [ |
||||
138 | 'year' => $currentYear, |
||||
139 | 'month' => $period, |
||||
140 | 'day' => $endDate, |
||||
141 | ] |
||||
142 | ) |
||||
143 | ->setRuleType(Rule::AUTOFILTER_RULETYPE_DATEGROUP); |
||||
144 | } |
||||
145 | // Display only sales values that are blank |
||||
146 | // Standard filter, operator equals, and value of NULL |
||||
147 | $autoFilter->getColumn('E') |
||||
148 | ->setFilterType(Column::AUTOFILTER_FILTERTYPE_FILTER) |
||||
149 | ->createRule() |
||||
150 | ->setRule( |
||||
151 | Rule::AUTOFILTER_COLUMN_RULE_EQUAL, |
||||
152 | '' |
||||
153 | ); |
||||
154 | |||||
155 | // Save |
||||
156 | $helper->write($spreadsheet, __FILE__); |
||||
157 |