GNOME Bugzilla – Bug 703355
Major problems with Export toward .ODS and .XLS files.
Last modified: 2013-07-03 17:57:21 UTC
Created attachment 248095 [details] Three spreadsheets zipped : one .ods strict conformal, one xls Excel 97 file and one gnumeric produced with gnumeric v1.12.3 I believe that there is many problems within export of gnumeric toward different files formats than the internal one. Please find in attachment an example of these problems. Here, this spreadsheet has two calc sheets inside. The first one contains 'data' and the second a linear regression performed on that data set. Thereafter, a conversion to the above mentionned formats can be done. However, the conversion is not working properly as neither EXCEL nor LIBREOFFICE can read properly this files. The references that come from the other are badly broken, especially for formula related stuff. I let you check what the actual problem is. Please find in attachment the three file format with .gnumeric file as the original source. This as more problematic consequencies as the .gnumeric file becomes more big and when there is a lot of references dependancies between sheets, especially for calculated values.
You need to be more specific with the problem you are seeing: I open the .gnumeric file in Gnumeric and save it as an ODF (extended) file. Then I open it in LibreOffice 3.5.4.2. Nothing seems obviously wrong. I do notice that your Gnumeric file uses SUMPRODUCT in Gnumeric rather than the ODF function ODF.SUMPRODUCT so you have a bunch of #NAME? results. But that would be expected. (SUMPRODUCT is the Excel compatible function and ODF.SUMPRODUCT the ODF compatible one. Unfortunately these functions are defined to act differently under certain circumstances.)
There was a problem with TDIST import/export from ODF (which has been fixed now) but I doubt that you encountered that one. I noticed it when saving the file in LO and opening it in Gnumeric.
I opened the .ods file with libreoffice.calc and looking in the Régression (1) sheet. There is 6 columns not succeeding to display correctly, displaying an error code which is "# NAME ?" in english. When I click on one of the concerned columns, I have this respectively in the "prediction" and "leverage" columns : * "=org.gnumeric.sumproduct($B$17:$B$18;TRANSPOSE(A21:B21))" * "=org.gnumeric.leverage($A$21:$B$31)" So it looks like the gnumeric functions are not properly replaced by the openoffice/excel equivalences.
That is a LibreOffice problem (or a user problem). There is no ODF function that matches Gnumeric's and Excel's SUMPRODUCT. Gnumeric also has an ODF function ODF.SUMPRODUCT that, under certain circumstances, behaves differently. You may wan tto use that function if you plan to save as ODF and use those files with LO or AOOo. ODF specifies that how such functions are supposed to be saved. We do exactly that.
I am reopening this since I forgot about the xls part. The ODF part is NOTGNOME but we still have to investigate the xls part.
This does indeed open with an error message in Excel. "Some number formats may have been lost." It also has lots of NAME errors due to the LEVERAGE function not being available, but that's not our fault. It's also not the cause of the format problems.
F16: empty format. (The 95%) Strangely I am unable to change the format for that one to "General".
This problem has been fixed in our software repository. The fix will go into the next software release. Thank you for your bug report. This was an issue with translations. It is fixed in the sense that we will no longer generate an invalid format in E16:F16. Existing sheets generated with French locale will need to have a format set for those cells. 0% will do.