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