Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Setting selected cells sometimes has no effect with XLSX writer #1887

Closed
francescom opened this issue Mar 1, 2021 · 6 comments
Closed

Setting selected cells sometimes has no effect with XLSX writer #1887

francescom opened this issue Mar 1, 2021 · 6 comments

Comments

@francescom
Copy link

francescom commented Mar 1, 2021 •
edited
Loading

This is:

  • [ X ] a bug report

What is the expected behavior?

At the end of the creation of a multiworksheet Spreadsheet I use setSelectedCells to leave the way the client expected: with one specific cell selected. The expected behaviour is that opening the generated file with Excel the user finds the cell selected

What is the current behavior?

The current behaviour is that on the first worksheet the right cell is selectred while on subsequent (different) worksheets the selected cell is the bottom right cell (the last written). When the file is saved, the functions take care of the saving, call getStyle() that calls setSelectedCells() altering the selection of the worksheets.

I managed to track the changing of the selection to an unwanted value inserting some debug code in Worksheet.php and this is a stack trace of the time the value is altered:

    Array
    (
        [0] => Array
            (
                [file] => C:\Users\fmm\Dropbox\sharedWorks\Web\www_htdocs\report\report\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Worksheet\Worksheet.php
                [line] => 1427
                [function] => setSelectedCells
                [class] => PhpOffice\PhpSpreadsheet\Worksheet\Worksheet
                [type] => ->
            )

        [1] => Array
            (
                [file] => C:\Users\fmm\Dropbox\sharedWorks\Web\www_htdocs\report\report\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Cell\Cell.php
                [line] => 532
                [function] => getStyle
                [class] => PhpOffice\PhpSpreadsheet\Worksheet\Worksheet
                [type] => ->
            )

        [2] => Array
            (
                [file] => C:\Users\fmm\Dropbox\sharedWorks\Web\www_htdocs\report\report\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php
                [line] => 3391
                [function] => getStyle
                [class] => PhpOffice\PhpSpreadsheet\Cell\Cell
                [type] => ->
            )

        [3] => Array
            (
                [file] => C:\Users\fmm\Dropbox\sharedWorks\Web\www_htdocs\report\report\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php
                [line] => 3233
                [function] => _calculateFormulaValue
                [class] => PhpOffice\PhpSpreadsheet\Calculation\Calculation
                [type] => ->
            )

        [4] => Array
            (
                [file] => C:\Users\fmm\Dropbox\sharedWorks\Web\www_htdocs\report\report\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Cell\Cell.php
                [line] => 257
                [function] => calculateCellValue
                [class] => PhpOffice\PhpSpreadsheet\Calculation\Calculation
                [type] => ->
            )

        [5] => Array
            (
                [file] => C:\Users\fmm\Dropbox\sharedWorks\Web\www_htdocs\report\report\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php
                [line] => 5094
                [function] => getCalculatedValue
                [class] => PhpOffice\PhpSpreadsheet\Cell\Cell
                [type] => ->
            )

        [6] => Array
            (
                [file] => C:\Users\fmm\Dropbox\sharedWorks\Web\www_htdocs\report\report\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php
                [line] => 4612
                [function] => extractCellRange
                [class] => PhpOffice\PhpSpreadsheet\Calculation\Calculation
                [type] => ->
            )

        [7] => Array
            (
                [file] => C:\Users\fmm\Dropbox\sharedWorks\Web\www_htdocs\report\report\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php
                [line] => 3442
                [function] => processTokenStack
                [class] => PhpOffice\PhpSpreadsheet\Calculation\Calculation
                [type] => ->
            )

        [8] => Array
            (
                [file] => C:\Users\fmm\Dropbox\sharedWorks\Web\www_htdocs\report\report\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php
                [line] => 3233
                [function] => _calculateFormulaValue
                [class] => PhpOffice\PhpSpreadsheet\Calculation\Calculation
                [type] => ->
            )

        [9] => Array
            (
                [file] => C:\Users\fmm\Dropbox\sharedWorks\Web\www_htdocs\report\report\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Cell\Cell.php
                [line] => 257
                [function] => calculateCellValue
                [class] => PhpOffice\PhpSpreadsheet\Calculation\Calculation
                [type] => ->
            )

        [10] => Array
            (
                [file] => C:\Users\fmm\Dropbox\sharedWorks\Web\www_htdocs\report\report\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Writer\Xlsx\Worksheet.php
                [line] => 1120
                [function] => getCalculatedValue
                [class] => PhpOffice\PhpSpreadsheet\Cell\Cell
                [type] => ->
            )

        [11] => Array
            (
                [file] => C:\Users\fmm\Dropbox\sharedWorks\Web\www_htdocs\report\report\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Writer\Xlsx\Worksheet.php
                [line] => 1190
                [function] => writeCellFormula
                [class] => PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet
                [type] => ->
            )

        [12] => Array
            (
                [file] => C:\Users\fmm\Dropbox\sharedWorks\Web\www_htdocs\report\report\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Writer\Xlsx\Worksheet.php
                [line] => 1047
                [function] => writeCell
                [class] => PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet
                [type] => ->
            )

        [13] => Array
            (
                [file] => C:\Users\fmm\Dropbox\sharedWorks\Web\www_htdocs\report\report\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Writer\Xlsx\Worksheet.php
                [line] => 67
                [function] => writeSheetData
                [class] => PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet
                [type] => ->
            )

        [14] => Array
            (
                [file] => C:\Users\fmm\Dropbox\sharedWorks\Web\www_htdocs\report\report\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Writer\Xlsx.php
                [line] => 274
                [function] => writeWorksheet
                [class] => PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet
                [type] => ->
            )

        [15] => Array
            (
                [file] => C:\Users\fmm\Dropbox\sharedWorks\Web\www_htdocs\report\report\php_classes\Pgo\Report\ReportGenerator.php
                [line] => 93
                [function] => save
                [class] => PhpOffice\PhpSpreadsheet\Writer\Xlsx
                [type] => ->
            )

        [16] => Array
            (
                [file] => C:\Users\fmm\Dropbox\sharedWorks\Web\www_htdocs\report\report\php_classes\Pgo\Report\ReportGenerator.php
                [line] => 101
                [function] => write
                [class] => Pgo\Report\ReportGenerator
                [type] => ->
            )

        [17] => Array
            (
                [file] => C:\Users\fmm\Dropbox\sharedWorks\Web\www_htdocs\report\report\index.php
                [line] => 124
                [function] => download
                [class] => Pgo\Report\ReportGenerator
                [type] => ->
            )

    )

The last few entries are mine, but you can see the steps arte started by seving the sheet.
save -> writeWorksheet -> writeSheetData -> writeCell -> writeCellFormula -> getCalculatedValue

What are the steps to reproduce?

Unfortunately I can't provide this for now, but for sure you can make a lot by following the trace. I think writeSheetData should save and recover the state of the selection. If I have time I may contribute to this.

Which versions of PhpSpreadsheet and PHP are affected?

This was tested on 1.15.0 - 2020-10-11

TEMPORARY FIX

The problem is caused by writeSheetData ending up calling getCalculatedValue so, if values are in cache this does not happen:
calling getCalculatedValue() after every formula writ,e fixed the wrong behaviour.
@oleibman
Copy link
Collaborator

oleibman commented Mar 7, 2021

Based on your description, I was able to at least partly duplicate your symptom. The spreadsheet that I write has its selected cells set correctly on each sheet. However, the spreadsheet object in the program now has the selected cells changed as you describe. I was able to avoid that by coding:

$writer->setPreCalculateFormulas(false);

This is probably an easier workaround than adding a lot of getCalculatedValue calls to your program. It is, of course, not a complete solution to your problem.

@oleibman oleibman mentioned this issue Mar 7, 2021
Merged
5 tasks
MarkBaker pushed a commit that referenced this issue Mar 10, 2021
@oleibman
Fix for Issue #1887 - Lose Track of Selected Cells After Save (#1908)
13b62be 
* Fix for Issue #1887 - Lose Track of Selected Cells After Save

Issue #1887 reports that selected cells are lost after saving Xlsx. Testing indicates that this applies to the object in memory, though not to the saved spreadsheet.

Xlsx writer tries to save calculated values for cells which contain formulas. Calculation::_calculateFormulaValue issues a getStyle call merely to retrieve the quotePrefix property, which, if set, indicates that the cell does not contain a formula even though it looks like one. A side-effect of calls to getStyle is that selectedCell is updated. That is clearly accidental, and highly undesirable, in this case. Code is changed to save selectedCell before getStyle call and restore it afterwards.

The problem was reported only for Xlsx save. To be on the safe side, test is made for output formats of Xlsx, Xls, Ods, Html (which basically includes Pdf), and Csv. For all of those, the object in memory is tested after the save. For Xlsx and Xls, the saved file is also tested. It does not make sense to test the saved file for Csv and Html. It does make sense to test it for Ods, but the necessary support is not yet present in either the Ods Reader or Ods Writer - a project for another day.

* Move Logic Out of Calculation, Add Support for Ods ActiveSheet and SelectedCells

Mark Baker thought logic belonged in Worksheet, not Calculation.
I couldn't get it to work in Worksheet, but doing it in Cell works,
and that has already been used to preserve ActiveSheet over call to
getCalculatedValue, so this just extends that idea to SelectedCells.

Original tests could not completely support Ods because of a lack of support
for ActiveSheet and SelectedCells in Ods Reader and Writer.
There's a lot missing in Ods support, but a journey of 1000 miles ...
Those two particular concepts are now supported for Ods.
@stale
Copy link

stale bot commented Jun 26, 2021

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

@stale stale bot added the stale label Jun 26, 2021
@oleibman
Copy link
Collaborator

Should be resolved by PR 1887, which was part of Release 18.

@miguel2211
Copy link

Hello, I am having this problem too. I tried to use "setSelectedCells" or "setSelectedCell", but got unwanted behavior, as save function use another cell to be selected.

Did anyone found a solution?

@oleibman
Copy link
Collaborator

oleibman commented Dec 2, 2023

What release of PhpSpreadsheet? Assuming that it's a current version, I would need to see code/spreadsheet which demonstrate this problem.

@oleibman
Copy link
Collaborator

@miguel2211 It is possible that PR #3841, which has been merged but is not yet part of an official release, fixes this problem. If you can test against master, give it a try.

@oleibman oleibman removed the stale label Jul 3, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@francescom @oleibman @miguel2211