Bug Hunting Session
Bug 86575 - FILESAVE to .xslx with named ranges deleted gives error in Excel
Summary: FILESAVE to .xslx with named ranges deleted gives error in Excel
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.0 release
Hardware: Other All
: high normal
Assignee: Eike Rathke
URL:
Whiteboard: target:5.2.0 target:5.1.4
Keywords:
: 92841 (view as bug list)
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
 
Reported: 2014-11-22 13:27 UTC by vlb
Modified: 2017-07-17 09:35 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
error log (1.59 KB, text/plain)
2014-11-22 13:27 UTC, vlb
Details
Issue save as xlsx and open in Excel (2.40 MB, application/vnd.oasis.opendocument.spreadsheet)
2014-11-22 23:06 UTC, vlb
Details
new test file (66.34 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-12-30 10:37 UTC, vlb
Details
test file without hidden sheet (108.52 KB, application/vnd.openxmlformats-officedocument.spreadsheetml)
2014-12-30 18:02 UTC, raal
Details
simplified test file (5.24 KB, application/vnd.openxmlformats-officedocument.spreadsheetml)
2014-12-30 18:35 UTC, raal
Details
simplified test file (5.24 KB, application/vnd.openxmlformats-officedocument.spreadsheetml)
2014-12-30 18:37 UTC, raal
Details
simplified test file 2 (5.50 KB, application/vnd.openxmlformats-officedocument.spreadsheetml)
2014-12-30 18:39 UTC, raal
Details

Note You need to log in before you can comment on or make changes to this bug.
Description vlb 2014-11-22 13:27:22 UTC
Created attachment 109847 [details]
error log

I have a great file, when i save the calc file (*.ods)as *.xlsx and then open in excel it geve errors (see attachment log file).
Comment 1 Terrence Enger 2014-11-22 14:55:13 UTC
vib,

Thank you for taking the time to help us improve LibreOffice.

Can you attach your file to the bug report?  Remember that it will be
visible to the whole world.

When you answer, please set the bug status back to UNCONFIRMED.

Terry.
Comment 2 vlb 2014-11-22 23:06:56 UTC
Created attachment 109868 [details]
Issue save as xlsx and open in Excel

With this file in *.ods and save as *.xlsx and open in Excel are different errors.
Comment 3 Terrence Enger 2014-11-23 02:43:58 UTC
Thank you for the attachment, vlb.  I shall leave it to someone with
an Excel newer than mine to take it further.

Terry.
Comment 4 raal 2014-11-23 09:32:28 UTC
After save as xlsx error message in excel: 
Excel found unreadable content in 'Ligger v2.6.xlsx'. Do you want to recover the contents of this workbook?

After recovery:
Removed Feature: Data validation from /xl/worksheets/sheet1.xml part
Removed Feature: Conditional formatting from /xl/worksheets/sheet1.xml part
Removed Records: Named range from /xl/workbook.xml part (Workbook)
Removed Records: Formula from /xl/worksheets/sheet1.xml part

Version: 4.4.0.0.alpha2+
Build ID: d273a60bfdbf9bb7623bed38667ec0647753157c
TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2014-11-20_03:05:21

Excel 2010
Comment 5 Markus Mohrhard 2014-12-30 05:52:03 UTC
In this current form with this huge document this bug report is more or less useless.

Please minimize the test document and try to limit it to one problem per document and bug report. There are no validation errors in the document according to officeotron and the OOXML SDK so it is a difficult task to find out what is going wrong unless the document is really small.
Comment 6 vlb 2014-12-30 10:35:24 UTC
(In reply to Markus Mohrhard from comment #5)

> Please minimize the test document and try to limit it to one problem per
> document and bug report. There are no validation errors in the document
> according to officeotron and the OOXML SDK so it is a difficult task to find
> out what is going wrong unless the document is really small.

I have a other testsheet reduced (see attachment). The sheet is really empty, but there where many cell names. I hope you can find the problem.
Comment 7 vlb 2014-12-30 10:37:29 UTC
Created attachment 111509 [details]
new test file

This file is small and can reproduce the same issue.
Comment 8 Markus Mohrhard 2014-12-30 12:52:19 UTC
(In reply to vlb from comment #7)
> Created attachment 111509 [details]
> new test file
> 
> This file is small and can reproduce the same issue.

There is still a huge hidden sheet.
Comment 9 vlb 2014-12-30 14:09:48 UTC
> 
> There is still a huge hidden sheet.

I don't know howe reduce this, howe can i do this?
Must all the cell names reduced?
Comment 10 Markus Mohrhard 2014-12-30 17:45:58 UTC
(In reply to vlb from comment #9)
> > 
> > There is still a huge hidden sheet.
> 
> I don't know howe reduce this, howe can i do this?
> Must all the cell names reduced?

The hidden sheet is the problem and not the range names.
Comment 11 vlb 2014-12-30 18:00:50 UTC
(In reply to Markus Mohrhard from comment #10)
> (In reply to vlb from comment #9)
> > > 
> > > There is still a huge hidden sheet.
> > 
> > I don't know howe reduce this, howe can i do this?
> > Must all the cell names reduced?
> 
> The hidden sheet is the problem and not the range names.

Oke and now howe is this solved?
Can i do something to help.
Comment 12 raal 2014-12-30 18:02:27 UTC
Created attachment 111527 [details]
test file without hidden sheet

test file without hidden sheet. Excel still show error.
Comment 13 raal 2014-12-30 18:35:33 UTC
Created attachment 111528 [details]
simplified test file

deleted both sheets, saved as .xlsx. Excel show error.
Comment 14 raal 2014-12-30 18:37:32 UTC
Created attachment 111529 [details]
simplified test file

deleted both sheets, saved as .xlsx. Excel show error: Removed Records: Named range from /xl/workbook.xml part (Workbook)
Comment 15 raal 2014-12-30 18:39:21 UTC
Created attachment 111530 [details]
simplified test file 2

Error message in excel:Removed Records: Named range from /xl/workbook.xml part (Workbook)
Repaired Records: Worksheet properties from /xl/workbook.xml part (Workbook)
Comment 16 Buovjaga 2015-08-01 16:24:34 UTC
*** Bug 93045 has been marked as a duplicate of this bug. ***
Comment 17 Buovjaga 2015-08-01 16:26:04 UTC
Seems to be because of non-existing references (#REF!) in the named ranges.
Same category as bug 92841
Comment 18 Timur 2015-08-17 14:34:00 UTC
This has always been the same, from LO 3.3.
I change title from " FILEOPEN: LO4.4.00beta1 when save *.ods to *.xslx and open in excel give error" to "FILESAVE to .xslx with named ranges deleted gives error in Excel".

(In reply to Beluga from comment #17)
> Seems to be because of non-existing references (#REF!) in the named ranges.
> Same category as bug 92841
Good catch. That one looks like a duplicate.

Comment https://bugs.documentfoundation.org/show_bug.cgi?id=92841#c7 probably applies here:
"If someone could try out what MS-Excel exactly saves when saving such named expression that contains a reference to a deleted sheet we could mimic that if it differs."

Isn't that seen from OXML standards? Here is how it looks like:

LO content.xml: <office:spreadsheet> <table:named-expressions> <table:named-range table:name="nameA2" table:base-cell-address="" table:cell-range-address="$#REF!.$A$2"/> </table:named-expressions> </office:spreadsheet>

MS workbook.xml: <workbook>	<definedNames> <definedName name="NA2t">#REF!</definedName> </definedNames>  </workbook>


raal's XLSX test files make no sense, instead there should be ODS minimal test file for this error. 
Because it's not clear when this happens. I tried with a single named range in ODS, later deleted and saved as XLSX, and there's no error. 

So, looks like this bug should be a request to adjust non-existing references (#REF!) in the named ranges when saving as XLSX, but only after the cause is found.


BTW: When saving or opening as ODS or XLSX, there is no warning in LO or MS that named ranges don't exist anymore. I'm not aware of some validation technique.
Comment 19 Timur 2015-08-17 14:42:22 UTC
*** Bug 92841 has been marked as a duplicate of this bug. ***
Comment 20 vlb 2016-01-02 10:53:26 UTC
can solve this problem?
Comment 21 vlb 2016-02-17 18:34:24 UTC
The problem is already in LO 5.1.0.3 (x64).
Comment 22 Eike Rathke 2016-05-06 14:11:52 UTC
(In reply to Timur from comment #18)
> LO content.xml: <office:spreadsheet> <table:named-expressions>
> <table:named-range table:name="nameA2" table:base-cell-address=""
> table:cell-range-address="$#REF!.$A$2"/> </table:named-expressions>
> </office:spreadsheet>

Which btw is wrong as well, it should be only "#REF!"

> Because it's not clear when this happens. I tried with a single named range
> in ODS, later deleted and saved as XLSX, and there's no error. 
> 
> So, looks like this bug should be a request to adjust non-existing
> references (#REF!) in the named ranges when saving as XLSX, but only after
> the cause is found.

The cause is simple. The sheet where the reference pointed to was deleted. Which actually can be seen with the internal "#REF!.$A$2" display string, the information would get lost when writing the correct "REF!" only, but that's how it is defined.

> BTW: When saving or opening as ODS or XLSX, there is no warning in LO or MS
> that named ranges don't exist anymore. I'm not aware of some validation
> technique.

The name does exist, just not the sheet it was pointing to.
Comment 23 Eike Rathke 2016-05-06 14:12:12 UTC
Taking care of this.
Comment 24 Commit Notification 2016-05-06 15:10:05 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

write the [#REF!] as defined in ODFF, tdf#86575 related

It will be available in 5.2.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 25 Commit Notification 2016-05-06 15:10:09 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Resolves: tdf#86575 for OOXML write plain #REF! if deleted parts

It will be available in 5.2.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 26 Eike Rathke 2016-05-06 16:05:03 UTC
Geez.. turns out this resolves things, but only with newly created test cases. Not sure what can be done for the original document attached here. That will take some time.

Note that the original document produces also other errors, like sheet name being too long for Excel (which isn't specified but it appears they have a limit of 31 characters), which is tracked by some other bug but I don't have the bug number at hand.
Comment 27 Eike Rathke 2016-05-06 16:08:45 UTC
Pending review https://gerrit.libreoffice.org/24705 for 5-1
Comment 28 Commit Notification 2016-05-06 19:25:26 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7ff50286bf7a8d99711388dfe7bb5ebeca4aa4d0&h=libreoffice-5-1

tdf#86575 for OOXML write plain #REF! if deleted parts

It will be available in 5.1.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 29 Elmar 2016-05-11 08:42:50 UTC
This is not fixed in

Version: 5.2.0.0.alpha1+
Build ID: b871a97d35a4160b7403c07bfac10aaa744fbbfd
CPU Threads: 4; OS Version: Linux 3.19; UI Render: default; 
TinderBox: Linux-rpm_deb-x86@71-TDF, Branch:master, Time: 2016-05-11_01:00:26
Locale: en-ZA (en_GB.UTF-8)
Comment 30 Eike Rathke 2016-05-11 09:10:24 UTC
@Elmar: What exactly did you test? Note comment 26.
Comment 31 Commit Notification 2016-05-13 18:15:08 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

recognize #REF! particles of invalidated references, tdf#86575 follow-up

It will be available in 5.2.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 32 Eike Rathke 2016-05-13 18:57:44 UTC
With the latest commit we can repair the bad invalidated references so that they actually result in a #REF! error instead of a bad symbol #NAME? error.

However, that document has even more peculiarities than just an overlong sheet name of the hidden sheet.. Excel also does not accept named expressions/ranges that start with C{number} or R{number}, no matter if the actual name could be a valid R1C1 address notation or not, e.g. already C1foo is invalid. Affected are the names C1_10, C1_11, C2_10 and C2_11.

Also C and R are invalid because in R1C1 notation it would be the current column or row. Current versions of LibreOffice check that and adding such name is not allowed.

Additionally the one conditional formatting formula in that sheet with the long name is utterly broken and contains references like #REF!.[.C77]
I have no idea how exactly those ended up in there other than that the document was saved using 4.4.0.1, they won't be fixed.

So, to summarize, to be able to save the simplified document attachment 111530 [details] to .xlsx:
* load the .ods in a recent build that contains the latest fixes
* make the hidden sheet visible and rename it to something shorter than
  32 characters
* remove the conditional format on that sheet
* rename the named ranges C, C1_10, C1_11, C2_10 and C2_11 to something
  that does not clash with Excel's expectation

However, the original attachment 109868 [details] is a completely different document, but also contains a named range C, rename that and Excel complains only about some data validation entries, I don't know why.

I'll declare this as fixed, if someone is able to extract the data validation cause Excel complains about then please open a new bug with a simple document to reproduce.
Comment 33 Commit Notification 2016-05-14 00:26:05 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=442387bdf493aa6a752fd2531507625dc9e5a4fc

recognize #REF! particles of invalidated references, tdf#86575 follow-up

It will be available in 5.2.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 34 vlb 2016-05-14 17:31:11 UTC
(In reply to Eike Rathke from comment #32)
> 
> However, that document has even more peculiarities than just an overlong
> sheet name of the hidden sheet.. Excel also does not accept named
> expressions/ranges that start with C{number} or R{number}, no matter if the
> actual name could be a valid R1C1 address notation or not, e.g. already
> C1foo is invalid. Affected are the names C1_10, C1_11, C2_10 and C2_11.
> 
> Also C and R are invalid because in R1C1 notation it would be the current
> column or row. Current versions of LibreOffice check that and adding such
> name is not allowed.
> 
> So, to summarize, to be able to save the simplified document attachment
> 111530 [details] to .xlsx:
> * load the .ods in a recent build that contains the latest fixes
> * make the hidden sheet visible and rename it to something shorter than
>   32 characters
> * remove the conditional format on that sheet
> * rename the named ranges C, C1_10, C1_11, C2_10 and C2_11 to something
>   that does not clash with Excel's expectation
> 
Thank you very much for the explanation and i have rename the cells!

Now i have the bug make smaller, see bug 99842.
Comment 35 vlb 2016-05-14 19:57:23 UTC
(In reply to Eike Rathke from comment #32)
> 
> However, the original attachment 109868 [details] is a completely different
> document, but also contains a named range C, rename that and Excel complains
> only about some data validation entries, I don't know why.
> 
I have renamed range C and then when i saved as xslx the data cell E11 and E12 are destroyed.
Can you look at this?
Comment 36 vlb 2016-05-15 09:06:54 UTC
(In reply to vlb from comment #35)
> (In reply to Eike Rathke from comment #32)
> > 
> I have renamed range C and then when i saved as xslx the data cell E11 and
> E12 are destroyed.
I mean the data list (menu-data-validity is gone.
Comment 37 vlb 2016-05-15 12:04:55 UTC
(In reply to vlb from comment #36)
> (In reply to vlb from comment #35)
> > (In reply to Eike Rathke from comment #32)
> > > 
> > I have renamed range C and then when i saved as xslx the data cell E11 and
> > E12 are destroyed.
> I mean the data list (menu-data-validity is gone.

see also new bug 99856
Comment 38 vlb 2016-05-15 22:15:57 UTC
(In reply to Eike Rathke from comment #32)

> However, the original attachment 109868 [details] is a completely different
> document, but also contains a named range C, rename that and Excel complains
> only about some data validation entries, I don't know why.

When name C is delete, i found two problems when saved to xslx and open in excel:

1) conditional formatting in cell c7 is disappeared
2) The macro in cell T2:V4 is also disappeared
Comment 39 vlb 2016-05-18 21:02:36 UTC
I close this bug and have make new bugs 99842 99921 99856 99892 99917 99866