如何使用apache-poi将数据透视表样式从默认蓝色更改为其他颜色

问题描述：

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

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);

我想要的东西.

我能够通过代码生成的内容

答

XXSPivotTable默认情况下使用命名样式PivotStyleLight16.参见 setDefaultPivotTableDefinition .

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

到目前为止，还没有方法可以在高级XSSF类中对此进行更改.我们可以通过

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");

完整示例:

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);

  }

 }
}

可以从PivotTable Tools选项卡-Design中的Excel s GUI获取可能的命名样式的名称.

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

如何使用apache-poi将数据透视表样式从默认蓝色更改为其他颜色

相关推荐