Bug 44720 - compatibility options for older spreadsheets ...
Summary: compatibility options for older spreadsheets ...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: Other All
: medium major
Assignee: Kohei Yoshida
QA Contact:
URL:
Whiteboard: target:4.0
Keywords:
: 37860 50345 (view as bug list)
Depends on:
Blocks: 37860 50345
  Show dependency treegraph
 
Reported: 2012-01-12 06:18 UTC by Michael Meeks
Modified: 2013-02-08 20:00 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
new configuration option to toggle this behavior. (19.65 KB, image/png)
2012-07-24 02:16 UTC, Kohei Yoshida
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Michael Meeks 2012-01-12 06:18:55 UTC
bug #37860# has some complaints about changes to behaviour of some corner cases in calculation to make us more interoperable.
It would be good to add some per-document settings that are tweak-able to allow these things to be altered. I snip Kohei's description:

[snip]
The goal: introduce a configuration option to toggle this behavior in an
user-friendly way.  One idea is to use the Formula options page to add such
option.  But consult with the UX people on the
libreoffice-ux-advise@lists.freedesktop.org to get their opinion on how such
configuration option should be introduced.
...
Also, there are other conflicting formula behaviors between OOo and Excel, so
it's probably best to make such option extensible to make it easier to add more
configuration options in the future.
...
Code pointer:

Formula interpreter:

* sc is the module where all the Calc code is kept, including the formula
interpreter code (look for class ScInterpreter).

* ScInpterpreter::GetCellValueOrZero() is the method that gets called to
retrieve the value of another cell.  This method is relevant for this easy
hack.

Configuration options:

* This configuration option should be stored with the document, so look for
ScDocOptions class to add a new document configuration option (also in sc).  It
has one child class ScDocCfg, which handles loading and storing of the options
from and to the user configuration directory.  Add the new option to the
aFormulaItem member.

* Now, every user configuration option needs to be defined in the officecfg
module.  Look for officecfg/registry/schema/org/openoffice/Office/Calc.xcs. 
The new option should be under the "Formula" group.

UI:

* First, you need ask on
libreoffice-ux-advise@lists.freedesktop.org about how best to add this new
configuration option in the UI - no subscription is required.

* Tab pages for Calc related options pages are all located in
sc/source/ui/optdlg.

I hope this is enough pointer to get started.
Comment 1 Michael Meeks 2012-01-12 06:20:47 UTC
Please do not add non-development related comments to this bug that are not based on the coding interactions :-)
Comment 2 Kohei Yoshida 2012-03-20 06:42:41 UTC
*** Bug 37860 has been marked as a duplicate of this bug. ***
Comment 3 Florian Reisinger 2012-05-18 08:19:28 UTC
Removed EASYHACK from summary
Comment 4 Kohei Yoshida 2012-07-23 19:11:55 UTC
I'll look into this for 3.7.
Comment 5 Kohei Yoshida 2012-07-23 19:20:12 UTC
No longer an easy hack.
Comment 6 Kohei Yoshida 2012-07-24 02:16:01 UTC
Created attachment 64581 [details]
new configuration option to toggle this behavior.
Comment 7 Kohei Yoshida 2012-07-24 02:16:43 UTC
This is now implemented on master (for 3.7).
Comment 8 Kohei Yoshida 2012-07-24 02:18:32 UTC
*** Bug 50345 has been marked as a duplicate of this bug. ***
Comment 9 Rainer Bielefeld Retired 2013-01-13 07:57:56 UTC
@Kohei Yoshida:
Can you please tell me where the configuration option shown in screenshot can be found?
Comment 10 Morten Leikvoll 2013-01-14 08:42:02 UTC
It seems that most spreadsheet sw is missing a proper way to condtionally set an empty cell. Why not create an empty() function to be used like:

if(cond,result,empty())

I guess traditionally we use if(cond,result,"") to do this today, but the use of an empty string as empty cell has unexplored consequences.

Of course any function with empty() as argument, should return as if the argument cell was deleted/untouched.
Comment 11 Kohei Yoshida 2013-01-14 14:27:53 UTC
(In reply to comment #9)
> @Kohei Yoshida:
> Can you please tell me where the configuration option shown in screenshot
> can be found?

It's in Tools-Options-Calc-Formula, and set the detailed calculation settings to 'Custom', then click on Details (or similar).  I may not be totally accurate as I don't have a build I can use at the moment...
Comment 12 David A. Wheeler 2013-02-08 18:34:51 UTC
FYI, the OpenFormula specification already has ISBLANK(), which tells you if a given cell is blank or not (and empty strings do NOT count as a blank). LibreOffice 3.5.6.2 (at least) already implements ISBLANK(), too.  So I think what some commenters wanted is already available.
Comment 13 Morten Leikvoll 2013-02-08 20:00:17 UTC
(In reply to comment #12)
> FYI, the OpenFormula specification already has ISBLANK(), which tells you if
> a given cell is blank or not (and empty strings do NOT count as a blank).
> LibreOffice 3.5.6.2 (at least) already implements ISBLANK(), too.  So I
> think what some commenters wanted is already available.

I'm aware of that, but that is not what I asked for. My request is a function to return the equivalent of an empty cell. Now we are forced to use "" to set [conditional] empty cell and that causes issues, because its not really an empty cell, but an empty string.