Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
  • Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
  • PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.

Differences Between: [Versions 310 and 311] [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403] [Versions 39 and 311]

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