[Summary view]

   1  <?php
   2  
   3  declare(strict_types=1);
   4  
   5  namespace OpenSpout\Writer\XLSX\Manager;
   6  
   7  use OpenSpout\Common\Entity\Comment\Comment;
   8  use OpenSpout\Common\Entity\Row;
   9  use OpenSpout\Common\Helper\Escaper;
  10  use OpenSpout\Writer\Common\Entity\Worksheet;
  11  use OpenSpout\Writer\Common\Helper\CellHelper;
  12  
  13  /**
  14   * @internal
  15   *
  16   * This manager takes care of comments: writing them into two files:
  17   *  - commentsX.xml, containing the actual (rich) text of the comment
  18   *  - drawings/drawingX.vml, containing the layout of the panel showing the comment
  19   *
  20   * Each worksheet gets its unique set of 2 files, this class will make sure that these
  21   * files are created, closed and filled with the required data.
  22   */
  23  final class CommentsManager
  24  {
  25      public const COMMENTS_XML_FILE_HEADER = <<<'EOD'
  26          <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  27          <comments xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  28              <authors><author>Unknown</author></authors>
  29              <commentList>
  30          EOD;
  31  
  32      public const COMMENTS_XML_FILE_FOOTER = <<<'EOD'
  33              </commentList>
  34          </comments>
  35          EOD;
  36  
  37      public const DRAWINGS_VML_FILE_HEADER = <<<'EOD'
  38          <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  39          <xml xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel">
  40            <o:shapelayout v:ext="edit">
  41              <o:idmap v:ext="edit" data="1"/>
  42            </o:shapelayout>
  43            <v:shapetype id="_x0000_t202" coordsize="21600,21600" o:spt="202" path="m,l,21600r21600,l21600,xe">
  44              <v:stroke joinstyle="miter"/>
  45              <v:path gradientshapeok="t" o:connecttype="rect"/>
  46            </v:shapetype>
  47          EOD;
  48  
  49      public const DRAWINGS_VML_FILE_FOOTER = <<<'EOD'
  50          </xml>
  51          EOD;
  52  
  53      /**
  54       * File-pointers to the commentsX.xml files, where the index is the id of the worksheet.
  55       *
  56       * @var resource[]
  57       */
  58      private array $commentsFilePointers = [];
  59  
  60      /**
  61       * File-pointers to the vmlDrawingX.vml files, where the index is the id of the worksheet.
  62       *
  63       * @var resource[]
  64       */
  65      private array $drawingFilePointers = [];
  66  
  67      private string $xlFolder;
  68  
  69      private int $shapeId = 1024;
  70  
  71      private Escaper\XLSX $stringsEscaper;
  72  
  73      /**
  74       * @param string $xlFolder Path to the "xl" folder
  75       */
  76      public function __construct(string $xlFolder, Escaper\XLSX $stringsEscaper)
  77      {
  78          $this->xlFolder = $xlFolder;
  79          $this->stringsEscaper = $stringsEscaper;
  80      }
  81  
  82      /**
  83       * Create the two comment-files for the given worksheet.
  84       */
  85      public function createWorksheetCommentFiles(Worksheet $sheet): void
  86      {
  87          $sheetId = $sheet->getId();
  88          $commentFp = fopen($this->getCommentsFilePath($sheet), 'w');
  89          \assert(false !== $commentFp);
  90  
  91          $drawingFp = fopen($this->getDrawingFilePath($sheet), 'w');
  92          \assert(false !== $drawingFp);
  93  
  94          fwrite($commentFp, self::COMMENTS_XML_FILE_HEADER);
  95          fwrite($drawingFp, self::DRAWINGS_VML_FILE_HEADER);
  96  
  97          $this->commentsFilePointers[$sheetId] = $commentFp;
  98          $this->drawingFilePointers[$sheetId] = $drawingFp;
  99      }
 100  
 101      /**
 102       * Close the two comment-files for the given worksheet.
 103       */
 104      public function closeWorksheetCommentFiles(Worksheet $sheet): void
 105      {
 106          $sheetId = $sheet->getId();
 107  
 108          $commentFp = $this->commentsFilePointers[$sheetId];
 109          $drawingFp = $this->drawingFilePointers[$sheetId];
 110  
 111          fwrite($commentFp, self::COMMENTS_XML_FILE_FOOTER);
 112          fwrite($drawingFp, self::DRAWINGS_VML_FILE_FOOTER);
 113  
 114          fclose($commentFp);
 115          fclose($drawingFp);
 116      }
 117  
 118      public function addComments(Worksheet $worksheet, Row $row): void
 119      {
 120          $rowIndexZeroBased = 0 + $worksheet->getLastWrittenRowIndex();
 121          foreach ($row->getCells() as $columnIndexZeroBased => $cell) {
 122              if (null === $cell->comment) {
 123                  continue;
 124              }
 125  
 126              $this->addXmlComment($worksheet->getId(), $rowIndexZeroBased, $columnIndexZeroBased, $cell->comment);
 127              $this->addVmlComment($worksheet->getId(), $rowIndexZeroBased, $columnIndexZeroBased, $cell->comment);
 128          }
 129      }
 130  
 131      /**
 132       * @return string The file path where the comments for the given sheet will be stored
 133       */
 134      private function getCommentsFilePath(Worksheet $sheet): string
 135      {
 136          return $this->xlFolder.\DIRECTORY_SEPARATOR.'comments'.$sheet->getId().'.xml';
 137      }
 138  
 139      /**
 140       * @return string The file path where the VML comments for the given sheet will be stored
 141       */
 142      private function getDrawingFilePath(Worksheet $sheet): string
 143      {
 144          return $this->xlFolder.\DIRECTORY_SEPARATOR.'drawings'.\DIRECTORY_SEPARATOR.'vmlDrawing'.$sheet->getId().'.vml';
 145      }
 146  
 147      /**
 148       * Add a comment to the commentsX.xml file.
 149       *
 150       * @param int     $sheetId              The id of the sheet (starting with 1)
 151       * @param int     $rowIndexZeroBased    The row index, starting at 0, of the cell with the comment
 152       * @param int     $columnIndexZeroBased The column index, starting at 0, of the cell with the comment
 153       * @param Comment $comment              The actual comment
 154       */
 155      private function addXmlComment(int $sheetId, int $rowIndexZeroBased, int $columnIndexZeroBased, Comment $comment): void
 156      {
 157          $commentsFilePointer = $this->commentsFilePointers[$sheetId];
 158          $rowIndexOneBased = $rowIndexZeroBased + 1;
 159          $columnLetters = CellHelper::getColumnLettersFromColumnIndex($columnIndexZeroBased);
 160  
 161          $commentxml = '<comment ref="'.$columnLetters.$rowIndexOneBased.'" authorId="0"><text>';
 162          foreach ($comment->getTextRuns() as $line) {
 163              $commentxml .= '<r>';
 164              $commentxml .= '  <rPr>';
 165              if ($line->bold) {
 166                  $commentxml .= '    <b/>';
 167              }
 168              if ($line->italic) {
 169                  $commentxml .= '    <i/>';
 170              }
 171              $commentxml .= '    <sz val="'.$line->fontSize.'"/>';
 172              $commentxml .= '    <color rgb="'.$line->fontColor.'"/>';
 173              $commentxml .= '    <rFont val="'.$line->fontName.'"/>';
 174              $commentxml .= '    <family val="2"/>';
 175              $commentxml .= '  </rPr>';
 176              $commentxml .= '  <t xml:space="preserve">'.$this->stringsEscaper->escape($line->text).'</t>';
 177              $commentxml .= '</r>';
 178          }
 179          $commentxml .= '</text></comment>';
 180  
 181          fwrite($commentsFilePointer, $commentxml);
 182      }
 183  
 184      /**
 185       * Add a comment to the vmlDrawingX.vml file.
 186       *
 187       * @param int     $sheetId              The id of the sheet (starting with 1)
 188       * @param int     $rowIndexZeroBased    The row index, starting at 0, of the cell with the comment
 189       * @param int     $columnIndexZeroBased The column index, starting at 0, of the cell with the comment
 190       * @param Comment $comment              The actual comment
 191       */
 192      private function addVmlComment(int $sheetId, int $rowIndexZeroBased, int $columnIndexZeroBased, Comment $comment): void
 193      {
 194          $drawingFilePointer = $this->drawingFilePointers[$sheetId];
 195          ++$this->shapeId;
 196  
 197          $style = 'position:absolute;z-index:1';
 198          $style .= ';margin-left:'.$comment->marginLeft;
 199          $style .= ';margin-top:'.$comment->marginTop;
 200          $style .= ';width:'.$comment->width;
 201          $style .= ';height:'.$comment->height;
 202          if (!$comment->visible) {
 203              $style .= ';visibility:hidden';
 204          }
 205  
 206          $drawingVml = '<v:shape id="_x0000_s'.$this->shapeId.'"';
 207          $drawingVml .= ' type="#_x0000_t202" style="'.$style.'" fillcolor="'.$comment->fillColor.'" o:insetmode="auto">';
 208          $drawingVml .= '<v:fill color2="'.$comment->fillColor.'"/>';
 209          $drawingVml .= '<v:shadow on="t" color="black" obscured="t"/>';
 210          $drawingVml .= '<v:path o:connecttype="none"/>';
 211          $drawingVml .= '<v:textbox style="mso-direction-alt:auto">';
 212          $drawingVml .= '  <div style="text-align:left"/>';
 213          $drawingVml .= '</v:textbox>';
 214          $drawingVml .= '<x:ClientData ObjectType="Note">';
 215          $drawingVml .= '  <x:MoveWithCells/>';
 216          $drawingVml .= '  <x:SizeWithCells/>';
 217          $drawingVml .= '  <x:AutoFill>False</x:AutoFill>';
 218          $drawingVml .= '  <x:Row>'.$rowIndexZeroBased.'</x:Row>';
 219          $drawingVml .= '  <x:Column>'.$columnIndexZeroBased.'</x:Column>';
 220          $drawingVml .= '</x:ClientData>';
 221          $drawingVml .= '</v:shape>';
 222  
 223          fwrite($drawingFilePointer, $drawingVml);
 224      }
 225  }