数据透视图

数据透视图用图表来表示相关数据。与普通图表一样，轴心图显示数据系列、类别、图例、数据标记和轴。您可以更改标题、图例位置、数据标签、图表位置等。

数据透视图是交互式的，因为它反映了在关联数据透视表中所做的更改。数据透视表字段以按钮的形式显示在数据透视图上。您可以使用getPivotOptions方法来配置是显示图例,轴,值字段按钮,还是展开或折叠整个字段按钮。单击字段按钮时，将显示其筛选窗格。它帮助您排序和筛选主图的基础数据。

带数据透视图的Excel文件可以加载、修改和保存回Excel。下图显示了一个带有图例、轴和值字段按钮的轴心图。

创建数据透视表

下面提到的步骤解释了如何创建一个透视图::

创建数据透视表
使用IShapes界面的addChartInPixel方法添加一个正常的图表。
使用IChart interface的setSourceData方法，通过提供透视表范围内的源范围，将一个普通图表转换为数据透视图。

//create a new workbook
Workbook workbook = new Workbook();
Object sourceData = new Object[][]{
{"Order ID", "Product", "Category", "Amount", "Date", "Country"},
{1, "Bose 785593-0050", "Consumer Electronics", 4270, new GregorianCalendar(2018, 0, 6), "United States"},
{2, "Canon EOS 1500D", "Consumer Electronics", 8239, new GregorianCalendar(2018, 0, 7), "United Kingdom"},
{3, "Haier 394L 4Star", "Consumer Electronics", 617, new GregorianCalendar(2018, 0, 8), "United States"},
{4, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 8384, new GregorianCalendar(2018, 0, 10), "Canada"},
{5, "Mi LED 40inch", "Consumer Electronics", 2626, new GregorianCalendar(2018, 0, 10), "Germany"},
{6, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3610, new GregorianCalendar(2018, 0, 11), "United States"},
{7, "Iphone XR", "Mobile", 9062, new GregorianCalendar(2018, 0, 11), "Australia"},
{8, "OnePlus 7Pro", "Mobile", 6906, new GregorianCalendar(2018, 0, 16), "New Zealand"},
{9, "Redmi 7", "Mobile", 2417, new GregorianCalendar(2018, 0, 16), "France"},
{10, "Samsung S9", "Mobile", 7431, new GregorianCalendar(2018, 0, 16), "Canada"},
{11, "OnePlus 7Pro", "Mobile", 8250, new GregorianCalendar(2018, 0, 16), "Germany"},
{12, "Redmi 7", "Mobile", 7012, new GregorianCalendar(2018, 0, 18), "United States"},
{13, "Bose 785593-0050", "Consumer Electronics", 1903, new GregorianCalendar(2018, 0, 20), "Germany"},
{14, "Canon EOS 1500D", "Consumer Electronics", 2824, new GregorianCalendar(2018, 0, 22), "Canada"},
{15, "Haier 394L 4Star", "Consumer Electronics", 6946, new GregorianCalendar(2018, 0, 24), "France"},
};
                
IWorksheet worksheet = workbook.getWorksheets().get(0);
worksheet.getRange("A6:F21").setValue(sourceData);
worksheet.getRange("D6:D21").setNumberFormat("$#,##0.00");
// Create pivot cache
IPivotCache pivotcache = workbook.getPivotCaches().create(worksheet.getRange("A6:F21"));
// Create pivot table
IPivotTable pivottable = worksheet.getPivotTables().add(pivotcache, worksheet.getRange("A1"), "pivottable1");
                
//config pivot table's fields
pivottable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
pivottable.getPivotFields().get("Country").setOrientation(PivotFieldOrientation.ColumnField);
pivottable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
                
worksheet.getRange("A:I").autoFit();
                
// Add a column chart
IChart chart = worksheet.getShapes().addChartInPixel(ChartType.ColumnClustered, 0, 100, 689, 320).getChart();
                
// Set data source(use pivot table range).
chart.setSourceData(pivottable.getTableRange1());
                
//save to an excel file
workbook.save("CreatePivotChart.xlsx");

注意: 要把一个普通图表变成一个透视图，可以从下面列出的图表中任意添加一个。如果添加任何其他图表，则会抛出NotSupportedException;
Area
Bar
Column
Pie/Doughnut
Line
Radar
Surface

配置Pivot Chart的按钮

请参考下面的示例代码来配置pivot chart的按钮。

// Set not to show legend and axis buttons
chart.getPivotOptions().setShowLegendFieldButtons(false);
chart.getPivotOptions().setShowAxisFieldButtons(false);

// Set legend position to bottom
chart.getLegend().setPosition(LegendPosition.Bottom);

更新数据透视表以反映在数据透视图中

参考下面的示例代码更新数据透视表以反映在数据透视图中。

// Drag row field to hidden
chart.getPivotTable().getRowFields().get(0).setOrientation(PivotFieldOrientation.Hidden);

转换轴心图为法线图

参考下面的示例代码将透视图转换为法线图。

// Clear pivot table to turn a PivotChart into a normal chart.
pivottable.getTableRange2().clear();

局限性

数据透视表会以PDF格式或JSON格式导出。
如果您添加、更改或删除序列的源范围，它将不会在pivot chart中反映出来。