PivotChart Report

Aspose.Cells Product Family
1

Is there a way to create a PivotChart Report (with PivotTable) using ASPOSE.Cells?

Using the example at: Creating a Simple PivotTable I was able to create a PivotTable report.

I need functionality that would create PivotChart report.

Thanks

2

We don’t support to create pivotchart with code. But I think you can try to use our PivotTable and chart APIs to create a similiar report.

3

Hi,

I know that in Excel, you just need to fill the Data Range with the PivotTable range and Excel create automatically the PivotChart. Creating series is disabled.

But i couldn't find out the method or property which allow such a thing.

I hope that would help you some kind.

Tom.

4

Hi Tom,

Thanks for considering Aspose.

Well, you have to make them separately, First you will create a PivotTable Report using its related API and then use the Chart API to create the chart based on the pivot table report source.

Please check the following links for creating PivotTable Report and Chart:

Note: You can also consult the Source code of Aspose.Cells featured Demos related chart.

Thank you.

5

I know how to create a PivotTable from scratch, but i can't create a PivotChart from scratch. I have already seen your links and the PivotChart has been created from a spreadsheet model. You just have to update the NamedRange. I would like to create it from scratch in runtime. For instance:

- Sheet1 : DataSource
- Sheet2 : PivotTable at A1:F10 from the DataSource

With Excel:
- Insert chart...
- Data Source : i type "=Sheet2!A1:F10"
- And the PivotChart is automaticaly created

With Aspose.Cells
- I create Sheet1 and Sheet2 with some lines code easily
- I would create my PivotChart with:
Chart chart = sheet.Charts[sheet.Charts.Add(ChartType.Bar, 2, 2, 28, 13)];
chart.NSeries.Add("Sheet2!A1:F10", true); //false don't change anything
- The result is not what i have expected

Moreover, i can't get the PivotTable's content with :
Sheet2.Cells["B3"].Value

The only way i've found is to combine with Aspose.Grid.
- I create my PivotTable with Aspose.Grid
- I save the webworksheet in a MemoryStream
- I create a new Aspose.Cells workbook and open it from the MemoryStream
- Then now i can get the PivotTable's content from the new workbook.

I think it consume much more resources, so could you add this kind of feature in Aspose.Cells and Aspose.Grid ? (a mean to get the PivotTable's content)

Thank you.

Tom.

6

Hi Tom,

Well, we may consider PivotChart feature to further enhance Pivot Table Reports in future. Thank you for your suggestion.

7

@intphant,

We have included more enhancements regarding Pivot table features in Aspose.Cells. You can easily add pivot chart based on pivot table report for your needs. To create a PivotChart using Aspose.Cells, please do the following:

  1. Add a chart.
  2. Set the PivotSource of the chart to refer to an existing pivot table in the spreadsheet.
  3. Set other attributes.

e.g
Sample code:

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
    // Instantiating an Workbook object
    // Opening the excel file
    Workbook workbook = new Workbook("pivotTable_test.xlsx");
    // Adding a new sheet
    Worksheet sheet3 = workbook.Worksheets[workbook.Worksheets.Add(SheetType.Chart)];
    // Naming the sheet
    sheet3.Name = "PivotChart";
    // Adding a column chart
    int index = sheet3.Charts.Add(Aspose.Cells.Charts.ChartType.Column, 0, 5, 28, 16);
    // Setting the pivot chart data source
    sheet3.Charts[index].PivotSource = "PivotTable!PivotTable1";
    sheet3.Charts[index].HidePivotFieldButtons = false;
    // Saving the Excel file
    workbook.Save("pivotChart_test_out.xlsx");