Description: 工作表显示两位小数的近似值，筛选器里显示真实值，导致无法筛选 The worksheet shows the approximate value of two decimal places, and the filter shows the real value, so it cannot be filtered Steps to Reproduce: 1.Open my attachment and filter for quantity 2. 3. Actual Results: 当筛选条件是一组非整数的值，单元格内设置保留2位小数，但是在筛选时显示的是一个保留13位小数的真实值，导致无法依据2位小数进行筛选。 When the filter condition is a group of non integer values, 2 decimal places are reserved in the cell setting, but a real value with 13 decimal places is displayed during filtering, so it is impossible to filter according to 2 decimal places. Expected Results: 我认为这是个体验的问题，筛选这个事，从体验和逻辑上来看，就应该是所见即所得，应该采用看得见的相对值，而不是绝对值，因为到底筛选的精度如何，需要筛选人来通过小数位数来控制，在7.0.6和7.1.5版本的测试中，虽然可以筛选，但是筛选器也是绝对值，这个不是很合理。 I think this is a matter of experience. From the perspective of experience and logic, screening should be what you see is what you get, and the visible relative value should be used instead of the absolute value, because the accuracy of screening needs to be controlled by the filter through the decimal places. In the tests of versions 7.0.6 and 7.1.5, although it can be screened, the filter is also an absolute value, This is not very reasonable. For example, in most work and life scenarios, we can keep two digits. Generally speaking, there is no difference between 95.554 and 95.553 for the data analysis of non military scientific research. After all rounding, it is 95.55, and the filter search 95.55 is two. If you use the absolute value to screen, you can only see one. Of course, you can check the similar ones yourself, But this is less practical. 举个例子，大多数工作和生活场景下，我们保留2位即可，一般来说，95.554和95.553 对于做非军工科研的数据分析来说，没有区别，所有四舍五入后就是95.55，筛选器搜95.55就是2条，如果用绝对值筛选，那么只能看到一条，当然你自己去挑看起来差不多的都勾上也行，但是这个就比较不实用了。 Reproducible: Always User Profile Reset: No OpenGL enabled: Yes Additional Info: Version: 7.2.0.4 (x64) / LibreOffice Community Build ID: 9a9c6381e3f7a62afc1329bd359cc48accb6435b CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win Locale: zh-CN (zh_CN); UI: zh-CN Calc: CL
Created attachment 174714 [details] About the functionality of standard filters
Created attachment 174755 [details] The example file in Excel and Calc side by side when filtering them
Created attachment 174756 [details] The example file in Excel and Calc after filtering them Confirming with: Version: 7.3.0.0.alpha0+ (x64) / LibreOffice Community Build ID: bc7baa18435000f47f90e47d3300710bcb4cf56b CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: win Locale: hu-HU (hu_HU); UI: en-US Calc: CL Filtering for formatted values makes all of them disappear.
Seems to have started with https://cgit.freedesktop.org/libreoffice/core/commit/?id=3069df790cca2917e5aedd87bac1af65f9605d51 author Balazs Varga <balazs.varga991@gmail.com> 2021-07-02 09:40:32 +0200 committer Xisco Fauli <xiscofauli@libreoffice.org> 2021-07-08 09:50:09 +0200 tdf#142910 sc filter: fix "greater than" or "smaller than" etc
Created attachment 174757 [details] Minimal reproducer made in current Calc master This is not xlsx-specific. Filtering this file also reproduces the issue. There is another issue: the Autofilter dropdown and the Standard filter dialog does not display these calculated number values (but not with constant values!) according to the cell format. This seems to be an independent problem, I'll file another bug for that.
Thank you for your careful test and reply! Look forward to libreoffice getting better and better!
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/51375b48378915b6e95c1ac26b2ccf8e39880f7e tdf#144253 tdf#144324 sc filter: use formatted values in filters It will be available in 7.3.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.
Balazs Varga committed a patch related to this issue. It has been pushed to "libreoffice-7-2": https://git.libreoffice.org/core/commit/73a0deeda1bc63e78de6aa3df9c3324b4ebe0d3b tdf#144253 tdf#144324 sc filter: use formatted values in filters It will be available in 7.2.2. 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.
Thank you very much for solving this problem!
I have tested the 7.2.3 dev release and confirmed that this issue has been fixed. Thank you very much to the developers and everyone who worked on this issue!
(In reply to dongshili from comment #10) Mark as VEIFIED FIXED accordingly.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/b60b6bfaafa1315e07108dba50f016975b619c59 Resolves: tdf#144740 Fix broken compareByValue() query, tdf#142910 tdf#144253 It will be available in 7.3.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-2": https://git.libreoffice.org/core/commit/825a96114a7693335be000b2aa661df88a02e273 Resolves: tdf#144740 Fix broken compareByValue() query, tdf#142910 tdf#144253 It will be available in 7.2.3. 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.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-2-2": https://git.libreoffice.org/core/commit/dc1642c3225ff4aeada5df749946f406772eb1ff Resolves: tdf#144740 Fix broken compareByValue() query, tdf#142910 tdf#144253 It will be available in 7.2.2. 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.