Bug 140968 - FILEOPEN XLSX Autofilter does not take number formatting in account
Summary: FILEOPEN XLSX Autofilter does not take number formatting in account
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Balázs Varga
URL:
Whiteboard: target:7.2.0
Keywords:
Depends on:
Blocks: XLSX-Autofilter
  Show dependency treegraph
 
Reported: 2021-03-12 08:14 UTC by NISZ LibreOffice Team
Modified: 2021-06-18 07:20 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Example file from Excel (11.04 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2021-03-12 08:14 UTC, NISZ LibreOffice Team
Details
Screenshot of the original document side by side in Excel and Calc (150.59 KB, image/png)
2021-03-12 08:15 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description NISZ LibreOffice Team 2021-03-12 08:14:36 UTC
Created attachment 170421 [details]
Example file from Excel

Attached example file was made in Excel and contains some numbers formatted with two decimal places, some of which are filtered. Excels filter shows the numbers as they appear in the cells, with two decimals.
When opened in Calc the filter contains the unformatted numbers and none of them is selected.

Steps to reproduce:
    1. Open attached file

Actual results:
The autofilter dropdown does not show any items selected; numbers should be formatted as two decimals.

Expected results:
Autofilter should show 0,33 ; 4,00; 563,12; 12,60 selected and all numbers formatted with two decimals.

LibreOffice details:
Version: 7.2.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: 9d8accf03984a64a4105826e55b221962628eb93
CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: win
Locale: en-US (hu_HU); UI: en-GB
Calc: CL
Comment 1 NISZ LibreOffice Team 2021-03-12 08:15:04 UTC
Created attachment 170422 [details]
Screenshot of the original document side by side in Excel and Calc
Comment 2 Kevin Suo 2021-03-12 10:19:50 UTC
I do encounter this issue all the time. Set to NEW.
Comment 3 NISZ LibreOffice Team 2021-03-12 11:44:41 UTC
The attachment #118175 [details] in bug #93664 also has this issue with currency formatted cells being filtered as plain numbers.
Comment 4 Commit Notification 2021-03-25 14:01:27 UTC
Balazs Varga committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/4fd1333ba4bb4f2311e9098291154772bd310429

tdf#140968 tdf#140978 XLSX import: fix lost rounded filters

It will be available in 7.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 5 Kevin Suo 2021-03-26 10:16:41 UTC
(In reply to Commit Notification from comment #4)

Thank you for the commit.

However, I see the following description in the commit message:

"""
Now AutoFilter popup window shows the items according
to the visible cell format (e.g. 1.0 instead of 1.01 or
0.99), but still grouping them based on the "editing
format" (e.g. not rounded values which visible during
editing), i.e. there could be repeated values in the
filtering conditions (e.g. two options "1.0" and "1.0"
for 1.01 and 0.99).

Note: Next step will be to group and filter based on the
actual cell format, like MSO does, to simplify filtering
of values rounded by the cell format (e.g. selecting
the single AutoFilter condition "1.0" to filter both
1.01 and 0.99).
"""

It makes no sense to not group the same value as displayed in the autofilter. For instance, if the list has 1.000001, 1.000002, 1.000003, ..., 1.10000, then with a format of zero decimal place what we want to see in the autofilter list is just 1, but without the "next step" we will have 100,000 values.

This bug should not be marked as FIXED unless the "next step" is properly implemented, otherwise the displaying of 100,000 same value "1" in the list would be a new bug.

By the way: Balazs Varga are you already working on the "Next step", or you want someone else to work on this?
Comment 6 Balázs Varga 2021-03-29 07:12:14 UTC
> By the way: Balazs Varga are you already working on the "Next step", or you
> want someone else to work on this?

Yes i am already working on the "Next step". :)
Comment 7 Commit Notification 2021-05-04 08:14:35 UTC
Balazs Varga committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/d5c2584bf36d21580db677b231c57f99f49aa2cb

Related: tdf#140968 avoid duplicated filter values

It will be available in 7.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 8 NISZ LibreOffice Team 2021-05-11 05:55:58 UTC
Verified in: 

Version: 7.2.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: 1675a68526c43c6c6e4dc850ee911f0c1de75c88
CPU threads: 4; OS: Windows 10.0 Build 17134; UI render: default; VCL: win
Locale: hu-HU (hu_HU); UI: hu-HU
Calc: threaded