Created attachment 39954 [details] Original Excel spreadsheet The problem is observed during the import of an Excel spreadsheet file: The same area of cells can be redefined with several names, these various names are generated with the original name followed by an index. With LibO (as well as OOo 3.3 RC3) this index is separated from the name by a space: Example: profil_section 2 profil_section 3 This form is not correct. This is a regression because names are correctly generated with version 3.2 (an underscore is used instead of a space).
Created attachment 39955 [details] Calc spreadsheet with invalid names Result of the import of the Excel file
Confirmed under Ubuntu too. Reassigned to Kohei - Thanks - Sophie
Just took a quick look, tracing a reference. There is a "missing operator" error at tds:p11. The formula is "=SI(OU(G11<$H$7;G11>$J$7);"";ARRONDI.SUP(($O11+K11^2)^0,5;2))" I don't have Excel (or Microsoft windows), so I don't know what it should be. The same error message appears in neighbouring cells. This formula appears to have correct syntax - so it is an error by LibreOffice in resolving the formula. 1) if either G11 < H7 or G11 > J7 then should give a blank (or null) 2) otherwise should give square root of (O11 + K11^2), rounded up, to 2 decimal places Unless I'm missing something ...
further note : My LibreOffice is in French. In English, the formula giving the "missing operator" error message (probably) would be : "=IF(OR(G11<$H$7;G11>$J$7);"";ROUND.UP(($O11+K11^2)^0,5;2))" Also, I'm running Mandriva Linux - André
(In reply to comment #3) > Just took a quick look, tracing a reference. > > There is a "missing operator" error at tds:p11. > The formula is > > "=SI(OU(G11<$H$7;G11>$J$7);"";ARRONDI.SUP(($O11+K11^2)^0,5;2))" > > I don't have Excel (or Microsoft windows), so I don't know what it should be. > The same error message appears in neighbouring cells. > > This formula appears to have correct syntax - so it is an error by LibreOffice > in resolving the formula. > > 1) if either G11 < H7 or G11 > J7 then should give a blank (or null) > > 2) otherwise should give square root of (O11 + K11^2), rounded up, to 2 decimal > places > > Unless I'm missing something ... The formula you show is in sheet “CI” and not “tds” You don't need to use Excel to see the problem: look at the evaluation of cell P11 in sheet “CI”: P11 <= G11 <= F11 <= H27 <= C27:G27 (P11 <= G11 means that the formula in P11 use the evaluation of formula in G11) Formula in area C27:G27 refers to the named area “profil_section 3” (with a space between section and 3), this is an invalid name and the first occurrence of Err:509 in the series of cells. With Excel and OOo 3.2 the area is named “profil_section”.
What I don't understand is, while Excel shows only profil_section name, Calc shows profil_section profil_section 2 profil_section 3 and only the last entry contains valid reference. There is something definitely funny going on here during the import of range names.
Ok. dr changed something here. The bug number points to Oracle's internal bug tracker (#163146#), so we'll never know what his motivation really was.
Ah, dr's change is responsible for the underscore to space name changes, but that change is *not* responsible for the breakage of the formulas. That's just one harmless change that happened to be visible. The real issue here is this: the test document contains lots of local defined names i.e. names that are available only at sheet scope, but Calc only supports global names at the moment. So, this bug is just a manifestation of that limitation. The real fix is to properly support sheet-local names so that we could cleanly import Excel's sheet-local names. But that's for future versions.
IOW, the fact that the formula worked at all in 3.2 is just a coincidence; it shouldn't have worked.
Created attachment 39996 [details] name manager in Excel 2007 Excel 2007 has a better UI for defined names, which clearly shows multiple definitions of profil_section etc, and their associated scopes. The Workbook scope means the name is available globally to all sheets, otherwise the sheet name where the name is available is shown.
Changing the title accordingly. Technically this is an enhancement for better Excel interop.
Actually, this may have nothing to do with sheet-local name import. Changing the title back. Michel, I don't see any Err:509 here. Can you tell me exactly in what cells you see Err:509? I'm using the latest development build, so your problem may have already been fixed. But I do need to double-check that.
Created attachment 40001 [details] This is what I see in CI.P11 This is what I see in CI.P11, using the latest development build.
I still believe that we need to import sheet-local names correctly by implementing support for them in calc core, but this bug doesn't seem to be caused by that limitation.
(In reply to comment #5) > (In reply to comment #3) > > Just took a quick look, tracing a reference. > > > > ... > > > > Unless I'm missing something ... > > The formula you show is in sheet “CI” and not “tds” > You don't need to use Excel to see the problem: look at the evaluation of cell > P11 in sheet “CI”: > P11 <= G11 <= F11 <= H27 <= C27:G27 > (P11 <= G11 means that the formula in P11 use the evaluation of formula in G11) > Formula in area C27:G27 refers to the named area “profil_section 3” (with a > space between section and 3), this is an invalid name and the first occurrence > of Err:509 in the series of cells. > With Excel and OOo 3.2 the area is named “profil_section”. Ok, it is in sheet "CI". I see now. It is actually H27 <= f(C27 to G27) =RECHERCHEV($B27;profil_section 3;offset_column) So it's just carrying forward the Err:509 "missing operator" error. Maybe this worked (or at least seemed to work) in OOo 3.2 because the trailing " 3" was ignored ?
(In reply to comment #13) > Created an attachment (id=40001) [details] > This is what I see in CI.P11 > > This is what I see in CI.P11, using the latest development build. I downloaded the spreadsheet showing the errors. Everywhere in your screenshot you show a %, there was "Err :509". So it is evidently now fixed.
Thanks. I'll happily mark this fixed. :-)
(In reply to comment #12) > ... > > Michel, I don't see any Err:509 here. Can you tell me exactly in what cells > you see Err:509? > > I'm using the latest development build, so your problem may have already been > fixed. But I do need to double-check that. Checked with libreoffice-build 3.2.99.2 I obtain two different results according to the way of proceeding: 1) In one step: open the Excel file and save it with LibO in ODS format = > error 509 2) In two steps: a) open the Excel file and save it with LibO in XLS format, then close the file (closure is compulsory !) b) open the xls file created with LibO and save it in ODS format = > correct ! There is a solution which works, it is less urgent, but isn't it surprising ? What do you think of that ?
(In reply to comment #18) > I obtain two different results according to the way of proceeding: > 1) In one step: > open the Excel file and save it with LibO in ODS format > = > error 509 Reproduced. Not good. > 2) In two steps: > a) open the Excel file and save it with LibO in XLS format, then close the > file (closure is compulsory !) > b) open the xls file created with LibO and save it in ODS format > = > correct ! > There is a solution which works, it is less urgent, but isn't it surprising ? > What do you think of that ? Based on what we see, we may be using something that Excel supports (hence xls import / export retains fidelity) but ODF doesn't. Let me look into that a bit. Meanwhile, if you could also reduce the size of your test document that can still reproduce the bug, I will be very grateful. :-)
Ok. I'm pretty sure that the Err:509 is caused by an incorrect parsing of the formula string involving a name with whitespace. Now, fixing this will not be easy, and anything we do other than supporting sheet-local range names would be a hack. So, I still think the correct solution is to implement sheet-local range names even though that will require more engineering resources. Let me re-open this.
Changing the title once again.
(In reply to comment #19) > Meanwhile, if you could also reduce the size of > your test document that can still reproduce the bug, I will be very grateful. > :-) I'm going to try, but not before 2 or 3 days, that's ok ?
(In reply to comment #22) > I'm going to try, but not before 2 or 3 days, that's ok ? Yup, no rush. Whenever you have time. :-)
Created attachment 40126 [details] wrong result of importation Here are 2 files which seem identical (I use Excel 2003): - “reduced_file.xls” is obtained by reducing my initial file - “new_file.xls” is a file created by reproducing manually “reduced_file.xls”. They look identical, but the size is different, and the result of importation (in one step) is wrong with “reduced_file” and correct with “new_file”. Then, it is a bug of Calc or Excel ? There is something which is not visible in reduced_file and raises problem.
Created attachment 40127 [details] good result of importation
Looks like the next significant release will be 3.4, not 3.3.1. Resetting the target accordingly.
I'm working on this on master (for 3.4). The reduced file now calculates correctly. The original file causes Calc to crash now. Looking into that. :-/
(In reply to comment #27) > I'm working on this on master (for 3.4). The reduced file now calculates > correctly. The original file causes Calc to crash now. Looking into that. :-/ Ah! The crash has nothing to do with my change, but someone else's. ;-) Anyway, the original file now loads and I don't see any more Err:50X's. I still have to work on ODS import and export and the UI part, but I'm making progress.
The core change as well as ODS XLS import export are done & are merged into master. All that remains is the UI change.
All done. This just landed on master, in time for 3.4.