Free Support Forum - aspose.com

Custom Sorting with two different data types

Hello,

I am experiencing an issue with custom sorting. I have a pivot table which pulls in data that is programmatically loaded with Aspose, and it works fine. However, when trying to sort the pivot table (not the source), and subsequently the order of columns in the pivot chart it’s connected to, there is a problem. The first column of my pivot table is Months, but the loaded values are usually something like:

2018, 2019, 2020, Jan, Feb, Mar, Apr, May, June

When trying to use a custom sort, despite being in this order, the custom sort always winds up with months first, then the years. I feel like this might be caused by the fact that the data is of two different types (text and numbers), so I created a test file outside of my large report, and what I found is that if the values were created programmatically, then the sort works perfectly as intended. If the values previously exists and I just sort programmatically, then I get the odd backwards-behavior listed above.

Please grab the attached spreadsheet (BaseFile.xlsx) and run the following code:

    public static Random rand = new Random();
    private static void Test13()
    {
        Aspose.Cells.License license = new Aspose.Cells.License();
        license.SetLicense("Aspose.Cells.lic");

        string[] custom = new string[] { "2018", "2019", "2020", "2021", "2022", "2023", "2024", "2025", "2026", "2027", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" };

        List<string> list = new List<string> { "2018", "2019", "2020", "2021", "2022", "2023", "2024", "2025", "2026", "2027", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" };

        list = Shuffle(list);

        Workbook wb = new Workbook(AppDomain.CurrentDomain.BaseDirectory + "Test13.xlsx");
        Worksheet ws = wb.Worksheets[0];

        for (int i = 0; i < list.Count; i++)
        {
            string item = list[i];
            ws.Cells[i+1, 4].Value = item;
            ws.Cells[i+1, 6].Value = item;
        }

        DataSorter ds = wb.DataSorter;
        ds.AddKey(2, Aspose.Cells.SortOrder.Ascending, custom);
        CellArea dbgCa = CellArea.CreateCellArea("C2", "C23");
        ds.Sort(ws.Cells, dbgCa);
        ds.Keys.Clear();
        ds.AddKey(6, Aspose.Cells.SortOrder.Ascending, custom);
        dbgCa = CellArea.CreateCellArea("G2", "G23");
        ds.Sort(ws.Cells, dbgCa);
        
        wb.Save(AppDomain.CurrentDomain.BaseDirectory + "Test13.xlsx", Aspose.Cells.SaveFormat.Xlsx);
    }
    public static List<T> Shuffle<T>(List<T> original)
    {
        List<T> lst = new List<T>(original);
        for (int i = lst.Count - 1; i >= 1; i--)
        {
            int j = rand.Next(0, i + 1);
            T tmp = lst[j];
            lst[j] = lst[i];
            lst[i] = tmp;
        }
        return lst;
    }

When you open the file after execution, you’ll see columns A and C had data exisiting in them already (A is for reference, C was actually sorted), while columns E and G had the same values shuffled and entered randomly (E is for reference, G was actually sorted). Column I is the expected output.

Notice how with existing data, the month names get sorted before the years, despite the years coming first in the custom sort list. I have tried not treating them as numbers, and have cycled through all built in column data types in Excel with the same result: Months show up before numbers in the list.

My client wants to see the years at the left of the pivot chart so they should come first, so i need to sort the pivot table to show the years, then the months, both in chronological order. I have also attached a copy of my output of the code, in case there is something I am doing wrong and you are unable to reproduce the error.

Custom Sorting.zip (17.4 KB)

Thanks.

@mfox,
I have analyzed your sample data and code and observed that the column A in the source file contains mixed data i.e. Text and Number as General. Your custom list contains strings data only including the number (as strings) therefore it seems that the numbers from the general list are ignored and only text type data is sorted based on custom list. However if we remove the ‘custom’ argument from the AddKey function, the sorting works fine without any error. Could you please remove the third argument ‘custom’ from the AddKey function and analyze the scenario again.

//ds.AddKey(2, Aspose.Cells.SortOrder.Ascending, custom);
ds.AddKey(2, Aspose.Cells.SortOrder.Ascending);
//ds.AddKey(6, Aspose.Cells.SortOrder.Ascending, custom);
ds.AddKey(6, Aspose.Cells.SortOrder.Ascending);

Here is the program output:

Thank you. I feel a little silly but I previously didn’t see anywhere it was stated in the documentation for AddKey() that the input type had to match the output type to apply the sort. It might be worth mentioning that in the documentation. Under that logic, it makes complete sense to me why the years were not sorted. It was only after reviewing AddKey()'s documentation for this reply that I saw the overload which takes an object[] instead of string[], and when I replaced the year strings in the array for numeric year values, changed the datatype to object[], and used the AddKey(int key, SortOnType type, SortOrder order, Object customList) overload that I was able to successfully sort correctly.

Your method worked as well, but I am using the object[] overload because I would prefer precise control over what items appear in what order.

Thank you very much!

@mfox,

Good to know that you have sorted out your issue now. In the event of further queries or comments, feel free to write us back.

Unfortunately, this still doesn’t seem to work on a pivot table. I managed to get it to appear correctly by sorting the table the Pivot Table data is pulled from (supplied from a hidden sheet), but that isn’t a solution since I have many different pivot tables that use the same data source. I was hoping to provide custom sorting for each Pivot Table as necessary, since some of the other tables need the data to appear in order of other columns.

This is a sizable report and I can’t exactly trim it down to share – is there any other information you can provide about sorting the Pivot Table independent of the data source?

Thanks.

@mfox,
We are afraid that without the runnable sample console application and test data, it is not possible to reproduce the issue here. You may please prepare a standalone console application that can be used to observe this issue. It will help us to provide assistance at the earliest.