数据透视表样式
GcExcel Java允许用户将内置和自定义样式应用于透视表。
借助此功能，用户将能够保存具有不同样式的数据透视表（关于数据透视表布局和数据透视表字段）。 用户可以自定义透视表的显示方式，包括透视表方向、页面大小、透视表字段和许多其他特性，这些都是根据用户的自定义显示首选项进行的。 此外，用户还可以参考主题 导出数据透视表样式和格式 ，以便以PDF格式导出具有不同数据透视表样式的表单。
通常，当用户将数据透视表添加到工作表时，会自动应用默认的数据透视表样式。用户可以通过复制现有样式（也称为内置样式）或从头创建自定义透视表样式来修改添加到工作表中的透视表的默认样式。要将样式应用于透视表，可以参考以下部分：
应用内置数据透视表样式
可以通过应用任何内置样式来更改透视表的默认外观。为了将内置样式应用于透视表，用户可以使用 setStyle() 方法，也可以使用 IPivotTable 接口的 setTableStyle() 方法。
下图描述了一个具有内置样式的透视表。
请参阅以下示例代码，以便将内置样式应用于透视表。
// Initialize workbook
Workbook workbook = new Workbook();
// Fetch default worksheet
IWorksheet worksheet = workbook.getWorksheets().get(0);
// Create PivotTable
Object sourceData = new Object[][] {
{ "Order ID", "Product", "Category", "Amount", "Date", "Country" },
{ 1, "Carrots", "Vegetables", 4270, new GregorianCalendar(2012, 1, 6), "United States" },
{ 2, "Broccoli", "Vegetables", 8239, new GregorianCalendar(2012, 1, 7), "United Kingdom" },
{ 3, "Banana", "Fruit", 617, new GregorianCalendar(2012, 1, 8), "United States" },
{ 4, "Banana", "Fruit", 8384, new GregorianCalendar(2012, 1, 10), "Canada" },
{ 5, "Beans", "Vegetables", 2626, new GregorianCalendar(2012, 1, 10), "Germany" },
{ 6, "Orange", "Fruit", 3610, new GregorianCalendar(2012, 1, 11), "United States" },
{ 7, "Broccoli", "Vegetables", 9062, new GregorianCalendar(2012, 1, 11), "Australia" },
{ 8, "Banana", "Fruit", 6906, new GregorianCalendar(2012, 1, 16), "New Zealand" },
{ 9, "Apple", "Fruit", 2417, new GregorianCalendar(2012, 1, 16), "France" },
{ 10, "Apple", "Fruit", 7431, new GregorianCalendar(2012, 1, 16), "Canada" },
{ 11, "Banana", "Fruit", 8250, new GregorianCalendar(2012, 1, 16), "Germany" },
{ 12, "Broccoli", "Vegetables", 7012, new GregorianCalendar(2012, 1, 18), "United States" },
{ 13, "Carrots", "Vegetables", 1903, new GregorianCalendar(2012, 1, 20), "Germany" },
{ 14, "Broccoli", "Vegetables", 2824, new GregorianCalendar(2012, 1, 22), "Canada" },
{ 15, "Apple", "Fruit", 6946, new GregorianCalendar(2012, 1, 24), "France" }, };
worksheet.getRange("A20:F33").setValue(sourceData);
worksheet.getRange("A:F").setColumnWidth(10);
// Add pivot table
IPivotCache pivotcache =
workbook.getPivotCaches().create(worksheet.getRange("A20:F33"));
IPivotTable pivottable =
worksheet.getPivotTables().add(pivotcache, worksheet
.getRange("A1"), "pivottable1");
// Setting number format for a field
worksheet.getRange("D21:D35").setNumberFormat("$#,##0.00");
// Configure pivot table's fields
IPivotField field_Date =
pivottable.getPivotFields().get("Date");
field_Date.setOrientation(PivotFieldOrientation.PageField);
IPivotField field_Category =
pivottable.getPivotFields().get("Category");
field_Category.setOrientation(PivotFieldOrientation.RowField);
IPivotField field_Product =
pivottable.getPivotFields().get("Product");
field_Product.setOrientation(PivotFieldOrientation.ColumnField);
IPivotField field_Amount =
pivottable.getPivotFields().get("Amount");
field_Amount.setOrientation(PivotFieldOrientation.DataField);
field_Amount.setNumberFormat("$#,##0.00");
IPivotField field_Country =
pivottable.getPivotFields().get("Country");
field_Country.setOrientation(PivotFieldOrientation.RowField);
// Set pivot style
pivottable.setTableStyle("PivotStyleMedium20");
worksheet.getPageSetup().setTopMargin(30);
worksheet.getPageSetup().setLeftMargin(30);
worksheet.getRange("A1:H16").getColumns().autoFit();
// Saving workbook to PDF
workbook.save("PivotBuiltInStyle.pdf", SaveFileFormat.Pdf);注意: 在对透视表应用内置样式时，需要注意的是，如果用户应用 setShowAsAvailableTableStyle 方法为 true 的 TableStyle, 则会引发 InvalidOperationException。
应用自定义样式
如果不想应用任何内置样式，也可以创建自定义样式并将其应用于透视表。 这可以使用IPivotTable 接口的 setStyle() 方法完成。
下图描述了具有自定义样式的透视表。
请参阅以下示例代码，以便将自定义样式应用于透视表。
// Initialize workbook
Workbook workbook = new Workbook();
// Fetch default worksheet
IWorksheet worksheet = workbook.getWorksheets().get(0);
// Create PivotTable
Object sourceData = new Object[][] {
{ "Order ID", "Product", "Category", "Amount", "Date", "Country" },
{ 1, "Carrots", "Vegetables", 4270, new GregorianCalendar(2012, 1, 6), "United States" },
{ 2, "Broccoli", "Vegetables", 8239, new GregorianCalendar(2012, 1, 7), "United Kingdom" },
{ 3, "Banana", "Fruit", 617, new GregorianCalendar(2012, 1, 8), "United States" },
{ 4, "Banana", "Fruit", 8384, new GregorianCalendar(2012, 1, 10), "Canada" },
{ 5, "Beans", "Vegetables", 2626, new GregorianCalendar(2012, 1, 10), "Germany" },
{ 6, "Orange", "Fruit", 3610, new GregorianCalendar(2012, 1, 11), "United States" },
{ 7, "Broccoli", "Vegetables", 9062, new GregorianCalendar(2012, 1, 11), "Australia" },
{ 8, "Banana", "Fruit", 6906, new GregorianCalendar(2012, 1, 16), "New Zealand" },
{ 9, "Apple", "Fruit", 2417, new GregorianCalendar(2012, 1, 16), "France" },
{ 10, "Apple", "Fruit", 7431, new GregorianCalendar(2012, 1, 16), "Canada" },
{ 11, "Banana", "Fruit", 8250, new GregorianCalendar(2012, 1, 16), "Germany" },
{ 12, "Broccoli", "Vegetables", 7012, new GregorianCalendar(2012, 1, 18), "United States" },
{ 13, "Carrots", "Vegetables", 1903, new GregorianCalendar(2012, 1, 20), "Germany" },
{ 14, "Broccoli", "Vegetables", 2824, new GregorianCalendar(2012, 1, 22), "Canada" },
{ 15, "Apple", "Fruit", 6946, new GregorianCalendar(2012, 1, 24), "France" }, };
worksheet.getRange("A20:F33").setValue(sourceData);
worksheet.getRange("A:F").setColumnWidth(10);
// Add pivot table
IPivotCache pivotcache =
workbook.getPivotCaches().create(worksheet.getRange("A20:F33"));
IPivotTable pivottable =
worksheet.getPivotTables().add(pivotcache, worksheet.getRange("A1"),"pivottable1");
// Setting number format for a field
worksheet.getRange("D21:D35").setNumberFormat("$#,##0.00");
// Configure pivot table's fields
IPivotField field_Date = pivottable.getPivotFields().get("Date");
field_Date.setOrientation(PivotFieldOrientation.PageField);
IPivotField field_Category = pivottable.getPivotFields().get("Category");
field_Category.setOrientation(PivotFieldOrientation.RowField);
IPivotField field_Product = pivottable.getPivotFields().get("Product");
field_Product.setOrientation(PivotFieldOrientation.ColumnField);
IPivotField field_Amount = pivottable.getPivotFields().get("Amount");
field_Amount.setOrientation(PivotFieldOrientation.DataField);
field_Amount.setNumberFormat("$#,##0.00");
IPivotField field_Country = pivottable.getPivotFields().get("Country");
field_Country.setOrientation(PivotFieldOrientation.RowField);
// Create pivot style with name "CustomPivotstyle"
ITableStyle pivotStyle = workbook.getTableStyles().add("CustomPivotstyle");
// Set table style as pivot table style
pivotStyle.setShowAsAvailablePivotStyle(true);
pivotStyle.getTableStyleElements()
.get(TableStyleElementType.PageFieldLabels).getInterior()
.setColor(Color.GetLightGreen());
pivotStyle.getTableStyleElements()
.get(TableStyleElementType.PageFieldValues).getInterior()
.setColor(Color.GetLightGreen());
pivotStyle.getTableStyleElements()
.get(TableStyleElementType.GrandTotalColumn).getInterior()
.setColor(Color.GetPowderBlue());
pivotStyle.getTableStyleElements()
.get(TableStyleElementType.GrandTotalRow).getInterior()
.setColor(Color.GetPowderBlue());
pivotStyle.getTableStyleElements()
.get(TableStyleElementType.HeaderRow).getInterior()
.setColor(Color.GetMistyRose());
pivotStyle.getTableStyleElements()
.get(TableStyleElementType.FirstColumn).getInterior()
.setColor(Color.GetLightPink());
pivotStyle.getTableStyleElements()
.get(TableStyleElementType.FirstRowStripe).getInterior()
.setColor(Color.GetSteelBlue());
pivotStyle.getTableStyleElements()
.get(TableStyleElementType.SecondRowStripe).getInterior()
.setColor(Color.GetNavajoWhite());
// Set ShowTableStyleRowStripes as true
pivottable.setShowTableStyleRowStripes(true);
// Set pivot table style
pivottable.setStyle(pivotStyle);
worksheet.getRange("A1:H16").getColumns().autoFit();
worksheet.getPageSetup().setTopMargin(30);
worksheet.getPageSetup().setLeftMargin(30);
worksheet.getRange("A1:H16").getColumns().autoFit();
// Saving workbook to PDF
workbook.save("PivotTableCustomStyle.pdf", SaveFileFormat.Pdf);注意: 在将自定义样式应用于透视表时，需要注意的是，如果用户应用了 setShowAsAvailablePivotStyle 方法为false的TableStyle，则会引发InvalidOperationException。