Document Solutions for Excel, .NET Edition | Document Solutions
Features / Pivot Chart
In This Topic
    Pivot Chart
    In This Topic

    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.
    C#
    Copy Code 
    //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.

    C#
    Copy Code 
    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.

    C#
    Copy Code 
    // 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.

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

    Limitations