https://bugs.documentfoundation.org/show_bug.cgi?id=92256
--- Comment #11 from Christoph Lutz <chrl...@googlemail.com> --- The more I think about this issue, I think variant 3) is the correct choice. So this reply is just about points that touch variant 3): (In reply to Katarina Behrens (CIB) from comment #9) > The status quo (INDIRECT function uses preferred formula syntax from the > global settings and it is not possible to mix two different types of syntax > in one document) is The Right Thing (TM) to do from the developers' and > interoperability perspective. I now agree that a setting for different formula syntaxes is a good thing. It forces the user to make conscious decisions and in the end provides more control about what happens in case of exports. If I talk about "user" I mean this "experienced user" that is able to write the formula. This user needs to know what he is doing. I don't see any reason for only supporting "preferred formula syntax from the global settings". I agree that it is useful to have a global default setting, but I think this setting should not be global, but document specific. Just to make this clear: The attached example document is only a demonstration example. Yes, it contains both syntaxes in one document, but I don't think this is really an important requirement. So from my pov we don't need support for both syntaxes in the same document. But what we need is support for different syntaxes in different documents. I assume that our customer has documents with Excel- and documents with Calc-syntax and all these documents must work without forcing *ALL users* (even the "not experienced user", that just want to use the formula, that an "experienced user" has created) to know and understand the details about formula syntaxes. So I don't agree that the current behaviour is "yet the Right Thing", because it forces ALL users to switch between these settings, If a document is opened that uses a different syntax than the current selected one. And this is the reason why I suggested in variant 3) to introduce a new document specific metadata and store the document specific setting there. > For the user, I can understand that in comparison with the past version ( < > 4.0 ) this can be viewed as regression, as in their documents, there's > suddenly #REF! error message where their formulas used to be. It's just that > those older versions of LibO were bug-compatible I think we need to extend the existing behaviour up to a point that a "not experienced user" can work with these documents without noticing any change under the hood. And a broken document (what we currently have) will be noticed. > > 3) Don't evaluate both variants at the same time, but switch the > > syntax-format automatically. Indicators could be: > > > > * If it is an Excel-Document (.xlsx, .xls), set "Excel A1" > > Actually we already have this solution in place ( > http://cgit.freedesktop.org/libreoffice/core/commit/ > ?id=158b50763962f66515062300e265839828463efa ) for xls[x] documents produced > by MS Office > > It can't be done for ODF on filter level, sadly, as after the original Excel > document has been saved (by whichever app) as ODF, no one can really tell it > was an Excel document before. Yes, no algorithm is able to decide if a ODS document without the above mentioned metadata setting contains Excel or Calc syntax. But If we had the metadata, It would be easy to add this information. Just open the document, select the correct syntax and safe it back. These fixups are easy enough, so they can be done by supporters or the initial "experienced" authors very quickly. And the effect is that ALL other users can use the document without problems. One might say: fixing up a document is already possible if you just change the "!" into a ".". Yes, this is true, but If I look at the concrete document (not the attached example document, but the document that we recieved by the customer), I see big and nested formulars there. Some contain two INDIRECT-functions in one formular. And all these formulars are repeated multiple times. Changing "!" to "." there has two disadvantages: - The risk of introducing new bugs in formulars - the loss of interoperability with Excel So I think this kind of fixup is not what we want. > > > * In case of ODS-Documents, read setting from a new (LO specific) > > metadata-flag or fallback to default "Calc A1" > > > > We could then add the ability to write this (LO specific) metadata-flag > > according to the current formular-syntax setting in tools->options (on save) > > Yep, saving producent's string ref syntax (as a metadata flag, possibly in > document settings) and using that syntax when loading the document would be > a solution. It would however not repair existing documents automatically, > some form of user intervention or another would be required in any case. > > Correct me if I'm wrong, but the more I think about this issue, the more it > seems to me that the best course of action here is to document the > incompatibility (some errata? release notes?) and educate the users how can > they repair their documents I agree that manual fixups are probably the best thing we can do. Just to summarize the requirements: - support for both syntaxes in different files is required - fixups should be doable as easy as possible to avoid new risks and interoperability issues. - unexperienced users shall not need to know details about formulars. Nobody wants to train / inform 15k-20k users because of this. I think approach 3) could do that. -- You are receiving this mail because: You are the assignee for the bug.
_______________________________________________ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs