Unable to Sort Excel Pivot table

Aspose.Total Product Family
1

Hi,

We are trying to sort Excel book Pivot with Aspose. We are using verion18.7 aspose.cell.

Below is the code. It is not working. Sort is not happening for that column at the rowfield 0.

Workbook wb = new Workbook(@"../../APP_DATA/SampleData2.xlsb");
        Worksheet ws = wb.Worksheets[1];

        var pTable = ws.PivotTables[0];          
        pTable.DataFields["LTM Jan-17"].IsAutoSort = true;
        pTable.DataFields["LTM Jan-17"].IsAscendSort = true;           
       
        pTable.RowFields[0].IsAscendSort = true;
        //Sort column LTM Jan-17 by Mapping 1 row
        pTable.RowFields[0].AutoSortField = pTable.DataFields["LTM Jan-17"].Position;
        pTable.RowFields[0].IsAutoSort = true;

        pTable.RefreshDataOnOpeningFile = true;
        // pTable.RefreshData();
        pTable.CalculateData();
2

@LakshmiAys,

Thanks for the sample code segment and details.

Kindly provide us your template file “SampleData2.xlsb”, so we could evaluate your issue soon. Also, do you save to Excel file format (output Ms Excel file) or some other file format (e.g PDF)? If you are saving to MS Excel file format, then you should not refresh or calculate pivot table data, so the following lines can be removed:
e.g
Sample code:

.........
 // pTable.RefreshData();
        pTable.CalculateData();

Moreover, kindly check what index (numeric) value you are getting for “pTable.DataFields["LTM Jan-17"].Position” attribute (FYI, AutoSortField --> refers to indexed value (from the data fields’ list), e.g 0–> first data field, 1–> second data field and so on).

PS. We also recommend you to kindly try our latest version/fix: Aspose.Cells for .NET v18.9.x.

3

Thanks for your prompt reply. Attaching the SampleData2.

  1. The line pTable.DataFields[“LTM Jan-17”].Position gives the indexed value for AutoSortField.
    Rowfield index is o which is Mapping1 and Column field “LTM Jan-17” index is
    pTable.DataFields[“LTM Jan-17”].Position
  2. Our current version is 18.7 which we upgraded recently. Our previous version was 17.4. We had this issue with 17.4 as well then we upgraded to 18.7 recently.
  3. Tested commenting the below lines still seeing issue.
 //pTable.RefreshDataOnOpeningFile = true;
     // pTable.RefreshData();
     //pTable.CalculateData();
4

@LakshmiAys,

Thanks for your feedback. It seems that SampleData2.xlsb is not uploaded as I cannot see any link. Please load the file for our testing.

5

SampleData2.zip (123.2 KB)

That is the Sample Data file. It didnt get uploaded first time.

6

@LakshmiAys,

Thanks for the template file.

I evaluated your template file (containing the PivotTable) a bit. I opened your template file into Ms Excel, I manually selected a row item in “Mapping1” pivot field in the report (“PVT_Detailed_IS1” worksheet) and clicked on “Sort” under data options and tried to sort the “LTM Jan-17” data field in ascending order but it does nothing. In short, I could not accomplish the task in MS Excel manually. Could you perform your task in MS Excel manually for your specified data field and saved the file and provide us here, we will check it on how to do it via Aspose.Cells APIs. Also, give us complete details (steps involved) and screenshots on how did you perform the task using MS Excel options for the PivotTable report. This will help us really to evaluate your issue precisely to consequently figure it out soon.

7

Hi, We found that the sample data has AcctDesc_Order column in the pivot which was not allowing the manual sort and Asport sort. We removed that column and the Manual sort works for Ascending and Descending. Aspose sort is working when pTable.RowFields[2].IsAscendSort = true means ascending;

When pTable.RowFields[2].IsAscendSort =false it is still doing default Ascending order.

We want to know why Descending is not working. Attaching new sample file without AcctDesc_Order column in the pivot table. Also attaching how to do manual sort.SortDescNotWorking.zip (442.7 KB)

The way to do Manual sort is go to Pivot sheet (2nd sheet) -> Right click on the ACCDESC_CLEAN column -> click on MoreSortOptions -> click on radio button Descending order and select the column LTM

Below is the code. LTM column index is 43.
LoadAsposeLicenses();
//LoadOptions loadOptions = new LoadOptions(LoadFormat.CSV);
Workbook wb = new Workbook(@"…/…/APP_DATA/UnsortedSamleData1.xlsb");
Worksheet ws = wb.Worksheets[1];

        var pTable = ws.PivotTables[0];
        //pTable.RefreshData();           

        //Sort column LTM Jan-17 by Account Description row           
       
        pTable.RowFields[2].IsAutoSort = true;
        pTable.RowFields[2].IsAscendSort = false;
        pTable.RowFields[2].AutoSortField = 43;

        pTable.DataFields["LTM Jan-17"].IsAscendSort = false;
        pTable.DataFields["LTM Jan-17"].IsAutoSort = true;

        pTable.RefreshDataOnOpeningFile = true;           
        pTable.CalculateData();

        wb.Save(@"../../APP_DATA/Output.Xlsb", SaveFormat.Xlsb);
8

@LakshmiAys,

We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46387 - Problem sorting pivot table
9

Thank you. Look forward for a solution.

10

Is there any update on this issue?

11

@LakshmiAys,

I am afraid, your issue is not resolved yet. However, I have logged your concerns against your issue “CELLSNET-46387” into our database. Once we have further updates or an ETA for your issue, we will let you know.

12

@LakshmiAys,

This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

13

There are 2 messages one message said fixed and the other one CELLSNET-46387” not fixed.

I am assuming that the latest one which said fixed and is still in QA is the right one.

14

@LakshmiAys,

Yes, you are right, the issue is resolved but due to QA and incorporation of other enhancements and fixes, it may take few days to provide the fixed version. We will write back here when fixed version will be ready.

15

@LakshmiAys,

Please try our latest version/fix:
Aspose.Cells18.10.1 For .Net2_AuthenticodeSigned.Zip
Aspose.Cells18.10.1 For .Net4.0.Zip

Your issue should be fixed in it.

Let us know your feedback.

16

Thanks for fixing this. We tested and it works good. Is this the official release? We still don’t see this release in the .Net Nuget package.

17

@LakshmiAys,

It is official release and can be used in the production system, however we release it via Nuget Package once in a month when new version is released. This fix will be available via Nuget Package next month when new version Aspose.Cells for .NET 18.11 will be released in the last week of Nov 2018.

18

Thank you. We will wait until it is available in Nuget Package.

19

@LakshmiAys,

You are welcome. We will let you know when Aspose.Cells for .NET 18.11.0 will be released via Nuget Package.

20

The issues you have found earlier (filed as CELLSNET-46387) have been fixed in Aspose.Cells for .NET v18.11. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi