We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Sort date pivot column, set pivot filter default value

Aspose.Cells Product Family
, ,
#1

I have a pivot in one of my worksheets. The pivot column filter is on week ending date but on refresh it is not shown in a sorted date format.

Also secondly I need a default value to be set for a pivot filter from all the options available.

How can these 2 be done? Attaching my sheet and also attaching an image of what I need.

Basically when sheet is opened, the date should be correctly sorted and by default the current system month i.e “Dec” should be selected.

Pivot SS.jpg (334.4 KB)
Test Sheet.zip (297.1 KB)

#2

@sourav24,

Do you render to PDF format? Could you also share your sample code (runnable) that you are using on processing your file and refreshing the pivot data in it. Moreover, share a file having your expected pivot table intact, you may create/update the pivot table manually in MS Excel and save/re-save the file accordingly to provide us here.

#3

Hello Amjad,

Thanks for your response. No we render to excel file itself. Please find attached the requirements and desired pivot results, current code block and updated excel pivots.

            <a class="attachment" href="/uploads/discourse_instance3/45317">Files.zip</a> (406.6 KB)

Workbook unapproved1 = new Workbook(“Test Sheet.xlsx”);
Worksheet uapprovedSheet = unapproved1.getWorksheets().get(“PIVOT”);
PivotTable original = uapprovedSheet.getPivotTables().get(0);
PivotField colFieldofunapprovedSheet = original.getColumnFields().get(0);
colFieldofunapprovedSheet.setAutoSort(true);
colFieldofunapprovedSheet.setAscendSort(true);
colFieldofunapprovedSheet.setAutoSortField(-1);
original.getPageFields().get(“Months”).setMultipleItemSelectionAllowed(true);
original.getPageFields().get(“Months”).setCurrentPageItem((short) 1);
// original.setSaveData(true);
original.setRefreshDataFlag(true);
original.refreshData();

#4

Files.zip (406.6 KB)

#5

@sourav24,

Please see the following sample code on how to (page) filter to “Nov” month for your reference:
e.g.
Sample code:

  Workbook unapproved1 = new Workbook("f:\\files\\Test Sheet.xlsx");
        Worksheet uapprovedSheet = unapproved1.getWorksheets().get("PIVOT");
        PivotTable original = uapprovedSheet.getPivotTables().get(0);
        PivotField colFieldofunapprovedSheet = original.getColumnFields().get(0);
        colFieldofunapprovedSheet.setAutoSort(true);
        colFieldofunapprovedSheet.setAscendSort(true);
        colFieldofunapprovedSheet.setAutoSortField(-1);
        //set filter to Nov month.
        original.getPageFields().get("Months").setMultipleItemSelectionAllowed(false);
        original.getPageFields().get("Months").setCurrentPageItem((short) 11);
       unapproved1.save("f:\\files\\out1.xlsx");

Regarding sorting column field’s week value in ascending order, I even could not accomplish the task in MS Excel manually. It looks like your week values are mixed and some of the values are not in proper format, that’s why we cannot apply sort order upon it via MS Excel or Aspose.Cells. Even opening your desired file into MS Excel does not show ascending order properly. If you still think it is an issue with Aspose.Cells, kindly do share your expected file with your desired sort order, we will check it soon.

#7

Hi @Amjad_Sahi,

Can you please provide this code in C# format?
It will be very helpful.

#8

@Shivammirje1998,

See the parallel C# code for your reference:
e.g.
Sample code:

Workbook unapproved1 = new Workbook("f:\\files\\Test Sheet.xlsx");
                Worksheet uapprovedSheet = unapproved1.Worksheets["PIVOT"];
                PivotTable original = uapprovedSheet.PivotTables[0];
                PivotField colFieldofunapprovedSheet = original.ColumnFields[0];
                colFieldofunapprovedSheet.IsAutoSort = true;
                colFieldofunapprovedSheet.IsAscendSort  = true;
                colFieldofunapprovedSheet.AutoSortField = -1;
                //set filter to Nov month.
                original.PageFields["Months"].IsMultipleItemSelectionAllowed = false;
                original.PageFields["Months"].CurrentPageItem = 11;
                unapproved1.Save("f:\\files\\out1.xlsx");

Hope, this helps a bit.

#9

Thank you so much @Amjad_Sahi.
It worked :slight_smile:

#10

@Shivammirje1998,

Good to know that the suggested code segment works for your needs. In the event of further queries or issue, feel free to write us back.