[Summary view]

Differences Between: [Versions 310 and 401] [Versions 39 and 401]

   1  <?php
   2  
   3  namespace Box\Spout\Writer\XLSX\Manager;
   4  
   5  use Box\Spout\Common\Entity\Cell;
   6  use Box\Spout\Common\Entity\Row;
   7  use Box\Spout\Common\Entity\Style\Style;
   8  use Box\Spout\Common\Exception\InvalidArgumentException;
   9  use Box\Spout\Common\Exception\IOException;
  10  use Box\Spout\Common\Helper\Escaper\XLSX as XLSXEscaper;
  11  use Box\Spout\Common\Helper\StringHelper;
  12  use Box\Spout\Common\Manager\OptionsManagerInterface;
  13  use Box\Spout\Writer\Common\Creator\InternalEntityFactory;
  14  use Box\Spout\Writer\Common\Entity\Options;
  15  use Box\Spout\Writer\Common\Entity\Worksheet;
  16  use Box\Spout\Writer\Common\Helper\CellHelper;
  17  use Box\Spout\Writer\Common\Manager\RegisteredStyle;
  18  use Box\Spout\Writer\Common\Manager\RowManager;
  19  use Box\Spout\Writer\Common\Manager\Style\StyleMerger;
  20  use Box\Spout\Writer\Common\Manager\WorksheetManagerInterface;
  21  use Box\Spout\Writer\XLSX\Manager\Style\StyleManager;
  22  
  23  /**
  24   * Class WorksheetManager
  25   * XLSX worksheet manager, providing the interfaces to work with XLSX worksheets.
  26   */
  27  class WorksheetManager implements WorksheetManagerInterface
  28  {
  29      /**
  30       * Maximum number of characters a cell can contain
  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      const MAX_CHARACTERS_PER_CELL = 32767;
  36  
  37      const SHEET_XML_FILE_HEADER = <<<'EOD'
  38  <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  39  <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  40  EOD;
  41  
  42      /** @var bool Whether inline or shared strings should be used */
  43      protected $shouldUseInlineStrings;
  44  
  45      /** @var RowManager Manages rows */
  46      private $rowManager;
  47  
  48      /** @var StyleManager Manages styles */
  49      private $styleManager;
  50  
  51      /** @var StyleMerger Helper to merge styles together */
  52      private $styleMerger;
  53  
  54      /** @var SharedStringsManager Helper to write shared strings */
  55      private $sharedStringsManager;
  56  
  57      /** @var XLSXEscaper Strings escaper */
  58      private $stringsEscaper;
  59  
  60      /** @var StringHelper String helper */
  61      private $stringHelper;
  62  
  63      /** @var InternalEntityFactory Factory to create entities */
  64      private $entityFactory;
  65  
  66      /**
  67       * WorksheetManager constructor.
  68       *
  69       * @param OptionsManagerInterface $optionsManager
  70       * @param RowManager $rowManager
  71       * @param StyleManager $styleManager
  72       * @param StyleMerger $styleMerger
  73       * @param SharedStringsManager $sharedStringsManager
  74       * @param XLSXEscaper $stringsEscaper
  75       * @param StringHelper $stringHelper
  76       * @param InternalEntityFactory $entityFactory
  77       */
  78      public function __construct(
  79          OptionsManagerInterface $optionsManager,
  80          RowManager $rowManager,
  81          StyleManager $styleManager,
  82          StyleMerger $styleMerger,
  83          SharedStringsManager $sharedStringsManager,
  84          XLSXEscaper $stringsEscaper,
  85          StringHelper $stringHelper,
  86          InternalEntityFactory $entityFactory
  87      ) {
  88          $this->shouldUseInlineStrings = $optionsManager->getOption(Options::SHOULD_USE_INLINE_STRINGS);
  89          $this->rowManager = $rowManager;
  90          $this->styleManager = $styleManager;
  91          $this->styleMerger = $styleMerger;
  92          $this->sharedStringsManager = $sharedStringsManager;
  93          $this->stringsEscaper = $stringsEscaper;
  94          $this->stringHelper = $stringHelper;
  95          $this->entityFactory = $entityFactory;
  96      }
  97  
  98      /**
  99       * @return SharedStringsManager
 100       */
 101      public function getSharedStringsManager()
 102      {
 103          return $this->sharedStringsManager;
 104      }
 105  
 106      /**
 107       * {@inheritdoc}
 108       */
 109      public function startSheet(Worksheet $worksheet)
 110      {
 111          $sheetFilePointer = \fopen($worksheet->getFilePath(), 'w');
 112          $this->throwIfSheetFilePointerIsNotAvailable($sheetFilePointer);
 113  
 114          $worksheet->setFilePointer($sheetFilePointer);
 115  
 116          \fwrite($sheetFilePointer, self::SHEET_XML_FILE_HEADER);
 117          \fwrite($sheetFilePointer, '<sheetData>');
 118      }
 119  
 120      /**
 121       * Checks if the sheet has been sucessfully created. Throws an exception if not.
 122       *
 123       * @param bool|resource $sheetFilePointer Pointer to the sheet data file or FALSE if unable to open the file
 124       * @throws IOException If the sheet data file cannot be opened for writing
 125       * @return void
 126       */
 127      private function throwIfSheetFilePointerIsNotAvailable($sheetFilePointer)
 128      {
 129          if (!$sheetFilePointer) {
 130              throw new IOException('Unable to open sheet for writing.');
 131          }
 132      }
 133  
 134      /**
 135       * {@inheritdoc}
 136       */
 137      public function addRow(Worksheet $worksheet, Row $row)
 138      {
 139          if (!$this->rowManager->isEmpty($row)) {
 140              $this->addNonEmptyRow($worksheet, $row);
 141          }
 142  
 143          $worksheet->setLastWrittenRowIndex($worksheet->getLastWrittenRowIndex() + 1);
 144      }
 145  
 146      /**
 147       * Adds non empty row to the worksheet.
 148       *
 149       * @param Worksheet $worksheet The worksheet to add the row to
 150       * @param Row $row The row to be written
 151       * @throws IOException If the data cannot be written
 152       * @throws InvalidArgumentException If a cell value's type is not supported
 153       * @return void
 154       */
 155      private function addNonEmptyRow(Worksheet $worksheet, Row $row)
 156      {
 157          $rowStyle = $row->getStyle();
 158          $rowIndexOneBased = $worksheet->getLastWrittenRowIndex() + 1;
 159          $numCells = $row->getNumCells();
 160  
 161          $rowXML = '<row r="' . $rowIndexOneBased . '" spans="1:' . $numCells . '">';
 162  
 163          foreach ($row->getCells() as $columnIndexZeroBased => $cell) {
 164              $registeredStyle = $this->applyStyleAndRegister($cell, $rowStyle);
 165              $cellStyle = $registeredStyle->getStyle();
 166              if ($registeredStyle->isMatchingRowStyle()) {
 167                  $rowStyle = $cellStyle; // Replace actual rowStyle (possibly with null id) by registered style (with id)
 168              }
 169              $rowXML .= $this->getCellXML($rowIndexOneBased, $columnIndexZeroBased, $cell, $cellStyle->getId());
 170          }
 171  
 172          $rowXML .= '</row>';
 173  
 174          $wasWriteSuccessful = \fwrite($worksheet->getFilePointer(), $rowXML);
 175          if ($wasWriteSuccessful === false) {
 176              throw new IOException("Unable to write data in {$worksheet->getFilePath()}");
 177          }
 178      }
 179  
 180      /**
 181       * Applies styles to the given style, merging the cell's style with its row's style
 182       *
 183       * @param Cell  $cell
 184       * @param Style $rowStyle
 185       *
 186       * @throws InvalidArgumentException If the given value cannot be processed
 187       * @return RegisteredStyle
 188       */
 189      private function applyStyleAndRegister(Cell $cell, Style $rowStyle) : RegisteredStyle
 190      {
 191          $isMatchingRowStyle = false;
 192          if ($cell->getStyle()->isEmpty()) {
 193              $cell->setStyle($rowStyle);
 194  
 195              $possiblyUpdatedStyle = $this->styleManager->applyExtraStylesIfNeeded($cell);
 196  
 197              if ($possiblyUpdatedStyle->isUpdated()) {
 198                  $registeredStyle = $this->styleManager->registerStyle($possiblyUpdatedStyle->getStyle());
 199              } else {
 200                  $registeredStyle = $this->styleManager->registerStyle($rowStyle);
 201                  $isMatchingRowStyle = true;
 202              }
 203          } else {
 204              $mergedCellAndRowStyle = $this->styleMerger->merge($cell->getStyle(), $rowStyle);
 205              $cell->setStyle($mergedCellAndRowStyle);
 206  
 207              $possiblyUpdatedStyle = $this->styleManager->applyExtraStylesIfNeeded($cell);
 208  
 209              if ($possiblyUpdatedStyle->isUpdated()) {
 210                  $newCellStyle = $possiblyUpdatedStyle->getStyle();
 211              } else {
 212                  $newCellStyle = $mergedCellAndRowStyle;
 213              }
 214  
 215              $registeredStyle = $this->styleManager->registerStyle($newCellStyle);
 216          }
 217  
 218          return new RegisteredStyle($registeredStyle, $isMatchingRowStyle);
 219      }
 220  
 221      /**
 222       * Builds and returns xml for a single cell.
 223       *
 224       * @param int  $rowIndexOneBased
 225       * @param int  $columnIndexZeroBased
 226       * @param Cell $cell
 227       * @param int  $styleId
 228       *
 229       * @throws InvalidArgumentException If the given value cannot be processed
 230       * @return string
 231       */
 232      private function getCellXML($rowIndexOneBased, $columnIndexZeroBased, Cell $cell, $styleId)
 233      {
 234          $columnLetters = CellHelper::getColumnLettersFromColumnIndex($columnIndexZeroBased);
 235          $cellXML = '<c r="' . $columnLetters . $rowIndexOneBased . '"';
 236          $cellXML .= ' s="' . $styleId . '"';
 237  
 238          if ($cell->isString()) {
 239              $cellXML .= $this->getCellXMLFragmentForNonEmptyString($cell->getValue());
 240          } elseif ($cell->isBoolean()) {
 241              $cellXML .= ' t="b"><v>' . (int) ($cell->getValue()) . '</v></c>';
 242          } elseif ($cell->isNumeric()) {
 243              $cellXML .= '><v>' . $this->stringHelper->formatNumericValue($cell->getValue()) . '</v></c>';
 244          } elseif ($cell->isError() && is_string($cell->getValueEvenIfError())) {
 245              // only writes the error value if it's a string
 246              $cellXML .= ' t="e"><v>' . $cell->getValueEvenIfError() . '</v></c>';
 247          } elseif ($cell->isEmpty()) {
 248              if ($this->styleManager->shouldApplyStyleOnEmptyCell($styleId)) {
 249                  $cellXML .= '/>';
 250              } else {
 251                  // don't write empty cells that do no need styling
 252                  // NOTE: not appending to $cellXML is the right behavior!!
 253                  $cellXML = '';
 254              }
 255          } else {
 256              throw new InvalidArgumentException('Trying to add a value with an unsupported type: ' . \gettype($cell->getValue()));
 257          }
 258  
 259          return $cellXML;
 260      }
 261  
 262      /**
 263       * Returns the XML fragment for a cell containing a non empty string
 264       *
 265       * @param string $cellValue The cell value
 266       * @throws InvalidArgumentException If the string exceeds the maximum number of characters allowed per cell
 267       * @return string The XML fragment representing the cell
 268       */
 269      private function getCellXMLFragmentForNonEmptyString($cellValue)
 270      {
 271          if ($this->stringHelper->getStringLength($cellValue) > self::MAX_CHARACTERS_PER_CELL) {
 272              throw new InvalidArgumentException('Trying to add a value that exceeds the maximum number of characters allowed in a cell (32,767)');
 273          }
 274  
 275          if ($this->shouldUseInlineStrings) {
 276              $cellXMLFragment = ' t="inlineStr"><is><t>' . $this->stringsEscaper->escape($cellValue) . '</t></is></c>';
 277          } else {
 278              $sharedStringId = $this->sharedStringsManager->writeString($cellValue);
 279              $cellXMLFragment = ' t="s"><v>' . $sharedStringId . '</v></c>';
 280          }
 281  
 282          return $cellXMLFragment;
 283      }
 284  
 285      /**
 286       * {@inheritdoc}
 287       */
 288      public function close(Worksheet $worksheet)
 289      {
 290          $worksheetFilePointer = $worksheet->getFilePointer();
 291  
 292          if (!\is_resource($worksheetFilePointer)) {
 293              return;
 294          }
 295  
 296          \fwrite($worksheetFilePointer, '</sheetData>');
 297          \fwrite($worksheetFilePointer, '</worksheet>');
 298          \fclose($worksheetFilePointer);
 299      }
 300  }