Bug Hunting Session
Bug 101042 - XLSX file with a frozen column causes Excel on OSX to degrade
Summary: XLSX file with a frozen column causes Excel on OSX to degrade
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.1.4.2 release
Hardware: All All
: medium normal
Assignee: Bartosz
URL:
Whiteboard: interoperability target:5.3.0 target:...
Keywords: filter:xlsx
: 86957 (view as bug list)
Depends on:
Blocks:
 
Reported: 2016-07-20 19:09 UTC by Kuno Meyer
Modified: 2016-12-03 16:05 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
File created with LO 5.1.4 causing Excel/OSX behave badly (4.75 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-07-20 19:09 UTC, Kuno Meyer
Details
File created with LO 5.1.4 with manual fix (remove windowProtection="true") (4.75 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-07-21 21:27 UTC, Bartosz
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Kuno Meyer 2016-07-20 19:09:22 UTC
Created attachment 126330 [details]
File created with LO 5.1.4 causing Excel/OSX behave badly

This bug report has emerged from bug 100946 case b).

The attached XLSX file has been created with LO 5.1.4. It has the first column A marked as frozen for scrolling (i.e. "View > Freeze Rows and Columns").

When that file is opened with Excel 2011 on OSX, 
a) the document window cannot be moved around on the desktop
b) the document window be resized horizontally only once to approx. 50% width.
c) the document window *can* freely be resized vertically.

In conclusion, it looks as if there are some obvious incompatibilities between LO and Excel/OSX regarding "Freeze Rows and Columns".
Comment 1 Aron Budea 2016-07-21 04:37:47 UTC
Someone had a similar issue in bug 100014 (and bug 90975 was also similar).
Is Protect Workbook set in Excel with this file, and does unsetting it fixes the incorrect behavior?
Comment 2 Kuno Meyer 2016-07-21 09:17:38 UTC
Attachment 126330 [details] indeed has workbook protection flag set. I did not set this, so this might indeed be bug 90975, despite of the fact that that bug is RESOLVED WORKSFORME. 

According to https://support.office.com/en-us/article/Protect-a-workbook-7e365a4d-3e89-4616-84ca-1931257c1517, there are multiple types of workbook protection: a) structure protection, b) window protection. According to that link, Window protection has been dropped with MSO 2013, but what I observe here with Excel 2010 on Windows, it comes quite close to what I remember having observed with Excel 2011 on OSX.

More investigation needed at home this evening.
Comment 3 Bartosz 2016-07-21 21:27:36 UTC
Created attachment 126351 [details]
File created with LO 5.1.4 with manual fix (remove windowProtection="true")
Comment 4 Kuno Meyer 2016-07-23 11:16:34 UTC
With Excel 2010 on Windows
--------------------------

- setting workbook protection to "structure" alters:
    /xl/workbooks.xml: <workbookProtection lockStructure="1"/> is added

- setting workbook protection to "window" alters:
    /xl/workbooks.xml: <workbookProtection lockWindows="1"/> is added
    /xl/worksheets/sheet1.xml: windowProtection="1" to the <sheetView> element
    /xl/worksheets/sheet2.xml: windowProtection="1" to the <sheetView> element
    /xl/worksheets/sheet3.xml: windowProtection="1" to the <sheetView> element

- activating "View > Freeze Panes" alters:
    /xl/worksheets/sheet1.xml:
        - one <pane> and 3 <section> subelements (none yet there) to the <sheetView> element

- opening the "window" workbook-protected file in Excel 2011 on OSX exactly results in the described behaviour a)-c).


With LO 5.1.4, started from scratch:
-------------

- activating "View > Freeze Rows and Columns" alters:
    /xl/worksheets/sheet1.xml: 
        - windowProtection="true" to the <sheetView> element
        - one <pane> and 3 additional <section> subelements (one already there) to the <sheetView> element


Conclusion
----------

- LO really should not set windowProtection="true" in sheet1.xml when panes are frozen. Excel does not do that either. This is what this bug report is about.

- Excel/Win and Excel/OSX are insofar differently implemented as Excel/Win seems to look at <workbookProtection lockWindows="1"/> in workbook.xml (and does not assume workbook protection if it is missing) whereas Excel/OSX does not (and just follows the windowProtection flag in sheet1.xml).

- Excel uses 0/1 for boolean values, LO true/false. But this might be OK by definition of the file format.
Comment 5 Bartosz 2016-08-04 15:17:48 UTC
@Kuno Do you have proposal how it could be implemented to fix it properly on both OSX and Windows side?

Maybe bug on Excel itself should be submitted?
Comment 6 Kuno Meyer 2016-08-04 18:38:12 UTC
No need to blame Excel. Just make sure that windowProtection="1" is not added to the <sheetView> element in /xl/worksheets/sheet<i>.xml when you freeze columns/panes.

See https://gerrit.libreoffice.org/gitweb?p=core.git;a=blob;f=sc/source/filter/excel/xeview.cxx;h=09fbbc1fef52720f68d62f89314522ed9f895685;hb=40d892a2db4d750aaf0562c63004e693c028273c#l417:

   ...
   XML_windowProtection, XclXmlUtils::ToPsz( maData.mbFrozenPanes ),
   ...

is most probably wrong.
Comment 7 Bartosz 2016-08-05 02:17:52 UTC
Thanks for investigation. 
I will prepare the patch which will fix that.

I'm wondering when we should enable windowProtection="1"?
Do you know some LibreOffice setting, which are equivalent to windowProtection="1" ?
Comment 8 Kuno Meyer 2016-08-05 06:16:28 UTC
Based on https://gerrit.libreoffice.org/gitweb?p=core.git;a=blob;f=sc/source/filter/excel/excdoc.cxx;h=a1a2a75443d4bea88e0c63a49761f85ac0934c3e;hb=HEAD#l112, which seems to be the part that adds <workbookProtection ...> to workbooks.xml, the answer might be yes.
Comment 9 Kuno Meyer 2016-08-18 07:24:46 UTC
Or, in other words, as conclusion from comment 6 and comment 8:

replace: 

   ...
   XML_windowProtection, XclXmlUtils::ToPsz( maData.mbFrozenPanes ),
   ...

with something along the lines of:

   const ScTableProtection* pTabProtect = rDoc.GetTabProtection(mnScTab);

   ...
   XML_windowProtection, XclXmlUtils::ToPsz(
      pProtect 
      && pProtect->isProtected() 
      && pProtect->isOptionEnabled(ScDocProtection::WINDOWS)),
   ...
Comment 10 Xisco Faulí 2016-09-19 15:29:49 UTC Comment hidden (obsolete)
Comment 11 Bartosz 2016-10-20 08:57:33 UTC
Review for fixing the window freeze:
https://gerrit.libreoffice.org/#/c/30076/
Comment 12 Commit Notification 2016-11-11 13:03:09 UTC
Bartosz Kosiorek committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0637d0f43d3dc5b41a1bbc5726d6a68b7c36a519

tdf#101042 Fix for frozen windows in Excel

It will be available in 5.3.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 13 Commit Notification 2016-11-11 15:35:12 UTC
Bartosz Kosiorek committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=a6114042c590ff319e7d1877935e56e6c77bc3ee&h=libreoffice-5-2

tdf#101042 Fix for frozen windows in Excel

It will be available in 5.2.4.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 14 Kevin Suo 2016-12-03 16:05:11 UTC
*** Bug 86957 has been marked as a duplicate of this bug. ***