These results are based on our legacy PHP analysis, consider migrating to our new PHP analysis engine instead. Learn more
1 | <?php |
||
2 | |||
3 | namespace SRF; |
||
4 | |||
5 | use ImagePage; |
||
6 | use PhpOffice\PhpSpreadsheet\Calculation\DateTime; |
||
7 | use PhpOffice\PhpSpreadsheet\Cell\Cell; |
||
8 | use PhpOffice\PhpSpreadsheet\Cell\DataType; |
||
9 | use PhpOffice\PhpSpreadsheet\IOFactory; |
||
10 | use PhpOffice\PhpSpreadsheet\Spreadsheet; |
||
11 | use PhpOffice\PhpSpreadsheet\Style\NumberFormat; |
||
12 | use PhpOffice\PhpSpreadsheet\Worksheet\Row; |
||
13 | use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet; |
||
14 | use Sanitizer; |
||
15 | use SMW\FileExportPrinter; |
||
16 | use SMW\Query\ExportPrinter; |
||
17 | use SMWDataValue; |
||
18 | use SMWQueryResult; |
||
19 | use SMWResultArray; |
||
20 | use Title; |
||
21 | |||
22 | /** |
||
23 | * @author Kim Eik |
||
24 | * @since 1.9 |
||
25 | */ |
||
26 | class SpreadsheetPrinter extends FileExportPrinter { |
||
27 | |||
28 | const HEADER_ROW_OFFSET = 1; |
||
29 | |||
30 | protected $fileFormats = [ |
||
31 | 'xlsx' => [ |
||
32 | 'writer' => 'Xlsx', |
||
33 | 'mimetype' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', |
||
34 | 'extension' => '.xlsx', |
||
35 | ], |
||
36 | 'xls' => [ |
||
37 | 'writer' => 'Xls', |
||
38 | 'mimetype' => 'aapplication/vnd.ms-excel', |
||
39 | 'extension' => '.xls', |
||
40 | ], |
||
41 | 'ods' => [ |
||
42 | 'writer' => 'Ods', |
||
43 | 'mimetype' => 'application/vnd.oasis.opendocument.spreadsheet', |
||
44 | 'extension' => '.ods', |
||
45 | ], |
||
46 | 'csv' => [ |
||
47 | 'writer' => 'Csv', |
||
48 | 'mimetype' => 'text/csv', |
||
49 | 'extension' => '.csv', |
||
50 | ], |
||
51 | ]; |
||
52 | |||
53 | protected $styled = false; |
||
54 | protected $fileFormat; |
||
55 | |||
56 | /** |
||
57 | * @see ExportPrinter::getMimeType() |
||
58 | * |
||
59 | * @since 1.8 |
||
60 | * |
||
61 | * @param SMWQueryResult $queryResult |
||
62 | * |
||
63 | * @return string |
||
64 | */ |
||
65 | public function getMimeType( SMWQueryResult $queryResult ) { |
||
66 | return $this->fileFormat[ 'mimetype' ]; |
||
67 | } |
||
68 | |||
69 | /** |
||
70 | * @see ExportPrinter::getFileName |
||
71 | * |
||
72 | * @param SMWQueryResult $queryResult |
||
73 | * |
||
74 | * @return string |
||
75 | */ |
||
76 | public function getFileName( SMWQueryResult $queryResult ) { |
||
77 | return ( $this->params[ 'filename' ] ?: base_convert( uniqid(), 16, 36 ) ) . $this->fileFormat[ 'extension' ]; |
||
78 | } |
||
79 | |||
80 | /** |
||
81 | * @see ExportPrinter::outputAsFile |
||
82 | * |
||
83 | * @param SMWQueryResult $queryResult |
||
84 | * @param array $params |
||
85 | */ |
||
86 | public function outputAsFile( SMWQueryResult $queryResult, array $params ) { |
||
87 | |||
88 | if ( array_key_exists( 'fileformat', $params) && array_key_exists( $params[ 'fileformat' ]->getValue(), $this->fileFormats )) { |
||
89 | $this->fileFormat = $this->fileFormats[ $params[ 'fileformat' ]->getValue() ]; |
||
90 | } else { |
||
91 | $this->fileFormat = $this->fileFormats[ 'xlsx' ]; |
||
92 | } |
||
93 | |||
94 | parent::outputAsFile( $queryResult, $params ); |
||
95 | } |
||
96 | |||
97 | /** |
||
98 | * @param $definitions \ParamProcessor\ParamDefinition[] |
||
99 | * |
||
100 | * @return array |
||
101 | */ |
||
102 | public function getParamDefinitions( array $definitions ) { |
||
103 | $params = parent::getParamDefinitions( $definitions ); |
||
104 | |||
105 | $definitions[ 'searchlabel' ]->setDefault( wfMessage( 'srf-spreadsheet-link' )->inContentLanguage()->text() ); |
||
106 | |||
107 | $params[ 'templatefile' ] = [ |
||
108 | 'type' => 'string', |
||
109 | 'name' => 'templatefile', |
||
110 | 'default' => '', |
||
111 | 'message' => 'srf-paramdesc-spreadsheet-templatefile', |
||
112 | ]; |
||
113 | |||
114 | $params[ 'filename' ] = [ |
||
115 | 'type' => 'string', |
||
116 | 'name' => 'filename', |
||
117 | 'default' => '', |
||
118 | 'message' => 'srf-paramdesc-spreadsheet-filename', |
||
119 | ]; |
||
120 | |||
121 | $params[ 'fileformat' ] = [ |
||
122 | 'type' => 'string', |
||
123 | 'name' => 'fileformat', |
||
124 | 'default' => 'xlsx', |
||
125 | 'tolower' => true, |
||
126 | 'message' => 'srf-paramdesc-spreadsheet-fileformat', |
||
127 | ]; |
||
128 | |||
129 | return $params; |
||
130 | } |
||
131 | |||
132 | /* |
||
133 | * Turns the PhpSpreadsheet document object into a string |
||
134 | */ |
||
135 | |||
136 | /** |
||
137 | * Return serialised results in specified format. |
||
138 | * |
||
139 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
140 | * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception |
||
141 | */ |
||
142 | 1 | protected function getResultText( SMWQueryResult $queryResult, $outputMode ) { |
|
143 | |||
144 | 1 | if ( $outputMode === SMW_OUTPUT_FILE ) { |
|
145 | return $this->getResultFileContents( $queryResult ); |
||
146 | } |
||
147 | |||
148 | 1 | $this->isHTML = ( $outputMode === SMW_OUTPUT_HTML ); |
|
149 | 1 | return $this->getLink( $queryResult, $outputMode )->getText( $outputMode, $this->mLinker ); |
|
150 | } |
||
151 | |||
152 | /** |
||
153 | * @param SMWQueryResult $queryResult |
||
154 | * |
||
155 | * @return string |
||
156 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
157 | * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception |
||
158 | */ |
||
159 | protected function getResultFileContents( SMWQueryResult $queryResult ) { |
||
160 | |||
161 | $spreadsheet = $this->createSpreadsheet(); |
||
162 | $worksheet = $spreadsheet->getSheet( 0 ); |
||
163 | |||
164 | $this->populateWorksheet( $worksheet, $queryResult ); |
||
165 | |||
166 | //$spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(); |
||
167 | |||
168 | for ( $i = 0; $i < count( $queryResult->getPrintRequests() ); $i++ ) { |
||
0 ignored issues
–
show
Consider avoiding function calls on each iteration of the
for loop.
If you have a function call in the test part of a // count() is called on each iteration
for ($i=0; $i < count($collection); $i++) { }
// count() is only called once
for ($i=0, $c=count($collection); $i<$c; $i++) { }
Loading history...
|
|||
169 | $worksheet->getColumnDimensionByColumn( $i )->setAutoSize( true ); |
||
170 | } |
||
171 | |||
172 | $result = $this->getStringFromSpreadsheet( $spreadsheet ); |
||
173 | return $result; |
||
174 | } |
||
175 | |||
176 | /** |
||
177 | * Creates a new PhpSpreadsheet document and returns it |
||
178 | * |
||
179 | * @return Spreadsheet |
||
180 | * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception |
||
181 | */ |
||
182 | protected function createSpreadsheet() { |
||
183 | |||
184 | $fileTitle = Title::newFromText( $this->params[ 'templatefile' ], NS_FILE ); |
||
185 | |||
186 | if ( $fileTitle !== null && $fileTitle->exists() ) { |
||
187 | |||
188 | $spreadsheet = $this->createSpreadsheetFromTemplate( $fileTitle ); |
||
189 | |||
190 | $this->styled = true; |
||
191 | |||
192 | } else { |
||
193 | |||
194 | $spreadsheet = new Spreadsheet(); |
||
195 | |||
196 | } |
||
197 | |||
198 | // Set document properties |
||
199 | $spreadsheet->getProperties()->setCreator( 'SemanticMediaWiki Spreadsheet Export' ); |
||
200 | |||
201 | return $spreadsheet; |
||
202 | } |
||
203 | |||
204 | /** |
||
205 | * @param $fileTitle |
||
206 | * |
||
207 | * @return Spreadsheet |
||
208 | * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception |
||
209 | */ |
||
210 | private function createSpreadsheetFromTemplate( $fileTitle ) { |
||
211 | $filePage = new ImagePage( $fileTitle, $this ); |
||
212 | |||
213 | $virtualFile = $filePage->getDisplayedFile(); |
||
214 | $virtualFilePath = $virtualFile->getPath(); |
||
215 | |||
216 | $localFile = $virtualFile->getRepo()->getLocalReference( $virtualFilePath ); |
||
217 | $localFilePath = $localFile->getPath(); |
||
218 | |||
219 | return IOFactory::load( $localFilePath ); |
||
220 | } |
||
221 | |||
222 | /** |
||
223 | * @param SMWQueryResult $queryResult |
||
224 | * @param $worksheet |
||
225 | * |
||
226 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
227 | */ |
||
228 | protected function populateWorksheet( Worksheet $worksheet, SMWQueryResult $queryResult ) { |
||
229 | |||
230 | $rowIterator = $worksheet->getRowIterator( self::HEADER_ROW_OFFSET ); |
||
231 | |||
232 | //Get headers |
||
233 | if ( $this->mShowHeaders ) { |
||
234 | $this->populateHeaderRow( $rowIterator->current(), $queryResult ); |
||
235 | $rowIterator->next(); |
||
236 | } |
||
237 | |||
238 | while ( $resultRow = $queryResult->getNext() ) { |
||
239 | |||
240 | //Get data rows |
||
241 | $this->populateRow( $rowIterator->current(), $resultRow ); |
||
242 | $rowIterator->next(); |
||
243 | } |
||
244 | } |
||
245 | |||
246 | /** |
||
247 | * Populates the PhpSpreadsheet sheet with the headers from the result query |
||
248 | * |
||
249 | * @param Row $row |
||
250 | * @param SMWQueryResult $queryResult The query result |
||
251 | * |
||
252 | * @return Row |
||
253 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
254 | */ |
||
255 | protected function populateHeaderRow( Row $row, SMWQueryResult $queryResult ) { |
||
256 | |||
257 | $printRequests = $queryResult->getPrintRequests(); |
||
258 | $cellIterator = $row->getCellIterator(); |
||
259 | |||
260 | foreach ( $printRequests as $printRequest ) { |
||
261 | |||
262 | $cell = $cellIterator->current(); |
||
263 | |||
264 | $cell->setValue( $printRequest->getLabel() ); |
||
265 | |||
266 | $cell->getStyle() |
||
267 | ->getFont() |
||
268 | ->setBold( true ); |
||
269 | |||
270 | $cellIterator->next(); |
||
271 | |||
272 | } |
||
273 | |||
274 | return $row; |
||
275 | } |
||
276 | |||
277 | /** |
||
278 | * Populates the PhpSpreadsheet document with the query data |
||
279 | * |
||
280 | * @param Row $row |
||
281 | * @param $resultRow |
||
282 | * |
||
283 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
284 | */ |
||
285 | protected function populateRow( Row $row, $resultRow ) { |
||
286 | |||
287 | $cellIterator = $row->getCellIterator(); |
||
288 | |||
289 | foreach ( $resultRow as $resultField ) { |
||
290 | |||
291 | $this->populateCell( $cellIterator->current(), $resultField ); |
||
292 | $cellIterator->next(); |
||
293 | } |
||
294 | } |
||
295 | |||
296 | /** |
||
297 | * @param Cell $cell |
||
298 | * @param SMWResultArray $field |
||
299 | * |
||
300 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
301 | */ |
||
302 | protected function populateCell( Cell $cell, SMWResultArray $field ) { |
||
303 | |||
304 | $dataItems = $field->getContent(); |
||
305 | |||
306 | if ( $dataItems === false ) { |
||
307 | return; |
||
308 | } |
||
309 | |||
310 | if ( count( $dataItems ) > 1 ) { |
||
311 | |||
312 | while ( $value = $field->getNextText( SMW_OUTPUT_FILE ) ) { |
||
313 | $values[] = $value; |
||
314 | } |
||
315 | |||
316 | $cell->setValueExplicit( join( ', ', $values ), DataType::TYPE_STRING ); |
||
317 | |||
318 | } else { |
||
319 | |||
320 | $nextDataValue = $field->getNextDataValue(); |
||
321 | |||
322 | if ( $nextDataValue !== false ) { |
||
323 | $this->populateCellAccordingToType( $cell, $nextDataValue ); |
||
324 | } |
||
325 | |||
326 | } |
||
327 | } |
||
328 | |||
329 | /** |
||
330 | * Checks the type of the value, and set's it in the sheet accordingly |
||
331 | * |
||
332 | * @param Cell $cell |
||
333 | * @param SMWDataValue $value |
||
334 | * |
||
335 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
336 | */ |
||
337 | protected function populateCellAccordingToType( Cell $cell, SMWDataValue $value ) { |
||
338 | |||
339 | //NOTE: must check against subclasses before superclasses |
||
340 | if ( $value instanceof \SMWQuantityValue ) { |
||
341 | |||
342 | $this->setQuantityDataValue( $cell, $value ); |
||
343 | |||
344 | } elseif ( $value instanceof \SMWNumberValue ) { |
||
345 | |||
346 | $this->setNumberDataValue( $cell, $value ); |
||
347 | |||
348 | } elseif ( $value instanceof \SMWTimeValue ) { |
||
349 | |||
350 | $this->setTimeDataValue( $cell, $value ); |
||
351 | |||
352 | } else { |
||
353 | |||
354 | $this->setStringDataValue( $cell, $value ); |
||
355 | |||
356 | } |
||
357 | |||
358 | } |
||
359 | |||
360 | /** |
||
361 | * Sets a quantity value at the given col,row location |
||
362 | * |
||
363 | * @param Cell $cell |
||
364 | * @param \SMWQuantityValue $value SMWDataValue the raw data value object |
||
365 | * |
||
366 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
367 | */ |
||
368 | protected function setQuantityDataValue( Cell $cell, \SMWQuantityValue $value ) { |
||
369 | |||
370 | $type = DataType::TYPE_NUMERIC; |
||
371 | $unit = $value->getUnit(); |
||
372 | $number = $value->getNumber(); |
||
373 | |||
374 | $cell->setValueExplicit( $number, $type ); |
||
375 | |||
376 | if ( !$this->styled ) { |
||
377 | $cell->getStyle() |
||
378 | ->getNumberFormat() |
||
379 | ->setFormatCode( '0 "' . $unit . '"' ); |
||
380 | } |
||
381 | } |
||
382 | |||
383 | /** |
||
384 | * Sets a numeric value at the given col,row location |
||
385 | * |
||
386 | * @param Cell $cell |
||
387 | * @param \SMWNumberValue $value SMWDataValue the raw data value object |
||
388 | * |
||
389 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
390 | */ |
||
391 | protected function setNumberDataValue( Cell $cell, \SMWNumberValue $value ) { |
||
392 | |||
393 | $type = DataType::TYPE_NUMERIC; |
||
394 | $number = $value->getNumber(); |
||
395 | |||
396 | $cell->setValueExplicit( $number, $type ); |
||
397 | } |
||
398 | |||
399 | /** |
||
400 | * Sets a date/time value at the given col,row location |
||
401 | * |
||
402 | * @param Cell $cell |
||
403 | * @param \SMWTimeValue $value the raw data value object |
||
404 | * |
||
405 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
406 | */ |
||
407 | protected function setTimeDataValue( Cell $cell, \SMWTimeValue $value ) { |
||
408 | |||
409 | $type = DataType::TYPE_NUMERIC; |
||
410 | $number = DateTime::DATEVALUE( str_replace( 'T', ' ', $value->getISO8601Date() ) ); |
||
411 | |||
412 | $cell->setValueExplicit( $number, $type ); |
||
413 | |||
414 | if ( !$this->styled ) { |
||
415 | $cell->getStyle() |
||
416 | ->getNumberFormat() |
||
417 | ->setFormatCode( NumberFormat::FORMAT_DATE_DDMMYYYY ); |
||
418 | } |
||
419 | } |
||
420 | |||
421 | /** |
||
422 | * Sets or appends a string value at the given col,row location |
||
423 | * |
||
424 | * If there already exists a value at a given col,row location, then |
||
425 | * convert the cell to a string and append the data value. Creating |
||
426 | * a list of comma separated entries. |
||
427 | * |
||
428 | * @param Cell $cell |
||
429 | * @param $value SMWDataValue the raw data value object |
||
430 | * |
||
431 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
432 | */ |
||
433 | protected function setStringDataValue( Cell $cell, SMWDataValue $value ) { |
||
434 | |||
435 | $type = DataType::TYPE_STRING; |
||
436 | $text = $value->getWikiValue(); |
||
437 | $text = Sanitizer::decodeCharReferences( $text ); |
||
438 | $text = DataType::checkString( $text ); |
||
439 | |||
440 | $cell->setValueExplicit( $text, $type ); |
||
441 | } |
||
442 | |||
443 | /** |
||
444 | * @param Spreadsheet $spreadsheet |
||
445 | * |
||
446 | * @return string |
||
447 | * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception |
||
448 | */ |
||
449 | protected function getStringFromSpreadsheet( Spreadsheet $spreadsheet ) { |
||
450 | |||
451 | $writer = IOFactory::createWriter( $spreadsheet, $this->fileFormat[ 'writer' ] ); |
||
452 | |||
453 | ob_start(); |
||
454 | $writer->save( 'php://output' ); |
||
455 | return ob_get_clean(); |
||
456 | } |
||
457 | |||
458 | } |
||
459 | |||
460 |
If the size of the collection does not change during the iteration, it is generally a good practice to compute it beforehand, and not on each iteration: