ILIAS  release_5-4 Revision v5.4.26-12-gabc799a52e6
Html.php
Go to the documentation of this file.
1<?php
2
4
27
28class Html extends BaseWriter
29{
35 protected $spreadsheet;
36
42 private $sheetIndex = 0;
43
49 private $imagesRoot = '';
50
56 private $embedImages = false;
57
63 private $useInlineCss = false;
64
70 private $useEmbeddedCSS = true;
71
77 private $cssStyles;
78
85
91 private $defaultFont;
92
98 private $spansAreCalculated = false;
99
105 private $isSpannedCell = [];
106
112 private $isBaseCell = [];
113
119 private $isSpannedRow = [];
120
126 protected $isPdf = false;
127
134
141
146 {
147 $this->spreadsheet = $spreadsheet;
148 $this->defaultFont = $this->spreadsheet->getDefaultStyle()->getFont();
149 }
150
156 public function save($pFilename): void
157 {
158 // Open file
159 $this->openFileHandle($pFilename);
160
161 // Write html
162 fwrite($this->fileHandle, $this->generateHTMLAll());
163
164 // Close file
165 $this->maybeCloseFileHandle();
166 }
167
173 public function generateHtmlAll()
174 {
175 // garbage collect
176 $this->spreadsheet->garbageCollect();
177
178 $saveDebugLog = Calculation::getInstance($this->spreadsheet)->getDebugLog()->getWriteDebugLog();
179 Calculation::getInstance($this->spreadsheet)->getDebugLog()->setWriteDebugLog(false);
180 $saveArrayReturnType = Calculation::getArrayReturnType();
182
183 // Build CSS
184 $this->buildCSS(!$this->useInlineCss);
185
186 $html = '';
187
188 // Write headers
189 $html .= $this->generateHTMLHeader(!$this->useInlineCss);
190
191 // Write navigation (tabs)
192 if ((!$this->isPdf) && ($this->generateSheetNavigationBlock)) {
193 $html .= $this->generateNavigation();
194 }
195
196 // Write data
197 $html .= $this->generateSheetData();
198
199 // Write footer
200 $html .= $this->generateHTMLFooter();
201 $callback = $this->editHtmlCallback;
202 if ($callback) {
203 $html = $callback($html);
204 }
205
206 Calculation::setArrayReturnType($saveArrayReturnType);
207 Calculation::getInstance($this->spreadsheet)->getDebugLog()->setWriteDebugLog($saveDebugLog);
208
209 return $html;
210 }
211
218 public function setEditHtmlCallback(?callable $callback): void
219 {
220 $this->editHtmlCallback = $callback;
221 }
222
223 const VALIGN_ARR = [
224 Alignment::VERTICAL_BOTTOM => 'bottom',
226 Alignment::VERTICAL_CENTER => 'middle',
227 Alignment::VERTICAL_JUSTIFY => 'middle',
228 ];
229
237 private function mapVAlign($vAlign)
238 {
239 return array_key_exists($vAlign, self::VALIGN_ARR) ? self::VALIGN_ARR[$vAlign] : 'baseline';
240 }
241
242 const HALIGN_ARR = [
248 ];
249
257 private function mapHAlign($hAlign)
258 {
259 return array_key_exists($hAlign, self::HALIGN_ARR) ? self::HALIGN_ARR[$hAlign] : '';
260 }
261
262 const BORDER_ARR = [
263 Border::BORDER_NONE => 'none',
264 Border::BORDER_DASHDOT => '1px dashed',
265 Border::BORDER_DASHDOTDOT => '1px dotted',
266 Border::BORDER_DASHED => '1px dashed',
267 Border::BORDER_DOTTED => '1px dotted',
268 Border::BORDER_DOUBLE => '3px double',
269 Border::BORDER_HAIR => '1px solid',
270 Border::BORDER_MEDIUM => '2px solid',
271 Border::BORDER_MEDIUMDASHDOT => '2px dashed',
272 Border::BORDER_MEDIUMDASHDOTDOT => '2px dotted',
273 Border::BORDER_SLANTDASHDOT => '2px dashed',
274 Border::BORDER_THICK => '3px solid',
275 ];
276
284 private function mapBorderStyle($borderStyle)
285 {
286 return array_key_exists($borderStyle, self::BORDER_ARR) ? self::BORDER_ARR[$borderStyle] : '1px solid';
287 }
288
294 public function getSheetIndex()
295 {
296 return $this->sheetIndex;
297 }
298
306 public function setSheetIndex($pValue)
307 {
308 $this->sheetIndex = $pValue;
309
310 return $this;
311 }
312
319 {
321 }
322
330 public function setGenerateSheetNavigationBlock($pValue)
331 {
332 $this->generateSheetNavigationBlock = (bool) $pValue;
333
334 return $this;
335 }
336
342 public function writeAllSheets()
343 {
344 $this->sheetIndex = null;
345
346 return $this;
347 }
348
349 private static function generateMeta($val, $desc)
350 {
351 return $val ? (' <meta name="' . $desc . '" content="' . htmlspecialchars($val) . '" />' . PHP_EOL) : '';
352 }
353
361 public function generateHTMLHeader($pIncludeStyles = false)
362 {
363 // Construct HTML
364 $properties = $this->spreadsheet->getProperties();
365 $html = '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">' . PHP_EOL;
366 $html .= '<html xmlns="http://www.w3.org/1999/xhtml">' . PHP_EOL;
367 $html .= ' <head>' . PHP_EOL;
368 $html .= ' <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />' . PHP_EOL;
369 $html .= ' <meta name="generator" content="PhpSpreadsheet, https://github.com/PHPOffice/PhpSpreadsheet" />' . PHP_EOL;
370 $html .= ' <title>' . htmlspecialchars($properties->getTitle()) . '</title>' . PHP_EOL;
371 $html .= self::generateMeta($properties->getCreator(), 'author');
372 $html .= self::generateMeta($properties->getTitle(), 'title');
373 $html .= self::generateMeta($properties->getDescription(), 'description');
374 $html .= self::generateMeta($properties->getSubject(), 'subject');
375 $html .= self::generateMeta($properties->getKeywords(), 'keywords');
376 $html .= self::generateMeta($properties->getCategory(), 'category');
377 $html .= self::generateMeta($properties->getCompany(), 'company');
378 $html .= self::generateMeta($properties->getManager(), 'manager');
379
380 $html .= $pIncludeStyles ? $this->generateStyles(true) : $this->generatePageDeclarations(true);
381
382 $html .= ' </head>' . PHP_EOL;
383 $html .= '' . PHP_EOL;
384 $html .= ' <body>' . PHP_EOL;
385
386 return $html;
387 }
388
389 private function generateSheetPrep()
390 {
391 // Ensure that Spans have been calculated?
392 $this->calculateSpans();
393
394 // Fetch sheets
395 if ($this->sheetIndex === null) {
396 $sheets = $this->spreadsheet->getAllSheets();
397 } else {
398 $sheets = [$this->spreadsheet->getSheet($this->sheetIndex)];
399 }
400
401 return $sheets;
402 }
403
404 private function generateSheetStarts($sheet, $rowMin)
405 {
406 // calculate start of <tbody>, <thead>
407 $tbodyStart = $rowMin;
408 $theadStart = $theadEnd = 0; // default: no <thead> no </thead>
409 if ($sheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
410 $rowsToRepeatAtTop = $sheet->getPageSetup()->getRowsToRepeatAtTop();
411
412 // we can only support repeating rows that start at top row
413 if ($rowsToRepeatAtTop[0] == 1) {
414 $theadStart = $rowsToRepeatAtTop[0];
415 $theadEnd = $rowsToRepeatAtTop[1];
416 $tbodyStart = $rowsToRepeatAtTop[1] + 1;
417 }
418 }
419
420 return [$theadStart, $theadEnd, $tbodyStart];
421 }
422
423 private function generateSheetTags($row, $theadStart, $theadEnd, $tbodyStart)
424 {
425 // <thead> ?
426 $startTag = ($row == $theadStart) ? (' <thead>' . PHP_EOL) : '';
427 if (!$startTag) {
428 $startTag = ($row == $tbodyStart) ? (' <tbody>' . PHP_EOL) : '';
429 }
430 $endTag = ($row == $theadEnd) ? (' </thead>' . PHP_EOL) : '';
431 $cellType = ($row >= $tbodyStart) ? 'td' : 'th';
432
433 return [$cellType, $startTag, $endTag];
434 }
435
441 public function generateSheetData()
442 {
443 $sheets = $this->generateSheetPrep();
444
445 // Construct HTML
446 $html = '';
447
448 // Loop all sheets
449 $sheetId = 0;
450 foreach ($sheets as $sheet) {
451 // Write table header
452 $html .= $this->generateTableHeader($sheet);
453
454 // Get worksheet dimension
455 [$min, $max] = explode(':', $sheet->calculateWorksheetDataDimension());
456 [$minCol, $minRow] = Coordinate::indexesFromString($min);
457 [$maxCol, $maxRow] = Coordinate::indexesFromString($max);
458
459 [$theadStart, $theadEnd, $tbodyStart] = $this->generateSheetStarts($sheet, $minRow);
460
461 // Loop through cells
462 $row = $minRow - 1;
463 while ($row++ < $maxRow) {
464 [$cellType, $startTag, $endTag] = $this->generateSheetTags($row, $theadStart, $theadEnd, $tbodyStart);
465 $html .= $startTag;
466
467 // Write row if there are HTML table cells in it
468 if (!isset($this->isSpannedRow[$sheet->getParent()->getIndex($sheet)][$row])) {
469 // Start a new rowData
470 $rowData = [];
471 // Loop through columns
472 $column = $minCol;
473 while ($column <= $maxCol) {
474 // Cell exists?
475 if ($sheet->cellExistsByColumnAndRow($column, $row)) {
476 $rowData[$column] = Coordinate::stringFromColumnIndex($column) . $row;
477 } else {
478 $rowData[$column] = '';
479 }
480 ++$column;
481 }
482 $html .= $this->generateRow($sheet, $rowData, $row - 1, $cellType);
483 }
484
485 $html .= $endTag;
486 }
487 $html .= $this->extendRowsForChartsAndImages($sheet, $row);
488
489 // Write table footer
490 $html .= $this->generateTableFooter();
491 // Writing PDF?
492 if ($this->isPdf && $this->useInlineCss) {
493 if ($this->sheetIndex === null && $sheetId + 1 < $this->spreadsheet->getSheetCount()) {
494 $html .= '<div style="page-break-before:always" ></div>';
495 }
496 }
497
498 // Next sheet
499 ++$sheetId;
500 }
501
502 return $html;
503 }
504
510 public function generateNavigation()
511 {
512 // Fetch sheets
513 $sheets = [];
514 if ($this->sheetIndex === null) {
515 $sheets = $this->spreadsheet->getAllSheets();
516 } else {
517 $sheets[] = $this->spreadsheet->getSheet($this->sheetIndex);
518 }
519
520 // Construct HTML
521 $html = '';
522
523 // Only if there are more than 1 sheets
524 if (count($sheets) > 1) {
525 // Loop all sheets
526 $sheetId = 0;
527
528 $html .= '<ul class="navigation">' . PHP_EOL;
529
530 foreach ($sheets as $sheet) {
531 $html .= ' <li class="sheet' . $sheetId . '"><a href="#sheet' . $sheetId . '">' . $sheet->getTitle() . '</a></li>' . PHP_EOL;
532 ++$sheetId;
533 }
534
535 $html .= '</ul>' . PHP_EOL;
536 }
537
538 return $html;
539 }
540
556 private function extendRowsForCharts(Worksheet $pSheet, int $row)
557 {
558 $rowMax = $row;
559 $colMax = 'A';
560 $anyfound = false;
561 if ($this->includeCharts) {
562 foreach ($pSheet->getChartCollection() as $chart) {
563 if ($chart instanceof Chart) {
564 $anyfound = true;
565 $chartCoordinates = $chart->getTopLeftPosition();
566 $chartTL = Coordinate::coordinateFromString($chartCoordinates['cell']);
567 $chartCol = Coordinate::columnIndexFromString($chartTL[0]);
568 if ($chartTL[1] > $rowMax) {
569 $rowMax = $chartTL[1];
570 if ($chartCol > Coordinate::columnIndexFromString($colMax)) {
571 $colMax = $chartTL[0];
572 }
573 }
574 }
575 }
576 }
577
578 return [$rowMax, $colMax, $anyfound];
579 }
580
581 private function extendRowsForChartsAndImages(Worksheet $pSheet, int $row): string
582 {
583 [$rowMax, $colMax, $anyfound] = $this->extendRowsForCharts($pSheet, $row);
584
585 foreach ($pSheet->getDrawingCollection() as $drawing) {
586 $anyfound = true;
587 $imageTL = Coordinate::coordinateFromString($drawing->getCoordinates());
588 $imageCol = Coordinate::columnIndexFromString($imageTL[0]);
589 if ($imageTL[1] > $rowMax) {
590 $rowMax = $imageTL[1];
591 if ($imageCol > Coordinate::columnIndexFromString($colMax)) {
592 $colMax = $imageTL[0];
593 }
594 }
595 }
596
597 // Don't extend rows if not needed
598 if ($row === $rowMax || !$anyfound) {
599 return '';
600 }
601
602 $html = '';
603 ++$colMax;
604 ++$row;
605 while ($row <= $rowMax) {
606 $html .= '<tr>';
607 for ($col = 'A'; $col != $colMax; ++$col) {
608 $htmlx = $this->writeImageInCell($pSheet, $col . $row);
609 $htmlx .= $this->includeCharts ? $this->writeChartInCell($pSheet, $col . $row) : '';
610 if ($htmlx) {
611 $html .= "<td class='style0' style='position: relative;'>$htmlx</td>";
612 } else {
613 $html .= "<td class='style0'></td>";
614 }
615 }
616 ++$row;
617 $html .= '</tr>' . PHP_EOL;
618 }
619
620 return $html;
621 }
622
630 public static function winFileToUrl($filename)
631 {
632 // Windows filename
633 if (substr($filename, 1, 2) === ':\\') {
634 $filename = 'file:///' . str_replace('\\', '/', $filename);
635 }
636
637 return $filename;
638 }
639
648 private function writeImageInCell(Worksheet $pSheet, $coordinates)
649 {
650 // Construct HTML
651 $html = '';
652
653 // Write images
654 foreach ($pSheet->getDrawingCollection() as $drawing) {
655 if ($drawing->getCoordinates() != $coordinates) {
656 continue;
657 }
658 $filedesc = $drawing->getDescription();
659 $filedesc = $filedesc ? htmlspecialchars($filedesc, ENT_QUOTES) : 'Embedded image';
660 if ($drawing instanceof Drawing) {
661 $filename = $drawing->getPath();
662
663 // Strip off eventual '.'
664 $filename = preg_replace('/^[.]/', '', $filename);
665
666 // Prepend images root
667 $filename = $this->getImagesRoot() . $filename;
668
669 // Strip off eventual '.' if followed by non-/
670 $filename = preg_replace('@^[.]([^/])@', '$1', $filename);
671
672 // Convert UTF8 data to PCDATA
673 $filename = htmlspecialchars($filename);
674
675 $html .= PHP_EOL;
676 $imageData = self::winFileToUrl($filename);
677
678 if ($this->embedImages && !$this->isPdf) {
679 $picture = @file_get_contents($filename);
680 if ($picture !== false) {
681 $imageDetails = getimagesize($filename);
682 // base64 encode the binary data
683 $base64 = base64_encode($picture);
684 $imageData = 'data:' . $imageDetails['mime'] . ';base64,' . $base64;
685 }
686 }
687
688 $html .= '<img style="position: absolute; z-index: 1; left: ' .
689 $drawing->getOffsetX() . 'px; top: ' . $drawing->getOffsetY() . 'px; width: ' .
690 $drawing->getWidth() . 'px; height: ' . $drawing->getHeight() . 'px;" src="' .
691 $imageData . '" alt="' . $filedesc . '" />';
692 } elseif ($drawing instanceof MemoryDrawing) {
693 $imageResource = $drawing->getImageResource();
694 if ($imageResource) {
695 ob_start(); // Let's start output buffering.
696 imagepng($imageResource); // This will normally output the image, but because of ob_start(), it won't.
697 $contents = ob_get_contents(); // Instead, output above is saved to $contents
698 ob_end_clean(); // End the output buffer.
699
700 $dataUri = 'data:image/jpeg;base64,' . base64_encode($contents);
701
702 // Because of the nature of tables, width is more important than height.
703 // max-width: 100% ensures that image doesnt overflow containing cell
704 // width: X sets width of supplied image.
705 // As a result, images bigger than cell will be contained and images smaller will not get stretched
706 $html .= '<img alt="' . $filedesc . '" src="' . $dataUri . '" style="max-width:100%;width:' . $drawing->getWidth() . 'px;" />';
707 }
708 }
709 }
710
711 return $html;
712 }
713
729 private function writeChartInCell(Worksheet $pSheet, $coordinates)
730 {
731 // Construct HTML
732 $html = '';
733
734 // Write charts
735 foreach ($pSheet->getChartCollection() as $chart) {
736 if ($chart instanceof Chart) {
737 $chartCoordinates = $chart->getTopLeftPosition();
738 if ($chartCoordinates['cell'] == $coordinates) {
739 $chartFileName = File::sysGetTempDir() . '/' . uniqid('', true) . '.png';
740 if (!$chart->render($chartFileName)) {
741 return '';
742 }
743
744 $html .= PHP_EOL;
745 $imageDetails = getimagesize($chartFileName);
746 $filedesc = $chart->getTitle();
747 $filedesc = $filedesc ? self::getChartCaption($filedesc->getCaption()) : '';
748 $filedesc = $filedesc ? htmlspecialchars($filedesc, ENT_QUOTES) : 'Embedded chart';
749 if ($fp = fopen($chartFileName, 'rb', 0)) {
750 $picture = fread($fp, filesize($chartFileName));
751 fclose($fp);
752 // base64 encode the binary data
753 $base64 = base64_encode($picture);
754 $imageData = 'data:' . $imageDetails['mime'] . ';base64,' . $base64;
755
756 $html .= '<img style="position: absolute; z-index: 1; left: ' . $chartCoordinates['xOffset'] . 'px; top: ' . $chartCoordinates['yOffset'] . 'px; width: ' . $imageDetails[0] . 'px; height: ' . $imageDetails[1] . 'px;" src="' . $imageData . '" alt="' . $filedesc . '" />' . PHP_EOL;
757
758 unlink($chartFileName);
759 }
760 }
761 }
762 }
763
764 // Return
765 return $html;
766 }
767
784 private static function getChartCaption($cap)
785 {
786 return is_array($cap) ? implode(' ', $cap) : $cap;
787 }
788
796 public function generateStyles($generateSurroundingHTML = true)
797 {
798 // Build CSS
799 $css = $this->buildCSS($generateSurroundingHTML);
800
801 // Construct HTML
802 $html = '';
803
804 // Start styles
805 if ($generateSurroundingHTML) {
806 $html .= ' <style type="text/css">' . PHP_EOL;
807 $html .= (array_key_exists('html', $css)) ? (' html { ' . $this->assembleCSS($css['html']) . ' }' . PHP_EOL) : '';
808 }
809
810 // Write all other styles
811 foreach ($css as $styleName => $styleDefinition) {
812 if ($styleName != 'html') {
813 $html .= ' ' . $styleName . ' { ' . $this->assembleCSS($styleDefinition) . ' }' . PHP_EOL;
814 }
815 }
816 $html .= $this->generatePageDeclarations(false);
817
818 // End styles
819 if ($generateSurroundingHTML) {
820 $html .= ' </style>' . PHP_EOL;
821 }
822
823 // Return
824 return $html;
825 }
826
827 private function buildCssRowHeights(Worksheet $sheet, array &$css, int $sheetIndex): void
828 {
829 // Calculate row heights
830 foreach ($sheet->getRowDimensions() as $rowDimension) {
831 $row = $rowDimension->getRowIndex() - 1;
832
833 // table.sheetN tr.rowYYYYYY { }
834 $css['table.sheet' . $sheetIndex . ' tr.row' . $row] = [];
835
836 if ($rowDimension->getRowHeight() != -1) {
837 $pt_height = $rowDimension->getRowHeight();
838 $css['table.sheet' . $sheetIndex . ' tr.row' . $row]['height'] = $pt_height . 'pt';
839 }
840 if ($rowDimension->getVisible() === false) {
841 $css['table.sheet' . $sheetIndex . ' tr.row' . $row]['display'] = 'none';
842 $css['table.sheet' . $sheetIndex . ' tr.row' . $row]['visibility'] = 'hidden';
843 }
844 }
845 }
846
847 private function buildCssPerSheet(Worksheet $sheet, array &$css): void
848 {
849 // Calculate hash code
850 $sheetIndex = $sheet->getParent()->getIndex($sheet);
851
852 // Build styles
853 // Calculate column widths
854 $sheet->calculateColumnWidths();
855
856 // col elements, initialize
857 $highestColumnIndex = Coordinate::columnIndexFromString($sheet->getHighestColumn()) - 1;
858 $column = -1;
859 while ($column++ < $highestColumnIndex) {
860 $this->columnWidths[$sheetIndex][$column] = 42; // approximation
861 $css['table.sheet' . $sheetIndex . ' col.col' . $column]['width'] = '42pt';
862 }
863
864 // col elements, loop through columnDimensions and set width
865 foreach ($sheet->getColumnDimensions() as $columnDimension) {
866 $column = Coordinate::columnIndexFromString($columnDimension->getColumnIndex()) - 1;
867 $width = SharedDrawing::cellDimensionToPixels($columnDimension->getWidth(), $this->defaultFont);
868 $width = SharedDrawing::pixelsToPoints($width);
869 if ($columnDimension->getVisible() === false) {
870 $css['table.sheet' . $sheetIndex . ' .column' . $column]['display'] = 'none';
871 }
872 if ($width >= 0) {
873 $this->columnWidths[$sheetIndex][$column] = $width;
874 $css['table.sheet' . $sheetIndex . ' col.col' . $column]['width'] = $width . 'pt';
875 }
876 }
877
878 // Default row height
879 $rowDimension = $sheet->getDefaultRowDimension();
880
881 // table.sheetN tr { }
882 $css['table.sheet' . $sheetIndex . ' tr'] = [];
883
884 if ($rowDimension->getRowHeight() == -1) {
885 $pt_height = SharedFont::getDefaultRowHeightByFont($this->spreadsheet->getDefaultStyle()->getFont());
886 } else {
887 $pt_height = $rowDimension->getRowHeight();
888 }
889 $css['table.sheet' . $sheetIndex . ' tr']['height'] = $pt_height . 'pt';
890 if ($rowDimension->getVisible() === false) {
891 $css['table.sheet' . $sheetIndex . ' tr']['display'] = 'none';
892 $css['table.sheet' . $sheetIndex . ' tr']['visibility'] = 'hidden';
893 }
894
895 $this->buildCssRowHeights($sheet, $css, $sheetIndex);
896 }
897
905 public function buildCSS($generateSurroundingHTML = true)
906 {
907 // Cached?
908 if ($this->cssStyles !== null) {
909 return $this->cssStyles;
910 }
911
912 // Ensure that spans have been calculated
913 $this->calculateSpans();
914
915 // Construct CSS
916 $css = [];
917
918 // Start styles
919 if ($generateSurroundingHTML) {
920 // html { }
921 $css['html']['font-family'] = 'Calibri, Arial, Helvetica, sans-serif';
922 $css['html']['font-size'] = '11pt';
923 $css['html']['background-color'] = 'white';
924 }
925
926 // CSS for comments as found in LibreOffice
927 $css['a.comment-indicator:hover + div.comment'] = [
928 'background' => '#ffd',
929 'position' => 'absolute',
930 'display' => 'block',
931 'border' => '1px solid black',
932 'padding' => '0.5em',
933 ];
934
935 $css['a.comment-indicator'] = [
936 'background' => 'red',
937 'display' => 'inline-block',
938 'border' => '1px solid black',
939 'width' => '0.5em',
940 'height' => '0.5em',
941 ];
942
943 $css['div.comment']['display'] = 'none';
944
945 // table { }
946 $css['table']['border-collapse'] = 'collapse';
947
948 // .b {}
949 $css['.b']['text-align'] = 'center'; // BOOL
950
951 // .e {}
952 $css['.e']['text-align'] = 'center'; // ERROR
953
954 // .f {}
955 $css['.f']['text-align'] = 'right'; // FORMULA
956
957 // .inlineStr {}
958 $css['.inlineStr']['text-align'] = 'left'; // INLINE
959
960 // .n {}
961 $css['.n']['text-align'] = 'right'; // NUMERIC
962
963 // .s {}
964 $css['.s']['text-align'] = 'left'; // STRING
965
966 // Calculate cell style hashes
967 foreach ($this->spreadsheet->getCellXfCollection() as $index => $style) {
968 $css['td.style' . $index] = $this->createCSSStyle($style);
969 $css['th.style' . $index] = $this->createCSSStyle($style);
970 }
971
972 // Fetch sheets
973 $sheets = [];
974 if ($this->sheetIndex === null) {
975 $sheets = $this->spreadsheet->getAllSheets();
976 } else {
977 $sheets[] = $this->spreadsheet->getSheet($this->sheetIndex);
978 }
979
980 // Build styles per sheet
981 foreach ($sheets as $sheet) {
982 $this->buildCssPerSheet($sheet, $css);
983 }
984
985 // Cache
986 if ($this->cssStyles === null) {
987 $this->cssStyles = $css;
988 }
989
990 // Return
991 return $css;
992 }
993
999 private function createCSSStyle(Style $pStyle)
1000 {
1001 // Create CSS
1002 return array_merge(
1003 $this->createCSSStyleAlignment($pStyle->getAlignment()),
1004 $this->createCSSStyleBorders($pStyle->getBorders()),
1005 $this->createCSSStyleFont($pStyle->getFont()),
1006 $this->createCSSStyleFill($pStyle->getFill())
1007 );
1008 }
1009
1017 private function createCSSStyleAlignment(Alignment $pStyle)
1018 {
1019 // Construct CSS
1020 $css = [];
1021
1022 // Create CSS
1023 $css['vertical-align'] = $this->mapVAlign($pStyle->getVertical());
1024 $textAlign = $this->mapHAlign($pStyle->getHorizontal());
1025 if ($textAlign) {
1026 $css['text-align'] = $textAlign;
1027 if (in_array($textAlign, ['left', 'right'])) {
1028 $css['padding-' . $textAlign] = (string) ((int) $pStyle->getIndent() * 9) . 'px';
1029 }
1030 }
1031
1032 return $css;
1033 }
1034
1040 private function createCSSStyleFont(Font $pStyle)
1041 {
1042 // Construct CSS
1043 $css = [];
1044
1045 // Create CSS
1046 if ($pStyle->getBold()) {
1047 $css['font-weight'] = 'bold';
1048 }
1049 if ($pStyle->getUnderline() != Font::UNDERLINE_NONE && $pStyle->getStrikethrough()) {
1050 $css['text-decoration'] = 'underline line-through';
1051 } elseif ($pStyle->getUnderline() != Font::UNDERLINE_NONE) {
1052 $css['text-decoration'] = 'underline';
1053 } elseif ($pStyle->getStrikethrough()) {
1054 $css['text-decoration'] = 'line-through';
1055 }
1056 if ($pStyle->getItalic()) {
1057 $css['font-style'] = 'italic';
1058 }
1059
1060 $css['color'] = '#' . $pStyle->getColor()->getRGB();
1061 $css['font-family'] = '\'' . $pStyle->getName() . '\'';
1062 $css['font-size'] = $pStyle->getSize() . 'pt';
1063
1064 return $css;
1065 }
1066
1074 private function createCSSStyleBorders(Borders $pStyle)
1075 {
1076 // Construct CSS
1077 $css = [];
1078
1079 // Create CSS
1080 $css['border-bottom'] = $this->createCSSStyleBorder($pStyle->getBottom());
1081 $css['border-top'] = $this->createCSSStyleBorder($pStyle->getTop());
1082 $css['border-left'] = $this->createCSSStyleBorder($pStyle->getLeft());
1083 $css['border-right'] = $this->createCSSStyleBorder($pStyle->getRight());
1084
1085 return $css;
1086 }
1087
1095 private function createCSSStyleBorder(Border $pStyle)
1096 {
1097 // Create CSS - add !important to non-none border styles for merged cells
1098 $borderStyle = $this->mapBorderStyle($pStyle->getBorderStyle());
1099
1100 return $borderStyle . ' #' . $pStyle->getColor()->getRGB() . (($borderStyle == 'none') ? '' : ' !important');
1101 }
1102
1110 private function createCSSStyleFill(Fill $pStyle)
1111 {
1112 // Construct HTML
1113 $css = [];
1114
1115 // Create CSS
1116 $value = $pStyle->getFillType() == Fill::FILL_NONE ?
1117 'white' : '#' . $pStyle->getStartColor()->getRGB();
1118 $css['background-color'] = $value;
1119
1120 return $css;
1121 }
1122
1126 public function generateHTMLFooter()
1127 {
1128 // Construct HTML
1129 $html = '';
1130 $html .= ' </body>' . PHP_EOL;
1131 $html .= '</html>' . PHP_EOL;
1132
1133 return $html;
1134 }
1135
1136 private function generateTableTagInline($pSheet, $id)
1137 {
1138 $style = isset($this->cssStyles['table']) ?
1139 $this->assembleCSS($this->cssStyles['table']) : '';
1140
1141 $prntgrid = $pSheet->getPrintGridlines();
1142 $viewgrid = $this->isPdf ? $prntgrid : $pSheet->getShowGridlines();
1143 if ($viewgrid && $prntgrid) {
1144 $html = " <table border='1' cellpadding='1' $id cellspacing='1' style='$style' class='gridlines gridlinesp'>" . PHP_EOL;
1145 } elseif ($viewgrid) {
1146 $html = " <table border='0' cellpadding='0' $id cellspacing='0' style='$style' class='gridlines'>" . PHP_EOL;
1147 } elseif ($prntgrid) {
1148 $html = " <table border='0' cellpadding='0' $id cellspacing='0' style='$style' class='gridlinesp'>" . PHP_EOL;
1149 } else {
1150 $html = " <table border='0' cellpadding='1' $id cellspacing='0' style='$style'>" . PHP_EOL;
1151 }
1152
1153 return $html;
1154 }
1155
1156 private function generateTableTag($pSheet, $id, &$html, $sheetIndex): void
1157 {
1158 if (!$this->useInlineCss) {
1159 $gridlines = $pSheet->getShowGridlines() ? ' gridlines' : '';
1160 $gridlinesp = $pSheet->getPrintGridlines() ? ' gridlinesp' : '';
1161 $html .= " <table border='0' cellpadding='0' cellspacing='0' $id class='sheet$sheetIndex$gridlines$gridlinesp'>" . PHP_EOL;
1162 } else {
1163 $html .= $this->generateTableTagInline($pSheet, $id);
1164 }
1165 }
1166
1175 private function generateTableHeader($pSheet, $showid = true)
1176 {
1177 $sheetIndex = $pSheet->getParent()->getIndex($pSheet);
1178
1179 // Construct HTML
1180 $html = '';
1181 $id = $showid ? "id='sheet$sheetIndex'" : '';
1182 if ($showid) {
1183 $html .= "<div style='page: page$sheetIndex'>\n";
1184 } else {
1185 $html .= "<div style='page: page$sheetIndex' class='scrpgbrk'>\n";
1186 }
1187
1188 $this->generateTableTag($pSheet, $id, $html, $sheetIndex);
1189
1190 // Write <col> elements
1191 $highestColumnIndex = Coordinate::columnIndexFromString($pSheet->getHighestColumn()) - 1;
1192 $i = -1;
1193 while ($i++ < $highestColumnIndex) {
1194 if (!$this->useInlineCss) {
1195 $html .= ' <col class="col' . $i . '" />' . PHP_EOL;
1196 } else {
1197 $style = isset($this->cssStyles['table.sheet' . $sheetIndex . ' col.col' . $i]) ?
1198 $this->assembleCSS($this->cssStyles['table.sheet' . $sheetIndex . ' col.col' . $i]) : '';
1199 $html .= ' <col style="' . $style . '" />' . PHP_EOL;
1200 }
1201 }
1202
1203 return $html;
1204 }
1205
1209 private function generateTableFooter()
1210 {
1211 return ' </tbody></table>' . PHP_EOL . '</div>' . PHP_EOL;
1212 }
1213
1223 private function generateRowStart(Worksheet $pSheet, $sheetIndex, $pRow)
1224 {
1225 $html = '';
1226 if (count($pSheet->getBreaks()) > 0) {
1227 $breaks = $pSheet->getBreaks();
1228
1229 // check if a break is needed before this row
1230 if (isset($breaks['A' . $pRow])) {
1231 // close table: </table>
1232 $html .= $this->generateTableFooter();
1233 if ($this->isPdf && $this->useInlineCss) {
1234 $html .= '<div style="page-break-before:always" />';
1235 }
1236
1237 // open table again: <table> + <col> etc.
1238 $html .= $this->generateTableHeader($pSheet, false);
1239 $html .= '<tbody>' . PHP_EOL;
1240 }
1241 }
1242
1243 // Write row start
1244 if (!$this->useInlineCss) {
1245 $html .= ' <tr class="row' . $pRow . '">' . PHP_EOL;
1246 } else {
1247 $style = isset($this->cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow])
1248 ? $this->assembleCSS($this->cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]) : '';
1249
1250 $html .= ' <tr style="' . $style . '">' . PHP_EOL;
1251 }
1252
1253 return $html;
1254 }
1255
1256 private function generateRowCellCss($pSheet, $cellAddress, $pRow, $colNum)
1257 {
1258 $cell = ($cellAddress > '') ? $pSheet->getCell($cellAddress) : '';
1259 $coordinate = Coordinate::stringFromColumnIndex($colNum + 1) . ($pRow + 1);
1260 if (!$this->useInlineCss) {
1261 $cssClass = 'column' . $colNum;
1262 } else {
1263 $cssClass = [];
1264 // The statements below do nothing.
1265 // Commenting out the code rather than deleting it
1266 // in case someone can figure out what their intent was.
1267 //if ($cellType == 'th') {
1268 // if (isset($this->cssStyles['table.sheet' . $sheetIndex . ' th.column' . $colNum])) {
1269 // $this->cssStyles['table.sheet' . $sheetIndex . ' th.column' . $colNum];
1270 // }
1271 //} else {
1272 // if (isset($this->cssStyles['table.sheet' . $sheetIndex . ' td.column' . $colNum])) {
1273 // $this->cssStyles['table.sheet' . $sheetIndex . ' td.column' . $colNum];
1274 // }
1275 //}
1276 // End of mystery statements.
1277 }
1278
1279 return [$cell, $cssClass, $coordinate];
1280 }
1281
1282 private function generateRowCellDataValueRich($cell, &$cellData): void
1283 {
1284 // Loop through rich text elements
1285 $elements = $cell->getValue()->getRichTextElements();
1286 foreach ($elements as $element) {
1287 // Rich text start?
1288 if ($element instanceof Run) {
1289 $cellData .= '<span style="' . $this->assembleCSS($this->createCSSStyleFont($element->getFont())) . '">';
1290
1291 $cellEnd = '';
1292 if ($element->getFont()->getSuperscript()) {
1293 $cellData .= '<sup>';
1294 $cellEnd = '</sup>';
1295 } elseif ($element->getFont()->getSubscript()) {
1296 $cellData .= '<sub>';
1297 $cellEnd = '</sub>';
1298 }
1299
1300 // Convert UTF8 data to PCDATA
1301 $cellText = $element->getText();
1302 $cellData .= htmlspecialchars($cellText);
1303
1304 $cellData .= $cellEnd;
1305
1306 $cellData .= '</span>';
1307 } else {
1308 // Convert UTF8 data to PCDATA
1309 $cellText = $element->getText();
1310 $cellData .= htmlspecialchars($cellText);
1311 }
1312 }
1313 }
1314
1315 private function generateRowCellDataValue($pSheet, $cell, &$cellData): void
1316 {
1317 if ($cell->getValue() instanceof RichText) {
1318 $this->generateRowCellDataValueRich($cell, $cellData);
1319 } else {
1320 $origData = $this->preCalculateFormulas ? $cell->getCalculatedValue() : $cell->getValue();
1322 $origData,
1323 $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode(),
1324 [$this, 'formatColor']
1325 );
1326 if ($cellData === $origData) {
1327 $cellData = htmlspecialchars($cellData ?? '');
1328 }
1329 if ($pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont()->getSuperscript()) {
1330 $cellData = '<sup>' . $cellData . '</sup>';
1331 } elseif ($pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont()->getSubscript()) {
1332 $cellData = '<sub>' . $cellData . '</sub>';
1333 }
1334 }
1335 }
1336
1337 private function generateRowCellData($pSheet, $cell, &$cssClass, $cellType)
1338 {
1339 $cellData = '&nbsp;';
1340 if ($cell instanceof Cell) {
1341 $cellData = '';
1342 // Don't know what this does, and no test cases.
1343 //if ($cell->getParent() === null) {
1344 // $cell->attach($pSheet);
1345 //}
1346 // Value
1347 $this->generateRowCellDataValue($pSheet, $cell, $cellData);
1348
1349 // Converts the cell content so that spaces occuring at beginning of each new line are replaced by &nbsp;
1350 // Example: " Hello\n to the world" is converted to "&nbsp;&nbsp;Hello\n&nbsp;to the world"
1351 $cellData = preg_replace('/(?m)(?:^|\\G) /', '&nbsp;', $cellData);
1352
1353 // convert newline "\n" to '<br>'
1354 $cellData = nl2br($cellData);
1355
1356 // Extend CSS class?
1357 if (!$this->useInlineCss) {
1358 $cssClass .= ' style' . $cell->getXfIndex();
1359 $cssClass .= ' ' . $cell->getDataType();
1360 } else {
1361 if ($cellType == 'th') {
1362 if (isset($this->cssStyles['th.style' . $cell->getXfIndex()])) {
1363 $cssClass = array_merge($cssClass, $this->cssStyles['th.style' . $cell->getXfIndex()]);
1364 }
1365 } else {
1366 if (isset($this->cssStyles['td.style' . $cell->getXfIndex()])) {
1367 $cssClass = array_merge($cssClass, $this->cssStyles['td.style' . $cell->getXfIndex()]);
1368 }
1369 }
1370
1371 // General horizontal alignment: Actual horizontal alignment depends on dataType
1372 $sharedStyle = $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex());
1373 if (
1374 $sharedStyle->getAlignment()->getHorizontal() == Alignment::HORIZONTAL_GENERAL
1375 && isset($this->cssStyles['.' . $cell->getDataType()]['text-align'])
1376 ) {
1377 $cssClass['text-align'] = $this->cssStyles['.' . $cell->getDataType()]['text-align'];
1378 }
1379 }
1380 } else {
1381 // Use default borders for empty cell
1382 if (is_string($cssClass)) {
1383 $cssClass .= ' style0';
1384 }
1385 }
1386
1387 return $cellData;
1388 }
1389
1390 private function generateRowIncludeCharts($pSheet, $coordinate)
1391 {
1392 return $this->includeCharts ? $this->writeChartInCell($pSheet, $coordinate) : '';
1393 }
1394
1395 private function generateRowSpans($html, $rowSpan, $colSpan)
1396 {
1397 $html .= ($colSpan > 1) ? (' colspan="' . $colSpan . '"') : '';
1398 $html .= ($rowSpan > 1) ? (' rowspan="' . $rowSpan . '"') : '';
1399
1400 return $html;
1401 }
1402
1403 private function generateRowWriteCell(&$html, $pSheet, $coordinate, $cellType, $cellData, $colSpan, $rowSpan, $cssClass, $colNum, $sheetIndex, $pRow): void
1404 {
1405 // Image?
1406 $htmlx = $this->writeImageInCell($pSheet, $coordinate);
1407 // Chart?
1408 $htmlx .= $this->generateRowIncludeCharts($pSheet, $coordinate);
1409 // Column start
1410 $html .= ' <' . $cellType;
1411 if (!$this->useInlineCss && !$this->isPdf) {
1412 $html .= ' class="' . $cssClass . '"';
1413 if ($htmlx) {
1414 $html .= " style='position: relative;'";
1415 }
1416 } else {
1417 //** Necessary redundant code for the sake of \PhpOffice\PhpSpreadsheet\Writer\Pdf **
1418 // We must explicitly write the width of the <td> element because TCPDF
1419 // does not recognize e.g. <col style="width:42pt">
1420 if ($this->useInlineCss) {
1421 $xcssClass = $cssClass;
1422 } else {
1423 $html .= ' class="' . $cssClass . '"';
1424 $xcssClass = [];
1425 }
1426 $width = 0;
1427 $i = $colNum - 1;
1428 $e = $colNum + $colSpan - 1;
1429 while ($i++ < $e) {
1430 if (isset($this->columnWidths[$sheetIndex][$i])) {
1431 $width += $this->columnWidths[$sheetIndex][$i];
1432 }
1433 }
1434 $xcssClass['width'] = $width . 'pt';
1435
1436 // We must also explicitly write the height of the <td> element because TCPDF
1437 // does not recognize e.g. <tr style="height:50pt">
1438 if (isset($this->cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]['height'])) {
1439 $height = $this->cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]['height'];
1440 $xcssClass['height'] = $height;
1441 }
1442 //** end of redundant code **
1443
1444 if ($htmlx) {
1445 $xcssClass['position'] = 'relative';
1446 }
1447 $html .= ' style="' . $this->assembleCSS($xcssClass) . '"';
1448 }
1449 $html = $this->generateRowSpans($html, $rowSpan, $colSpan);
1450
1451 $html .= '>';
1452 $html .= $htmlx;
1453
1454 $html .= $this->writeComment($pSheet, $coordinate);
1455
1456 // Cell data
1457 $html .= $cellData;
1458
1459 // Column end
1460 $html .= '</' . $cellType . '>' . PHP_EOL;
1461 }
1462
1473 private function generateRow(Worksheet $pSheet, array $pValues, $pRow, $cellType)
1474 {
1475 // Sheet index
1476 $sheetIndex = $pSheet->getParent()->getIndex($pSheet);
1477 $html = $this->generateRowStart($pSheet, $sheetIndex, $pRow);
1478
1479 // Write cells
1480 $colNum = 0;
1481 foreach ($pValues as $cellAddress) {
1482 [$cell, $cssClass, $coordinate] = $this->generateRowCellCss($pSheet, $cellAddress, $pRow, $colNum);
1483
1484 $colSpan = 1;
1485 $rowSpan = 1;
1486
1487 // Cell Data
1488 $cellData = $this->generateRowCellData($pSheet, $cell, $cssClass, $cellType);
1489
1490 // Hyperlink?
1491 if ($pSheet->hyperlinkExists($coordinate) && !$pSheet->getHyperlink($coordinate)->isInternal()) {
1492 $cellData = '<a href="' . htmlspecialchars($pSheet->getHyperlink($coordinate)->getUrl()) . '" title="' . htmlspecialchars($pSheet->getHyperlink($coordinate)->getTooltip()) . '">' . $cellData . '</a>';
1493 }
1494
1495 // Should the cell be written or is it swallowed by a rowspan or colspan?
1496 $writeCell = !(isset($this->isSpannedCell[$pSheet->getParent()->getIndex($pSheet)][$pRow + 1][$colNum])
1497 && $this->isSpannedCell[$pSheet->getParent()->getIndex($pSheet)][$pRow + 1][$colNum]);
1498
1499 // Colspan and Rowspan
1500 $colspan = 1;
1501 $rowspan = 1;
1502 if (isset($this->isBaseCell[$pSheet->getParent()->getIndex($pSheet)][$pRow + 1][$colNum])) {
1503 $spans = $this->isBaseCell[$pSheet->getParent()->getIndex($pSheet)][$pRow + 1][$colNum];
1504 $rowSpan = $spans['rowspan'];
1505 $colSpan = $spans['colspan'];
1506
1507 // Also apply style from last cell in merge to fix borders -
1508 // relies on !important for non-none border declarations in createCSSStyleBorder
1509 $endCellCoord = Coordinate::stringFromColumnIndex($colNum + $colSpan) . ($pRow + $rowSpan);
1510 if (!$this->useInlineCss) {
1511 $cssClass .= ' style' . $pSheet->getCell($endCellCoord)->getXfIndex();
1512 }
1513 }
1514
1515 // Write
1516 if ($writeCell) {
1517 $this->generateRowWriteCell($html, $pSheet, $coordinate, $cellType, $cellData, $colSpan, $rowSpan, $cssClass, $colNum, $sheetIndex, $pRow);
1518 }
1519
1520 // Next column
1521 ++$colNum;
1522 }
1523
1524 // Write row end
1525 $html .= ' </tr>' . PHP_EOL;
1526
1527 // Return
1528 return $html;
1529 }
1530
1536 private function assembleCSS(array $pValue = [])
1537 {
1538 $pairs = [];
1539 foreach ($pValue as $property => $value) {
1540 $pairs[] = $property . ':' . $value;
1541 }
1542 $string = implode('; ', $pairs);
1543
1544 return $string;
1545 }
1546
1552 public function getImagesRoot()
1553 {
1554 return $this->imagesRoot;
1555 }
1556
1564 public function setImagesRoot($pValue)
1565 {
1566 $this->imagesRoot = $pValue;
1567
1568 return $this;
1569 }
1570
1576 public function getEmbedImages()
1577 {
1578 return $this->embedImages;
1579 }
1580
1588 public function setEmbedImages($pValue)
1589 {
1590 $this->embedImages = $pValue;
1591
1592 return $this;
1593 }
1594
1600 public function getUseInlineCss()
1601 {
1602 return $this->useInlineCss;
1603 }
1604
1612 public function setUseInlineCss($pValue)
1613 {
1614 $this->useInlineCss = $pValue;
1615
1616 return $this;
1617 }
1618
1628 public function getUseEmbeddedCSS()
1629 {
1630 return $this->useEmbeddedCSS;
1631 }
1632
1644 public function setUseEmbeddedCSS($pValue)
1645 {
1646 $this->useEmbeddedCSS = $pValue;
1647
1648 return $this;
1649 }
1650
1659 public function formatColor($pValue, $pFormat)
1660 {
1661 // Color information, e.g. [Red] is always at the beginning
1662 $color = null; // initialize
1663 $matches = [];
1664
1665 $color_regex = '/^\\[[a-zA-Z]+\\]/';
1666 if (preg_match($color_regex, $pFormat, $matches)) {
1667 $color = str_replace(['[', ']'], '', $matches[0]);
1668 $color = strtolower($color);
1669 }
1670
1671 // convert to PCDATA
1672 $value = htmlspecialchars($pValue);
1673
1674 // color span tag
1675 if ($color !== null) {
1676 $value = '<span style="color:' . $color . '">' . $value . '</span>';
1677 }
1678
1679 return $value;
1680 }
1681
1685 private function calculateSpans(): void
1686 {
1687 if ($this->spansAreCalculated) {
1688 return;
1689 }
1690 // Identify all cells that should be omitted in HTML due to cell merge.
1691 // In HTML only the upper-left cell should be written and it should have
1692 // appropriate rowspan / colspan attribute
1693 $sheetIndexes = $this->sheetIndex !== null ?
1694 [$this->sheetIndex] : range(0, $this->spreadsheet->getSheetCount() - 1);
1695
1696 foreach ($sheetIndexes as $sheetIndex) {
1697 $sheet = $this->spreadsheet->getSheet($sheetIndex);
1698
1699 $candidateSpannedRow = [];
1700
1701 // loop through all Excel merged cells
1702 foreach ($sheet->getMergeCells() as $cells) {
1703 [$cells] = Coordinate::splitRange($cells);
1704 $first = $cells[0];
1705 $last = $cells[1];
1706
1707 [$fc, $fr] = Coordinate::indexesFromString($first);
1708 $fc = $fc - 1;
1709
1711 $lc = $lc - 1;
1712
1713 // loop through the individual cells in the individual merge
1714 $r = $fr - 1;
1715 while ($r++ < $lr) {
1716 // also, flag this row as a HTML row that is candidate to be omitted
1717 $candidateSpannedRow[$r] = $r;
1718
1719 $c = $fc - 1;
1720 while ($c++ < $lc) {
1721 if (!($c == $fc && $r == $fr)) {
1722 // not the upper-left cell (should not be written in HTML)
1723 $this->isSpannedCell[$sheetIndex][$r][$c] = [
1724 'baseCell' => [$fr, $fc],
1725 ];
1726 } else {
1727 // upper-left is the base cell that should hold the colspan/rowspan attribute
1728 $this->isBaseCell[$sheetIndex][$r][$c] = [
1729 'xlrowspan' => $lr - $fr + 1, // Excel rowspan
1730 'rowspan' => $lr - $fr + 1, // HTML rowspan, value may change
1731 'xlcolspan' => $lc - $fc + 1, // Excel colspan
1732 'colspan' => $lc - $fc + 1, // HTML colspan, value may change
1733 ];
1734 }
1735 }
1736 }
1737 }
1738
1739 $this->calculateSpansOmitRows($sheet, $sheetIndex, $candidateSpannedRow);
1740
1741 // TODO: Same for columns
1742 }
1743
1744 // We have calculated the spans
1745 $this->spansAreCalculated = true;
1746 }
1747
1748 private function calculateSpansOmitRows($sheet, $sheetIndex, $candidateSpannedRow): void
1749 {
1750 // Identify which rows should be omitted in HTML. These are the rows where all the cells
1751 // participate in a merge and the where base cells are somewhere above.
1752 $countColumns = Coordinate::columnIndexFromString($sheet->getHighestColumn());
1753 foreach ($candidateSpannedRow as $rowIndex) {
1754 if (isset($this->isSpannedCell[$sheetIndex][$rowIndex])) {
1755 if (count($this->isSpannedCell[$sheetIndex][$rowIndex]) == $countColumns) {
1756 $this->isSpannedRow[$sheetIndex][$rowIndex] = $rowIndex;
1757 }
1758 }
1759 }
1760
1761 // For each of the omitted rows we found above, the affected rowspans should be subtracted by 1
1762 if (isset($this->isSpannedRow[$sheetIndex])) {
1763 foreach ($this->isSpannedRow[$sheetIndex] as $rowIndex) {
1764 $adjustedBaseCells = [];
1765 $c = -1;
1766 $e = $countColumns - 1;
1767 while ($c++ < $e) {
1768 $baseCell = $this->isSpannedCell[$sheetIndex][$rowIndex][$c]['baseCell'];
1769
1770 if (!in_array($baseCell, $adjustedBaseCells)) {
1771 // subtract rowspan by 1
1772 --$this->isBaseCell[$sheetIndex][$baseCell[0]][$baseCell[1]]['rowspan'];
1773 $adjustedBaseCells[] = $baseCell;
1774 }
1775 }
1776 }
1777 }
1778 }
1779
1789 private function writeComment(Worksheet $pSheet, $coordinate)
1790 {
1791 $result = '';
1792 if (!$this->isPdf && isset($pSheet->getComments()[$coordinate])) {
1793 $sanitizer = new HTMLPurifier();
1794 $sanitizedString = $sanitizer->purify($pSheet->getComment($coordinate)->getText()->getPlainText());
1795 if ($sanitizedString !== '') {
1796 $result .= '<a class="comment-indicator"></a>';
1797 $result .= '<div class="comment">' . nl2br($sanitizedString) . '</div>';
1798 $result .= PHP_EOL;
1799 }
1800 }
1801
1802 return $result;
1803 }
1804
1812 private function generatePageDeclarations($generateSurroundingHTML)
1813 {
1814 // Ensure that Spans have been calculated?
1815 $this->calculateSpans();
1816
1817 // Fetch sheets
1818 $sheets = [];
1819 if ($this->sheetIndex === null) {
1820 $sheets = $this->spreadsheet->getAllSheets();
1821 } else {
1822 $sheets[] = $this->spreadsheet->getSheet($this->sheetIndex);
1823 }
1824
1825 // Construct HTML
1826 $htmlPage = $generateSurroundingHTML ? ('<style type="text/css">' . PHP_EOL) : '';
1827
1828 // Loop all sheets
1829 $sheetId = 0;
1830 foreach ($sheets as $pSheet) {
1831 $htmlPage .= "@page page$sheetId { ";
1832 $left = StringHelper::formatNumber($pSheet->getPageMargins()->getLeft()) . 'in; ';
1833 $htmlPage .= 'margin-left: ' . $left;
1834 $right = StringHelper::FormatNumber($pSheet->getPageMargins()->getRight()) . 'in; ';
1835 $htmlPage .= 'margin-right: ' . $right;
1836 $top = StringHelper::FormatNumber($pSheet->getPageMargins()->getTop()) . 'in; ';
1837 $htmlPage .= 'margin-top: ' . $top;
1838 $bottom = StringHelper::FormatNumber($pSheet->getPageMargins()->getBottom()) . 'in; ';
1839 $htmlPage .= 'margin-bottom: ' . $bottom;
1840 $orientation = $pSheet->getPageSetup()->getOrientation();
1841 if ($orientation === \PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE) {
1842 $htmlPage .= 'size: landscape; ';
1843 } elseif ($orientation === \PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_PORTRAIT) {
1844 $htmlPage .= 'size: portrait; ';
1845 }
1846 $htmlPage .= "}\n";
1847 ++$sheetId;
1848 }
1849 $htmlPage .= <<<EOF
1850.navigation {page-break-after: always;}
1851.scrpgbrk, div + div {page-break-before: always;}
1852@media screen {
1853 .gridlines td {border: 1px solid black;}
1854 .gridlines th {border: 1px solid black;}
1855 body>div {margin-top: 5px;}
1856 body>div:first-child {margin-top: 0;}
1857 .scrpgbrk {margin-top: 1px;}
1858}
1859@media print {
1860 .gridlinesp td {border: 1px solid black;}
1861 .gridlinesp th {border: 1px solid black;}
1862 .navigation {display: none;}
1863}
1864
1865EOF;
1866 $htmlPage .= $generateSurroundingHTML ? ('</style>' . PHP_EOL) : '';
1867
1868 return $htmlPage;
1869 }
1870}
$result
HTMLPurifier($html, $config=null)
Purify HTML.
const EOF
How fgetc() reports an End Of File.
Definition: JSMin_lib.php:92
if(! $in) print
$filename
Definition: buildRTE.php:89
An exception for terminatinating execution or to throw for unit testing.
static setArrayReturnType($returnType)
Set the Array Return Type (Array or Value of first element in the array).
static getArrayReturnType()
Return the Array Return Type (Array or Value of first element in the array).
static getInstance(?Spreadsheet $spreadsheet=null)
Get an instance of this class.
Helper class to manipulate cell coordinates.
Definition: Coordinate.php:15
static coordinateFromString($pCoordinateString)
Coordinate from string.
Definition: Coordinate.php:32
static indexesFromString(string $coordinates)
Get indexes from a string coordinates.
Definition: Coordinate.php:52
static columnIndexFromString($pString)
Column index from string.
Definition: Coordinate.php:265
static stringFromColumnIndex($columnIndex)
String from column index.
Definition: Coordinate.php:313
static splitRange($pRange)
Split range into coordinate strings.
Definition: Coordinate.php:140
static sysGetTempDir()
Get the systems temporary directory.
Definition: File.php:111
static formatNumber($value)
Formats a numeric value as a string for output in various output writers forcing point as decimal sep...
getBorderStyle()
Get Border style.
Definition: Border.php:142
getStartColor()
Get Start Color.
Definition: Fill.php:239
getStrikethrough()
Get Strikethrough.
Definition: Font.php:463
getUnderline()
Get Underline.
Definition: Font.php:423
static toFormattedString($value, $format, $callBack=null)
Convert a value in a pre-defined format to a PHP string.
openFileHandle($filename)
Open file handle.
Definition: BaseWriter.php:102
maybeCloseFileHandle()
Close file handle only if we opened it ourselves.
Definition: BaseWriter.php:123
generateRowStart(Worksheet $pSheet, $sheetIndex, $pRow)
Generate row start.
Definition: Html.php:1223
setImagesRoot($pValue)
Set images root.
Definition: Html.php:1564
createCSSStyleFill(Fill $pStyle)
Create CSS style (Fill).
Definition: Html.php:1110
generateRow(Worksheet $pSheet, array $pValues, $pRow, $cellType)
Generate row.
Definition: Html.php:1473
setEditHtmlCallback(?callable $callback)
Set a callback to edit the entire HTML.
Definition: Html.php:218
calculateSpansOmitRows($sheet, $sheetIndex, $candidateSpannedRow)
Definition: Html.php:1748
generateRowCellCss($pSheet, $cellAddress, $pRow, $colNum)
Definition: Html.php:1256
setEmbedImages($pValue)
Set embed images.
Definition: Html.php:1588
mapVAlign($vAlign)
Map VAlign.
Definition: Html.php:237
generateRowSpans($html, $rowSpan, $colSpan)
Definition: Html.php:1395
writeAllSheets()
Write all sheets (resets sheetIndex to NULL).
Definition: Html.php:342
generateRowCellDataValue($pSheet, $cell, &$cellData)
Definition: Html.php:1315
generateSheetTags($row, $theadStart, $theadEnd, $tbodyStart)
Definition: Html.php:423
generateRowCellDataValueRich($cell, &$cellData)
Definition: Html.php:1282
generateSheetData()
Generate sheet data.
Definition: Html.php:441
calculateSpans()
Calculate information about HTML colspan and rowspan which is not always the same as Excel's.
Definition: Html.php:1685
generateStyles($generateSurroundingHTML=true)
Generate CSS styles.
Definition: Html.php:796
getImagesRoot()
Get images root.
Definition: Html.php:1552
static getChartCaption($cap)
Extend Row if chart is placed after nominal end of row.
Definition: Html.php:784
generateRowWriteCell(&$html, $pSheet, $coordinate, $cellType, $cellData, $colSpan, $rowSpan, $cssClass, $colNum, $sheetIndex, $pRow)
Definition: Html.php:1403
createCSSStyleFont(Font $pStyle)
Create CSS style (\PhpOffice\PhpSpreadsheet\Style\Font).
Definition: Html.php:1040
generateHtmlAll()
Save Spreadsheet as html to variable.
Definition: Html.php:173
generateHTMLFooter()
Generate HTML footer.
Definition: Html.php:1126
generatePageDeclarations($generateSurroundingHTML)
Definition: Html.php:1812
generateTableFooter()
Generate table footer.
Definition: Html.php:1209
createCSSStyle(Style $pStyle)
Create CSS style.
Definition: Html.php:999
extendRowsForChartsAndImages(Worksheet $pSheet, int $row)
Definition: Html.php:581
generateTableHeader($pSheet, $showid=true)
Generate table header.
Definition: Html.php:1175
createCSSStyleBorders(Borders $pStyle)
Create CSS style (Borders).
Definition: Html.php:1074
buildCssPerSheet(Worksheet $sheet, array &$css)
Definition: Html.php:847
setSheetIndex($pValue)
Set sheet index.
Definition: Html.php:306
generateHTMLHeader($pIncludeStyles=false)
Generate HTML header.
Definition: Html.php:361
createCSSStyleBorder(Border $pStyle)
Create CSS style (Border).
Definition: Html.php:1095
assembleCSS(array $pValue=[])
Takes array where of CSS properties / values and converts to CSS string.
Definition: Html.php:1536
generateRowIncludeCharts($pSheet, $coordinate)
Definition: Html.php:1390
getEmbedImages()
Get embed images.
Definition: Html.php:1576
generateTableTag($pSheet, $id, &$html, $sheetIndex)
Definition: Html.php:1156
generateRowCellData($pSheet, $cell, &$cssClass, $cellType)
Definition: Html.php:1337
static winFileToUrl($filename)
Convert Windows file name to file protocol URL.
Definition: Html.php:630
generateTableTagInline($pSheet, $id)
Definition: Html.php:1136
setUseInlineCss($pValue)
Set use inline CSS?
Definition: Html.php:1612
static generateMeta($val, $desc)
Definition: Html.php:349
getGenerateSheetNavigationBlock()
Get sheet index.
Definition: Html.php:318
buildCSS($generateSurroundingHTML=true)
Build CSS styles.
Definition: Html.php:905
getSheetIndex()
Get sheet index.
Definition: Html.php:294
generateNavigation()
Generate sheet tabs.
Definition: Html.php:510
writeImageInCell(Worksheet $pSheet, $coordinates)
Generate image tag in cell.
Definition: Html.php:648
buildCssRowHeights(Worksheet $sheet, array &$css, int $sheetIndex)
Definition: Html.php:827
setGenerateSheetNavigationBlock($pValue)
Set sheet index.
Definition: Html.php:330
setUseEmbeddedCSS($pValue)
Set use embedded CSS?
Definition: Html.php:1644
writeChartInCell(Worksheet $pSheet, $coordinates)
Generate chart tag in cell.
Definition: Html.php:729
formatColor($pValue, $pFormat)
Add color to formatted string as inline style.
Definition: Html.php:1659
mapHAlign($hAlign)
Map HAlign.
Definition: Html.php:257
save($pFilename)
Save Spreadsheet to file.
Definition: Html.php:156
mapBorderStyle($borderStyle)
Map border style.
Definition: Html.php:284
createCSSStyleAlignment(Alignment $pStyle)
Create CSS style (\PhpOffice\PhpSpreadsheet\Style\Alignment).
Definition: Html.php:1017
getUseEmbeddedCSS()
Get use embedded CSS?
Definition: Html.php:1628
getUseInlineCss()
Get use inline CSS?
Definition: Html.php:1600
__construct(Spreadsheet $spreadsheet)
Create a new HTML.
Definition: Html.php:145
writeComment(Worksheet $pSheet, $coordinate)
Write a comment in the same format as LibreOffice.
Definition: Html.php:1789
extendRowsForCharts(Worksheet $pSheet, int $row)
Extend Row if chart is placed after nominal end of row.
Definition: Html.php:556
generateSheetStarts($sheet, $rowMin)
Definition: Html.php:404
PHP_EOL
Definition: complexTest.php:7
$lc
Definition: date.php:267
$i
Definition: disco.tpl.php:19
$html
Definition: example_001.php:87
$style
Definition: example_012.php:70
$r
Definition: example_031.php:79
if(!array_key_exists('StateId', $_REQUEST)) $id
$index
Definition: metadata.php:60
$lr
$row
if(empty( $files_to_scan)) $sanitizer
Definition: svg-scanner.php:90