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