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).
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.
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.
Thank you for the attachment, vlb. I shall leave it to someone with an Excel newer than mine to take it further. Terry.
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
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.
(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.
Created attachment 111509 [details] new test file This file is small and can reproduce the same issue.
(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.
> > 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?
(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.
(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.
Created attachment 111527 [details] test file without hidden sheet test file without hidden sheet. Excel still show error.
Created attachment 111528 [details] simplified test file deleted both sheets, saved as .xlsx. Excel show error.
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)
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)
*** Bug 93045 has been marked as a duplicate of this bug. ***
Seems to be because of non-existing references (#REF!) in the named ranges. Same category as bug 92841
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.
*** Bug 92841 has been marked as a duplicate of this bug. ***
can solve this problem?
The problem is already in LO 5.1.0.3 (x64).
(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.
Taking care of this.
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.
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.
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.
Pending review https://gerrit.libreoffice.org/24705 for 5-1
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.
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)
@Elmar: What exactly did you test? Note comment 26.
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.
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.
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.
(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.
(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?
(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.
(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
(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
I close this bug and have make new bugs 99842 99921 99856 99892 99917 99866