Bug 42910 - [Calc] xls export corrupts some formulas with named ranges
Summary: [Calc] xls export corrupts some formulas with named ranges
Status: RESOLVED DUPLICATE of bug 81195
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.4.3 release
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2011-11-14 03:37 UTC by Stephan Zietsman
Modified: 2015-01-22 07:34 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:
Regression By:

Original.xls file (from MS Excel 2007) (57.50 KB, application/vnd.ms-excel)
2011-11-14 03:37 UTC, Stephan Zietsman

Note You need to log in before you can comment on or make changes to this bug.
Description Stephan Zietsman 2011-11-14 03:37:24 UTC
Created attachment 53512 [details]
Original.xls file (from MS Excel 2007)

When you open the attached Original.xls file in LibO Calc (3.4.3) and re-save it as .xls (using "Save As...") some of the formulas that contain named ranges produce Err:520.

--==  Steps to reproduce  ==--
1. Open attached file (Original.xls) in LibO
2. Select "File" -> "Save As... (Ctrl + Shift + S)"
3. Enter some file name (e.g. "Saved_as_xls.xls")
4. Click "Save" to confirm
5. Close LibO
6. Open newly saved file (e.g. "Saved_as_xls.xls")

Expected result: File should look exactly the same as the original.

Actual result: Some (not all) formulas containing named ranges produce Err:520, specifically D8:E24.
--==  End of reproduction  ==--

Additional comments:
All the formulas in D6:E24 should contain the same formula (with updated relative references).  However, only D8:E24 produce the error (D6:E7 appear fine).

The attached file "Original.xls" was created with MS Excel 2007.  The names have local scope (local to the sheet "Standard").  The file I attached is a trimmed version of the file I noticed it with.

If the newly saved file (e.g. "Saved_as_xls.xls") is opened in Excel, it also produces an error (#NAME?).

If original.xls is opened and saved to .ods, there is no problem.  Additionally, if that .ods file is opened and saved as .xls, there is also no problem.

Using LibreOfficePortable 3.4.3 (as from portableapps.com)
Windows 7 Professional x64
LibreOffice 3.4.3 
OOO340m1 (Build:302)
Comment 1 Stephan Zietsman 2011-11-14 03:40:00 UTC
May be related to https://bugs.freedesktop.org/show_bug.cgi?id=38204
Comment 2 Markus Mohrhard 2011-11-29 00:47:16 UTC
let me have a look at it

I suspect another case of named ranges referencing each other
Comment 3 Markus Mohrhard 2011-11-30 06:19:19 UTC
the linked bug report had a totally different reason and is fixed for a long time
Comment 4 Markus Mohrhard 2011-11-30 07:40:29 UTC
and just to amke it clear, I think it is an import bug but had not yet time to look into it
Comment 5 Christian Lohmaier 2011-12-28 15:35:14 UTC
reproducible with 3.5.0beta2
Comment 6 QA Administrators 2015-01-05 17:51:03 UTC
** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present on a currently supported version of LibreOffice (4.3.5 or later): https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior

If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case)

Thank you for your help!

-- The LibreOffice QA Team
Comment 7 Buovjaga 2015-01-20 09:33:12 UTC
No problem anymore.

Win 7 Pro 64-bit, LibO Version:
Build ID: 9629686a67dd1f357477c13325e45a66f3452bb9

Build ID: 5f6bdce0c0ac687f418821ce328f2987bf340cda
TinderBox: Win-x86@62-TDF, Branch:MASTER, Time: 2015-01-17_01:06:46
Comment 8 Stephan Zietsman 2015-01-22 07:06:43 UTC
This still appears to be a problem.

I notice that the file has a lot of other problems (invalid references), but the bug I originally reported still appears.

--==  Steps to reproduce  ==--
1. Open the file (Original.xls)
2. RE-SAVE IT as an xls file (Saved_as_xls.xls)
6. Open the re-saved file (Saved_as_xls.xls)

Compare the formula in cell D8 between the two xls files (Original.xls vs. Saved_as_xls.xls)

Expected result:  The formula (in D8) should read "=B6-x_"

Actual result:  The formula (in D8) reads "=B6-" in Saved_as_xls.xls

--==  End of reproduction  ==--

Additional comments:  I no longer have access to MS Office, so I cannot test the file in Excel.

I notice that many of the cells contain reference errors.  Those are beyond the scope of this bug report; only concentrate on the formula at D6.

@Beluga:  Can you give more details about how you tested?  Did you re-save the file?  What was the formula in cell D8?

About: LibO Version:
Build ID: 430m0(Build:2)
Ubuntu 14.04 LTS
Comment 9 Markus Mohrhard 2015-01-22 07:34:36 UTC
Fixed with http://cgit.freedesktop.org/libreoffice/core/commit/?h=libreoffice-4-3-6&id=1d45667276f671f6b73dd03550978458f27cc52d which is only in 4.3.6 and not in 4.3.5.

Please don't just reopen bug reports if you don't read in detail what the QA guys did.

*** This bug has been marked as a duplicate of bug 81195 ***