Pivot Table sorting issues


#1

Aspose.Cells not sorting the pivot table on the basis of value fields when the data goes beyond a page while exporting it to pdf.

When the pivot table can be printed on the same page then it is sorting properly but when it goes to more than one page then it is not sorting the data.

Please look into this issue.


#2

@vaibhavshrotriya,

Thanks for providing us some details.

Please create a sample console demo application (runnable), zip the project (excluding Aspose.Cells library) and post us here, we will check it soon. Also provide your template file (if any) and output PDF.


#3

Hi @Amjad_Sahi,

I’m sharing the zip with all the sample files. You can find the template as “wine_sample_template.xlsx”. I’m putting the .csv data into the Sheet1 of the template using the Test.java file. In the Sheet2 of this template you will find the pivot table which I want to export as pdf. “wine_sample.pdf” and “wine_sample.xlsx” are the outputs generated when filtering the data in the pivot table to show only top3 rows. When I changed it to filter the top 30 row in the pivot table the outputs generated are “wine_sample2.xlsx” and “wine_sample2.pdf”.

You can see in both the output that when the data is less in pivot table (i.e. top 3 rows only) the data is sorting correctly but when I increased the filter to 30 the pivot table is distributed to 2 pages and the data is not sorted correctly.

Thanks,
Vaibhav


#4

@vaibhavshrotriya,

Thanks for the sample files and sample code.

I evaluated your scenario/case a bit using your template files and sample code. I found both “wine_sample.xlsx” and “wine_sample2.xlsx” files are corrupt. If the source files have corrupt data/pivot table, the output files would not be accurate or reliable. MS Excel shows error messages when opening the files into it. How did you create these files? Also kindly provide your desired file(s) with your PivotTable’s data sorted out, you may manually edit/update the PivotTable based on the source data and save it to provide us here.


#5

@Amjad_Sahi

The template is “wine_sample_template.xlsx”, not “wine_sample.xlsx” and “wine_sample2.xlsx”. They are the outputs generated using the template file. You can try to use the “wine_sample_template.xlsx” and put the data of file “winemag-data_first150k.csv” into Sheet1 and refresh the pivot table present in Sheet2. You can refer to the java file named “Test.java” to see how I want to export the file to pdf.

The outputs “wine_sample.xlsx” and “wine_sample.pdf” are generated after filtering the pivot table in the template to show top 3 items and the outputs “wine_sample2.xlsx” and “wine_sample2.pdf” are generated after increasing the filter to top 30. See the output pdf for better explanation.


#6

@vaibhavshrotriya,

Thanks for providing us further details.

After further tests, I am able to observe the issue as you mentioned by using your sample code but with your template file “wine_sample_template.xlsx”. I found Pivot table is not refreshed and corrupted after updating the source data in the output file. The output file is rather corrupt and MS Excel prompts some error messages regarding PivotTable when opening the output file into it. Hence, the data is not sorted out at all. I have logged a ticket with an id “CELLSJAVA-42474” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.


#7

@Amjad_Sahi

Any update on this?


#8

@vaibhavshrotriya,

I am afraid, your issue is not resolved yet. However, I have logged your concerns against your issue “CELLSJAVA-42474” and asked the concerned developer from product team to update on it or provide an ETA (if possible).

Once we have any new information, we will share it with you.


#9

@vaibhavshrotriya,

We are working over your issue and your issue should be fixed in the last week of December 2017.

Keep in touch.


#10

We blocked on this issue and need this to be expedited, appreciate some update on this.


#11

@chjohnz,

Your issue is In Progress and hopefully it will be fixed in the next 3-5 days or so.

Once we figure it out, we will share the Download link for the fix here.


#12

@chjohnz,

We are pleased to inform you that your issue has been fixed now. We will soon provide the fix after performing QA and including other enhancements and fixes.

You can expect the fix in the next 2-3 days or so.


#13

@chjohnz,

Please try our latest version/fix: Aspose.Cells for Java v17.12.2.

Your issue (logged earlier as “CELLSJAVA-42474”) should be fixed in it.

Let us know your feedback.


#14

@Amjad_Sahi

Hi,

I tried to refresh the pivot tables using the updated version but still the data is not coming in sorted order according to the calculated value.

Thanks,
Vaibhav


#15

@vaibhavshrotriya,

I tested your scenario/case using the latest version/fix. I noticed although the output file is not corrupted but the data seems to be not sorted/refreshed. Could you also provide us output XLSX and PDF files by Aspose.Cells. Also provide your expected Excel file, we might re-open your issue to sort it out accordingly.

We are sorry for the inconvenience caused!


#16

@Amjad_Sahi

The files I shared previously also contains the sorting scenario. Here is the zip.


#17

@vaibhavshrotriya,

Thanks for the sample files.

I have reopened your issue “CELLSJAVA-42474” now. Our concerned developer from product team will soon evaluate it and try to fix the data sorting problem.

Once we have an update on it, we will let you know here.


#18

@vaibhavshrotriya,

We have evaluated your issue further. I am afraid, we cannot support sorting the PivotField according to DataField for now. We will support it later on in future versions (it cannot be supported in short time).

Once we have any new information, we will share it with you.


#19

Hi @Amjad_Sahi , Any update on this?


#20

@vaibhavshrotriya,

I am afraid, the issue is not fixed yet. I have recorded your concerns against your issue into our database.

Once we have an update on it, we will let you know here.

We are sorry for any inconvenience caused!