Search moodle.org's
Developer Documentation

See Release Notes
Long Term Support Release

  • Bug fixes for general core bugs in 3.9.x will end* 10 May 2021 (12 months).
  • Bug fixes for security issues in 3.9.x will end* 8 May 2023 (36 months).
  • PHP version: minimum PHP 7.2.0 Note: minimum PHP version has increased since Moodle 3.8. PHP 7.3.x and 7.4.x are supported too.

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? (&lt;style&gt; and &lt;/style&gt;)
 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 = '&nbsp;';
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 &nbsp;
1286                  // Example: "  Hello\n to the world" is converted to "&nbsp;&nbsp;Hello\n&nbsp;to the world"
1287                  $cellData = preg_replace('/(?m)(?:^|\\G) /', '&nbsp;', $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  }