| Summary: | ISLOGICAL function depends on column formatting EDITING | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | C Hemingway <chemingway> |
| Component: | Calc | Assignee: | Eike Rathke <erack> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | CC: | chemingway, erack, ilmari.lauhakangas |
| Priority: | medium | ||
| Version: | Inherited From OOo | ||
| Hardware: | All | ||
| OS: | All | ||
| See Also: | https://bugs.documentfoundation.org/show_bug.cgi?id=122191 | ||
| Whiteboard: | target:6.1.0 target:6.0.3 | ||
| Crash report or crash signature: | Regression By: | ||
| Attachments: |
ISLOGICAL before XLS (with Boolean formatting)
ISLOGICAL after XLS (boolean formatting lost completely) |
||
|
Description
C Hemingway
2018-01-31 21:05:25 UTC
Created attachment 139507 [details]
ISLOGICAL before XLS (with Boolean formatting)
Created attachment 139508 [details]
ISLOGICAL after XLS (boolean formatting lost completely)
Ok, the example file does not match the description, but anyway: column D has this formatting code: [>0]"T";[<0]"T";"F" ISLOGICAL seems to think it is broken and thus says FALSE for all the column D values. I want to check with Eike before doing anything. It is like it is. Calc does not have a distinct logical data type, but formatting as BOOLEAN displays 0 as FALSE and any other value as TRUE. Asking a cell for ISLOGICAL() therefore has to query the cell format whether it is bool. Excel has a distinct logical type, but does not (or does it meanwhile?) handle the ==0=>FALSE, <>0=>TRUE behaviour with a boolean format, so we have to export a "TRUE";"TRUE";"FALSE" number format for that. That again is not a boolean format but just a specific format with implicit >0;<0;=0 conditions. We maybe could recognize that very format during import and convert to boolean for ISLOGICAL() to work as expected, but note that ISLOGICAL() in Excel with an exported numeric value doesn't work either. However, converting this specific number format to a true BOOLEAN format during import might be expected. [>0]"T";[<0]"T";"F" is definitely a numeric user defined format and will never work in the way that ISLOGICAL() would recognize it. Bug Clarification: When I save an XLS or XLSX spreadsheet with a Logical field, when I later reload it, it loses the logical designation and becomes that [>0]"T";[<0]"T";"F" format. Clarification - on that 4th column, I changed the display format of the Boolean field to T;;F. When LibreOffice opens the file later, the boolean field has become numeric, and the column becomes [>0]"T";[<0]"T";"F". Should assigning a custom display change the column type on reload? For the ISLOGICAL test, is LibreOffice remembering (caching?) that the column is Boolean until the spreadsheet is reloaded? My apologies if I am explaining this incorrectly. (In reply to C Hemingway from comment #5) > When I save an XLS or XLSX spreadsheet with a Logical field, when I later > reload it, it loses the logical designation and becomes that > [>0]"T";[<0]"T";"F" format. There is no boolean logical type for D2:D3, even in the file format the cell type is office:value-type="float" calcext:value-type="float". That is because the [>0]"T";[<0]"T";"F" format is of type number and not boolean. > Clarification - on that 4th column, I changed the display format of the > Boolean field to T;;F. What do you expect? T;;F works only by accident (if T and F don't have some other meaning as format keywords) and then is the same as "T";;"F" Which when re-loaded becomes [>0]"T";[<0]"";"F" that is identical in meaning. Note the second subformat has an empty string, not "T" as your other format. I don't know how you got to the [>0]"T";[<0]"T";"F" > When LibreOffice opens the file later, the boolean > field has become numeric There never was a boolean "field" if you applied the T;;F format. > and the column becomes [>0]"T";[<0]"T";"F". > Should assigning a custom display change the column type on reload? There is no column type, there are cell formats which lead to numeric/date/boolean display cell types. > For the > ISLOGICAL test, is LibreOffice remembering (caching?) that the column is > Boolean until the spreadsheet is reloaded? No, as explained previously already, there is no distinct boolean type, it is all about number display formats. Btw, there's an odd user-defined number format in that document which loads as [>0]"T"[~jewish]YYYY"U"YY;[<0]"T"[~jewish]YYYY"U"YY;"FAL"S[~jewish]YY listed as T5663U663 and currently displays a FAL0660 preview on an empty cell or 0. Seems to be some result of your previous experiments, but it illustrates how almost any combination of valid keywords yields some result ;-) Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=ce4fc2fc08be8ea2773194e303ed42d2579e93a0 Resolves: tdf#115351 convert boolean equivalent format codes to proper Boolean It will be available in 6.1.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. Pending review https://gerrit.libreoffice.org/50645 for 6-0 Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-6-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=feb3da3dc53c9b702e39ec12b037b07a67bedcbd&h=libreoffice-6-0 Resolves: tdf#115351 convert boolean equivalent format codes to proper Boolean It will be available in 6.0.3. 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. |