we are evualuating Aspose to replace our current solution for generating pivots. Most of the stuff works, but we are unable to match our current output in terms of labels.
Aspose seems to by default put in Row Label / Column Label instead of the desired ones.
Please see the attached ZIP:
aspose_test.zip (19.2 KB)
It contains “manually created” excel (AsposeTest.xlsx) - when you move the row / column items, the row / column labels change. However the one changed with Aspose (AsposeTest_FINAL.xlsx) has Row Labels and Column Labels and we can’t figure out how to make it be contextual depending on the selected items like in the manual one.
Any help would be appreciated.
Could you please elaborate the issue more as we not able to observe it here? Share complete steps along with images showing Excel file before and after the desired opertion. Also share your runnable console application that can be compiled and executed here without any missing reference.
this is the original file created manually:
image.png (7.2 KB)
When I move B to Columns, the labels are updated and show both A and B in the column labels:
image.png (9.0 KB)
When I take the same file and modify the pivot with Aspose (loading in new data, changing the pivot layout and refreshing the pivot) the pivot has “Row Lables” and “Column Labels” instead:
image.png (9.7 KB)
When I move fields from Rows to Column and Filters, it still has this generic label:
image.png (18.7 KB)
I can manually set the captions using
pivotTable.setRowHeaderCaption(“SOME STRING HERE”);
It is set to constant values, not updated based on how I move the fields.
Here is a code snippet we are using to alter the layout:
image.png (59.1 KB)
Let me know if this helps illustrate the issue or if you need more information.
Thank you for providing details. We will analyse it and share our feedback soon.
We have tried this scenario manually in MS Excel only and observed the similar behavior as shown by Aspose.Cells. You may please perform all these tasks using MS Excel only with the target sample data (animal, color, fur, weight) and share your feedback. If issue is not resolved, share images for the same data in MS Excel and file processed by Aspose.Cells.
Here is a video that demonstrates this entire process in MS Excel at our end.
CreatePivotTable.m4v.zip (1.8 MB)
thanks for your video - we have traced the labeling to the “Classic PivotTable layout” option in Excel which enables it.
image.png (15.3 KB)
When trying to prepare a minimum example to illustrate the issue, we found that this is not cause by simply “refreshing” the data, but most likely by ours setting of the overall pivot layout and styles.
I will get back to you later with a working example when we figure out what is causing this.
Thanks for your assistance.
Take your time and get back to us with more details.