Corruption errors for saved Xlsx docs with frozen panes

[billblume]

This is:

- [x] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

Saved Xlsx documents should not generate corrupt file message when first opened.

What is the current behavior?

When one saves an Xlsx document that contains a worksheet with a frozen pane. Opening such a document may generate a corrupt file alert ("We found a problem with some content in 'foo.xlsx'. ...)

The underlying recovery log is

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <logFileName>Repair Result to foo.xml</logFileName>
  <summary>Errors were detected in file 'foo.xlsx'</summary>
  <repairedRecords summary="Following is a list of repairs:">
    <repairedRecord>Repaired Records: View from /xl/worksheets/sheet6.xml part</repairedRecord>
    <repairedRecord>Repaired Records: View from /xl/worksheets/sheet7.xml part</repairedRecord>
  </repairedRecords>
</recoveryLog>

The source of this error is a bad cell selection set for the worksheet with the frozen pane:

<sheetView tabSelected="0" workbookViewId="0" showGridLines="false" showRowColHeaders="1">
  <pane ySplit="6" topLeftCell="A24" activePane="bottomLeft" state="frozen"/>
  <selection pane="bottomLeft" activeCell="A24" sqref="F5"/>
</sheetView>

The underlying issue is that the sqref attribute of the selection element is not valid for the value specified by attribute activeCell. Excel requires activeCell be in the range of cell(s) specified in sqref.

This is a regression introduced by recent pull request #435

This pull request changes sqref to be set to the currently selected range of cells. However, Writer\Xlsx\Worksheet::writeSheetViews modifies activeCell for frozen panes to be the frozen pane's top-left cell. Unfortunately, this top-left cell is not in the range of currently selected cells resulting in an invalid selection.

What are the steps to reproduce?

Here is a unit test that reproduces the issue

    public function testFrozenPaneSelection()
    {
        // Create a dummy workbook with two worksheets
        $workbook = new Spreadsheet();
        $worksheet = $workbook->getActiveSheet();
        $worksheet->freezePane('A7', 'A24');
        $worksheet->setSelectedCells('F5');

        Settings::setLibXmlLoaderOptions(null); // reset to default options

        $resultFilename = tempnam(sys_get_temp_dir(), 'xlsx');
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($workbook);
        $writer->save($resultFilename);

        try {
            $this->assertFileExists($resultFilename);

            $resultZip = new ZipArchive();
            $resultZip->open($resultFilename);
            $worksheetXmlStr = $resultZip->getFromName('xl/worksheets/sheet1.xml');
            $worksheetXml = simplexml_load_string($worksheetXmlStr);
            $this->assertInstanceOf('SimpleXMLElement', $worksheetXml);
            $sheetViewEl = $worksheetXml->sheetViews->sheetView;

            $paneEl = $sheetViewEl->pane;
            $this->assertEquals('6', (string) $paneEl['ySplit']);
            $this->assertEquals('A24', (string) $paneEl['topLeftCell']);
            $this->assertEquals('bottomLeft', (string) $paneEl['activePane']);
            $this->assertEquals('frozen', (string) $paneEl['state']);

            $selectionEl = $sheetViewEl->selection;
            $this->assertEquals('bottomLeft', (string) $selectionEl['pane']);
            $this->assertEquals('A24', (string) $selectionEl['activeCell']);
            $this->assertEquals('A24', (string) $selectionEl['sqref']);
        } finally {
            unlink($resultFilename);
        }
    }

Which versions of PhpSpreadsheet and PHP are affected?

This is in the post 1.2.1 develop branch. At this time, it has not been released yet.

[billblume]

PR is coming