如何使用 apache-poi 将数据透视表样式从默认蓝色更改为其他颜色 [英] How to change the pivot table style from default blue to other colors using apache-poi

问题描述

当我使用以下代码创建数据透视表时，它带有一些默认模板样式(蓝色).如何使用 apache-poi 库更改数据透视表的这种默认样式

pivotTable = sheet2.createPivotTable(aref, new CellReference("A4"), sheet1);

When i create a pivot table using the below code, it comes with some default template style(blue). How to change this default style of pivot table using apache-poi library

pivotTable = sheet2.createPivotTable(aref, new CellReference("A4"), sheet1);

What I want.

What i am able to generate through code

解决方案

XXSPivotTable is using named style PivotStyleLight16 per default. See setDefaultPivotTableDefinition.

There is no method for changing this in high level XSSF classes until now. Bu we can get the underlying low level CTPivotTableStyle form the CTPivotTableDefinition got via XSSFPivotTable.getCTPivotTableDefinition. Then we can use CTPivotTableStyle.setName to set another named style:

pivotTable.getCTPivotTableDefinition().getPivotTableStyleInfo().setName("PivotStyleMedium7");

Complete example:

import java.io.FileOutputStream;

import org.apache.poi.ss.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.xssf.usermodel.*;

class CreatePivotTableStyle {

 public static void main(String[] args) throws Exception {

  try (Workbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {

   Sheet pivotSheet = workbook.createSheet("Pivot");
   Sheet dataSheet = workbook.createSheet("Data");

   Row row;
   Cell cell;
   Object[][] data = new Object[][]{
    new Object[]{"Name", "Count"},
    new Object[]{"A", 2d},
    new Object[]{"B", 4d},
    new Object[]{"A", 1d},
    new Object[]{"B", 7d}
   };
   for (int r = 0; r < data.length; r++) {
    row = dataSheet.createRow(r);
    Object[] rowData = data[r];
    for (int c = 0; c < rowData.length; c++) {
     cell = row.createCell(c);
     if (rowData[c] instanceof String) {
      cell.setCellValue((String)rowData[c]);
     } else if (rowData[c] instanceof Double) {
      cell.setCellValue((Double)rowData[c]);
     }
    }
   }

   AreaReference arerReference = new AreaReference("A1:B5", SpreadsheetVersion.EXCEL2007);

   XSSFPivotTable pivotTable = ((XSSFSheet)pivotSheet).createPivotTable(arerReference, new CellReference("A4"), dataSheet);

   pivotTable.addRowLabel(0);
   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1, "Sum of count");

   pivotTable.getCTPivotTableDefinition().getPivotTableStyleInfo().setName("PivotStyleMedium7");

   workbook.write(fileout);

  }

 }
}

Names of possible named styles can be got from Excels GUI in PivotTable Tools tab - Design.

这篇关于如何使用 apache-poi 将数据透视表样式从默认蓝色更改为其他颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！