Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 4.2.x will end 22 April 2024 (12 months).
  • Bug fixes for security issues in 4.2.x will end 7 October 2024 (18 months).
  • PHP version: minimum PHP 8.0.0 Note: minimum PHP version has increased since Moodle 4.1. PHP 8.1.x is supported too.

Differences Between: [Versions 402 and 403]

   1  <?php
   2  
   3  declare(strict_types=1);
   4  
   5  namespace OpenSpout\Writer\XLSX\Manager;
   6  
   7  use OpenSpout\Common\Entity\Cell;
   8  use OpenSpout\Common\Entity\Row;
   9  use OpenSpout\Common\Entity\Style\Style;
  10  use OpenSpout\Common\Exception\InvalidArgumentException;
  11  use OpenSpout\Common\Exception\IOException;
  12  use OpenSpout\Common\Helper\Escaper\XLSX as XLSXEscaper;
  13  use OpenSpout\Common\Helper\StringHelper;
  14  use OpenSpout\Writer\Common\Entity\Worksheet;
  15  use OpenSpout\Writer\Common\Helper\CellHelper;
  16  use OpenSpout\Writer\Common\Manager\RegisteredStyle;
  17  use OpenSpout\Writer\Common\Manager\Style\StyleMerger;
  18  use OpenSpout\Writer\Common\Manager\WorksheetManagerInterface;
  19  use OpenSpout\Writer\XLSX\Helper\DateHelper;
  20  use OpenSpout\Writer\XLSX\Manager\Style\StyleManager;
  21  use OpenSpout\Writer\XLSX\Options;
  22  
  23  /**
  24   * @internal
  25   */
  26  final class WorksheetManager implements WorksheetManagerInterface
  27  {
  28      /**
  29       * Maximum number of characters a cell can contain.
  30       *
  31       * @see https://support.office.com/en-us/article/Excel-specifications-and-limits-16c69c74-3d6a-4aaf-ba35-e6eb276e8eaa [Excel 2007]
  32       * @see https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 [Excel 2010]
  33       * @see https://support.office.com/en-us/article/Excel-specifications-and-limits-ca36e2dc-1f09-4620-b726-67c00b05040f [Excel 2013/2016]
  34       */
  35      public const MAX_CHARACTERS_PER_CELL = 32767;
  36  
  37      /** @var CommentsManager Manages comments */
  38      private CommentsManager $commentsManager;
  39  
  40      private Options $options;
  41  
  42      /** @var StyleManager Manages styles */
  43      private StyleManager $styleManager;
  44  
  45      /** @var StyleMerger Helper to merge styles together */
  46      private StyleMerger $styleMerger;
  47  
  48      /** @var SharedStringsManager Helper to write shared strings */
  49      private SharedStringsManager $sharedStringsManager;
  50  
  51      /** @var XLSXEscaper Strings escaper */
  52      private XLSXEscaper $stringsEscaper;
  53  
  54      /** @var StringHelper String helper */
  55      private StringHelper $stringHelper;
  56  
  57      /**
  58       * WorksheetManager constructor.
  59       */
  60      public function __construct(
  61          Options $options,
  62          StyleManager $styleManager,
  63          StyleMerger $styleMerger,
  64          CommentsManager $commentsManager,
  65          SharedStringsManager $sharedStringsManager,
  66          XLSXEscaper $stringsEscaper,
  67          StringHelper $stringHelper
  68      ) {
  69          $this->options = $options;
  70          $this->styleManager = $styleManager;
  71          $this->styleMerger = $styleMerger;
  72          $this->commentsManager = $commentsManager;
  73          $this->sharedStringsManager = $sharedStringsManager;
  74          $this->stringsEscaper = $stringsEscaper;
  75          $this->stringHelper = $stringHelper;
  76      }
  77  
  78      public function getSharedStringsManager(): SharedStringsManager
  79      {
  80          return $this->sharedStringsManager;
  81      }
  82  
  83      /**
  84       * {@inheritdoc}
  85       */
  86      public function startSheet(Worksheet $worksheet): void
  87      {
  88          $sheetFilePointer = fopen($worksheet->getFilePath(), 'w');
  89          \assert(false !== $sheetFilePointer);
  90  
  91          $worksheet->setFilePointer($sheetFilePointer);
  92          $this->commentsManager->createWorksheetCommentFiles($worksheet);
  93      }
  94  
  95      /**
  96       * {@inheritdoc}
  97       */
  98      public function addRow(Worksheet $worksheet, Row $row): void
  99      {
 100          if (!$row->isEmpty()) {
 101              $this->addNonEmptyRow($worksheet, $row);
 102              $this->commentsManager->addComments($worksheet, $row);
 103          }
 104  
 105          $worksheet->setLastWrittenRowIndex($worksheet->getLastWrittenRowIndex() + 1);
 106      }
 107  
 108      /**
 109       * {@inheritdoc}
 110       */
 111      public function close(Worksheet $worksheet): void
 112      {
 113          $this->commentsManager->closeWorksheetCommentFiles($worksheet);
 114          fclose($worksheet->getFilePointer());
 115      }
 116  
 117      /**
 118       * Adds non empty row to the worksheet.
 119       *
 120       * @param Worksheet $worksheet The worksheet to add the row to
 121       * @param Row       $row       The row to be written
 122       *
 123       * @throws InvalidArgumentException If a cell value's type is not supported
 124       * @throws IOException              If the data cannot be written
 125       */
 126      private function addNonEmptyRow(Worksheet $worksheet, Row $row): void
 127      {
 128          $sheetFilePointer = $worksheet->getFilePointer();
 129          $rowStyle = $row->getStyle();
 130          $rowIndexOneBased = $worksheet->getLastWrittenRowIndex() + 1;
 131          $numCells = $row->getNumCells();
 132  
 133          $rowHeight = $row->getHeight();
 134          $hasCustomHeight = ($this->options->DEFAULT_ROW_HEIGHT > 0 || $rowHeight > 0) ? '1' : '0';
 135          $rowXML = "<row r=\"{$rowIndexOneBased}\" spans=\"1:{$numCells}\" ".($rowHeight > 0 ? "ht=\"{$rowHeight}\" " : '')."customHeight=\"{$hasCustomHeight}\">";
 136  
 137          foreach ($row->getCells() as $columnIndexZeroBased => $cell) {
 138              $registeredStyle = $this->applyStyleAndRegister($cell, $rowStyle);
 139              $cellStyle = $registeredStyle->getStyle();
 140              if ($registeredStyle->isMatchingRowStyle()) {
 141                  $rowStyle = $cellStyle; // Replace actual rowStyle (possibly with null id) by registered style (with id)
 142              }
 143              $rowXML .= $this->getCellXML($rowIndexOneBased, $columnIndexZeroBased, $cell, $cellStyle->getId());
 144          }
 145  
 146          $rowXML .= '</row>';
 147  
 148          $wasWriteSuccessful = fwrite($sheetFilePointer, $rowXML);
 149          if (false === $wasWriteSuccessful) {
 150              throw new IOException("Unable to write data in {$worksheet->getFilePath()}");
 151          }
 152      }
 153  
 154      /**
 155       * Applies styles to the given style, merging the cell's style with its row's style.
 156       *
 157       * @throws InvalidArgumentException If the given value cannot be processed
 158       */
 159      private function applyStyleAndRegister(Cell $cell, Style $rowStyle): RegisteredStyle
 160      {
 161          $isMatchingRowStyle = false;
 162          if ($cell->getStyle()->isEmpty()) {
 163              $cell->setStyle($rowStyle);
 164  
 165              $possiblyUpdatedStyle = $this->styleManager->applyExtraStylesIfNeeded($cell);
 166  
 167              if ($possiblyUpdatedStyle->isUpdated()) {
 168                  $registeredStyle = $this->styleManager->registerStyle($possiblyUpdatedStyle->getStyle());
 169              } else {
 170                  $registeredStyle = $this->styleManager->registerStyle($rowStyle);
 171                  $isMatchingRowStyle = true;
 172              }
 173          } else {
 174              $mergedCellAndRowStyle = $this->styleMerger->merge($cell->getStyle(), $rowStyle);
 175              $cell->setStyle($mergedCellAndRowStyle);
 176  
 177              $possiblyUpdatedStyle = $this->styleManager->applyExtraStylesIfNeeded($cell);
 178  
 179              if ($possiblyUpdatedStyle->isUpdated()) {
 180                  $newCellStyle = $possiblyUpdatedStyle->getStyle();
 181              } else {
 182                  $newCellStyle = $mergedCellAndRowStyle;
 183              }
 184  
 185              $registeredStyle = $this->styleManager->registerStyle($newCellStyle);
 186          }
 187  
 188          return new RegisteredStyle($registeredStyle, $isMatchingRowStyle);
 189      }
 190  
 191      /**
 192       * Builds and returns xml for a single cell.
 193       *
 194       * @throws InvalidArgumentException If the given value cannot be processed
 195       */
 196      private function getCellXML(int $rowIndexOneBased, int $columnIndexZeroBased, Cell $cell, ?int $styleId): string
 197      {
 198          $columnLetters = CellHelper::getColumnLettersFromColumnIndex($columnIndexZeroBased);
 199          $cellXML = '<c r="'.$columnLetters.$rowIndexOneBased.'"';
 200          $cellXML .= ' s="'.$styleId.'"';
 201  
 202          if ($cell instanceof Cell\StringCell) {
 203              $cellXML .= $this->getCellXMLFragmentForNonEmptyString($cell->getValue());
 204          } elseif ($cell instanceof Cell\BooleanCell) {
 205              $cellXML .= ' t="b"><v>'.(int) $cell->getValue().'</v></c>';
 206          } elseif ($cell instanceof Cell\NumericCell) {
 207              $cellXML .= '><v>'.$cell->getValue().'</v></c>';
 208          } elseif ($cell instanceof Cell\FormulaCell) {
 209              $cellXML .= '><f>'.substr($cell->getValue(), 1).'</f></c>';
 210          } elseif ($cell instanceof Cell\DateTimeCell) {
 211              $cellXML .= '><v>'.DateHelper::toExcel($cell->getValue()).'</v></c>';
 212          } elseif ($cell instanceof Cell\ErrorCell) {
 213              // only writes the error value if it's a string
 214              $cellXML .= ' t="e"><v>'.$cell->getRawValue().'</v></c>';
 215          } elseif ($cell instanceof Cell\EmptyCell) {
 216              if ($this->styleManager->shouldApplyStyleOnEmptyCell($styleId)) {
 217                  $cellXML .= '/>';
 218              } else {
 219                  // don't write empty cells that do no need styling
 220                  // NOTE: not appending to $cellXML is the right behavior!!
 221                  $cellXML = '';
 222              }
 223          }
 224  
 225          return $cellXML;
 226      }
 227  
 228      /**
 229       * Returns the XML fragment for a cell containing a non empty string.
 230       *
 231       * @param string $cellValue The cell value
 232       *
 233       * @return string The XML fragment representing the cell
 234       *
 235       * @throws InvalidArgumentException If the string exceeds the maximum number of characters allowed per cell
 236       */
 237      private function getCellXMLFragmentForNonEmptyString(string $cellValue): string
 238      {
 239          if ($this->stringHelper->getStringLength($cellValue) > self::MAX_CHARACTERS_PER_CELL) {
 240              throw new InvalidArgumentException('Trying to add a value that exceeds the maximum number of characters allowed in a cell (32,767)');
 241          }
 242  
 243          if ($this->options->SHOULD_USE_INLINE_STRINGS) {
 244              $cellXMLFragment = ' t="inlineStr"><is><t>'.$this->stringsEscaper->escape($cellValue).'</t></is></c>';
 245          } else {
 246              $sharedStringId = $this->sharedStringsManager->writeString($cellValue);
 247              $cellXMLFragment = ' t="s"><v>'.$sharedStringId.'</v></c>';
 248          }
 249  
 250          return $cellXMLFragment;
 251      }
 252  }