Repository: box/spout (php)

Issue:
Wrong order of empty columns (Google Spreadsheets generated XLSX)
Hi,

I'm using Spout 3.1.0 and I'm running into the issue where the reader adds empty columns to the back of the row if the XLSX file was generated by Google Spreadsheets.
[demo_spreadsheet.xlsx](https://github.com/box/spout/files/4592322/demo_spreadsheet.xlsx)

If I open the file in Microsoft Excel 2019 and (re)save it, it's fixed.

I tried enabling setShouldPreserveEmptyRows to see if it would make any difference, but it didn't.
Some test code:
```
        $reader = ReaderEntityFactory::createReaderFromFile(__DIR__.'/demo_spreadsheet.xlsx');
        //$reader->setShouldPreserveEmptyRows(true);
        $reader->open(__DIR__.'/demo_spreadsheet.xlsx');

        foreach($reader->getSheetIterator() as $sheet)
        {
            foreach($sheet->getRowIterator() as $row)
            {
                echo 'Row iterator'.PHP_EOL;
                $cells = $row->getCells();
                $data = array();
                foreach($cells as $id=> $cell)
                    $data[] = $cell->getValue();

                var_dump($data);
                var_dump($row->toArray());
                echo PHP_EOL;
            }
        }
```
The output:
[demo_spreadsheet_output.txt](https://github.com/box/spout/files/4592330/demo_spreadsheet_output.txt)

The toArray() function clearly shows the order is wrong, as the empty column with ID 1 is at the end of the array (but with the correct key).

EDIT: Just checked if this also occured with Spout 2.X, it does not. This is apparently a new bug introduced in Spout 3.(1?)

Pull request:
Skipped cells are in wrong order
This only happens when no sheet's dimension is specified.

When filling empty cells with empty strings, we push these new cells with the correct cell index but they are added at the end of the cells array (normal PHP behavior). This means that we were going from `{[0] => 'A', [2] => 'C'}` to `{[0] => 'A', [2] => 'C', [1] => ''}`. We therefore need to sort the array to get the values in the correct order ( `{[0] => 'A', [1] => '', [2] => 'C'}`).

Fixes #740 

Patch:
diff --git a/src/Spout/Reader/Common/Manager/RowManager.php b/src/Spout/Reader/Common/Manager/RowManager.php
index 623c8d8..67c8fb1 100644
--- a/src/Spout/Reader/Common/Manager/RowManager.php
+++ b/src/Spout/Reader/Common/Manager/RowManager.php
@@ -56,12 +56,25 @@ class RowManager
         $rowCells = $row->getCells();
         $maxCellIndex = $numCells;
 
+        // If the row has empty cells, calling "setCellAtIndex" will add the cell
+        // but in the wrong place (the new cell is added at the end of the array).
+        // Therefore, we need to sort the array using keys to have proper order.
+        // @see https://github.com/box/spout/issues/740
+        $needsSorting = false;
+
         for ($cellIndex = 0; $cellIndex < $maxCellIndex; $cellIndex++) {
             if (!isset($rowCells[$cellIndex])) {
                 $row->setCellAtIndex($this->entityFactory->createCell(''), $cellIndex);
+                $needsSorting = true;
             }
         }
 
+        if ($needsSorting) {
+            $rowCells = $row->getCells();
+            ksort($rowCells);
+            $row->setCells($rowCells);
+        }
+
         return $row;
     }
 }


Test patch:
diff --git a/tests/Spout/Reader/XLSX/ReaderTest.php b/tests/Spout/Reader/XLSX/ReaderTest.php
index af7ca37..5b155b4 100644
--- a/tests/Spout/Reader/XLSX/ReaderTest.php
+++ b/tests/Spout/Reader/XLSX/ReaderTest.php
@@ -692,13 +692,29 @@ class ReaderTest extends TestCase
         $allRows = $this->getAllRowsForFile('sheet_with_empty_cells.xlsx');
 
         $expectedRows = [
-            ['A', 'B', 'C'],
+            ['A', '', 'C'],
             ['0', '', ''],
             ['1', '1', ''],
         ];
         $this->assertEquals($expectedRows, $allRows, 'There should be 3 rows, with equal length');
     }
 
+    /**
+     * https://github.com/box/spout/issues/184
+     * @return void
+     */
+    public function testReadShouldCreateOutputEmptyCellPreservedWhenNoDimensionsSpecified()
+    {
+        $allRows = $this->getAllRowsForFile('sheet_with_empty_cells_without_dimensions.xlsx');
+
+        $expectedRows = [
+            ['A', '', 'C'],
+            ['0'],
+            ['1', '1'],
+        ];
+        $this->assertEquals($expectedRows, $allRows);
+    }
+
     /**
      * https://github.com/box/spout/issues/195
      * @return void