Bug Hunting Session
Bug 92841 - FILESAVE: some .xlsx files can't be opened with MS FileFormatConverter or older LibO
Summary: FILESAVE: some .xlsx files can't be opened with MS FileFormatConverter or old...
Status: RESOLVED DUPLICATE of bug 86575
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.4.4.3 release
Hardware: All All
: medium major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-07-20 11:49 UTC by Andrej
Modified: 2015-08-17 14:42 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Example file (5.08 KB, application/zip)
2015-07-20 11:49 UTC, Andrej
Details
Example file, more correct (5.17 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-07-20 12:38 UTC, Andrej
Details
Original; saved from Office2010; saved from LibreOffice again. (70.93 KB, application/zip)
2015-07-24 06:56 UTC, Andrej
Details
This file has a name with a reference to a sheet that was deleted (8.43 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-08-01 12:30 UTC, Buovjaga
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Andrej 2015-07-20 11:49:35 UTC
Created attachment 117341 [details]
Example file

When try to open attached file with FileFormatConverter for MS Office 2000, it shows "The converter failed to save the file."
When try to open this file with LibreOffice 3.4.2, it crashes.

I can open this file with LibreOffice 4.4.4.3, but if i do "Save" or "Save as..." .xlsx and send file to someone with old office or LibreOffice 3, they cannot open it.

Desired behavior: after "Save as..." .xlsx file can be opened anywhere  where .xlsx files are accepted.


I took my time and found that problem is caused by this content in file, hope it will help with solving this issue:
plagued_file.xlsx > workbook.xml > definedName > SUBSTITUTE(INDEX(Nosaukums,MATCH(#REF!!$C1,Nosaukums_sais,0))," ","_")
Comment 1 Andrej 2015-07-20 12:38:51 UTC
Created attachment 117342 [details]
Example file, more correct

Now i remember, this may be related to Menu > Data > Validity... custom lists.
Comment 2 tommy27 2015-07-22 02:18:16 UTC
please check the files you uploaded
I can open them both on LibO 4.4.5 and 3.4.3 but they look as empty spreadsheets.
am I missing something?
Comment 3 Andrej 2015-07-22 06:56:10 UTC
(In reply to tommy27 from comment #2)
> please check the files you uploaded
> I can open them both on LibO 4.4.5 and 3.4.3 but they look as empty
> spreadsheets.
> am I missing something?

Yes, sorry, i was testing with 3.4.3 portable version, i thought there is no difference.
Now i tested with regular 3.4.3 and 3.3.3 versions, they open document without crashing.

Yes, i deleted spreedsheet data from example file and it is empty, but there are some kind of metadata left, which i don't know how normal user can delete.
Comment 4 tommy27 2015-07-22 08:23:58 UTC
which portable version do you use?
I use the winPenPack version from http://www.winpenpack.com/main/download.php?view.1354

and it opens those files without any crash either using a recent LibO version or an old one (see the whole archive here: http://sourceforge.net/projects/winpenpack/files/X-LibreOffice/releases/ )

I suspect you use the PortableApps LibO version which IMHO is inferior to the winPenPack package.

anyway, do you still have the issues with MS FileFormatConverter?
I don't know that software, would you please explain what is it?
Comment 5 Andrej 2015-07-22 08:53:04 UTC
(In reply to tommy27 from comment #4)
> which portable version do you use?
> I use the winPenPack version from
> http://www.winpenpack.com/main/download.php?view.1354
> 
> and it opens those files without any crash either using a recent LibO
> version or an old one (see the whole archive here:
> http://sourceforge.net/projects/winpenpack/files/X-LibreOffice/releases/ )
> 
> I suspect you use the PortableApps LibO version which IMHO is inferior to
> the winPenPack package.
> 
> anyway, do you still have the issues with MS FileFormatConverter?
> I don't know that software, would you please explain what is it?

Now i installed and tested with LibreOffice 3.4.2 from http://www.oldapps.com/libreoffice.php?old_libreoffice=6155
and crash happens there.


FileFormatConverter is compatibility pack for MS Office 2000, 2003 etc, it allows to open .xlsx files in these old versions.
http://lmgtfy.com/?q=microsoft+office+2000+compatibility+pack  It can be installed without MS office by the way, but may break file associations.
It fails to open file if i not clean spreadsheet and delete "Names" data from file(see below).



> Yes, i deleted spreedsheet data from example file and it is empty, but there
> are some kind of metadata left, which i don't know how normal user can
> delete.
Ok, this metadata is in menu "Insert > Names > Manage..."
If i delete name "Lapa", then MS "FileFormatConverter" and LibreOffice 3.4.2 Portable can open this document.
Comment 6 Andrej 2015-07-24 06:56:15 UTC
Created attachment 117409 [details]
Original; saved from Office2010; saved from LibreOffice again.

"Save as..." from MS Office 2010 partially fixed file and FileFormatConverter can open it.
Would be good, if LibreOffice could repair file in same way (of course, if this will not take too much development resources from other and more important tasks)
Comment 7 Eike Rathke 2015-07-31 12:48:51 UTC
There's not much we could do about, in plagued_file.xlsx the defined name Lapa has the expression
SUBSTITUTE(INDEX(Nosaukums,MATCH(#REF!!$C1,Nosaukums_sais,0))," ","_")
where the #REF! is a result of a sheet being deleted where the original reference pointed to, presumably Sheet1.

In the other files of the attached .zip there's no #REF! in all three files, the expression references Sheet1 and Sheet1 is present, so probably there even wasn't anything to repair.

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.
Comment 8 Buovjaga 2015-08-01 12:30:04 UTC
Created attachment 117573 [details]
This file has a name with a reference to a sheet that was deleted

A file created from scratch in MSO 2013.
Comment 9 Andrej 2015-08-03 14:05:52 UTC
test result table:

file:                                   MS Converter    LO 3.4.2    LO 4.4.4
plagued_file2.xlsx                      Failed          Crash       Opened
FILE001.xlsx                            Failed          Crash       Opened
FILE001_Office2010.xlsx                 Converted       Crash       Opened
FILE001_LibreOffice.xlsx                Converted       Crash       Opened
sheet_match_fresh_deleted_sheet.xlsx    Converted       Opened      Opened
Comment 10 Timur 2015-08-17 14:42:22 UTC
As noticed in https://bugs.documentfoundation.org/show_bug.cgi?id=86575#c17, looks like a duplicate of Bug 86575, so I'll close as such.
Feel free to reopen if proved otherwise.

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