PivotTable Formatting Questions

Aspose.Cells Product Family
1

I have 4 Questions all pertaining to creating PivotTables:

1. What is the number system for when you use the setNumber(int) method (type PivotField)?

2. On my pivot how do I change the Data source from the default (sum) to (count)? Attempted to follow

http://www.aspose.com/docs/display/cellsjava/Customizing+the+Appearance+of+PivotTable+Reportsv

guide, but it uses setSubtotals method which doesn't work with particular pivot.

3. How do I have my Pivot ordered by a Row?

4. What method/class type can I use to get an entire reference of all the data. (e.g. =Sheet1!A1:W900)?

Most of my code to create this was taken from guide on how to create pivot.

2

Hi,


1) Please see the table (“Using Built-in Number Formats”) in the document for your reference:
http://www.aspose.com/docs/display/cellsjava/Setting+Display+Formats+of+Numbers++and++Dates

2) Well, you need to use: PivotField.setSubtotals() method to specify your desired Subtotal type for your needs.

3) You got to instantiate the PivotField to get row field and then use the sample code:
E.g
//Setting Subtotals.

pivotField.setSubtotals(PivotFieldSubtotalType.SUM,true);

pivotField.setSubtotals(PivotFieldSubtotalType.COUNT,true);


//Setting autosort options.

//Setting the field auto sort.

pivotField.setAutoSort(true);


//Setting the field auto sort ascend.

pivotField.setAscendSort(true);


4) Please use PivotTable.getDataSource/setDataSource to get/set the data source of the PivotTable report.

Thank you.

3

Hi,

Pertaining to question 2:

I used this line on the PivotTable object and nothing changed in my pivot.

pivotTable.getDataFields().get(0).setSubtotals(PivotFieldSubtotalType.COUNT, true);

Thank You,

Daniel

4

Hi,


Please provide us your sample runnable code or JAVA program, so that we could look into your issue soon. Also, provide us your template Excel file if you have any.

Thank you.
5

Here is the code that deals with the pivot. Unfortunately I cannot provide much more than this due to privacy rules. The pivot is being created and is what I want except for the Value (DATA) is a sum not count, and it is not sorting anything. There are no compilation errors or exceptions being generated.

NOTE: str is the filepath

Workbook workbook = new Workbook(str);
int sheetIndex = workbook.getWorksheets().add();
Worksheet sheet2 = workbook.getWorksheets().get(sheetIndex);
sheet2.setName("Pivot");
PivotTableCollection pivotTables = sheet2.getPivotTables();
String range = "=Sheet1!A1:W25000";
int index = pivotTables.add(range, "A1", "Pivot");
PivotTable pivotTable = pivotTables.get(index);
pivotTable.setRowGrand(true);
pivotTable.setColumnGrand(true);
pivotTable.setAutoFormat(true);
pivotTable.setAutoFormatType(PivotTableAutoFormatType.REPORT_6);
pivotTable.addFieldToArea(PivotFieldType.ROW,13);
pivotTable.addFieldToArea(PivotFieldType.PAGE,3);
pivotTable.addFieldToArea(PivotFieldType.PAGE,19);
pivotTable.addFieldToArea(PivotFieldType.PAGE,10);
pivotTable.addFieldToArea(PivotFieldType.COLUMN,12);
pivotTable.addFieldToArea(PivotFieldType.DATA,13);
pivotTable.getDataFields().get(0).setNumber(0);
//These lines are not doing anything to the pivot
pivotTable.getDataFields().get(0).setSubtotals(PivotFieldSubtotalType.COUNT, true);
pivotTable.getDataFields().get(0).setAutoSort(true);

workbook.save(str)

6

Hi,


Well, unless we have your template file we might not evaluate your issue properly using your code. I think you may use some dummy data in your template file and provide us here or create a JAVA program that use some dynamic dummy/sample data to fill the worksheet first and then create the pivot table based on your range of data accordingly to show the issue on our end.

By the way, you are subtotaling by data fields, I think you may/should try to set subtotals for Row fields, e.g. you may try to change the line of code.

pivotTable.getDataFields().get(0).setSubtotals(PivotFieldSubtotalType.COUNT, true);
to:
pivotTable.getRowFields().get(0).setSubtotals(PivotFieldSubtotalType.COUNT, true);

Thank you.
7

Thank you for helping me. The issue was I had the setNumber(0) line before the sort and count lines.

8

Hi,


Good to know that your issue is resolved now.
Feel free to contact us any time if you need further help or have some other issue, we will be happy to assist you soon.

Thank you.