search

ONLYOFFICE / DocumentServer

ONLYOFFICE Docs is a free collaborative online office suite comprising viewers and editors for texts, spreadsheets and presentations, forms and PDF, fully compatible with Office Open XML formats: .docx, .xlsx, .pptx and enabling collaborative editing in real time.
https://www.onlyoffice.com
GNU Affero General Public License v3.0
4.87k stars 1.09k forks source link

Spreadsheet view location not saved on document #230

Closed tavinus closed 2 years ago

tavinus commented 6 years ago

Hi there! Thanks for this great app!

I was wondering if it is possible to make OnlyOffice save the position inside a document like MS Office does (at least for spreadsheets).

For instance, if I open a large spreadsheet and do something at the 3rd sheet, line 13245 and then leave the selection there, that is not saved. So if I close and open the file again, it would go back to where it was saved before. This could be A1 if it is a new file, or any position saved before (on Excel for example).

I understand that because OnlyOffice can have more than one person connected this could be more tricky. Probably need to save the last position of the last person, not sure.

Not a serious bug but can be quite annoying if you are working with big spreadsheets and need to keep rolling thousand of lines and columns every time you open the document because the view is reset. Also, holding the last Desktop view forever is not optimal.

Do you want to request a feature or report a bug? It is kind of both. Small bug, little feature :) The Spreadsheet app seems to open at the saved position, but it does not save its last position.

What is the current behavior? Current view is lost/reset when a spreadsheet is closed. If you saved a "view position" at Excel for example, that will remain forever if just using online afterwards.

If the current behavior is a bug, please provide the steps to reproduce and if possible a minimal demo of the problem. Save a spreadsheet at the second Sheet, ZY3456 on focus. On Excel this would be saved and you would go back to this position when reopening the document.

What is the expected behavior? Hold/Save last view

Did this work in previous versions of DocumentServer? Not sure, guess not

DocumentServer version: NextCloud: 12.0.4 OO Document Server: 5.0.6-14

Operating System: Debian Jessie 8.10

Browser version: Chrome, Firefox latest Windows

Cheers! Gus

tavinus commented 6 years ago

Hi!
Sorry to bump, but it has been almost an year.
Was there any development on this front?

BTW, the simplest solution is to just "save the view" of the person who asked to save the file

Cheers!
Gus

Rita-Bubnova commented 6 years ago

Hello, @tavinus. In the current stable version of DocumentServer (5.1.5 (build: 59)) only the sheet with the last selection is saved. Currently your task about saving cell has low priority. You can contact sales team sales@onlyoffice.com to speed up thins.

This is a bug, issue 26291 in our internal issue tracker.

tavinus commented 6 years ago

Hi @Rita-Bubnova .
Thanks for the reply.

Not sure I understand...

In the current stable version of DocumentServer (5.1.5 (build: 59)) only the sheet with the last selection is saved.

It saves the current selection and then re-apply the selection when opening it?
That is nice and from my findings it is kind of related.
Selection adds to the same tag and on the same XML file as what we need for saving the view.
I couldn't find a 5.1.5 to download and test though.

I think I will try to investigate on how to do it.
Maybe I can help with that.
I am gonna leave my findings below. Bear with me please.

The XLSX format part

This is my first endeavour on Open XML format by MS, but seems quite simple.
They are all zip files with a bunch of XML inside them.

I created a very simple spreadsheet on MS, then saved the file, extracted the zip
into a folder and started to inspect the XMLs.

First thing I noticed is that most of them are invalid by XML standards.
It is common for tags to be closed two times like this

<!-- here we close two times - Y U DO DIS MICROSOFT? -->
<myTag id="1" /></myTag>

<!-- here we close once -->
<myTag id="1" />

<!-- here we close once -->
<myTag id="1"></myTag>

Maybe that is something normal to the ISO/IEC 29500-4:2016 standard,
but that is definitely not valid XML by any standard.

Because of that, ATOM Beautify could not run well on most files, so I had to format the XMLs manually in order to read it easier. Since I had to change the XLSX files a few times for some extra tests, reformatting was kind of a pain.

Easily enough though, I found out the files and tags where the magic happens inside the XML.

Each worksheet has its own definition of a topLeftCell
and there is only one with the tabSelected="1" set.

So the program open the tabSelected sheet with the topLeftCell as view location.

This is from worksheets / sheet2.xml, selected and view defined (top-left O14)

<sheetView tabSelected="1" topLeftCell="O14" workbookViewId="0">

This is worksheets / sheet1.xml, not selected, but has a view defined (topLeft H34)

<sheetView topLeftCell="H34" workbookViewId="0">

This is worksheets / sheet3.xml, not selected and no view defined

<sheetView workbookViewId="0"/>

The tag sheetView always resides on worksheet / sheetViews,
for each spreadsheet file worksheets / sheetNUMBER.xml.

So now we know what needs to be done.
We need for each sheet to have its topLeftCell="XY" defined / updated when appropriate.
We need tabSelected="1" to be added / removed when appropriate.

Here is the full XML of sheet2.xml for reference

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet
    xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
    xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    mc:Ignorable="x14ac"
    xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
    <dimension ref="A1:X30"/>
    <sheetViews>
        <sheetView tabSelected="1" topLeftCell="O14" workbookViewId="0">
            <selection activeCell="X30" sqref="X30"/>
        </sheetView>
    </sheetViews>
    <sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>
    <sheetData>
        <row r="1" spans="1:1" x14ac:dyDescent="0.25"><c r="A1" s="1"/>
        </row>
        <row r="28" spans="24:24" x14ac:dyDescent="0.25">
            <c r="X28" t="s">
                <v>2</v>
            </c>
        </row>
        <row r="29" spans="24:24" x14ac:dyDescent="0.25">
            <c r="X29" t="s">
                <v>3</v>
            </c>
        </row>
        <row r="30" spans="24:24" x14ac:dyDescent="0.25"><c r="X30" s="2"/>
        </row>
    </sheetData>
    <pageMargins left="0.511811024" right="0.511811024" top="0.78740157499999996" bottom="0.78740157499999996" header="0.31496062000000002" footer="0.31496062000000002"/>
    <pageSetup orientation="portrait" r:id="rId1"/>
</worksheet>

The OnlyOffice part

I tested on SpreadSheet Editor versions:

I got basically the same results on both.

OnlyOffice can already read tabSelected="1" and open on it.
But it does not save the tabSelected info, which means that whatever is set by MS will remain forever
(it will always open on that spreadsheet).

This may have been fixed on the new version you mentioned, since these ones also do not seem to remember selected areas.

Let's not worry about selected areas here, but I noticed that they also reside at the same XML tag sheetView (but inside a selection tag). So maybe the person who did that part can add this easy as well.

I am not sure if we already have an easy way to fetch the Top-Left position of the spreadsheet view (the first cell that is visible on top-left). If we have that, we just need to add that to the XML when saving it. If we don't, we need a method for that. The same goes for a method that moves the view to a Top-Left position, we will need it.

I am guessing that saving the current tab selected should be implemented together, since they both add/change the same XML tag and also both work together to provide the current view.

Since onlyoffice already reads tabSelected, there is already at least some code tapping on it.

Things to consider

Wrapping up

I guess this is enough for a first exploration.

I now need to check the Spreadsheet Editor code and see if I can help there.
I am still also not sure how I would go about having a test environment to help implementing that though. Any references or tips would be appreciated.

I expect a painful learning curve for me to be able to find myself there, but that should be fine.

PS: Did many edits to add/fix stuff

PS2: Forgot to mention that I feel cold just by looking at your picture ❤️ ❄️

Feedback is always welcome!
Cheers!
Gus

HaffMedia commented 5 years ago

Dear devs, I installed OnlyOffice a few weeks ago. Love the interface. But this issue of not keeping the last location is the single reason I cannot use it.

Please let me know once this issue has been resolved!

Thanks

jagdishadusumalli commented 3 years ago

This is Dec 2020 and i needed this feature for efficient workflow. Tried this on OnlyOffice 6.1 (443) on macOS 11.0.1

Currently how it works is I have a spreadsheet with 9-10 long subsheets with over 500 rows. When i close and open the spreadsheet onlyoffice opens the subsheet which i last did any editing and saving on (not the sheet which was viewed and closed which could be different also). Also It does not go to the location (row) where i last stopped my work (updating values or viewing). I need to always scroll to the last row (Usually its last row) of the subsheet and append/ edit values which becomes sometimes cumbersome. I use the Fn + Down Arrow to qucikly reach to the bottom of sheet (it helps but not perfect)

Soutions:

  1. Ideally remember the last opened sheet in spreadsheet and the row position when the sheet was closed or
  2. Please provide a shortcut to move down to the last row with data of the sheet.

Such minor usability issues are hindering adoption of this beautiful software

tavinus commented 3 years ago

Yep. Been 3 years now.

And it is still the reason why we didn't migrate to OnlyOffice as our main suite.

We use OO inside our Nextcloud installation and in some simple terminals, but we still use MS Office as our main Office program in most cases just because of this missing feature.

From my breakdown above, it seems simple enough, but must be done from the spreadsheet client itself.

This would certainly be a great addition to the software.

ShockwaveNN commented 3 years ago

@tavinus Sorry to hear that, I show your message to our dev team, but cannot guarantee this will speed things up, priorities is out of our paygrades, sorry

tavinus commented 3 years ago

It's just that

people that work on Spreadsheets
the whole day, every day
really want their spreadsheets to open back where they were.

It really does add a lot to the workflow. Can't argue that.

Also, you NEED to open the file into MS Office and save it there if you want to change where it loads. Which can be annoying.

If you ever open the XLSX in Excel and send it back to someone with OO, it will be forever stuck into that view position if you don't have MS Office to re-save the file. This is when minor becomes major...

ShockwaveNN commented 3 years ago

This issue fixed in https://github.com/ONLYOFFICE/sdkjs/commit/dbc46a317e48a4433dbf77a0a87f43c7ad6c76fc

And will be released in next major releas

ShockwaveNN commented 2 years ago

DocumentServer and DesktopEditors v7.0.0 are released, so this issue should be resolved and I'm closing it

Feel free to comment or open a new issue if the problem is still actual