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++ ) { |
|
|
|
|
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: