Apache OpenOffice (AOO) Bugzilla – Issue 77996
Localized Excel's CELL function fails to re-calculate
Last modified: 2013-08-07 15:14:03 UTC
We've encountered an issue where opening an Excel file saved in French version of Office 2003 in Calc causes Err502 upon recalculation. Further investigation revealed that it was due to CELL function's 1st argument being localized in Excel, but Calc only handles the English keywords in the 1st arg of CELL function. French version of MS Office does the following mapping (fr->en): adresse - address colonne - col contenu - contents couleur - color format - format largeur - width ligne - row nomfichier - filename parentheses - parentheses prefixe - prefix protege - protect type - type A temporary fix is to translate these French keywords into English in ScInterpreter::ScCell() before the 1st arg is compared in a series of if/else-if statements, but I was wondering what the "right" fix would be to solve this problem. Kohei
Created attachment 45552 [details] Excel file containing CELL function (English)
Created attachment 45553 [details] Excel file containing CELL function (French)
There is no "right fix" for this type of brain-dead nonsense. Localized string arguments are a no-go. Localized Excel versions know the localized and the English strings, an English Excel knows only English strings. A German localized Excel is not able to interpret a localized document originating from a French localized Excel. The only thing we could do would be to implement some mapping tables that make parameters accept dozens of different localized strings, preferring those of the UI localization and the working locale in case the same string would have a different meaning in different languages. IMHO there is no mechanism though in the l10n framework that would allow to add arbitrary localizations to an existing item and have all localizations available in each UI localization.. so we'd have to start something from scratch. I definitely do not want a list of strings in the source code. Btw, also MOOXML and ODFF define English strings only.
Kohei - I agree with Eike ;-) on the other hand - *iff* we can be sure that the French Excel accepts English string names eg. "address" as well as french ones (worth checking older versions too I guess): then we could add the translation as part of the import process [perhaps] - or, failing that - part of the export to ODF process :-) [ so we don't have that mess in our file-format ].
> *iff* we can be sure that the > French Excel accepts English string names Well, that's what Daniel alleged ;-) I just had an English UI at hand and to have French strings accepted it is not sufficient to enable the French language support in MS Office Language Settings and set the Windows Regional Settings to French. > then we could add the translation as part of the import process > [perhaps] - or, failing that - part of the export to ODF process :-) > [ so we don't have that mess in our file-format ]. Import/export translation would only work for constant parameters. It would fail as soon as a parameter is built from a cell reference or some formula.
> *iff* we can be sure that the > French Excel accepts English string names I just verified this. So, in French version of MSO 2007, the following two formula inputs produce the same result: =CELLULE("adresse";C2) =CELLULE("address";C2) I'm downloading now the French version of MSO 2003 just to make sure this still applies in the previous version. Will post my test result here once done. > it is not sufficient to enable the > French language support in MS Office Language Settings and set the > Windows Regional Settings to French. I initially tried that too, and didn't work either. It seems that the only way to test this is to have both the French version of Windows and MSO. :-(
> I'm downloading now the French version of MSO 2003 just to make sure this still > applies in the previous version. Will post my test result here once done. Yup. It works the same way in MSO 2003.
nice; so my vote (in general) would be to bloat and slow down the microsoft importer, rather than the OO.o core :-) I believe in the past I wrote a little util to map LOG(n) -> LOG10(n) for ODF export [ and some other missing argument handling ]: can we do the same for XL import & just normalize it nicely ?
> Import/export translation would only work for constant parameters. It > would fail as soon as a parameter is built from a cell reference or > some formula. urgh - good point & unbelievable ! ;-)
Created attachment 45734 [details] Patch to add cell keyword translator singleton.
Ok. This patch adds a simple-to-call translator singleton class in case a cell function needs its argument translated. I've already added bits to call this service for the CELL function (just a one-liner). Unfortunately we don't have any more information on whether there are other cell functions with localized arguments, but it's easy to extend the translation table later. Kohei
Nice one. Since in Excel the existence of localized keywords depends on the UI localization (that usually does not include country information) I think it would be better to not have lclLocaleEqual() match on the full locale only but if no full match was found fall back to language_country and then to language, and use addToMap() with a 'fr' language only. What do you think? Eike
Ah, I forgot, yes there is one more function with string arguments, INFO, that may have localized keywords. It seems you could easily verify with your French Excel.
@er: I agree on full locale match -> language-country -> language fall back mechanism. Let me work on this, and I'll post an updated patch. I also got a feedback from mmeeks about tidying up the initialization code. So, I'll make some change there as well. Kohei
Created attachment 45743 [details] revised patch
This new patch adds the locale fallback logic that er proposed. Also, I've consolidated all transKeyword methods into one by using default parameters & did some clean up in the initialization. And more mapping for the INFO function as well.
Yes, fallback is good I think, and init is also cleaner. However, the OpCode isn't part of the data anymore and by having declared the default parameter in addToMap() it isn't noticed. Btw, for type safety that should be ocNone instead of SC_OPCODE_NONE. The SC_OPCODE_... defines are only needed/used for the resource and are assigned to OpCode values in sc/inc/opcode.hxx and not used elsewhere. For simplicity I'd add the OpCode to the TransItem array. The call to ScCellKeywordTranslator::transKeyword() then should include ScGlobal::pSysLocale and the calling function's OpCode. Are the French translations really simple ASCII strings in Excel? No letters with accents? If there were non-ASCII characters involved the thing wouldn't work.. maFR doesn't need to be a member variable of ScCellKeywordTranslator, it can be constructed temporarily in ScCellKeywordTranslator::init(). Btw, a word on coding style: for references to objects we use the 'r' prefix, so for parameters it would be rKey instead of aKey, rName instead of aName, rLocale/aLocale, ...
>Are the French translations really simple ASCII strings in Excel? No >letters with accents? As far as I know there is no accented letters allowed in a keyword, even in the French version of Excel. According to the online help file, The Excel version of INFO is supposed to accept "répertoire", which has an accented letter. But even then, the function only accepts "repertoire" without the accent. If you give it "répertoire" literally, it produces an error.
Created attachment 45787 [details] revised patch
This patch addresses er's comments. It also stores names in sal_Char* pointer instead of duplicated String's, to save some memory usage. The keys are still stored as String instances, however, because cell function arguments are given as String in ScInterpreter.
This patch addresses Eike's comments. It also stores names in sal_Char* pointer instead of duplicated String's, to save some memory usage. The keys are still stored as String instances, however, because cell function arguments are given as String in ScInterpreter.
@er: any remaining issues with this? If there is no more issues, I'll go ahead and work on integrating this.
Ah, yes, looks ok now. Please go ahead, I'm reassigning this issue to you. Once when ready and the code is in one of your CWSs that goes ready-for-QA please reassign the issue to the QA-rep then and adjust the target milestone to something appropriate. Thanks Eike
Will do. Thanks for the "go ahead". :-)
Hi Kohei, If you need hands for tests, don't hesitate to ask. I'm adding me in CC. Kind regards - Sophie
Hi Sophie, Thanks for the offer. :-) I think I'm ready to hand my cws celltrans01 for QA.... though I don't know exactly how to do that (haven't integrated cws before). Anyway, the info below is for the QA purposes. The following keyword mapping should be recognized. // CELL function, 1st argument (French -> English) adresse -> address colonne -> col contenu -> contents couleur -> color largeur -> width ligne -> row nomfichier -> filename prefixe -> prefix protege -> protect // INFO function, 1st argument (French -> English) nbfich -> numfile recalcul -> recalc systexpl -> system version -> release This mapping should work universally regardless of current locale setting. Also, typing the English keywords should also work. Two notes: INFO("numfile") and INFO("nbfich") should always return 1, and INFO("osversion") and INFO("versionse") should return "Windows (32-bit) NT 5.01" for "compatibility reasons". I personally don't know the exact reason why, though. ;-)
I forgot the copy & paste the last line. So, here is the complete list to test for. // CELL function, 1st argument (French -> English) adresse -> address colonne -> col contenu -> contents couleur -> color largeur -> width ligne -> row nomfichier -> filename prefixe -> prefix protege -> protect // INFO function, 1st argument (French -> English) nbfich -> numfile recalcul -> recalc systexpl -> system version -> release versionse -> osversion
@oc: I found your name here: http://wiki.services.openoffice.org/wiki/Team_Leads Could you recommend someone to QA this cws (celltrans01) ?
Created attachment 46501 [details] Hungarian (hu) translation of parameters
@timar: Thanks for your input! One question. Some of those Hungarian keywords appear to have an accent character. Is that what the Hungarian version of Excel uses? This feature is primarily for Excel compatibility, so the keywords need to be identical to those that the Excel version of corresponding functions uses. The French version of Excel, for instance, doesn't allow accent characters in keywords.
This is _only_ for Excel compatibility. If the Hungarian localized Excel version does not have localized keywords they must not be added to OOo.
adding me to cc
re-assigning it to fst for QA.
setting the status to FIXED
Make target milestone match the CWS' release target.
Created attachment 46940 [details] testdoc with CVELL and INFO function parameters in French
found fixed on cws celltrans01 using Solaris, Linux and Windows build.
Re: Hungarian strings I forgot to add myself to CC and I did not see your comments. :( I confirm that these localised parameters work in Hungarian Excel (version 2003 was tested). You can use attachment later when you have a chance.
found integrated on master OOG680m1 using Linux, Solaris and Windows build