Sorting data containing non alpha numeric characters is not working correctly in Java

Aspose.Cells Product Family
,
1

Hi,
we are using aspose cell java four our project. The sorting is not working as expected when it contains non alpha numeric characters. The result does not match with the excel result
Excample : aa-ab, aab, |aa, aa cd, ab!cd

we are using aspose cell version 19.8

thanks

2

@cgkrish,

Please try using our latest version/fix: Aspose.Cells for Java v21.1. If you still find the issue with latest version, let us know with details, sample code (runnable) and sample file(s), we will check it soon.

PS. please zip the Excel file(s) prior attaching here.

3

Thanks for quick repose. we have the last version 20.9.8 in our system. This also gives the same incorrect result. you can check with the above sample data in both API and excel sorting. :
aa-ab
aab
|aa
aa cd
ab!cd

4

@cgkrish,

Please notice, I am able to reproduce the issue as you mentioned by using the following sample code (which I created for testing). I found sorting is not working when data contains some non-alpha numeric characters:
e.g.
Sample code:

Workbook workbook = new Workbook();
        Cells cells = workbook.getWorksheets().get(0).getCells();
        int maxRow = 4;
        cells.get("A1").putValue("aa-ab");
        cells.get("A2").putValue("aab");
        cells.get("A3").putValue("|aa");
        cells.get("A4").putValue("aa cd");
        cells.get("A5").putValue("ab!cd");
        DataSorter sorter = workbook.getDataSorter();
        sorter.setKey1(0);
        sorter.setOrder1(SortOrder.ASCENDING);
        sorter.sort(cells, 0, 0, maxRow, 0);
        workbook.save("f:\\files\\out1.xlsx");

I have logged a ticket with an id “CELLSJAVA-43386” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.

5

Thanks,
let us know once it fixed. We will get the latest library.
Thanks very much for quick response.

6

@cgkrish,

Sure, we will keep you posted with latest updates on your issue.

7

@cgkrish,
From Aspose.Cells for Java 21.4 version, we will provide new APIs for complicated string comparison:

GlobalizationSettings.getCollationKey(String, boolean)/compare(String, String, boolean)

By default we just use the compare method of String object of Java.

To get the locale dependent string comparison/sorting, please implement those new APIs to give the expected result of comparison (such as by java.text.Collator, or similar APIs of icu library). By our test, it seems java.text.Collator cannot sort all of those strings in your case correctly either. The icu library can work much better.

Example of the implementation which may work for your case:

Workbook wb = ...;
...
wb.getSettings().setGlobalizationSettings(new MyGlobalization());
...

class MyGlobalization extends GlobalizationSettings
{ //here you may import java.text.Collator of JDK or com.ibm.icu.text.Collator of icu library
    private final Collator ciCaseIgnore = Collator.getInstance();
    private final Collator ciCaseSensitive = Collator.getInstance();

    public MyGlobalization()
    {
        ciCaseIgnore.setStrength(java.text.Collator.SECONDARY);
        ciCaseSensitive.setStrength(java.text.Collator.TERTIARY);
    }
    @Override
    public Comparable getCollationKey(String s, boolean ignoreCase)
    {
        return (ignoreCase ? ciCaseIgnore : ciCaseSensitive).getCollationKey(s);
    }
    @Override
    public int compare(String x, String y, boolean ignoreCase)
    {
        return (ignoreCase ? ciCaseIgnore : ciCaseSensitive).compare(x,  y);
    }
}

Let us know your feedback.

8

Thanks very much for your feedback and fixing the issue. please let us know when will the new version available. Then we can order though our company.

9

@cgkrish,
The new version will be released in a couple of days.

10

The issues you have found earlier (filed as CELLSJAVA-43386) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi