Search moodle.org's
Developer Documentation
Developer Documentation
See Release Notes
Long Term Support Release
- Bug fixes for general core bugs in 4.1.x will end 13 November 2023 (12 months).
- Bug fixes for security issues in 4.1.x will end 10 November 2025 (36 months).
- PHP version: minimum PHP 7.4.0 Note: minimum PHP version has increased since Moodle 4.0. PHP 8.0.x is supported too.
Differences Between: [Versions 310 and 401] [Versions 311 and 401] [Versions 39 and 401] [Versions 400 and 401] [Versions 401 and 402] [Versions 401 and 403]
1 <?php 2 3 namespace PhpOffice\PhpSpreadsheet\Writer; 4 5 use HTMLPurifier; 6 use PhpOffice\PhpSpreadsheet\Calculation\Calculation; 7 use PhpOffice\PhpSpreadsheet\Cell\Cell; 8 use PhpOffice\PhpSpreadsheet\Cell\Coordinate; 9 use PhpOffice\PhpSpreadsheet\Chart\Chart; 10 use PhpOffice\PhpSpreadsheet\RichText\RichText; 11 use PhpOffice\PhpSpreadsheet\RichText\Run; 12 use PhpOffice\PhpSpreadsheet\Settings; 13 use PhpOffice\PhpSpreadsheet\Shared\Drawing as SharedDrawing; 14 use PhpOffice\PhpSpreadsheet\Shared\File; 15 use PhpOffice\PhpSpreadsheet\Shared\Font as SharedFont; 16 use PhpOffice\PhpSpreadsheet\Shared\StringHelper; 17 use PhpOffice\PhpSpreadsheet\Spreadsheet; 18 use PhpOffice\PhpSpreadsheet\Style\Alignment; 19 use PhpOffice\PhpSpreadsheet\Style\Border; 20 use PhpOffice\PhpSpreadsheet\Style\Borders; 21 use PhpOffice\PhpSpreadsheet\Style\Fill; 22 use PhpOffice\PhpSpreadsheet\Style\Font; 23 use PhpOffice\PhpSpreadsheet\Style\NumberFormat; 24 use PhpOffice\PhpSpreadsheet\Style\Style; 25 use PhpOffice\PhpSpreadsheet\Worksheet\Drawing; 26 use PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing; 27 use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup; 28 use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet; 29 30 class Html extends BaseWriter 31 { 32 /** 33 * Spreadsheet object. 34 * 35 * @var Spreadsheet 36 */ 37 protected $spreadsheet; 38 39 /** 40 * Sheet index to write. 41 * 42 * @var null|int 43 */ 44 private $sheetIndex = 0; 45 46 /** 47 * Images root. 48 * 49 * @var string 50 */ 51 private $imagesRoot = ''; 52 53 /** 54 * embed images, or link to images. 55 * 56 * @var bool 57 */ 58 protected $embedImages = false; 59 60 /** 61 * Use inline CSS? 62 * 63 * @var bool 64 */ 65 private $useInlineCss = false; 66 67 /** 68 * Use embedded CSS? 69 * 70 * @var bool 71 */ 72 private $useEmbeddedCSS = true; 73 74 /** 75 * Array of CSS styles. 76 * 77 * @var array 78 */ 79 private $cssStyles; 80 81 /** 82 * Array of column widths in points. 83 * 84 * @var array 85 */ 86 private $columnWidths; 87 88 /** 89 * Default font. 90 * 91 * @var Font 92 */ 93 private $defaultFont; 94 95 /** 96 * Flag whether spans have been calculated. 97 * 98 * @var bool 99 */ 100 private $spansAreCalculated = false; 101 102 /** 103 * Excel cells that should not be written as HTML cells. 104 * 105 * @var array 106 */ 107 private $isSpannedCell = []; 108 109 /** 110 * Excel cells that are upper-left corner in a cell merge. 111 * 112 * @var array 113 */ 114 private $isBaseCell = []; 115 116 /** 117 * Excel rows that should not be written as HTML rows. 118 * 119 * @var array 120 */ 121 private $isSpannedRow = []; 122 123 /** 124 * Is the current writer creating PDF? 125 * 126 * @var bool 127 */ 128 protected $isPdf = false; 129 130 /** 131 * Is the current writer creating mPDF? 132 * 133 * @var bool 134 */ 135 protected $isMPdf = false; 136 137 /** 138 * Generate the Navigation block. 139 * 140 * @var bool 141 */ 142 private $generateSheetNavigationBlock = true; 143 144 /** 145 * Callback for editing generated html. 146 * 147 * @var null|callable 148 */ 149 private $editHtmlCallback; 150 151 /** 152 * Create a new HTML. 153 */ 154 public function __construct(Spreadsheet $spreadsheet) 155 { 156 $this->spreadsheet = $spreadsheet; 157 $this->defaultFont = $this->spreadsheet->getDefaultStyle()->getFont(); 158 } 159 160 /** 161 * Save Spreadsheet to file. 162 * 163 * @param resource|string $filename 164 */ 165 public function save($filename, int $flags = 0): void 166 { 167 $this->processFlags($flags); 168 169 // Open file 170 $this->openFileHandle($filename); 171 172 // Write html 173 fwrite($this->fileHandle, $this->generateHTMLAll()); 174 175 // Close file 176 $this->maybeCloseFileHandle(); 177 } 178 179 /** 180 * Save Spreadsheet as html to variable. 181 * 182 * @return string 183 */ 184 public function generateHtmlAll() 185 { 186 // garbage collect 187 $this->spreadsheet->garbageCollect(); 188 189 $saveDebugLog = Calculation::getInstance($this->spreadsheet)->getDebugLog()->getWriteDebugLog(); 190 Calculation::getInstance($this->spreadsheet)->getDebugLog()->setWriteDebugLog(false); 191 $saveArrayReturnType = Calculation::getArrayReturnType(); 192 Calculation::setArrayReturnType(Calculation::RETURN_ARRAY_AS_VALUE); 193 194 // Build CSS 195 $this->buildCSS(!$this->useInlineCss); 196 197 $html = ''; 198 199 // Write headers 200 $html .= $this->generateHTMLHeader(!$this->useInlineCss); 201 202 // Write navigation (tabs) 203 if ((!$this->isPdf) && ($this->generateSheetNavigationBlock)) { 204 $html .= $this->generateNavigation(); 205 } 206 207 // Write data 208 $html .= $this->generateSheetData(); 209 210 // Write footer 211 $html .= $this->generateHTMLFooter(); 212 $callback = $this->editHtmlCallback; 213 if ($callback) { 214 $html = $callback($html); 215 } 216 217 Calculation::setArrayReturnType($saveArrayReturnType); 218 Calculation::getInstance($this->spreadsheet)->getDebugLog()->setWriteDebugLog($saveDebugLog); 219 220 return $html; 221 } 222 223 /** 224 * Set a callback to edit the entire HTML. 225 * 226 * The callback must accept the HTML as string as first parameter, 227 * and it must return the edited HTML as string. 228 */ 229 public function setEditHtmlCallback(?callable $callback): void 230 { 231 $this->editHtmlCallback = $callback; 232 } 233 234 /** 235 * Map VAlign. 236 * 237 * @param string $vAlign Vertical alignment 238 * 239 * @return string 240 */ 241 private function mapVAlign($vAlign) 242 { 243 return Alignment::VERTICAL_ALIGNMENT_FOR_HTML[$vAlign] ?? ''; 244 } 245 246 /** 247 * Map HAlign. 248 * 249 * @param string $hAlign Horizontal alignment 250 * 251 * @return string 252 */ 253 private function mapHAlign($hAlign) 254 { 255 return Alignment::HORIZONTAL_ALIGNMENT_FOR_HTML[$hAlign] ?? ''; 256 } 257 258 const BORDER_ARR = [ 259 Border::BORDER_NONE => 'none', 260 Border::BORDER_DASHDOT => '1px dashed', 261 Border::BORDER_DASHDOTDOT => '1px dotted', 262 Border::BORDER_DASHED => '1px dashed', 263 Border::BORDER_DOTTED => '1px dotted', 264 Border::BORDER_DOUBLE => '3px double', 265 Border::BORDER_HAIR => '1px solid', 266 Border::BORDER_MEDIUM => '2px solid', 267 Border::BORDER_MEDIUMDASHDOT => '2px dashed', 268 Border::BORDER_MEDIUMDASHDOTDOT => '2px dotted', 269 Border::BORDER_SLANTDASHDOT => '2px dashed', 270 Border::BORDER_THICK => '3px solid', 271 ]; 272 273 /** 274 * Map border style. 275 * 276 * @param int|string $borderStyle Sheet index 277 * 278 * @return string 279 */ 280 private function mapBorderStyle($borderStyle) 281 { 282 return array_key_exists($borderStyle, self::BORDER_ARR) ? self::BORDER_ARR[$borderStyle] : '1px solid'; 283 } 284 285 /** 286 * Get sheet index. 287 */ 288 public function getSheetIndex(): ?int 289 { 290 return $this->sheetIndex; 291 } 292 293 /** 294 * Set sheet index. 295 * 296 * @param int $sheetIndex Sheet index 297 * 298 * @return $this 299 */ 300 public function setSheetIndex($sheetIndex) 301 { 302 $this->sheetIndex = $sheetIndex; 303 304 return $this; 305 } 306 307 /** 308 * Get sheet index. 309 * 310 * @return bool 311 */ 312 public function getGenerateSheetNavigationBlock() 313 { 314 return $this->generateSheetNavigationBlock; 315 } 316 317 /** 318 * Set sheet index. 319 * 320 * @param bool $generateSheetNavigationBlock Flag indicating whether the sheet navigation block should be generated or not 321 * 322 * @return $this 323 */ 324 public function setGenerateSheetNavigationBlock($generateSheetNavigationBlock) 325 { 326 $this->generateSheetNavigationBlock = (bool) $generateSheetNavigationBlock; 327 328 return $this; 329 } 330 331 /** 332 * Write all sheets (resets sheetIndex to NULL). 333 * 334 * @return $this 335 */ 336 public function writeAllSheets() 337 { 338 $this->sheetIndex = null; 339 340 return $this; 341 } 342 343 private static function generateMeta(?string $val, string $desc): string 344 { 345 return $val 346 ? (' <meta name="' . $desc . '" content="' . htmlspecialchars($val, Settings::htmlEntityFlags()) . '" />' . PHP_EOL) 347 : ''; 348 } 349 350 public const BODY_LINE = ' <body>' . PHP_EOL; 351 352 /** 353 * Generate HTML header. 354 * 355 * @param bool $includeStyles Include styles? 356 * 357 * @return string 358 */ 359 public function generateHTMLHeader($includeStyles = false) 360 { 361 // Construct HTML 362 $properties = $this->spreadsheet->getProperties(); 363 $html = '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">' . PHP_EOL; 364 $html .= '<html xmlns="http://www.w3.org/1999/xhtml">' . PHP_EOL; 365 $html .= ' <head>' . PHP_EOL; 366 $html .= ' <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />' . PHP_EOL; 367 $html .= ' <meta name="generator" content="PhpSpreadsheet, https://github.com/PHPOffice/PhpSpreadsheet" />' . PHP_EOL; 368 $html .= ' <title>' . htmlspecialchars($properties->getTitle(), Settings::htmlEntityFlags()) . '</title>' . PHP_EOL; 369 $html .= self::generateMeta($properties->getCreator(), 'author'); 370 $html .= self::generateMeta($properties->getTitle(), 'title'); 371 $html .= self::generateMeta($properties->getDescription(), 'description'); 372 $html .= self::generateMeta($properties->getSubject(), 'subject'); 373 $html .= self::generateMeta($properties->getKeywords(), 'keywords'); 374 $html .= self::generateMeta($properties->getCategory(), 'category'); 375 $html .= self::generateMeta($properties->getCompany(), 'company'); 376 $html .= self::generateMeta($properties->getManager(), 'manager'); 377 378 $html .= $includeStyles ? $this->generateStyles(true) : $this->generatePageDeclarations(true); 379 380 $html .= ' </head>' . PHP_EOL; 381 $html .= '' . PHP_EOL; 382 $html .= self::BODY_LINE; 383 384 return $html; 385 } 386 387 private function generateSheetPrep(): array 388 { 389 // Ensure that Spans have been calculated? 390 $this->calculateSpans(); 391 392 // Fetch sheets 393 if ($this->sheetIndex === null) { 394 $sheets = $this->spreadsheet->getAllSheets(); 395 } else { 396 $sheets = [$this->spreadsheet->getSheet($this->sheetIndex)]; 397 } 398 399 return $sheets; 400 } 401 402 private function generateSheetStarts(Worksheet $sheet, int $rowMin): array 403 { 404 // calculate start of <tbody>, <thead> 405 $tbodyStart = $rowMin; 406 $theadStart = $theadEnd = 0; // default: no <thead> no </thead> 407 if ($sheet->getPageSetup()->isRowsToRepeatAtTopSet()) { 408 $rowsToRepeatAtTop = $sheet->getPageSetup()->getRowsToRepeatAtTop(); 409 410 // we can only support repeating rows that start at top row 411 if ($rowsToRepeatAtTop[0] == 1) { 412 $theadStart = $rowsToRepeatAtTop[0]; 413 $theadEnd = $rowsToRepeatAtTop[1]; 414 $tbodyStart = $rowsToRepeatAtTop[1] + 1; 415 } 416 } 417 418 return [$theadStart, $theadEnd, $tbodyStart]; 419 } 420 421 private function generateSheetTags(int $row, int $theadStart, int $theadEnd, int $tbodyStart): array 422 { 423 // <thead> ? 424 $startTag = ($row == $theadStart) ? (' <thead>' . PHP_EOL) : ''; 425 if (!$startTag) { 426 $startTag = ($row == $tbodyStart) ? (' <tbody>' . PHP_EOL) : ''; 427 } 428 $endTag = ($row == $theadEnd) ? (' </thead>' . PHP_EOL) : ''; 429 $cellType = ($row >= $tbodyStart) ? 'td' : 'th'; 430 431 return [$cellType, $startTag, $endTag]; 432 } 433 434 /** 435 * Generate sheet data. 436 * 437 * @return string 438 */ 439 public function generateSheetData() 440 { 441 $sheets = $this->generateSheetPrep(); 442 443 // Construct HTML 444 $html = ''; 445 446 // Loop all sheets 447 $sheetId = 0; 448 foreach ($sheets as $sheet) { 449 // Write table header 450 $html .= $this->generateTableHeader($sheet); 451 452 // Get worksheet dimension 453 [$min, $max] = explode(':', $sheet->calculateWorksheetDataDimension()); 454 [$minCol, $minRow] = Coordinate::indexesFromString($min); 455 [$maxCol, $maxRow] = Coordinate::indexesFromString($max); 456 457 [$theadStart, $theadEnd, $tbodyStart] = $this->generateSheetStarts($sheet, $minRow); 458 459 // Loop through cells 460 $row = $minRow - 1; 461 while ($row++ < $maxRow) { 462 [$cellType, $startTag, $endTag] = $this->generateSheetTags($row, $theadStart, $theadEnd, $tbodyStart); 463 $html .= $startTag; 464 465 // Write row if there are HTML table cells in it 466 if (!isset($this->isSpannedRow[$sheet->getParent()->getIndex($sheet)][$row])) { 467 // Start a new rowData 468 $rowData = []; 469 // Loop through columns 470 $column = $minCol; 471 while ($column <= $maxCol) { 472 // Cell exists? 473 $cellAddress = Coordinate::stringFromColumnIndex($column) . $row; 474 $rowData[$column++] = ($sheet->getCellCollection()->has($cellAddress)) ? $cellAddress : ''; 475 } 476 $html .= $this->generateRow($sheet, $rowData, $row - 1, $cellType); 477 } 478 479 $html .= $endTag; 480 } 481 --$row; 482 $html .= $this->extendRowsForChartsAndImages($sheet, $row); 483 484 // Write table footer 485 $html .= $this->generateTableFooter(); 486 // Writing PDF? 487 if ($this->isPdf && $this->useInlineCss) { 488 if ($this->sheetIndex === null && $sheetId + 1 < $this->spreadsheet->getSheetCount()) { 489 $html .= '<div style="page-break-before:always" ></div>'; 490 } 491 } 492 493 // Next sheet 494 ++$sheetId; 495 } 496 497 return $html; 498 } 499 500 /** 501 * Generate sheet tabs. 502 * 503 * @return string 504 */ 505 public function generateNavigation() 506 { 507 // Fetch sheets 508 $sheets = []; 509 if ($this->sheetIndex === null) { 510 $sheets = $this->spreadsheet->getAllSheets(); 511 } else { 512 $sheets[] = $this->spreadsheet->getSheet($this->sheetIndex); 513 } 514 515 // Construct HTML 516 $html = ''; 517 518 // Only if there are more than 1 sheets 519 if (count($sheets) > 1) { 520 // Loop all sheets 521 $sheetId = 0; 522 523 $html .= '<ul class="navigation">' . PHP_EOL; 524 525 foreach ($sheets as $sheet) { 526 $html .= ' <li class="sheet' . $sheetId . '"><a href="#sheet' . $sheetId . '">' . $sheet->getTitle() . '</a></li>' . PHP_EOL; 527 ++$sheetId; 528 } 529 530 $html .= '</ul>' . PHP_EOL; 531 } 532 533 return $html; 534 } 535 536 /** 537 * Extend Row if chart is placed after nominal end of row. 538 * This code should be exercised by sample: 539 * Chart/32_Chart_read_write_PDF.php. 540 * 541 * @param int $row Row to check for charts 542 * 543 * @return array 544 */ 545 private function extendRowsForCharts(Worksheet $worksheet, int $row) 546 { 547 $rowMax = $row; 548 $colMax = 'A'; 549 $anyfound = false; 550 if ($this->includeCharts) { 551 foreach ($worksheet->getChartCollection() as $chart) { 552 if ($chart instanceof Chart) { 553 $anyfound = true; 554 $chartCoordinates = $chart->getTopLeftPosition(); 555 $chartTL = Coordinate::coordinateFromString($chartCoordinates['cell']); 556 $chartCol = Coordinate::columnIndexFromString($chartTL[0]); 557 if ($chartTL[1] > $rowMax) { 558 $rowMax = $chartTL[1]; 559 if ($chartCol > Coordinate::columnIndexFromString($colMax)) { 560 $colMax = $chartTL[0]; 561 } 562 } 563 } 564 } 565 } 566 567 return [$rowMax, $colMax, $anyfound]; 568 } 569 570 private function extendRowsForChartsAndImages(Worksheet $worksheet, int $row): string 571 { 572 [$rowMax, $colMax, $anyfound] = $this->extendRowsForCharts($worksheet, $row); 573 574 foreach ($worksheet->getDrawingCollection() as $drawing) { 575 $anyfound = true; 576 $imageTL = Coordinate::coordinateFromString($drawing->getCoordinates()); 577 $imageCol = Coordinate::columnIndexFromString($imageTL[0]); 578 if ($imageTL[1] > $rowMax) { 579 $rowMax = $imageTL[1]; 580 if ($imageCol > Coordinate::columnIndexFromString($colMax)) { 581 $colMax = $imageTL[0]; 582 } 583 } 584 } 585 586 // Don't extend rows if not needed 587 if ($row === $rowMax || !$anyfound) { 588 return ''; 589 } 590 591 $html = ''; 592 ++$colMax; 593 ++$row; 594 while ($row <= $rowMax) { 595 $html .= '<tr>'; 596 for ($col = 'A'; $col != $colMax; ++$col) { 597 $htmlx = $this->writeImageInCell($worksheet, $col . $row); 598 $htmlx .= $this->includeCharts ? $this->writeChartInCell($worksheet, $col . $row) : ''; 599 if ($htmlx) { 600 $html .= "<td class='style0' style='position: relative;'>$htmlx</td>"; 601 } else { 602 $html .= "<td class='style0'></td>"; 603 } 604 } 605 ++$row; 606 $html .= '</tr>' . PHP_EOL; 607 } 608 609 return $html; 610 } 611 612 /** 613 * Convert Windows file name to file protocol URL. 614 * 615 * @param string $filename file name on local system 616 * 617 * @return string 618 */ 619 public static function winFileToUrl($filename, bool $mpdf = false) 620 { 621 // Windows filename 622 if (substr($filename, 1, 2) === ':\\') { 623 $protocol = $mpdf ? '' : 'file:///'; 624 $filename = $protocol . str_replace('\\', '/', $filename); 625 } 626 627 return $filename; 628 } 629 630 /** 631 * Generate image tag in cell. 632 * 633 * @param Worksheet $worksheet \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet 634 * @param string $coordinates Cell coordinates 635 * 636 * @return string 637 */ 638 private function writeImageInCell(Worksheet $worksheet, $coordinates) 639 { 640 // Construct HTML 641 $html = ''; 642 643 // Write images 644 foreach ($worksheet->getDrawingCollection() as $drawing) { 645 if ($drawing->getCoordinates() != $coordinates) { 646 continue; 647 } 648 $filedesc = $drawing->getDescription(); 649 $filedesc = $filedesc ? htmlspecialchars($filedesc, ENT_QUOTES) : 'Embedded image'; 650 if ($drawing instanceof Drawing) { 651 $filename = $drawing->getPath(); 652 653 // Strip off eventual '.' 654 $filename = (string) preg_replace('/^[.]/', '', $filename); 655 656 // Prepend images root 657 $filename = $this->getImagesRoot() . $filename; 658 659 // Strip off eventual '.' if followed by non-/ 660 $filename = (string) preg_replace('@^[.]([^/])@', '$1', $filename); 661 662 // Convert UTF8 data to PCDATA 663 $filename = htmlspecialchars($filename, Settings::htmlEntityFlags()); 664 665 $html .= PHP_EOL; 666 $imageData = self::winFileToUrl($filename, $this->isMPdf); 667 668 if ($this->embedImages || substr($imageData, 0, 6) === 'zip://') { 669 $picture = @file_get_contents($filename); 670 if ($picture !== false) { 671 $imageDetails = getimagesize($filename) ?: []; 672 // base64 encode the binary data 673 $base64 = base64_encode($picture); 674 $imageData = 'data:' . $imageDetails['mime'] . ';base64,' . $base64; 675 } 676 } 677 678 $html .= '<img style="position: absolute; z-index: 1; left: ' . 679 $drawing->getOffsetX() . 'px; top: ' . $drawing->getOffsetY() . 'px; width: ' . 680 $drawing->getWidth() . 'px; height: ' . $drawing->getHeight() . 'px;" src="' . 681 $imageData . '" alt="' . $filedesc . '" />'; 682 } elseif ($drawing instanceof MemoryDrawing) { 683 $imageResource = $drawing->getImageResource(); 684 if ($imageResource) { 685 ob_start(); // Let's start output buffering. 686 imagepng($imageResource); // This will normally output the image, but because of ob_start(), it won't. 687 $contents = (string) ob_get_contents(); // Instead, output above is saved to $contents 688 ob_end_clean(); // End the output buffer. 689 690 $dataUri = 'data:image/png;base64,' . base64_encode($contents); 691 692 // Because of the nature of tables, width is more important than height. 693 // max-width: 100% ensures that image doesnt overflow containing cell 694 // width: X sets width of supplied image. 695 // As a result, images bigger than cell will be contained and images smaller will not get stretched 696 $html .= '<img alt="' . $filedesc . '" src="' . $dataUri . '" style="max-width:100%;width:' . $drawing->getWidth() . 'px;" />'; 697 } 698 } 699 } 700 701 return $html; 702 } 703 704 /** 705 * Generate chart tag in cell. 706 * This code should be exercised by sample: 707 * Chart/32_Chart_read_write_PDF.php. 708 */ 709 private function writeChartInCell(Worksheet $worksheet, string $coordinates): string 710 { 711 // Construct HTML 712 $html = ''; 713 714 // Write charts 715 foreach ($worksheet->getChartCollection() as $chart) { 716 if ($chart instanceof Chart) { 717 $chartCoordinates = $chart->getTopLeftPosition(); 718 if ($chartCoordinates['cell'] == $coordinates) { 719 $chartFileName = File::sysGetTempDir() . '/' . uniqid('', true) . '.png'; 720 if (!$chart->render($chartFileName)) { 721 return ''; 722 } 723 724 $html .= PHP_EOL; 725 $imageDetails = getimagesize($chartFileName) ?: []; 726 $filedesc = $chart->getTitle(); 727 $filedesc = $filedesc ? $filedesc->getCaptionText() : ''; 728 $filedesc = $filedesc ? htmlspecialchars($filedesc, ENT_QUOTES) : 'Embedded chart'; 729 $picture = file_get_contents($chartFileName); 730 if ($picture !== false) { 731 $base64 = base64_encode($picture); 732 $imageData = 'data:' . $imageDetails['mime'] . ';base64,' . $base64; 733 734 $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; 735 } 736 unlink($chartFileName); 737 } 738 } 739 } 740 741 // Return 742 return $html; 743 } 744 745 /** 746 * Generate CSS styles. 747 * 748 * @param bool $generateSurroundingHTML Generate surrounding HTML tags? (<style> and </style>) 749 * 750 * @return string 751 */ 752 public function generateStyles($generateSurroundingHTML = true) 753 { 754 // Build CSS 755 $css = $this->buildCSS($generateSurroundingHTML); 756 757 // Construct HTML 758 $html = ''; 759 760 // Start styles 761 if ($generateSurroundingHTML) { 762 $html .= ' <style type="text/css">' . PHP_EOL; 763 $html .= (array_key_exists('html', $css)) ? (' html { ' . $this->assembleCSS($css['html']) . ' }' . PHP_EOL) : ''; 764 } 765 766 // Write all other styles 767 foreach ($css as $styleName => $styleDefinition) { 768 if ($styleName != 'html') { 769 $html .= ' ' . $styleName . ' { ' . $this->assembleCSS($styleDefinition) . ' }' . PHP_EOL; 770 } 771 } 772 $html .= $this->generatePageDeclarations(false); 773 774 // End styles 775 if ($generateSurroundingHTML) { 776 $html .= ' </style>' . PHP_EOL; 777 } 778 779 // Return 780 return $html; 781 } 782 783 private function buildCssRowHeights(Worksheet $sheet, array &$css, int $sheetIndex): void 784 { 785 // Calculate row heights 786 foreach ($sheet->getRowDimensions() as $rowDimension) { 787 $row = $rowDimension->getRowIndex() - 1; 788 789 // table.sheetN tr.rowYYYYYY { } 790 $css['table.sheet' . $sheetIndex . ' tr.row' . $row] = []; 791 792 if ($rowDimension->getRowHeight() != -1) { 793 $pt_height = $rowDimension->getRowHeight(); 794 $css['table.sheet' . $sheetIndex . ' tr.row' . $row]['height'] = $pt_height . 'pt'; 795 } 796 if ($rowDimension->getVisible() === false) { 797 $css['table.sheet' . $sheetIndex . ' tr.row' . $row]['display'] = 'none'; 798 $css['table.sheet' . $sheetIndex . ' tr.row' . $row]['visibility'] = 'hidden'; 799 } 800 } 801 } 802 803 private function buildCssPerSheet(Worksheet $sheet, array &$css): void 804 { 805 // Calculate hash code 806 $sheetIndex = $sheet->getParent()->getIndex($sheet); 807 808 // Build styles 809 // Calculate column widths 810 $sheet->calculateColumnWidths(); 811 812 // col elements, initialize 813 $highestColumnIndex = Coordinate::columnIndexFromString($sheet->getHighestColumn()) - 1; 814 $column = -1; 815 while ($column++ < $highestColumnIndex) { 816 $this->columnWidths[$sheetIndex][$column] = 42; // approximation 817 $css['table.sheet' . $sheetIndex . ' col.col' . $column]['width'] = '42pt'; 818 } 819 820 // col elements, loop through columnDimensions and set width 821 foreach ($sheet->getColumnDimensions() as $columnDimension) { 822 $column = Coordinate::columnIndexFromString($columnDimension->getColumnIndex()) - 1; 823 $width = SharedDrawing::cellDimensionToPixels($columnDimension->getWidth(), $this->defaultFont); 824 $width = SharedDrawing::pixelsToPoints($width); 825 if ($columnDimension->getVisible() === false) { 826 $css['table.sheet' . $sheetIndex . ' .column' . $column]['display'] = 'none'; 827 } 828 if ($width >= 0) { 829 $this->columnWidths[$sheetIndex][$column] = $width; 830 $css['table.sheet' . $sheetIndex . ' col.col' . $column]['width'] = $width . 'pt'; 831 } 832 } 833 834 // Default row height 835 $rowDimension = $sheet->getDefaultRowDimension(); 836 837 // table.sheetN tr { } 838 $css['table.sheet' . $sheetIndex . ' tr'] = []; 839 840 if ($rowDimension->getRowHeight() == -1) { 841 $pt_height = SharedFont::getDefaultRowHeightByFont($this->spreadsheet->getDefaultStyle()->getFont()); 842 } else { 843 $pt_height = $rowDimension->getRowHeight(); 844 } 845 $css['table.sheet' . $sheetIndex . ' tr']['height'] = $pt_height . 'pt'; 846 if ($rowDimension->getVisible() === false) { 847 $css['table.sheet' . $sheetIndex . ' tr']['display'] = 'none'; 848 $css['table.sheet' . $sheetIndex . ' tr']['visibility'] = 'hidden'; 849 } 850 851 $this->buildCssRowHeights($sheet, $css, $sheetIndex); 852 } 853 854 /** 855 * Build CSS styles. 856 * 857 * @param bool $generateSurroundingHTML Generate surrounding HTML style? (html { }) 858 * 859 * @return array 860 */ 861 public function buildCSS($generateSurroundingHTML = true) 862 { 863 // Cached? 864 if ($this->cssStyles !== null) { 865 return $this->cssStyles; 866 } 867 868 // Ensure that spans have been calculated 869 $this->calculateSpans(); 870 871 // Construct CSS 872 $css = []; 873 874 // Start styles 875 if ($generateSurroundingHTML) { 876 // html { } 877 $css['html']['font-family'] = 'Calibri, Arial, Helvetica, sans-serif'; 878 $css['html']['font-size'] = '11pt'; 879 $css['html']['background-color'] = 'white'; 880 } 881 882 // CSS for comments as found in LibreOffice 883 $css['a.comment-indicator:hover + div.comment'] = [ 884 'background' => '#ffd', 885 'position' => 'absolute', 886 'display' => 'block', 887 'border' => '1px solid black', 888 'padding' => '0.5em', 889 ]; 890 891 $css['a.comment-indicator'] = [ 892 'background' => 'red', 893 'display' => 'inline-block', 894 'border' => '1px solid black', 895 'width' => '0.5em', 896 'height' => '0.5em', 897 ]; 898 899 $css['div.comment']['display'] = 'none'; 900 901 // table { } 902 $css['table']['border-collapse'] = 'collapse'; 903 904 // .b {} 905 $css['.b']['text-align'] = 'center'; // BOOL 906 907 // .e {} 908 $css['.e']['text-align'] = 'center'; // ERROR 909 910 // .f {} 911 $css['.f']['text-align'] = 'right'; // FORMULA 912 913 // .inlineStr {} 914 $css['.inlineStr']['text-align'] = 'left'; // INLINE 915 916 // .n {} 917 $css['.n']['text-align'] = 'right'; // NUMERIC 918 919 // .s {} 920 $css['.s']['text-align'] = 'left'; // STRING 921 922 // Calculate cell style hashes 923 foreach ($this->spreadsheet->getCellXfCollection() as $index => $style) { 924 $css['td.style' . $index . ', th.style' . $index] = $this->createCSSStyle($style); 925 //$css['th.style' . $index] = $this->createCSSStyle($style); 926 } 927 928 // Fetch sheets 929 $sheets = []; 930 if ($this->sheetIndex === null) { 931 $sheets = $this->spreadsheet->getAllSheets(); 932 } else { 933 $sheets[] = $this->spreadsheet->getSheet($this->sheetIndex); 934 } 935 936 // Build styles per sheet 937 foreach ($sheets as $sheet) { 938 $this->buildCssPerSheet($sheet, $css); 939 } 940 941 // Cache 942 if ($this->cssStyles === null) { 943 $this->cssStyles = $css; 944 } 945 946 // Return 947 return $css; 948 } 949 950 /** 951 * Create CSS style. 952 * 953 * @return array 954 */ 955 private function createCSSStyle(Style $style) 956 { 957 // Create CSS 958 return array_merge( 959 $this->createCSSStyleAlignment($style->getAlignment()), 960 $this->createCSSStyleBorders($style->getBorders()), 961 $this->createCSSStyleFont($style->getFont()), 962 $this->createCSSStyleFill($style->getFill()) 963 ); 964 } 965 966 /** 967 * Create CSS style. 968 * 969 * @return array 970 */ 971 private function createCSSStyleAlignment(Alignment $alignment) 972 { 973 // Construct CSS 974 $css = []; 975 976 // Create CSS 977 $verticalAlign = $this->mapVAlign($alignment->getVertical() ?? ''); 978 if ($verticalAlign) { 979 $css['vertical-align'] = $verticalAlign; 980 } 981 $textAlign = $this->mapHAlign($alignment->getHorizontal() ?? ''); 982 if ($textAlign) { 983 $css['text-align'] = $textAlign; 984 if (in_array($textAlign, ['left', 'right'])) { 985 $css['padding-' . $textAlign] = (string) ((int) $alignment->getIndent() * 9) . 'px'; 986 } 987 } 988 $rotation = $alignment->getTextRotation(); 989 if ($rotation !== 0 && $rotation !== Alignment::TEXTROTATION_STACK_PHPSPREADSHEET) { 990 if ($this->isMPdf) { 991 $css['text-rotate'] = "$rotation"; 992 } else { 993 $css['transform'] = "rotate({$rotation}deg)"; 994 } 995 } 996 997 return $css; 998 } 999 1000 /** 1001 * Create CSS style. 1002 * 1003 * @return array 1004 */ 1005 private function createCSSStyleFont(Font $font) 1006 { 1007 // Construct CSS 1008 $css = []; 1009 1010 // Create CSS 1011 if ($font->getBold()) { 1012 $css['font-weight'] = 'bold'; 1013 } 1014 if ($font->getUnderline() != Font::UNDERLINE_NONE && $font->getStrikethrough()) { 1015 $css['text-decoration'] = 'underline line-through'; 1016 } elseif ($font->getUnderline() != Font::UNDERLINE_NONE) { 1017 $css['text-decoration'] = 'underline'; 1018 } elseif ($font->getStrikethrough()) { 1019 $css['text-decoration'] = 'line-through'; 1020 } 1021 if ($font->getItalic()) { 1022 $css['font-style'] = 'italic'; 1023 } 1024 1025 $css['color'] = '#' . $font->getColor()->getRGB(); 1026 $css['font-family'] = '\'' . $font->getName() . '\''; 1027 $css['font-size'] = $font->getSize() . 'pt'; 1028 1029 return $css; 1030 } 1031 1032 /** 1033 * Create CSS style. 1034 * 1035 * @param Borders $borders Borders 1036 * 1037 * @return array 1038 */ 1039 private function createCSSStyleBorders(Borders $borders) 1040 { 1041 // Construct CSS 1042 $css = []; 1043 1044 // Create CSS 1045 $css['border-bottom'] = $this->createCSSStyleBorder($borders->getBottom()); 1046 $css['border-top'] = $this->createCSSStyleBorder($borders->getTop()); 1047 $css['border-left'] = $this->createCSSStyleBorder($borders->getLeft()); 1048 $css['border-right'] = $this->createCSSStyleBorder($borders->getRight()); 1049 1050 return $css; 1051 } 1052 1053 /** 1054 * Create CSS style. 1055 * 1056 * @param Border $border Border 1057 */ 1058 private function createCSSStyleBorder(Border $border): string 1059 { 1060 // Create CSS - add !important to non-none border styles for merged cells 1061 $borderStyle = $this->mapBorderStyle($border->getBorderStyle()); 1062 1063 return $borderStyle . ' #' . $border->getColor()->getRGB() . (($borderStyle == 'none') ? '' : ' !important'); 1064 } 1065 1066 /** 1067 * Create CSS style (Fill). 1068 * 1069 * @param Fill $fill Fill 1070 * 1071 * @return array 1072 */ 1073 private function createCSSStyleFill(Fill $fill) 1074 { 1075 // Construct HTML 1076 $css = []; 1077 1078 // Create CSS 1079 if ($fill->getFillType() !== Fill::FILL_NONE) { 1080 $value = $fill->getFillType() == Fill::FILL_NONE ? 1081 'white' : '#' . $fill->getStartColor()->getRGB(); 1082 $css['background-color'] = $value; 1083 } 1084 1085 return $css; 1086 } 1087 1088 /** 1089 * Generate HTML footer. 1090 */ 1091 public function generateHTMLFooter(): string 1092 { 1093 // Construct HTML 1094 $html = ''; 1095 $html .= ' </body>' . PHP_EOL; 1096 $html .= '</html>' . PHP_EOL; 1097 1098 return $html; 1099 } 1100 1101 private function generateTableTagInline(Worksheet $worksheet, string $id): string 1102 { 1103 $style = isset($this->cssStyles['table']) ? 1104 $this->assembleCSS($this->cssStyles['table']) : ''; 1105 1106 $prntgrid = $worksheet->getPrintGridlines(); 1107 $viewgrid = $this->isPdf ? $prntgrid : $worksheet->getShowGridlines(); 1108 if ($viewgrid && $prntgrid) { 1109 $html = " <table border='1' cellpadding='1' $id cellspacing='1' style='$style' class='gridlines gridlinesp'>" . PHP_EOL; 1110 } elseif ($viewgrid) { 1111 $html = " <table border='0' cellpadding='0' $id cellspacing='0' style='$style' class='gridlines'>" . PHP_EOL; 1112 } elseif ($prntgrid) { 1113 $html = " <table border='0' cellpadding='0' $id cellspacing='0' style='$style' class='gridlinesp'>" . PHP_EOL; 1114 } else { 1115 $html = " <table border='0' cellpadding='1' $id cellspacing='0' style='$style'>" . PHP_EOL; 1116 } 1117 1118 return $html; 1119 } 1120 1121 private function generateTableTag(Worksheet $worksheet, string $id, string &$html, int $sheetIndex): void 1122 { 1123 if (!$this->useInlineCss) { 1124 $gridlines = $worksheet->getShowGridlines() ? ' gridlines' : ''; 1125 $gridlinesp = $worksheet->getPrintGridlines() ? ' gridlinesp' : ''; 1126 $html .= " <table border='0' cellpadding='0' cellspacing='0' $id class='sheet$sheetIndex$gridlines$gridlinesp'>" . PHP_EOL; 1127 } else { 1128 $html .= $this->generateTableTagInline($worksheet, $id); 1129 } 1130 } 1131 1132 /** 1133 * Generate table header. 1134 * 1135 * @param Worksheet $worksheet The worksheet for the table we are writing 1136 * @param bool $showid whether or not to add id to table tag 1137 * 1138 * @return string 1139 */ 1140 private function generateTableHeader(Worksheet $worksheet, $showid = true) 1141 { 1142 $sheetIndex = $worksheet->getParent()->getIndex($worksheet); 1143 1144 // Construct HTML 1145 $html = ''; 1146 $id = $showid ? "id='sheet$sheetIndex'" : ''; 1147 if ($showid) { 1148 $html .= "<div style='page: page$sheetIndex'>" . PHP_EOL; 1149 } else { 1150 $html .= "<div style='page: page$sheetIndex' class='scrpgbrk'>" . PHP_EOL; 1151 } 1152 1153 $this->generateTableTag($worksheet, $id, $html, $sheetIndex); 1154 1155 // Write <col> elements 1156 $highestColumnIndex = Coordinate::columnIndexFromString($worksheet->getHighestColumn()) - 1; 1157 $i = -1; 1158 while ($i++ < $highestColumnIndex) { 1159 if (!$this->useInlineCss) { 1160 $html .= ' <col class="col' . $i . '" />' . PHP_EOL; 1161 } else { 1162 $style = isset($this->cssStyles['table.sheet' . $sheetIndex . ' col.col' . $i]) ? 1163 $this->assembleCSS($this->cssStyles['table.sheet' . $sheetIndex . ' col.col' . $i]) : ''; 1164 $html .= ' <col style="' . $style . '" />' . PHP_EOL; 1165 } 1166 } 1167 1168 return $html; 1169 } 1170 1171 /** 1172 * Generate table footer. 1173 */ 1174 private function generateTableFooter(): string 1175 { 1176 return ' </tbody></table>' . PHP_EOL . '</div>' . PHP_EOL; 1177 } 1178 1179 /** 1180 * Generate row start. 1181 * 1182 * @param int $sheetIndex Sheet index (0-based) 1183 * @param int $row row number 1184 * 1185 * @return string 1186 */ 1187 private function generateRowStart(Worksheet $worksheet, $sheetIndex, $row) 1188 { 1189 $html = ''; 1190 if (count($worksheet->getBreaks()) > 0) { 1191 $breaks = $worksheet->getBreaks(); 1192 1193 // check if a break is needed before this row 1194 if (isset($breaks['A' . $row])) { 1195 // close table: </table> 1196 $html .= $this->generateTableFooter(); 1197 if ($this->isPdf && $this->useInlineCss) { 1198 $html .= '<div style="page-break-before:always" />'; 1199 } 1200 1201 // open table again: <table> + <col> etc. 1202 $html .= $this->generateTableHeader($worksheet, false); 1203 $html .= '<tbody>' . PHP_EOL; 1204 } 1205 } 1206 1207 // Write row start 1208 if (!$this->useInlineCss) { 1209 $html .= ' <tr class="row' . $row . '">' . PHP_EOL; 1210 } else { 1211 $style = isset($this->cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $row]) 1212 ? $this->assembleCSS($this->cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $row]) : ''; 1213 1214 $html .= ' <tr style="' . $style . '">' . PHP_EOL; 1215 } 1216 1217 return $html; 1218 } 1219 1220 private function generateRowCellCss(Worksheet $worksheet, string $cellAddress, int $row, int $columnNumber): array 1221 { 1222 $cell = ($cellAddress > '') ? $worksheet->getCellCollection()->get($cellAddress) : ''; 1223 $coordinate = Coordinate::stringFromColumnIndex($columnNumber + 1) . ($row + 1); 1224 if (!$this->useInlineCss) { 1225 $cssClass = 'column' . $columnNumber; 1226 } else { 1227 $cssClass = []; 1228 // The statements below do nothing. 1229 // Commenting out the code rather than deleting it 1230 // in case someone can figure out what their intent was. 1231 //if ($cellType == 'th') { 1232 // if (isset($this->cssStyles['table.sheet' . $sheetIndex . ' th.column' . $colNum])) { 1233 // $this->cssStyles['table.sheet' . $sheetIndex . ' th.column' . $colNum]; 1234 // } 1235 //} else { 1236 // if (isset($this->cssStyles['table.sheet' . $sheetIndex . ' td.column' . $colNum])) { 1237 // $this->cssStyles['table.sheet' . $sheetIndex . ' td.column' . $colNum]; 1238 // } 1239 //} 1240 // End of mystery statements. 1241 } 1242 1243 return [$cell, $cssClass, $coordinate]; 1244 } 1245 1246 private function generateRowCellDataValueRich(Cell $cell, string &$cellData): void 1247 { 1248 // Loop through rich text elements 1249 $elements = $cell->getValue()->getRichTextElements(); 1250 foreach ($elements as $element) { 1251 // Rich text start? 1252 if ($element instanceof Run) { 1253 $cellEnd = ''; 1254 if ($element->getFont() !== null) { 1255 $cellData .= '<span style="' . $this->assembleCSS($this->createCSSStyleFont($element->getFont())) . '">'; 1256 1257 if ($element->getFont()->getSuperscript()) { 1258 $cellData .= '<sup>'; 1259 $cellEnd = '</sup>'; 1260 } elseif ($element->getFont()->getSubscript()) { 1261 $cellData .= '<sub>'; 1262 $cellEnd = '</sub>'; 1263 } 1264 } 1265 1266 // Convert UTF8 data to PCDATA 1267 $cellText = $element->getText(); 1268 $cellData .= htmlspecialchars($cellText, Settings::htmlEntityFlags()); 1269 1270 $cellData .= $cellEnd; 1271 1272 $cellData .= '</span>'; 1273 } else { 1274 // Convert UTF8 data to PCDATA 1275 $cellText = $element->getText(); 1276 $cellData .= htmlspecialchars($cellText, Settings::htmlEntityFlags()); 1277 } 1278 } 1279 } 1280 1281 private function generateRowCellDataValue(Worksheet $worksheet, Cell $cell, string &$cellData): void 1282 { 1283 if ($cell->getValue() instanceof RichText) { 1284 $this->generateRowCellDataValueRich($cell, $cellData); 1285 } else { 1286 $origData = $this->preCalculateFormulas ? $cell->getCalculatedValue() : $cell->getValue(); 1287 $formatCode = $worksheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode(); 1288 1289 $cellData = NumberFormat::toFormattedString( 1290 $origData ?? '', 1291 $formatCode ?? NumberFormat::FORMAT_GENERAL, 1292 [$this, 'formatColor'] 1293 ); 1294 1295 if ($cellData === $origData) { 1296 $cellData = htmlspecialchars($cellData, Settings::htmlEntityFlags()); 1297 } 1298 if ($worksheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont()->getSuperscript()) { 1299 $cellData = '<sup>' . $cellData . '</sup>'; 1300 } elseif ($worksheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont()->getSubscript()) { 1301 $cellData = '<sub>' . $cellData . '</sub>'; 1302 } 1303 } 1304 } 1305 1306 /** 1307 * @param null|Cell|string $cell 1308 * @param array|string $cssClass 1309 */ 1310 private function generateRowCellData(Worksheet $worksheet, $cell, &$cssClass, string $cellType): string 1311 { 1312 $cellData = ' '; 1313 if ($cell instanceof Cell) { 1314 $cellData = ''; 1315 // Don't know what this does, and no test cases. 1316 //if ($cell->getParent() === null) { 1317 // $cell->attach($worksheet); 1318 //} 1319 // Value 1320 $this->generateRowCellDataValue($worksheet, $cell, $cellData); 1321 1322 // Converts the cell content so that spaces occuring at beginning of each new line are replaced by 1323 // Example: " Hello\n to the world" is converted to " Hello\n to the world" 1324 $cellData = (string) preg_replace('/(?m)(?:^|\\G) /', ' ', $cellData); 1325 1326 // convert newline "\n" to '<br>' 1327 $cellData = nl2br($cellData); 1328 1329 // Extend CSS class? 1330 if (!$this->useInlineCss && is_string($cssClass)) { 1331 $cssClass .= ' style' . $cell->getXfIndex(); 1332 $cssClass .= ' ' . $cell->getDataType(); 1333 } elseif (is_array($cssClass)) { 1334 if ($cellType == 'th') { 1335 if (isset($this->cssStyles['th.style' . $cell->getXfIndex()])) { 1336 $cssClass = array_merge($cssClass, $this->cssStyles['th.style' . $cell->getXfIndex()]); 1337 } 1338 } else { 1339 if (isset($this->cssStyles['td.style' . $cell->getXfIndex()])) { 1340 $cssClass = array_merge($cssClass, $this->cssStyles['td.style' . $cell->getXfIndex()]); 1341 } 1342 } 1343 1344 // General horizontal alignment: Actual horizontal alignment depends on dataType 1345 $sharedStyle = $worksheet->getParent()->getCellXfByIndex($cell->getXfIndex()); 1346 if ( 1347 $sharedStyle->getAlignment()->getHorizontal() == Alignment::HORIZONTAL_GENERAL 1348 && isset($this->cssStyles['.' . $cell->getDataType()]['text-align']) 1349 ) { 1350 $cssClass['text-align'] = $this->cssStyles['.' . $cell->getDataType()]['text-align']; 1351 } 1352 } 1353 } else { 1354 // Use default borders for empty cell 1355 if (is_string($cssClass)) { 1356 $cssClass .= ' style0'; 1357 } 1358 } 1359 1360 return $cellData; 1361 } 1362 1363 private function generateRowIncludeCharts(Worksheet $worksheet, string $coordinate): string 1364 { 1365 return $this->includeCharts ? $this->writeChartInCell($worksheet, $coordinate) : ''; 1366 } 1367 1368 private function generateRowSpans(string $html, int $rowSpan, int $colSpan): string 1369 { 1370 $html .= ($colSpan > 1) ? (' colspan="' . $colSpan . '"') : ''; 1371 $html .= ($rowSpan > 1) ? (' rowspan="' . $rowSpan . '"') : ''; 1372 1373 return $html; 1374 } 1375 1376 /** 1377 * @param array|string $cssClass 1378 */ 1379 private function generateRowWriteCell(string &$html, Worksheet $worksheet, string $coordinate, string $cellType, string $cellData, int $colSpan, int $rowSpan, $cssClass, int $colNum, int $sheetIndex, int $row): void 1380 { 1381 // Image? 1382 $htmlx = $this->writeImageInCell($worksheet, $coordinate); 1383 // Chart? 1384 $htmlx .= $this->generateRowIncludeCharts($worksheet, $coordinate); 1385 // Column start 1386 $html .= ' <' . $cellType; 1387 if (!$this->useInlineCss && !$this->isPdf && is_string($cssClass)) { 1388 $html .= ' class="' . $cssClass . '"'; 1389 if ($htmlx) { 1390 $html .= " style='position: relative;'"; 1391 } 1392 } else { 1393 //** Necessary redundant code for the sake of \PhpOffice\PhpSpreadsheet\Writer\Pdf ** 1394 // We must explicitly write the width of the <td> element because TCPDF 1395 // does not recognize e.g. <col style="width:42pt"> 1396 if ($this->useInlineCss) { 1397 $xcssClass = is_array($cssClass) ? $cssClass : []; 1398 } else { 1399 if (is_string($cssClass)) { 1400 $html .= ' class="' . $cssClass . '"'; 1401 } 1402 $xcssClass = []; 1403 } 1404 $width = 0; 1405 $i = $colNum - 1; 1406 $e = $colNum + $colSpan - 1; 1407 while ($i++ < $e) { 1408 if (isset($this->columnWidths[$sheetIndex][$i])) { 1409 $width += $this->columnWidths[$sheetIndex][$i]; 1410 } 1411 } 1412 $xcssClass['width'] = (string) $width . 'pt'; 1413 // We must also explicitly write the height of the <td> element because TCPDF 1414 // does not recognize e.g. <tr style="height:50pt"> 1415 if (isset($this->cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $row]['height'])) { 1416 $height = $this->cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $row]['height']; 1417 $xcssClass['height'] = $height; 1418 } 1419 //** end of redundant code ** 1420 1421 if ($htmlx) { 1422 $xcssClass['position'] = 'relative'; 1423 } 1424 $html .= ' style="' . $this->assembleCSS($xcssClass) . '"'; 1425 } 1426 $html = $this->generateRowSpans($html, $rowSpan, $colSpan); 1427 1428 $html .= '>'; 1429 $html .= $htmlx; 1430 1431 $html .= $this->writeComment($worksheet, $coordinate); 1432 1433 // Cell data 1434 $html .= $cellData; 1435 1436 // Column end 1437 $html .= '</' . $cellType . '>' . PHP_EOL; 1438 } 1439 1440 /** 1441 * Generate row. 1442 * 1443 * @param array $values Array containing cells in a row 1444 * @param int $row Row number (0-based) 1445 * @param string $cellType eg: 'td' 1446 * 1447 * @return string 1448 */ 1449 private function generateRow(Worksheet $worksheet, array $values, $row, $cellType) 1450 { 1451 // Sheet index 1452 $sheetIndex = $worksheet->getParent()->getIndex($worksheet); 1453 $html = $this->generateRowStart($worksheet, $sheetIndex, $row); 1454 $generateDiv = $this->isMPdf && $worksheet->getRowDimension($row + 1)->getVisible() === false; 1455 if ($generateDiv) { 1456 $html .= '<div style="visibility:hidden; display:none;">' . PHP_EOL; 1457 } 1458 1459 // Write cells 1460 $colNum = 0; 1461 foreach ($values as $cellAddress) { 1462 [$cell, $cssClass, $coordinate] = $this->generateRowCellCss($worksheet, $cellAddress, $row, $colNum); 1463 1464 // Cell Data 1465 $cellData = $this->generateRowCellData($worksheet, $cell, $cssClass, $cellType); 1466 1467 // Hyperlink? 1468 if ($worksheet->hyperlinkExists($coordinate) && !$worksheet->getHyperlink($coordinate)->isInternal()) { 1469 $cellData = '<a href="' . htmlspecialchars($worksheet->getHyperlink($coordinate)->getUrl(), Settings::htmlEntityFlags()) . '" title="' . htmlspecialchars($worksheet->getHyperlink($coordinate)->getTooltip(), Settings::htmlEntityFlags()) . '">' . $cellData . '</a>'; 1470 } 1471 1472 // Should the cell be written or is it swallowed by a rowspan or colspan? 1473 $writeCell = !(isset($this->isSpannedCell[$worksheet->getParent()->getIndex($worksheet)][$row + 1][$colNum]) 1474 && $this->isSpannedCell[$worksheet->getParent()->getIndex($worksheet)][$row + 1][$colNum]); 1475 1476 // Colspan and Rowspan 1477 $colSpan = 1; 1478 $rowSpan = 1; 1479 if (isset($this->isBaseCell[$worksheet->getParent()->getIndex($worksheet)][$row + 1][$colNum])) { 1480 $spans = $this->isBaseCell[$worksheet->getParent()->getIndex($worksheet)][$row + 1][$colNum]; 1481 $rowSpan = $spans['rowspan']; 1482 $colSpan = $spans['colspan']; 1483 1484 // Also apply style from last cell in merge to fix borders - 1485 // relies on !important for non-none border declarations in createCSSStyleBorder 1486 $endCellCoord = Coordinate::stringFromColumnIndex($colNum + $colSpan) . ($row + $rowSpan); 1487 if (!$this->useInlineCss) { 1488 $cssClass .= ' style' . $worksheet->getCell($endCellCoord)->getXfIndex(); 1489 } 1490 } 1491 1492 // Write 1493 if ($writeCell) { 1494 $this->generateRowWriteCell($html, $worksheet, $coordinate, $cellType, $cellData, $colSpan, $rowSpan, $cssClass, $colNum, $sheetIndex, $row); 1495 } 1496 1497 // Next column 1498 ++$colNum; 1499 } 1500 1501 // Write row end 1502 if ($generateDiv) { 1503 $html .= '</div>' . PHP_EOL; 1504 } 1505 $html .= ' </tr>' . PHP_EOL; 1506 1507 // Return 1508 return $html; 1509 } 1510 1511 /** 1512 * Takes array where of CSS properties / values and converts to CSS string. 1513 * 1514 * @return string 1515 */ 1516 private function assembleCSS(array $values = []) 1517 { 1518 $pairs = []; 1519 foreach ($values as $property => $value) { 1520 $pairs[] = $property . ':' . $value; 1521 } 1522 $string = implode('; ', $pairs); 1523 1524 return $string; 1525 } 1526 1527 /** 1528 * Get images root. 1529 * 1530 * @return string 1531 */ 1532 public function getImagesRoot() 1533 { 1534 return $this->imagesRoot; 1535 } 1536 1537 /** 1538 * Set images root. 1539 * 1540 * @param string $imagesRoot 1541 * 1542 * @return $this 1543 */ 1544 public function setImagesRoot($imagesRoot) 1545 { 1546 $this->imagesRoot = $imagesRoot; 1547 1548 return $this; 1549 } 1550 1551 /** 1552 * Get embed images. 1553 * 1554 * @return bool 1555 */ 1556 public function getEmbedImages() 1557 { 1558 return $this->embedImages; 1559 } 1560 1561 /** 1562 * Set embed images. 1563 * 1564 * @param bool $embedImages 1565 * 1566 * @return $this 1567 */ 1568 public function setEmbedImages($embedImages) 1569 { 1570 $this->embedImages = $embedImages; 1571 1572 return $this; 1573 } 1574 1575 /** 1576 * Get use inline CSS? 1577 * 1578 * @return bool 1579 */ 1580 public function getUseInlineCss() 1581 { 1582 return $this->useInlineCss; 1583 } 1584 1585 /** 1586 * Set use inline CSS? 1587 * 1588 * @param bool $useInlineCss 1589 * 1590 * @return $this 1591 */ 1592 public function setUseInlineCss($useInlineCss) 1593 { 1594 $this->useInlineCss = $useInlineCss; 1595 1596 return $this; 1597 } 1598 1599 /** 1600 * Get use embedded CSS? 1601 * 1602 * @return bool 1603 * 1604 * @codeCoverageIgnore 1605 * 1606 * @deprecated no longer used 1607 */ 1608 public function getUseEmbeddedCSS() 1609 { 1610 return $this->useEmbeddedCSS; 1611 } 1612 1613 /** 1614 * Set use embedded CSS? 1615 * 1616 * @param bool $useEmbeddedCSS 1617 * 1618 * @return $this 1619 * 1620 * @codeCoverageIgnore 1621 * 1622 * @deprecated no longer used 1623 */ 1624 public function setUseEmbeddedCSS($useEmbeddedCSS) 1625 { 1626 $this->useEmbeddedCSS = $useEmbeddedCSS; 1627 1628 return $this; 1629 } 1630 1631 /** 1632 * Add color to formatted string as inline style. 1633 * 1634 * @param string $value Plain formatted value without color 1635 * @param string $format Format code 1636 * 1637 * @return string 1638 */ 1639 public function formatColor($value, $format) 1640 { 1641 // Color information, e.g. [Red] is always at the beginning 1642 $color = null; // initialize 1643 $matches = []; 1644 1645 $color_regex = '/^\\[[a-zA-Z]+\\]/'; 1646 if (preg_match($color_regex, $format, $matches)) { 1647 $color = str_replace(['[', ']'], '', $matches[0]); 1648 $color = strtolower($color); 1649 } 1650 1651 // convert to PCDATA 1652 $result = htmlspecialchars($value, Settings::htmlEntityFlags()); 1653 1654 // color span tag 1655 if ($color !== null) { 1656 $result = '<span style="color:' . $color . '">' . $result . '</span>'; 1657 } 1658 1659 return $result; 1660 } 1661 1662 /** 1663 * Calculate information about HTML colspan and rowspan which is not always the same as Excel's. 1664 */ 1665 private function calculateSpans(): void 1666 { 1667 if ($this->spansAreCalculated) { 1668 return; 1669 } 1670 // Identify all cells that should be omitted in HTML due to cell merge. 1671 // In HTML only the upper-left cell should be written and it should have 1672 // appropriate rowspan / colspan attribute 1673 $sheetIndexes = $this->sheetIndex !== null ? 1674 [$this->sheetIndex] : range(0, $this->spreadsheet->getSheetCount() - 1); 1675 1676 foreach ($sheetIndexes as $sheetIndex) { 1677 $sheet = $this->spreadsheet->getSheet($sheetIndex); 1678 1679 $candidateSpannedRow = []; 1680 1681 // loop through all Excel merged cells 1682 foreach ($sheet->getMergeCells() as $cells) { 1683 [$cells] = Coordinate::splitRange($cells); 1684 $first = $cells[0]; 1685 $last = $cells[1]; 1686 1687 [$fc, $fr] = Coordinate::indexesFromString($first); 1688 $fc = $fc - 1; 1689 1690 [$lc, $lr] = Coordinate::indexesFromString($last); 1691 $lc = $lc - 1; 1692 1693 // loop through the individual cells in the individual merge 1694 $r = $fr - 1; 1695 while ($r++ < $lr) { 1696 // also, flag this row as a HTML row that is candidate to be omitted 1697 $candidateSpannedRow[$r] = $r; 1698 1699 $c = $fc - 1; 1700 while ($c++ < $lc) { 1701 if (!($c == $fc && $r == $fr)) { 1702 // not the upper-left cell (should not be written in HTML) 1703 $this->isSpannedCell[$sheetIndex][$r][$c] = [ 1704 'baseCell' => [$fr, $fc], 1705 ]; 1706 } else { 1707 // upper-left is the base cell that should hold the colspan/rowspan attribute 1708 $this->isBaseCell[$sheetIndex][$r][$c] = [ 1709 'xlrowspan' => $lr - $fr + 1, // Excel rowspan 1710 'rowspan' => $lr - $fr + 1, // HTML rowspan, value may change 1711 'xlcolspan' => $lc - $fc + 1, // Excel colspan 1712 'colspan' => $lc - $fc + 1, // HTML colspan, value may change 1713 ]; 1714 } 1715 } 1716 } 1717 } 1718 1719 $this->calculateSpansOmitRows($sheet, $sheetIndex, $candidateSpannedRow); 1720 1721 // TODO: Same for columns 1722 } 1723 1724 // We have calculated the spans 1725 $this->spansAreCalculated = true; 1726 } 1727 1728 private function calculateSpansOmitRows(Worksheet $sheet, int $sheetIndex, array $candidateSpannedRow): void 1729 { 1730 // Identify which rows should be omitted in HTML. These are the rows where all the cells 1731 // participate in a merge and the where base cells are somewhere above. 1732 $countColumns = Coordinate::columnIndexFromString($sheet->getHighestColumn()); 1733 foreach ($candidateSpannedRow as $rowIndex) { 1734 if (isset($this->isSpannedCell[$sheetIndex][$rowIndex])) { 1735 if (count($this->isSpannedCell[$sheetIndex][$rowIndex]) == $countColumns) { 1736 $this->isSpannedRow[$sheetIndex][$rowIndex] = $rowIndex; 1737 } 1738 } 1739 } 1740 1741 // For each of the omitted rows we found above, the affected rowspans should be subtracted by 1 1742 if (isset($this->isSpannedRow[$sheetIndex])) { 1743 foreach ($this->isSpannedRow[$sheetIndex] as $rowIndex) { 1744 $adjustedBaseCells = []; 1745 $c = -1; 1746 $e = $countColumns - 1; 1747 while ($c++ < $e) { 1748 $baseCell = $this->isSpannedCell[$sheetIndex][$rowIndex][$c]['baseCell']; 1749 1750 if (!in_array($baseCell, $adjustedBaseCells, true)) { 1751 // subtract rowspan by 1 1752 --$this->isBaseCell[$sheetIndex][$baseCell[0]][$baseCell[1]]['rowspan']; 1753 $adjustedBaseCells[] = $baseCell; 1754 } 1755 } 1756 } 1757 } 1758 } 1759 1760 /** 1761 * Write a comment in the same format as LibreOffice. 1762 * 1763 * @see https://github.com/LibreOffice/core/blob/9fc9bf3240f8c62ad7859947ab8a033ac1fe93fa/sc/source/filter/html/htmlexp.cxx#L1073-L1092 1764 * 1765 * @param string $coordinate 1766 * 1767 * @return string 1768 */ 1769 private function writeComment(Worksheet $worksheet, $coordinate) 1770 { 1771 $result = ''; 1772 if (!$this->isPdf && isset($worksheet->getComments()[$coordinate])) { 1773 $sanitizer = new HTMLPurifier(); 1774 $cachePath = File::sysGetTempDir() . '/phpsppur'; 1775 if (is_dir($cachePath) || mkdir($cachePath)) { 1776 $sanitizer->config->set('Cache.SerializerPath', $cachePath); 1777 } 1778 $sanitizedString = $sanitizer->purify($worksheet->getComment($coordinate)->getText()->getPlainText()); 1779 if ($sanitizedString !== '') { 1780 $result .= '<a class="comment-indicator"></a>'; 1781 $result .= '<div class="comment">' . nl2br($sanitizedString) . '</div>'; 1782 $result .= PHP_EOL; 1783 } 1784 } 1785 1786 return $result; 1787 } 1788 1789 public function getOrientation(): ?string 1790 { 1791 // Expect Pdf classes to override this method. 1792 return $this->isPdf ? PageSetup::ORIENTATION_PORTRAIT : null; 1793 } 1794 1795 /** 1796 * Generate @page declarations. 1797 * 1798 * @param bool $generateSurroundingHTML 1799 * 1800 * @return string 1801 */ 1802 private function generatePageDeclarations($generateSurroundingHTML) 1803 { 1804 // Ensure that Spans have been calculated? 1805 $this->calculateSpans(); 1806 1807 // Fetch sheets 1808 $sheets = []; 1809 if ($this->sheetIndex === null) { 1810 $sheets = $this->spreadsheet->getAllSheets(); 1811 } else { 1812 $sheets[] = $this->spreadsheet->getSheet($this->sheetIndex); 1813 } 1814 1815 // Construct HTML 1816 $htmlPage = $generateSurroundingHTML ? ('<style type="text/css">' . PHP_EOL) : ''; 1817 1818 // Loop all sheets 1819 $sheetId = 0; 1820 foreach ($sheets as $worksheet) { 1821 $htmlPage .= "@page page$sheetId { "; 1822 $left = StringHelper::formatNumber($worksheet->getPageMargins()->getLeft()) . 'in; '; 1823 $htmlPage .= 'margin-left: ' . $left; 1824 $right = StringHelper::FormatNumber($worksheet->getPageMargins()->getRight()) . 'in; '; 1825 $htmlPage .= 'margin-right: ' . $right; 1826 $top = StringHelper::FormatNumber($worksheet->getPageMargins()->getTop()) . 'in; '; 1827 $htmlPage .= 'margin-top: ' . $top; 1828 $bottom = StringHelper::FormatNumber($worksheet->getPageMargins()->getBottom()) . 'in; '; 1829 $htmlPage .= 'margin-bottom: ' . $bottom; 1830 $orientation = $this->getOrientation() ?? $worksheet->getPageSetup()->getOrientation(); 1831 if ($orientation === PageSetup::ORIENTATION_LANDSCAPE) { 1832 $htmlPage .= 'size: landscape; '; 1833 } elseif ($orientation === PageSetup::ORIENTATION_PORTRAIT) { 1834 $htmlPage .= 'size: portrait; '; 1835 } 1836 $htmlPage .= '}' . PHP_EOL; 1837 ++$sheetId; 1838 } 1839 $htmlPage .= implode(PHP_EOL, [ 1840 '.navigation {page-break-after: always;}', 1841 '.scrpgbrk, div + div {page-break-before: always;}', 1842 '@media screen {', 1843 ' .gridlines td {border: 1px solid black;}', 1844 ' .gridlines th {border: 1px solid black;}', 1845 ' body>div {margin-top: 5px;}', 1846 ' body>div:first-child {margin-top: 0;}', 1847 ' .scrpgbrk {margin-top: 1px;}', 1848 '}', 1849 '@media print {', 1850 ' .gridlinesp td {border: 1px solid black;}', 1851 ' .gridlinesp th {border: 1px solid black;}', 1852 ' .navigation {display: none;}', 1853 '}', 1854 '', 1855 ]); 1856 $htmlPage .= $generateSurroundingHTML ? ('</style>' . PHP_EOL) : ''; 1857 1858 return $htmlPage; 1859 } 1860 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
