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