Bug 56209 - FILEOPEN: opening a .ods file saved in Excel (pre-ODF1.2) damages all formula
Summary: FILEOPEN: opening a .ods file saved in Excel (pre-ODF1.2) damages all formula
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.6.2.2 release
Hardware: Other All
: high major
Assignee: Eike Rathke
URL:
Whiteboard: target:4.2.0 target:4.1.4
Keywords: regression
: 67252 68176 (view as bug list)
Depends on:
Blocks: mab4.0
  Show dependency treegraph
 
Reported: 2012-10-19 21:58 UTC by Horst
Modified: 2013-11-01 12:08 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
TFRACXLS.XLSX original Excel file (11.57 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2012-10-19 21:59 UTC, Horst
Details
TFRAC.ODS saved in Excel as .ods (3.27 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-10-19 22:01 UTC, Horst
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Horst 2012-10-19 21:58:27 UTC
Opening the file TFRAC.ODS in CALC has all formulas replaced with
msoxl=[formula].
Col A contains =PI() -> msoxl=PI()
Col C contains =A2..7 -> msoxl=A2..7.
The file is only 1/3 of the .xlsx or .ods saved out of CALC.
Gnumeric opens the file correctly.
Comment 1 Horst 2012-10-19 21:59:51 UTC
Created attachment 68819 [details]
TFRACXLS.XLSX original Excel file

Created this file in Excel for another bug.
Comment 2 Horst 2012-10-19 22:01:34 UTC
Created attachment 68820 [details]
TFRAC.ODS saved in Excel as .ods

Saved TFracXLS.XLSX as TFRAC.ODS in Excel
Comment 3 Horst 2012-10-21 00:53:56 UTC
TFRAC.ODS loads fine in LO 3.5.7.
Comment 4 Markus Mohrhard 2012-10-25 21:22:44 UTC
Excel uses its own custom namespace. You need to use Excel 2013 which finally supports OpenFormula.

You can't excange ods files with Excel before Excel 2013.
Comment 5 Horst 2012-10-26 01:14:00 UTC
(In reply to comment #4)
> Excel uses its own custom namespace. You need to use Excel 2013 which
> finally supports OpenFormula.
> 
> You can't excange ods files with Excel before Excel 2013.

Why does LO 3.5x read the file???
Comment 6 Michael Meeks 2012-10-26 07:45:05 UTC
Good question; I assume that we provided support for importing formulae from the MS namespaced formulae in ODF into Calc in the past - no idea why we might have dropped that. A quick git grep for msoxl in sc/ suggests we had some work for that in the past.

Markus - are you sure this is notourbug ? :-)
Comment 7 Maxim Monastirsky 2013-07-31 06:54:08 UTC
*** Bug 67252 has been marked as a duplicate of this bug. ***
Comment 8 p_kongstad 2013-07-31 16:55:43 UTC
I think that the importance of this bug should be much higher.

If a document created in LibreOffice and saved as .ods is being modified in Excel 2010 then formulas are being destroyed. This is a nasty bug and should be resolved quickly
Comment 9 Michael Meeks 2013-07-31 20:12:40 UTC
Making this an enhancement - to support Microsoft's pre-ODF1.2 mashed formula syntax.
Comment 10 p_kongstad 2013-08-01 09:52:17 UTC
In the newly release SP2 for Microsoft Office 2010 the bug seems to be corrected.
Comment 11 Maxim Monastirsky 2013-08-01 10:57:45 UTC
(In reply to comment #10)
> In the newly release SP2 for Microsoft Office 2010 the bug seems to be
> corrected.

Unfortunately I can't confirm this. I opened TFRACXLS.XLSX in Excel 2010 SP2, saved as ODS, and then opened it in LO 4.1.0.4. The results are the same.
Comment 12 tommy27 2013-08-03 08:56:23 UTC
tested with LibO 3.5.7, 3.6.7, 4.0.4 and 4.1.0 final releases on Win7 64bit.

all versions can load the original .xlsx file and save it as .ods with no corruption.

all versions but 3.5.7 have troubles visualizing the .ods file saved with Excel.

so it's a regression in the 3.6.x branch.

moving from 3.6mab to 4.0mab since 3.6.x is EOL and issue still affects 4.0.x branch
Comment 13 ecoromka 2013-08-21 06:23:10 UTC
Have this bug on 4.1.0.4 on Windows.
tfracxls.xlsx opens normally. tfrac.ods is broken.
Comment 14 Maxim Monastirsky 2013-09-15 15:37:01 UTC
*** Bug 68176 has been marked as a duplicate of this bug. ***
Comment 15 Markus Mohrhard 2013-10-26 12:27:12 UTC
At least exporting ODS files and importing them into MSO Excel before 2013 will not work. I'm unsure about the import. If that worked it was a not very widely used feature and at least I'm not ware that it ever worked correctly.

I think we have no own grammar for the MSO Excel 2010 ODF formulas so this feature was always only a second class citizen in the code. Maybe we mapped them to OOXML formulas or tried to use OpenFormula without the namespace prefix.

The only person who might know if that was ever truly supported is Eike.
Comment 16 Eike Rathke 2013-10-28 12:25:33 UTC
AFAIR there wasn't an explicit support of these 'msoxl:' namespace
formulas (except the reference address part), but things may have worked
by chance, we may have assumed OOXML syntax in that case and used
a different formula parser which for some reason we don't do now. I'd
have to dig through 3.5 code and compare.

The joy of Excel 2010 "supporting" ODF is that they deliberately broke
the formula part, to their "excuse" as it wasn't specified with ODF 1.0
(ISO/IEC 26300:2006) and they purposely implemented only what was
specified in the ISO standard. On import of ODF it even strips all
formulas. However, with Excel 2013 they implemented OpenFormula of ODF
1.2, so if you want real ODF spreadsheet interoperability in MS-Excel
you need Excel 2013.
Comment 17 Eike Rathke 2013-10-28 18:20:10 UTC
Presumably found the cause, trying to fix.
Comment 18 Commit Notification 2013-10-28 22:11:03 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

resolved fdo#56209 reviving FilterFormulaParser



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 19 Eike Rathke 2013-10-28 23:17:42 UTC
Pending review
for 4-1 as https://gerrit.libreoffice.org/6469
for 4-0 as https://gerrit.libreoffice.org/6470
Comment 20 Commit Notification 2013-10-31 12:15:04 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=4a7096b9c8c0e6451fa0ced06143cb8a65ec10fc&h=libreoffice-4-1

resolved fdo#56209 reviving FilterFormulaParser


It will be available in LibreOffice 4.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 21 p_kongstad 2013-11-01 10:16:04 UTC
Hi tested Version: 4.2.0.0.alpha0+
Build ID: 2ee41f402fdea5bed8076295ae3e94c79f92bd64
against one of my own files.

I confirm this patch resolves issues with my file.

Just wondering why this essential patch was not shipped against 4.1.3 also as many user gets files from MSO 2010. Now we have to wait for 4.1.4. :(
Comment 22 Cor Nouws 2013-11-01 12:08:04 UTC
(In reply to comment #21)

> I confirm this patch resolves issues with my file.

thanks for verifying!
 
> Just wondering why this essential patch was not shipped against 4.1.3 also
> as many user gets files from MSO 2010. Now we have to wait for 4.1.4. :(

Ah yes ..  4.1.3 was far ready when the patch arived. Luckily 4.1.4 will be there soon. RC1 will be there in about 4 weeks.
  https://wiki.documentfoundation.org/ReleasePlan/4.1#4.1.4_release