[Summary view]

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