Add support for cells in error when writing XLSX and ODS

When appending data to an existing sheet, it was possible to get cells in error when reading (DIV/0 for instance). When trying to write them back, `addRow` would throw because `Cell`s in error were not supported by the writers.
box · Dec 2, 2019 · 7964dad · 7964dad
1 parent eb88bb4
commit 7964dad
Show file tree

Hide file tree

Showing 5 changed files with 53 additions and 0 deletions.
diff --git a/src/Spout/Common/Entity/Cell.php b/src/Spout/Common/Entity/Cell.php
@@ -91,6  91,14 @@ public function getValue()
         return !$this->isError() ? $this->value : null;
     }
 
     /**
      * @return mixed
      */
     public function getValueEvenIfError()
     {
         return $this->value;
     }
 
     /**
      * @param Style|null $style
      */

diff --git a/src/Spout/Writer/ODS/Manager/WorksheetManager.php b/src/Spout/Writer/ODS/Manager/WorksheetManager.php
@@ -204,6  204,11 @@ private function getCellXML(Cell $cell, $styleIndex, $numTimesValueRepeated)
             $data .= ' office:value-type="float" calcext:value-type="float" office:value="' . $cell->getValue() . '">';
             $data .= '<text:p>' . $cell->getValue() . '</text:p>';
             $data .= '</table:table-cell>';
         } elseif ($cell->isError() && is_string($cell->getValueEvenIfError())) {
             // only writes the error value if it's a string
             $data .= ' office:value-type="string" calcext:value-type="error" office:value="">';
             $data .= '<text:p>' . $cell->getValueEvenIfError() . '</text:p>';
             $data .= '</table:table-cell>';
         } elseif ($cell->isEmpty()) {
             $data .= '/>';
         } else {

diff --git a/src/Spout/Writer/XLSX/Manager/WorksheetManager.php b/src/Spout/Writer/XLSX/Manager/WorksheetManager.php
@@ -218,6  218,9 @@ private function getCellXML($rowIndexOneBased, $columnIndexZeroBased, Cell $cell
             $cellXML .= ' t="b"><v>' . (int) ($cell->getValue()) . '</v></c>';
         } elseif ($cell->isNumeric()) {
             $cellXML .= '><v>' . $cell->getValue() . '</v></c>';
         } elseif ($cell->isError() && is_string($cell->getValueEvenIfError())) {
             // only writes the error value if it's a string
             $cellXML .= ' t="e"><v>' . $cell->getValueEvenIfError() . '</v></c>';
         } elseif ($cell->isEmpty()) {
             if ($this->styleManager->shouldApplyStyleOnEmptyCell($styleId)) {
                 $cellXML .= '/>';

diff --git a/tests/Spout/Writer/ODS/WriterTest.php b/tests/Spout/Writer/ODS/WriterTest.php
@@ -2,6  2,7 @@
 
 namespace Box\Spout\Writer\ODS;
 
 use Box\Spout\Common\Entity\Cell;
 use Box\Spout\Common\Entity\Row;
 use Box\Spout\Common\Exception\InvalidArgumentException;
 use Box\Spout\Common\Exception\IOException;
@@ -321,6  322,24 @@ public function testAddRowShouldUseNumberColumnsRepeatedForRepeatedValues($dataR
         }
     }
 
     /**
      * @return void
      */
     public function testAddRowShouldSupportCellInError()
     {
         $fileName = 'test_add_row_should_support_cell_in_error.ods';
 
         $cell = WriterEntityFactory::createCell('#DIV/0');
         $cell->setType(Cell::TYPE_ERROR);
 
         $row = WriterEntityFactory::createRow([$cell]);
 
         $this->writeToODSFile([$row], $fileName);
 
         $this->assertValueWasWritten($fileName, 'calcext:value-type="error"');
         $this->assertValueWasWritten($fileName, '<text:p>#DIV/0</text:p>');
     }
 
     /**
      * @return void
      */

diff --git a/tests/Spout/Writer/XLSX/WriterTest.php b/tests/Spout/Writer/XLSX/WriterTest.php
@@ -2,6  2,7 @@
 
 namespace Box\Spout\Writer\XLSX;
 
 use Box\Spout\Common\Entity\Cell;
 use Box\Spout\Common\Entity\Row;
 use Box\Spout\Common\Exception\InvalidArgumentException;
 use Box\Spout\Common\Exception\IOException;
@@ -375,6  376,23 @@ public function testAddRowShouldSupportMultipleTypesOfData()
         $this->assertInlineDataWasWrittenToSheet($fileName, 1, 10.2);
     }
 
     /**
      * @return void
      */
     public function testAddRowShouldSupportCellInError()
     {
         $fileName = 'test_add_row_should_support_cell_in_error.xlsx';
 
         $cell = WriterEntityFactory::createCell('#DIV/0');
         $cell->setType(Cell::TYPE_ERROR);
 
         $row = WriterEntityFactory::createRow([$cell]);
 
         $this->writeToXLSXFile([$row], $fileName);
 
         $this->assertInlineDataWasWrittenToSheet($fileName, 1, 't="e"><v>#DIV/0</v>');
     }
 
     /**
      * @return void
      */