PivotTables loop and set order

Aspose.Cells Product Family
1 
Hello
LAst editon installed (8.7.1.0)
This doesn't work:

 for (int i = 0; i <= _sheetActiva.PivotTables[0].Fields(Aspose.Cells.Pivot.PivotFieldType.Data).Count; i++)

{
 _sheetActiva.PivotTables[0].DataFields[i].IsAutoSort = true;
 _sheetActiva.PivotTables[0].DataFields[i].IsAscendSort = true;
 _sheetActiva.PivotTables[0].DataFields[i].AutoSortField = -1;
 }

Error: 
{“Object reference not set to an instance of an object.”} in "IsAscendSort = true;"

No problem with RowFields:
                    Aspose.Cells.Pivot.PivotFieldCollection pivotFields = _sheetActiva.PivotTables[0].RowFields; //Accessing the row fields.

Aspose.Cells.Pivot.PivotField pivotField = pivotFields[0]; //Accessing the first row field in the row fields.

pivotField.IsAutoSort = true; //Setting the field auto sort.

pivotField.IsAscendSort = true; //Setting the field auto sort ascend.

pivotField.AutoSortField = -1;//Setting the field auto sort using the field itself.
2

Hi,


Thanks for providing us some details and code snippet.

I think, currently, Aspose.Cells might have issues to sort data field values in Pivot Table report. Could you create a simple console demo application (runnable), zip it and post us here to show the issue, we will check your issue soon. Also provide your template Excel file (if you have any). We will evaluate it and log your issue into our database.

Thank you.
3

Ok


Button2 test
4

Hi,


Thanks for the sample project and template file.

Well, you can sort the Data field with respect to Row field. Please try to change your code segment (on button2_Click event handler) as following, it works fine as I tested:
e.g
Sample code:

for (int i = 0; i < _sheetActiva.PivotTables[“Tabla dinámica5”].RowFields.Count; i++)
{
_sheetActiva.PivotTables[0].RowFields[i].IsAutoSort = true;
_sheetActiva.PivotTables[0].RowFields[i].AutoSortField = 0;//Apply the sorting for the first data field.
}
_AsposeWorkbook.Save(@“E:\test2\pivottables loop and set\out1Book1.xls”);


Hope, this helps a bit.

Thank you.
5

Hi


Not working.
Attach the proj. I want to order first column Ascend and Descend
Excel is the same file
Push “OrdenPivotfields” button

6

Hi,


Thanks for providing us new sample project.

Please try our latest version/fix: Aspose.Cells for .NET v8.8.0.2
I have tested your scenario/ case using your sample project with v8.8.0.2, it works fine. I first selected “Ascending” from the combo box and pressed the “OrdenPivotfields” to generate the first file. I then selected “Descending” from the combo box and pressed the “OrdenPivotfields” again to generate the second file. Both files are attached. I believe the files are fine. Do you see any issue in it, could you check the file. If you find any issue, kindly do provide complete details and also provide us your expected file (which you may create and sort the PivotTable report manually in MS Excel). Also give us steps on how did you sort the PivotTable as per your requirements, we will check it soon.

Thank you.

7

Hi


I do the same steps…
Not working

Same dll version
8

Hi,


Thanks for the demo, sample project and template files.

It looks like you are not using latest version/fix: Aspose.Cells for .NET v8.8.0.2 (download URL is already provided in my previous post) in your project. I have tested your scenario/ case using your new sample project with v8.8.0.2 and your original template file, it works fine and as expected. Please find attached the updated project (referenced with Aspose.Cells for .NET v8.8.0.2) and output Excel files for your reference. Please open the attached output Excel files if these are fine tuned.

Thank you.
9

“It looks like you are not using latest version/fix: Aspose.Cells for .NET v8.8.0.2 (download URL is already provided in my previous post) in your project.”


Yes i do…see property window in file “sshot-385.gif” of my last post

Ok i try your proj

10

Hello


I tried it again in another computer. Not working

book1_OUT_Asc an book1_OUT_Desc are identical

8.8.0.2 dll version

Same files that I have attached other times

If you want i can give you remote control of my machine to see the problem
11

Hi,

Well, now that is strange. It works fine on my end as you can see my attached output Excel files for confirmation. It should also work equally fine on your end using v8.8.0.2 (latest fix). Could you add a line to your code segment to get the version number of the Aspose.Cells assembly that your are using:
e.g.
Sample code:

...
MessageBox.Show(CellsHelper.GetVersion());
…

Also, please build (as it is) and run my attached project (you just need to edit the file paths before compiling the project in your VS.NET) and provide the output Excel files here. Also provide your updated project with v8.8.0 (you may zip it prior attaching here) and input file, so I could just run it and generate the output Excel files on my end as well.

Also, here is my environment Details:
OS: Windows 8, VS.NET 2010. Target .NET Framework version: 4.0, etc.

Please provide your environment details.

Thank you.

12

I delete my project and use yours… same problem,. Attach again


Fmk is in the project: 4.0. Other info in screenchots

Dll version: https://www.dropbox.com/s/xa2slccnsve4bbu/Aspose.Cells_20160511_102315.rar?dl=1

Please make sure you use my Excel file (attach again)

I create new Excel file just in case… same problem



13

Hi,


Thanks for providing us further details, sample project and screenshots.

Please use the attached “book1.xls” file to test your scenario/ case using your attached project, it works fine with it as I have tested. I have attached the “book1.xls” (which you previously attached the file) and output XLS files for your reference. After running the project with my attached "book1.xls"file, please generate the output XLS files after clicking on the third button (while selecting the item (on by one) from the drop down and attach the output XLS files here.

I also did test your project with your attached book1.xls and Book2.xlsx files and it does not work on my end this time.

Thank you.
14

Ok the problem is the Excel file… but Why?

I attach the two files to determine whats happend… Do you know it?

Most worring is that the second NEW excel file (book2.xlsx) have same problem
15

Hi,


Yes, it looks like template specific issue. I re-tested your issue using the same project with your newly attached XLS files and found that it does not work when using “book1_KO.xls” whereas it works as expected with “book1_OK.xls”. How did you create the file “book1_KO.xls”, did you manually create the Pivot Table in MS Excel or generated it by some tool or other APIs? Apparently both files are same but we have to evaluate the files internally, we will do so soon.

Thank you.
16

" How did you create the file “book1_KO.xls”, did you manually create the Pivot Table in MS Excel or generated it by some tool or other APIs? "


Normal method, no APIs:
- Inside folder right mouse button
- New/Excel file option

I create new fle Excel a test it. See attach demo video
17

Hi,


Thanks for providing us demo video.

I have a detailed discussion with the concerned developer from product team. We come to conclusion that you should always use the sample code as following (it might produce your desired results to some extent but not fully):
e.g
Sample code:

…
//Please change the name of the pivot table accordingly for your template file’s PivotTable in the worksheet.
PivotTable pt = _sheetActiva.PivotTables[“Tabla dinámica5”];
for (int i = 0; i < pt.RowFields.Count; i++)
{
pt.RowFields[i].IsAutoSort = true;
pt.RowFields[i].IsAscendSort = _bolOrdenAsc;
pt.RowFields[i].AutoSortField = -1;
}
…

There are two main things here which should be noted/considered:
1) You should always get PivotTable via its name and not by its indexed position. We noticed it because the PivotTable in two different files has different index numbers.
2) I am afraid, currently, we don’t support to apply data sorting based on Data fields in PivotTable report. In short, Aspose.Cells does not support data sorting via DataField. So, if you want to sort the PivotField by itself, you can use PivotField.IsAutoSort and PivotField.IsAscendSort to try to control the order. We have already logged a ticket to add support to sort data based on DataField into our database.

If you still could not evaluate and accomplish the task for your needs, kindly do provide us the original input file and your expected file (which you may create in MS Excel manually for your desired sort order in PivotTable), we will check it soon.

Thank you.
18
Hi,

"we don't support to apply data sorting based on Data fields in PivotTable report"

You're saying that what you said above is not correct?:
"_sheetActiva.PivotTables[0].RowFields[i].AutoSortField = 0;//Apply the sorting for the first data field.""


Then the order of pivot table not working?

" you can use PivotField.IsAutoSort and PivotField.IsAscendSort to try to control the order"..

Whats that? Order of entire document? I need to apply multiple order: first column in asc, second in desc, third in asc, etc...


19
Hi,

ageinfo:
"we don't support to apply data sorting based on Data fields in PivotTable report"

You're saying that what you said above is not correct?:
"_sheetActiva.PivotTables[0].RowFields[i].AutoSortField = 0;//Apply the sorting for the first data field.""

Yes, we are sorry for the confusion. Actually, the feature "Sort data in Data field with respect to / by Row pivot field in the PivotTable" is still not supported or implemented at the moment (see the reference thread: https://forum.aspose.com/t/65804)
We did log a ticket with an id "CELLSNET-43257" for the feature a year ago.

ageinfo:
Then the order of pivot table not working?

" you can use PivotField.IsAutoSort and PivotField.IsAscendSort to try to control the order"..

Whats that? Order of entire document? I need to apply multiple order: first column in asc, second in desc, third in asc, etc...

Could you provide us the original input file and your expected file (which you may create in MS Excel manually for your desired sort orders in PivotTable), we will check it soon.

Thank you.
20

Hello


I have ‘book2.xlsx’ and want ‘book2 - End.xlsx’

Please note in ’ book2 - End.xlsx’ i want to sort by ‘Suma de Sales1’ in descending mode