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