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.