[]
产品首页 在线Demo 产品文档 API参考 技术社区
立即试用
(Showing Draft Content)

Pivot Chart

Pivot chart represents the data of associated pivot table in a chart. Like a normal chart, the pivot chart displays data series, categories, legends, data markers and axes. You can change the titles, legend placement, data labels, chart location etc.

A pivot chart is interactive as it reflects the changes made in its associated pivot table. The pivot table fields are displayed on a pivot chart as buttons. You can configure whether to display the legend, axis, value field buttons or expanding or collapsing entire field buttons by using the PivotOptions property. When a field button is clicked, its filter pane appears. It helps you to sort and filter pivot chart's underlying data.

Excel files with pivot charts can be loaded, modified and saved back to Excel. The below image displays a pivot chart with legend, axis and value field buttons.


Create Pivot Chart

The below mentioned steps explain how to create a pivot chart:

  1. Create a pivot table.

  2. Add a normal chart by using AddChart method of IShapes interface.

  3. Use SetSourceData method of IChart interface to turn a normal chart into a PivotChart by providing the source range inside the pivot table's range.

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

object[,] sourceData = new object[,] {
{ "Order ID", "Product",               "Category",              "Amount", "Date",                    "Country" },
{ 1,          "Bose 785593-0050",      "Consumer Electronics",  4270,     new DateTime(2018, 1, 6),  "United States" },
{ 2,          "Canon EOS 1500D",       "Consumer Electronics",  8239,     new DateTime(2018, 1, 7),  "United Kingdom" },
{ 3,          "Haier 394L 4Star",      "Consumer Electronics",  617,      new DateTime(2018, 1, 8),  "United States" },
{ 4,          "IFB 6.5 Kg FullyAuto",  "Consumer Electronics",  8384,     new DateTime(2018, 1, 10), "Canada" },
{ 5,          "Mi LED 40inch",         "Consumer Electronics",  2626,     new DateTime(2018, 1, 10), "Germany" },
{ 6,          "Sennheiser HD 4.40-BT", "Consumer Electronics",  3610,     new DateTime(2018, 1, 11), "United States" },
{ 7,          "Iphone XR",             "Mobile",                9062,     new DateTime(2018, 1, 11), "Australia" },
{ 8,          "OnePlus 7Pro",          "Mobile",                6906,     new DateTime(2018, 1, 16), "New Zealand" },
{ 9,          "Redmi 7",               "Mobile",                2417,     new DateTime(2018, 1, 16), "France" },
{ 10,         "Samsung S9",            "Mobile",                7431,     new DateTime(2018, 1, 16), "Canada" },
{ 11,         "OnePlus 7Pro",          "Mobile",                8250,     new DateTime(2018, 1, 16), "Germany" },
{ 12,         "Redmi 7",               "Mobile",                7012,     new DateTime(2018, 1, 18), "United States" },
{ 13,         "Bose 785593-0050",      "Consumer Electronics",  1903,     new DateTime(2018, 1, 20), "Germany" },
{ 14,         "Canon EOS 1500D",       "Consumer Electronics",  2824,     new DateTime(2018, 1, 22), "Canada" },
{ 15,         "Haier 394L 4Star",      "Consumer Electronics",  6946,     new DateTime(2018, 1, 24), "France" },
};

 IWorksheet worksheet = workbook.Worksheets[0];
 worksheet.Range["A6:F21"].Value = sourceData;
 worksheet.Range["D6:D21"].NumberFormat = "$#,##0.00";
 // Create pivot cache
 var pivotcache = workbook.PivotCaches.Create(worksheet.Range["A6:F21"]);
 // Create pivot table
 var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["A1"], "pivottable1");

 //config pivot table's fields
 pivottable.PivotFields["Category"].Orientation = PivotFieldOrientation.RowField;
 pivottable.PivotFields["Country"].Orientation = PivotFieldOrientation.ColumnField;
 pivottable.PivotFields["Amount"].Orientation = PivotFieldOrientation.DataField;

 worksheet.Range["A:I"].AutoFit();

 // Add a column chart
 IChart chart = worksheet.Shapes.AddChartInPixel(ChartType.ColumnClustered, 0, 100, 689, 320).Chart;

 // Set data source(use pivot table range).
 chart.SetSourceData(pivottable.TableRange1);

 //save to an excel file
 workbook.Save("createpivotchart.xlsx");

Note: To turn a normal chart into a pivot chart, add any chart from the ones listed below. A NotSupportedException will be thrown if any other chart is added.

  • Area

  • Bar

  • Column

  • Pie/Doughnut

  • Line

  • Radar

  • Surface

Configure Pivot Chart's Buttons

Refer to the following example code to configure pivot chart's buttons.

chart.PivotOptions.ShowLegendFieldButtons = false;
chart.PivotOptions.ShowAxisFieldButtons = false;

// Set legend position to bottom
chart.Legend.Position = LegendPosition.Bottom;

Update Pivot Table to Reflect in Pivot Chart

Refer to the following example code to update pivot table to reflect in pivot chart.

// Drag row field to hidden
chart.PivotTable.RowFields[0].Orientation = PivotFieldOrientation.Hidden;

Convert Pivot Chart to Normal Chart

Refer to the following example code to convert pivot chart to normal chart.

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

Limitations

  • The pivot chart is exported as a normal chart in PDF or while doing JSON I/O.

  • If you add, change or delete the source range of a series, it will not reflect in pivot chart.