Created attachment 102584 [details] example worksheet that cannot be saved and reopened Problem description: Opening and saving many older XLS files trashes formulas. Steps to reproduce: 1) Open and Save As... the attached file (don't need to do any edits). 2) Close Libre Spreadsheet 3) Open saved file - formulas now have assorted errors. Current behavior: Destroyed worksheet. LibreOffice unusable for many worksheets. Expected behavior: Do not trash formulas... Operating System: Windows 7 Version: 4.2.5.2 release
Hi Dave.. Looks like cell name that has 'Scope' for a sheet (perhaps same sheet) is missing after filesave. Please see cell F23: polar_a , polar_b , polar_c is missing after filesave. But it doesn't happen with sheetname that has 'Scope' = "Document (Global)". Cell E23 that has KNOTS_PER_MPS which is global scope not missing. Could you please retest with same sheet & different sheet 'Scope' whether its the cause(s) ? I'll also continue my observation.
Confirmed with libreoffice 4.2.5.2 and 4.3.0.1, Ubuntu 14.04 x86, set to NEW. The xls file contains named ranges. When save-as xls, all reference to named ranges "polar_a", "polar_b", and "polar_c" are lost, thus the cell gives "ERR 502". The scopes for Named ranges "polar_a", "polar_b", and "polar_c" were set as sheet "Fit" in the original file. When set the scopes of all these three named ranges to "Document (Global)" then save-as, it works fine. (Insert - Named Ranges - Manage)
Created attachment 102653 [details] resaved xls file
Using original file attached, I can also confirm that behavior since LO 3.5.7.2. No problem with that in AOO 4.1.0. But I can't reproduce that from scratch in new file using LO 4.2.6.1 and 4.3.0.2. Still don't find the pattern to reproduce it from scratch. @Dave, your file created/saved with which LO version ?
The example file provided was most recently saved with Microsoft Office Excel 2007 (I had to use Excel to get my work done as LibreOffice is too buggy). This XLS goes back some years and I have no idea what version was originally used to create it. I have *MANY* XLS files that exhibit flakiness in LOO, where things seem to work for a while and then after a few edits the formulas suddenly go to #NAME or #ERR or similar. It took me a while to see that simply opening and saving some files trashes the formulas. I suspect something is going horribly wrong constructing the formula data-structures on file-open (specifically name references), leading to corruption later... Hope that helps, Best Regards, Dave
This affects sheet local range names in formulas especially in shared formulas. I have a fix for it that works only as long as we don't support referencing sheet local range names from other sheets. As soon as we start to support that (not in the near future) we need to change our range name handling in formulas anyway a bit and add an additional member variable to the token class that stores the sheet number.
Markus Mohrhard committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=4ebdadfda60f108556ea9cc4436dd440bf122ca4 use the correct tab for sheet local names, fdo#81195 It will be available in 4.5.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.
Markus Mohrhard committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=339521b5448ae3f63b790f144976e78c9941ed33 add test for fdo#81195 It will be available in 4.5.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.
Review request for 4-4 and 4-3 are in gerrit.
Markus Mohrhard committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=0aefc72fd42e744beb218c14121147bfcef89ca3 correct test for fdo#81195 It will be available in 4.5.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.
Markus Mohrhard committed a patch related to this issue. It has been pushed to "libreoffice-4-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=1d45667276f671f6b73dd03550978458f27cc52d&h=libreoffice-4-3 use the correct tab for sheet local names, fdo#81195 It will be available in 4.3.6. 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.
Markus Mohrhard committed a patch related to this issue. It has been pushed to "libreoffice-4-4": http://cgit.freedesktop.org/libreoffice/core/commit/?id=a390df308eab5976cc1a30eaf4c405397c75eca2&h=libreoffice-4-4 use the correct tab for sheet local names, fdo#81195 It will be available in 4.4.0.2. 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.
*** Bug 42910 has been marked as a duplicate of this bug. ***