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 Soluble\FlexStore\Writer\Excel; |
||
4 | |||
5 | use Soluble\FlexStore\Writer\AbstractSendableWriter; |
||
6 | use Soluble\FlexStore\Writer\Exception; |
||
7 | use Soluble\Spreadsheet\Library\LibXL; |
||
8 | use Soluble\FlexStore\Writer\Http\SimpleHeaders; |
||
9 | use Soluble\FlexStore\Column\ColumnType; |
||
10 | use Soluble\FlexStore\Options; |
||
11 | use Soluble\FlexStore\StoreInterface; |
||
12 | use ExcelBook; |
||
13 | use ExcelFormat; |
||
14 | use ArrayObject; |
||
15 | |||
16 | class LibXLWriter extends AbstractSendableWriter |
||
17 | { |
||
18 | /** |
||
19 | * Cache for currency formats |
||
20 | * @var ArrayObject |
||
21 | */ |
||
22 | protected $currency_formats; |
||
23 | |||
24 | /** |
||
25 | * Cache for unit formats |
||
26 | * @var ArrayObject |
||
27 | */ |
||
28 | protected $unit_formats; |
||
29 | |||
30 | /** |
||
31 | * |
||
32 | * @var SimpleHeaders |
||
33 | */ |
||
34 | protected $headers; |
||
35 | protected $column_width_multiplier = 1.7; |
||
36 | protected $column_max_width = 75; |
||
37 | |||
38 | /** |
||
39 | * |
||
40 | * @var array |
||
41 | */ |
||
42 | protected static $default_license; |
||
43 | |||
44 | /** |
||
45 | * |
||
46 | * @var ExcelBook |
||
47 | */ |
||
48 | protected $excelBook; |
||
49 | |||
50 | /** |
||
51 | * |
||
52 | * @var string |
||
53 | */ |
||
54 | protected $file_format = LibXL::FILE_FORMAT_XLSX; |
||
55 | |||
56 | /** |
||
57 | * |
||
58 | * @var array |
||
59 | */ |
||
60 | protected $currencyMap = array( |
||
61 | 'EUR' => '€', |
||
62 | 'GBP' => '£', |
||
63 | 'CNY' => 'CN¥', |
||
64 | 'USD' => '$', |
||
65 | 'CAD' => 'CA$' |
||
66 | ); |
||
67 | |||
68 | /** |
||
69 | * |
||
70 | * @var array |
||
71 | */ |
||
72 | protected $typeMap = array( |
||
73 | ColumnType::TYPE_BIT => 'number', |
||
74 | ColumnType::TYPE_BLOB => 'text', |
||
75 | ColumnType::TYPE_BOOLEAN => 'number', |
||
76 | ColumnType::TYPE_DATE => 'date', |
||
77 | ColumnType::TYPE_DATETIME => 'datetime', |
||
78 | ColumnType::TYPE_DECIMAL => 'number', |
||
79 | ColumnType::TYPE_INTEGER => 'number', |
||
80 | ColumnType::TYPE_STRING => 'text', |
||
81 | ColumnType::TYPE_TIME => 'text', |
||
82 | ); |
||
83 | |||
84 | /** |
||
85 | * |
||
86 | * @param StoreInterface|null $store |
||
87 | * @param array|\Traversable|null $options |
||
88 | */ |
||
89 | public function __construct(StoreInterface $store = null, $options = null) |
||
90 | { |
||
91 | $this->currency_formats = new ArrayObject(); |
||
92 | $this->unit_formats = new ArrayObject(); |
||
93 | parent::__construct($store, $options); |
||
94 | } |
||
95 | |||
96 | /** |
||
97 | * Set file format (xls, xlsx), default is xlsx |
||
98 | * |
||
99 | * @param string $file_format |
||
100 | * @return LibXLWriter |
||
101 | * @throws Exception\InvalidArgumentException |
||
102 | */ |
||
103 | public function setFormat($file_format) |
||
104 | { |
||
105 | if (!LibXL::isSupportedFormat($file_format)) { |
||
106 | throw new Exception\InvalidArgumentException(__METHOD__ . " Unsupported format given '$file_format'"); |
||
107 | } |
||
108 | $this->file_format = $file_format; |
||
109 | return $this; |
||
110 | } |
||
111 | |||
112 | |||
113 | /** |
||
114 | * |
||
115 | * @param ExcelBook $book |
||
116 | * @param string $currency |
||
117 | * @param int $decimals |
||
118 | * @return ExcelFormat |
||
119 | */ |
||
120 | protected function getCurrencyFormat(ExcelBook $book, $currency, $decimals) |
||
121 | { |
||
122 | $id = "$currency/$decimals"; |
||
123 | if (!$this->currency_formats->offsetExists($id)) { |
||
124 | if (array_key_exists($currency, $this->currencyMap)) { |
||
125 | $symbol = $this->currencyMap[$currency]; |
||
126 | } else { |
||
127 | $symbol = $currency; |
||
128 | } |
||
129 | |||
130 | |||
131 | $formatString = '#,##0'; |
||
132 | |||
133 | if ($decimals > 0) { |
||
134 | $zeros = str_repeat("0", $decimals); |
||
135 | $formatString = $formatString . '.' . $zeros; |
||
136 | } |
||
137 | $formatString = $formatString . ' "' . $symbol . '"_-'; |
||
138 | //$formatString = $formatString . ' [$€-80C]_-'; |
||
139 | //$format_code = '"C$"' . $format_code . '_-'; |
||
140 | |||
141 | $cfid = $book->addCustomFormat($formatString); |
||
142 | $format = $book->addFormat(); |
||
143 | $format->numberFormat($cfid); |
||
144 | $this->currency_formats->offsetSet($id, $format); |
||
145 | } |
||
146 | return $this->currency_formats->offsetGet($id); |
||
147 | } |
||
148 | |||
149 | |||
150 | |||
151 | /** |
||
152 | * |
||
153 | * @param ExcelBook $book |
||
154 | * @param string $unit |
||
155 | * @param int $decimals |
||
156 | * @return ExcelFormat |
||
157 | */ |
||
158 | protected function getUnitFormat(ExcelBook $book, $unit, $decimals) |
||
159 | { |
||
160 | $id = "$unit/$decimals"; |
||
161 | if (!$this->unit_formats->offsetExists($id)) { |
||
162 | $symbol = $unit; |
||
163 | |||
164 | $formatString = '#,##0'; |
||
165 | |||
166 | if ($decimals > 0) { |
||
167 | $zeros = str_repeat("0", $decimals); |
||
168 | $formatString = $formatString . '.' . $zeros; |
||
169 | } |
||
170 | $formatString = $formatString . ' "' . $symbol . '"_-'; |
||
171 | |||
172 | $cfid = $book->addCustomFormat($formatString); |
||
173 | $format = $book->addFormat(); |
||
174 | $format->numberFormat($cfid); |
||
175 | $this->unit_formats->offsetSet($id, $format); |
||
176 | } |
||
177 | return $this->unit_formats->offsetGet($id); |
||
178 | } |
||
179 | |||
180 | |||
181 | |||
182 | /** |
||
183 | * |
||
184 | * @throws Exception\ExtensionNotLoadedException |
||
185 | * @throws Exception\InvalidArgumentException |
||
186 | * |
||
187 | * @param string $file_format |
||
188 | * @param string $locale default to en_US.UTF-8 |
||
189 | * |
||
190 | * @return ExcelBook |
||
191 | */ |
||
192 | public function getExcelBook($file_format = null, $locale = 'en_US.UTF-8') |
||
193 | { |
||
194 | if (!extension_loaded('excel')) { |
||
195 | throw new Exception\ExtensionNotLoadedException(__METHOD__ . ' LibXLWriter requires excel (php_exccel) extension to be loaded'); |
||
196 | } |
||
197 | |||
198 | if ($this->excelBook === null) { |
||
199 | $libXL = new LibXL(); |
||
200 | if (is_array(self::$default_license)) { |
||
201 | $libXL->setLicense(self::$default_license); |
||
202 | } |
||
203 | if ($file_format === null) { |
||
204 | $file_format = $this->file_format; |
||
205 | } elseif (!LibXL::isSupportedFormat($file_format)) { |
||
206 | throw new Exception\InvalidArgumentException(__METHOD__ . " Unsupported format given '$file_format'"); |
||
207 | } |
||
208 | |||
209 | $this->excelBook = $libXL->getExcelBook($file_format, $locale); |
||
210 | } |
||
211 | return $this->excelBook; |
||
212 | } |
||
213 | |||
214 | /** |
||
215 | * @param Options $options |
||
216 | * @return string |
||
217 | */ |
||
218 | public function getData(Options $options = null) |
||
219 | { |
||
220 | if ($options === null) { |
||
221 | // Take store global/default options |
||
222 | $options = $this->store->getOptions(); |
||
223 | } |
||
224 | |||
225 | // Get unformatted data when using excel writer |
||
226 | $options->getHydrationOptions()->disableFormatters(); |
||
227 | |||
228 | // Some colu |
||
229 | $options->getHydrationOptions()->disableColumnExclusion(); |
||
230 | |||
231 | $book = $this->getExcelBook(); |
||
232 | $this->generateExcel($book, $options); |
||
233 | //$book->setLocale($locale); |
||
234 | |||
235 | $temp_dir = sys_get_temp_dir(); |
||
236 | if (!is_dir($temp_dir)) { |
||
237 | throw new \Exception(__METHOD__ . " System temporary directory '$temp_dir' does not exists."); |
||
238 | } |
||
239 | |||
240 | if (!is_writable($temp_dir)) { |
||
241 | throw new \Exception(__METHOD__ . " System temporary directory '$temp_dir' is not writable."); |
||
242 | } |
||
243 | |||
244 | $filename = tempnam($temp_dir, 'libxl'); |
||
245 | |||
246 | $book->save($filename); |
||
247 | |||
248 | $data = file_get_contents($filename); |
||
249 | unlink($filename); |
||
250 | return $data; |
||
251 | } |
||
252 | |||
253 | /** |
||
254 | * @return ArrayObject |
||
255 | */ |
||
256 | protected function getMetadataSpecs(ExcelBook $book) |
||
257 | { |
||
258 | $hide_thousands_separator = true; |
||
259 | |||
260 | |||
261 | $specs = new ArrayObject(); |
||
262 | $cm = $this->store->getColumnModel(); |
||
263 | $metadata = $cm->getMetadata(); |
||
264 | |||
265 | $columns = $cm->getColumns(); |
||
266 | foreach ($columns as $name => $column) { |
||
267 | $decimals = null; |
||
268 | $format = null; |
||
269 | $custom_column = null; |
||
270 | $formatter = $column->getFormatter(); |
||
271 | |||
272 | if ($formatter instanceof \Soluble\FlexStore\Formatter\FormatterNumberInterface) { |
||
273 | $type = 'number'; |
||
274 | $decimals = $formatter->getDecimals(); |
||
275 | if ($formatter instanceof \Soluble\FlexStore\Formatter\CurrencyFormatter) { |
||
276 | $currency = $formatter->getCurrencyCode(); |
||
277 | if ($currency instanceof \Soluble\FlexStore\Formatter\RowColumn) { |
||
278 | // TODO better handling of callbacks |
||
279 | $format = function (ExcelBook $book, $currency, $decimals) { |
||
280 | return $this->getCurrencyFormat($book, $currency, $decimals); |
||
281 | }; |
||
282 | $custom_column = $currency->getColumnName(); |
||
283 | } else { |
||
284 | $format = $this->getCurrencyFormat($book, $currency, $decimals); |
||
285 | } |
||
286 | } elseif ($formatter instanceof \Soluble\FlexStore\Formatter\UnitFormatter) { |
||
287 | $unit = $formatter->getUnit(); |
||
288 | if ($unit instanceof \Soluble\FlexStore\Formatter\RowColumn) { |
||
289 | // TODO better handling of callbacks |
||
290 | $format = function (ExcelBook $book, $unit, $decimals) { |
||
291 | return $this->getUnitFormat($book, $unit, $decimals); |
||
292 | }; |
||
293 | $custom_column = $unit->getColumnName(); |
||
294 | } else { |
||
295 | $format = $this->getUnitFormat($book, $unit, $decimals); |
||
296 | } |
||
297 | } |
||
298 | } else { |
||
299 | $model_type = $column->getType()->getName(); |
||
300 | //$spec['meta_type'] = $model_type; |
||
301 | if ($model_type == ColumnType::TYPE_INTEGER) { |
||
302 | $decimals = 0; |
||
303 | } |
||
304 | if (array_key_exists($model_type, $this->typeMap)) { |
||
305 | $type = $this->typeMap[$model_type]; |
||
306 | } else { |
||
307 | $type = "text"; |
||
308 | } |
||
309 | } |
||
310 | |||
311 | // We now have the type |
||
312 | if ($type == "number" && $decimals === null && $metadata !== null && $metadata->offsetExists($name)) { |
||
313 | // try to guess from metadata |
||
314 | $decimals = $metadata->offsetGet($name)->getNumericPrecision(); |
||
315 | if (!$decimals) { |
||
316 | $decimals = 0; |
||
317 | } |
||
318 | } |
||
319 | |||
320 | // Let's make the format |
||
321 | |||
322 | if ($format === null) { |
||
323 | switch ($type) { |
||
324 | case 'date': |
||
325 | $mask = 'd/mm/yyyy'; |
||
326 | $cfid = $book->addCustomFormat($mask); |
||
327 | $format = $book->addFormat(); |
||
328 | $format->numberFormat($cfid); |
||
329 | break; |
||
330 | case 'datetime': |
||
331 | $mask = 'd/mm/yyyy h:mm'; |
||
332 | $cfid = $book->addCustomFormat($mask); |
||
333 | $format = $book->addFormat(); |
||
334 | $format->numberFormat($cfid); |
||
335 | break; |
||
336 | case 'number': |
||
337 | if ($hide_thousands_separator) { |
||
338 | $formatString = '0'; |
||
339 | } else { |
||
340 | $formatString = '#,##0'; |
||
341 | } |
||
342 | if ($decimals > 0) { |
||
343 | $zeros = str_repeat("0", $decimals); |
||
344 | $formatString = $formatString . '.' . $zeros; |
||
345 | } |
||
346 | $cfid = $book->addCustomFormat($formatString); |
||
347 | $format = $book->addFormat(); |
||
348 | $format->numberFormat($cfid); |
||
349 | |||
350 | break; |
||
351 | default: |
||
352 | $format = null; |
||
353 | } |
||
354 | } |
||
355 | |||
356 | if ($format === null) { |
||
357 | $format = $this->getDefaultTextFormat($book); |
||
358 | } else { |
||
359 | /* Not yet supported, waiting php_excel 1.1 |
||
360 | $format->horizontalAlign($this->getFormatStyle('horizontalAlign')); |
||
361 | $format->verticalAlign($this->getFormatStyle('verticalAlign')); |
||
362 | |||
363 | */ |
||
364 | } |
||
365 | |||
366 | // Save the spec |
||
367 | $spec = new ArrayObject(); |
||
368 | $spec['name'] = $name; |
||
369 | $spec['header'] = $column->getHeader(); |
||
370 | $spec['type'] = $type; |
||
371 | $spec['decimals'] = $decimals; |
||
372 | $spec['format'] = $format; |
||
373 | $spec['custom_column'] = $custom_column; |
||
374 | $specs->offsetSet($name, $spec); |
||
375 | } |
||
376 | |||
377 | //var_dump((array) $specs); |
||
378 | return $specs; |
||
379 | } |
||
380 | |||
381 | protected function getDefaultTextFormat(ExcelBook $book) |
||
382 | { |
||
383 | $format = $book->addFormat(); |
||
384 | /* Not yet supported, waiting php_excel 1.1 |
||
385 | $format->horizontalAlign($this->getFormatStyle('horizontalFormat')); |
||
386 | $format->verticalAlign($this->getFormatStyle('verticalFormat')); |
||
387 | * |
||
388 | */ |
||
389 | return $format; |
||
390 | } |
||
391 | |||
392 | protected function getHeaderFormat(ExcelBook $book) |
||
393 | { |
||
394 | |||
395 | // Font selection |
||
396 | $headerFont = $book->addFont(); |
||
397 | $headerFont->name("Tahoma"); |
||
398 | $headerFont->size(12); |
||
399 | $headerFont->color(ExcelFormat::COLOR_WHITE); |
||
400 | |||
401 | $headerFormat = $book->addFormat(); |
||
402 | |||
403 | $headerFormat->setFont($headerFont); |
||
0 ignored issues
–
show
|
|||
404 | |||
405 | $headerFormat->borderStyle(ExcelFormat::BORDERSTYLE_THIN); |
||
406 | $headerFormat->verticalAlign(ExcelFormat::ALIGNV_CENTER); |
||
407 | $headerFormat->borderColor(ExcelFormat::COLOR_GRAY50); |
||
408 | //$headerFormat->patternBackgroundColor(ExcelFormat:COLOR_LIGHTBLUE); |
||
409 | $headerFormat->patternForegroundColor(ExcelFormat::COLOR_LIGHTBLUE); |
||
410 | $headerFormat->fillPattern(ExcelFormat::FILLPATTERN_SOLID); |
||
411 | return $headerFormat; |
||
412 | } |
||
413 | |||
414 | /** |
||
415 | * |
||
416 | * @param ExcelBook $book |
||
417 | * @param Options $options |
||
418 | * @return ExcelBook |
||
419 | */ |
||
420 | protected function generateExcel(ExcelBook $book, Options $options = null) |
||
421 | { |
||
422 | $sheet = $book->addSheet("Sheet"); |
||
423 | $headerFormat = $this->getHeaderFormat($book); |
||
424 | |||
425 | // Step 1, print header |
||
426 | $specs = $this->getMetadataSpecs($book); |
||
427 | $column_max_widths = array_fill_keys(array_keys((array) $specs), 0); |
||
428 | $col_idx = 0; |
||
429 | foreach ($specs as $name => $spec) { |
||
430 | $sheet->write($row = 0, $col_idx, $spec['header'], $headerFormat); |
||
431 | $column_max_widths[$name] = max(strlen($spec['header']) * $this->column_width_multiplier, $column_max_widths[$name]); |
||
432 | $col_idx++; |
||
433 | } |
||
434 | |||
435 | $sheet->setRowHeight(0, 30); |
||
436 | |||
437 | // Fix the header |
||
438 | $sheet->splitSheet(1, 0); |
||
439 | |||
440 | // Fill document content |
||
441 | |||
442 | |||
443 | |||
444 | $data = $this->store->getData($options); |
||
445 | |||
446 | foreach ($data as $idx => $row) { |
||
447 | $col_idx = 0; |
||
448 | $row_idx = $idx + 1; |
||
449 | $rowHeight = $sheet->rowHeight($row_idx); |
||
450 | |||
451 | foreach ($specs as $name => $spec) { |
||
452 | |||
453 | // Row may have no key in the case of customer renderers, |
||
454 | // to prevent notice let's make it to null |
||
455 | $value = isset($row[$name]) ? $row[$name] : null; |
||
456 | |||
457 | if ($spec['format'] !== null) { |
||
458 | $format = $spec['format']; |
||
459 | if (is_callable($format)) { |
||
460 | // Dynamic column format |
||
461 | $sheet->write($row_idx, $col_idx, (string) $value, $format($book, $row[$spec['custom_column']], $spec['decimals']), ExcelFormat::AS_NUMERIC_STRING); |
||
462 | } else { |
||
463 | switch ($spec['type']) { |
||
464 | case 'number': |
||
465 | $sheet->write($row_idx, $col_idx, (string) $value, $spec['format'], ExcelFormat::AS_NUMERIC_STRING); |
||
466 | break; |
||
467 | case 'date': |
||
468 | case 'datetime': |
||
469 | if ($value != '') { |
||
470 | $time = strtotime($value); |
||
471 | } else { |
||
472 | $time = null; |
||
473 | } |
||
474 | $sheet->write($row_idx, $col_idx, $time, $spec['format'], ExcelFormat::AS_DATE); |
||
475 | break; |
||
476 | default: |
||
477 | if (preg_match('/(\n)/', $value)) { |
||
478 | // More height when one cell contains multiple lines |
||
479 | $sheet->setRowHeight($row_idx, ceil($rowHeight * 1.9)); |
||
480 | } |
||
481 | |||
482 | $sheet->write($row_idx, $col_idx, $value, $format); |
||
483 | } |
||
484 | } |
||
485 | } else { |
||
486 | $sheet->write($row_idx, $col_idx, $value); |
||
487 | } |
||
488 | $column_max_widths[$name] = max(strlen((string) $value) * $this->column_width_multiplier, $column_max_widths[$name]); |
||
489 | $col_idx++; |
||
490 | } |
||
491 | } |
||
492 | |||
493 | foreach (array_values($column_max_widths) as $idx => $width) { |
||
494 | $sheet->setColWidth($idx, ceil($idx), min($width, $this->column_max_width)); |
||
495 | } |
||
496 | |||
497 | $sheet->setPrintGridlines(true); |
||
498 | //$sheet->setPrintRepeatRows(1, 2); |
||
499 | //$sheet->setPrintHeaders(true); |
||
500 | //$sheet->setVerPageBreak($col_idx, true); |
||
501 | |||
502 | return $book; |
||
503 | } |
||
504 | |||
505 | |||
506 | /** |
||
507 | * |
||
508 | * @param string $license_name |
||
509 | * @param string $license_key |
||
510 | */ |
||
511 | public static function setDefaultLicense($license_name, $license_key) |
||
512 | { |
||
513 | self::$default_license = array('name' => $license_name, 'key' => $license_key); |
||
514 | } |
||
515 | |||
516 | |||
517 | public function getFormatStyle($style) |
||
518 | { |
||
519 | $styles = $this->getFormatStyles(); |
||
520 | return $styles[$style]; |
||
521 | } |
||
522 | |||
523 | public function getFormatStyles() |
||
524 | { |
||
525 | $styles = array( |
||
526 | 'horizontalAlign' => ExcelFormat::ALIGNH_LEFT, |
||
527 | 'verticalAlign' => ExcelFormat::ALIGNV_TOP |
||
528 | ); |
||
529 | return $styles; |
||
530 | } |
||
531 | |||
532 | /** |
||
533 | * Return default headers for sending store data via http |
||
534 | * @return SimpleHeaders |
||
535 | */ |
||
536 | public function getHttpHeaders() |
||
537 | { |
||
538 | if ($this->headers === null) { |
||
539 | $this->headers = new SimpleHeaders(); |
||
540 | $this->headers->setContentType('application/excel', 'utf-8'); |
||
541 | $this->headers->setContentDispositionType(SimpleHeaders::DIPOSITION_ATTACHEMENT); |
||
542 | } |
||
543 | return $this->headers; |
||
544 | } |
||
545 | } |
||
546 |
Unless you are absolutely sure that the expression can never be null because of other conditions, we strongly recommend to add an additional type check to your code: