Apache POI how to apply Workbook Style to all Pivo

2019-09-13 01:38发布

问题:

So i am working on a program which reads a text file and writes this data in an excel workbook. after the data is written i create pivot tables from the data. to get style in the pivot table i've set the whole workbook style which somewhy only applys to the sheets with the pivot tables and not the data sheets. now i want to exclude one sheet with pivot table from the workbook style. is there a way to exclude one sheet or set the style for the sheets which need it?

code for workbook style:

wb.createDataFormat().putFormat((short) 0, "_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)");

pivot table:

CellReference cr = new CellReference("A1");
CellReference c1 = new CellReference(0, 0);
CellReference c2 = new CellReference(data.getPhysicalNumberOfRows() - 1, data.getRow(0).getLastCellNum() - 1);

AreaReference ar = new AreaReference(c1, c2);
XSSFPivotTable pivotTable = sheet.createPivotTable(ar, cr, data);

pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(4).setAxis(STAxis.AXIS_COL);
pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(4).addNewItems();
pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(4).getItems().addNewItem()
        .setT(STItemType.DEFAULT);

pivotTable.getCTPivotTableDefinition().addNewColFields().addNewField().setX(4);

pivotTable.addRowLabel(10);
pivotTable.addRowLabel(11);
pivotTable.addRowLabel(1);
pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 0);

i already tried to set the style for the cells in the sheet but didn't work. any suggestions?

回答1:

so i found an solution here: Apache Poi set data field style for pivot table. this question might also be more accurate on what i wanted to achieve