0

I working in an application in Java to write to an excel. I am using apache poi libraries. I have a requirement to create pivot table. I am able to create pivot table and sum the columns using below code.

CellReference topLeft = new CellReference(0, 0);
CellReference bottomRight = new CellReference(10, 3);
AreaReference aref = new AreaReference(topLeft, bottomRight);
CellReference pos = new CellReference(0, 0);
XSSFSheet pivotSheet = workbook.createSheet("PivotSheet");
XSSFPivotTable pivotTable = pivotSheet.createPivotTable(aref,pos,dataSheet)
pivotOrgWiseSheet.setDisplayGridlines(true);
pivotTable.addRowLabel(0);
pivotTable.addRowLabel(1);
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 2, "Sum of column3");
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3, "Sum of column4");

But the above code generate excel like enter image description here

But I am not sure why the keyword "values" comes in 2nd column header and also is it possible to change the value "Row Label" to custom text like "Category"

I want it something like below.

enter image description here

I am not sure how to remove the keyword "Values", but I guess to change the header to custom string, we have to get the value and set it out ?

Improve this question

1 Answer 1

Reset to default
2

In Excel there is a setting Field Headers on the Analyze or Options tab, in the Show group. This switches between showing and hiding field headers.

See Change the layout of columns, rows, and subtotals.

The corresponding setting in org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotTableDefinition is setShowHeaders.

So 

...
pivotTable.getCTPivotTableDefinition().setShowHeaders(false);
...

should hiding field headers in your pivot table.

But your picture of the wanted result looks more as if the data headers are visible but the RowHeaderCaption was changed and first row containing the DataCaption is hidden. That would be:

...
//pivotTable.getCTPivotTableDefinition().setShowHeaders(false);
pivotTable.getCTPivotTableDefinition().setRowHeaderCaption("Category");
pivotTable.getCTPivotTableDefinition().setDataCaption("Changed Data Caption");
CellUtil.getRow(pos.getRow(), pivotSheet).setZeroHeight(true);
...
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.