How do I make the pivot table "sums" into columns instead of rows?

Aspose.Cells Product Family
1
How do I make the pivot table "sums" into columns instead of rows?

The code:

pivotTable.addFieldToArea(PivotFieldType.ROW, 1); // App
pivotTable.addFieldToArea(PivotFieldType.ROW, 2); // Advertiser
pivotTable.addFieldToArea(PivotFieldType.ROW, 3); // Ad
pivotTable.addFieldToArea(PivotFieldType.ROW, 0); // Date

pivotTable.addFieldToArea(PivotFieldType.DATA, 5); // Impressions
pivotTable.addFieldToArea(PivotFieldType.DATA, 6); // Clicks
pivotTable.addFieldToArea(PivotFieldType.DATA, 7); // Engagements

The data:

screenshot

The result, noticed that the "sums" are rows:

screenshot

I need the "sums" to be columns like this:

screenshot

Thanks.
2

Hi,

Thank you for considering Aspose.

You may use PivotFieldType.COLUMN in pivotTable.addFieldToArea method to get your desired result. Please see the following sample code and change your code accordingly to get your desired results.

Workbook workbook = new Workbook();

Worksheet sheet = workbook.getWorksheets().getSheet(0);

Cells cells = sheet.getCells();

Cell cell = cells.getCell("A1");

cell.setValue("Sport");

cell = cells.getCell("B1");

cell.setValue("Quarter");

cell = cells.getCell("C1");

cell.setValue("Sales");

cell = cells.getCell("A2");

cell.setValue("Golf");

cell = cells.getCell("A3");

cell.setValue("Golf");

cell = cells.getCell("A4");

cell.setValue("Tennis");

cell = cells.getCell("A5");

cell.setValue("Tennis");

cell = cells.getCell("A6");

cell.setValue("Tennis");

cell = cells.getCell("A7");

cell.setValue("Tennis");

cell = cells.getCell("A8");

cell.setValue("Golf");

cell = cells.getCell("B2");

cell.setValue("Qtr3");

cell = cells.getCell("B3");

cell.setValue("Qtr4");

cell = cells.getCell("B4");

cell.setValue("Qtr3");

cell = cells.getCell("B5");

cell.setValue("Qtr4");

cell = cells.getCell("B6");

cell.setValue("Qtr3");

cell = cells.getCell("B7");

cell.setValue("Qtr4");

cell = cells.getCell("B8");

cell.setValue("Qtr3");

cell = cells.getCell("C2");

cell.setValue(1500);

cell = cells.getCell("C3");

cell.setValue(2000);

cell = cells.getCell("C4");

cell.setValue(600);

cell = cells.getCell("C5");

cell.setValue(1500);

cell = cells.getCell("C6");

cell.setValue(4070);

cell = cells.getCell("C7");

cell.setValue(5000);

cell = cells.getCell("C8");

cell.setValue(6430);

PivotTables pivotTables = sheet.getPivotTables();

**int** index = pivotTables.add("=A1:C8","E3","PivotTable1");

PivotTable pivotTable = pivotTables.get(index);

pivotTable.addFieldToArea(PivotFieldType.** ***COLUMN*** **,0);

pivotTable.addFieldToArea(PivotFieldType.** ***COLUMN*** **,1);

pivotTable.addFieldToArea(PivotFieldType.*DATA* ,2);

pivotTable.setSaveData(**true** );

workbook.save("C:\\book_pivot.xls");

Thank You & Best Regards,

3

Please look at the screenshots I have provided. You have not answered my question. Thanks.

4

I need the DATA dimension to be a column, not a row, as in the screenshot. Please look at the screenshot before copying and pasting another canned response here. Is this possible with your API? Thanks.

5

I figured it out thanks to an old VB post from 2006. The secret is to add this line of code after you add all your DATA items:


pivotTable.addFieldToArea(PivotFieldType.COLUMN, pivotTable.getDataField());