Pivot Chart Trendline

Aspose.Cells Product Family
, , ,
1

Trying to create a Trendline on a Pivot chart but it doesn’t recognize the Pivot Chart Series.
Sample code for initiating the Pivot table and chart (from your documentation - Create Pivot Tables and Pivot Charts|Documentation) :
Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheet.PivotTables;
int index = pivotTables.Add(“=Data!A1:F30”, “B3”, “PivotTable1”);
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];
int index = sheet.Charts.Add(ChartType.Line, 8, 6, 41, 22);
sheet.Charts[index].PivotSource = “PivotTable!PivotTable1”;

// I’ve tried both:

  1. Series s = chart.NSeries[0]; //-> throws index out of range
    s.TrendLines.Add(TrendlineType.Linear);

  2. SeriesCollection nseries = chart.NSeries;
    Series s1 = nseries[0]; // → Throws index out of range
    s1.TrendLines.Add(TrendlineType.Linear);

It doesn’t capture the series as when try to count them (nseries.Count → displays 0). Can you please attach a sample here on a simple snippet adding a Trendline to a Pivot Chart?

2

@Remus87,
You are requested to create the desired output Excel file using MS Excel only for our reference. Also, share your runnable console application that can be compiled and executed here to analyze this issue where data source is added but cannot be accessed later to add TrendLines.

3

I’ve attached below a runnable console application and also on the project folder i added the DesiredOutput.xlsx file -> what we want to achieve . The method hat holds the funcionality as seen in Main is -> CreateTrendLine() SampleProject1.zip (6.1 MB)

4

@Remus87,
You need to call RefreshData() and CalculateData() for pivot table before using it as source in the chart. Similarly call RefreshPivotData() and Calculate() for chart before accessing the NSeries collection of the chart to add trend line. Here is the modified code for your reference. (Check code with comments //Add these lines).

public static void CreateTrendLine()
{
    string path, csvTitle, sourceDataPT;
    path = "../../";
    csvTitle = path + "LA_SUM.csv";
    LoadOptions loadOptions = new TxtLoadOptions(LoadFormat.CSV);
    Workbook book = new Workbook(csvTitle, loadOptions);
    Worksheet sheet = book.Worksheets[0];
    Range rngAllData = sheet.Cells.MaxDisplayRange;
    Worksheet wsLASumPiv = book.Worksheets.Add("LA_SUM_Pivot");
    sheet.Cells["E1"].PutValue("Total Cas");
    sheet.Cells["R1"].PutValue("Cas per billion veh km");

    // Build Pivot table
    PivotTableCollection pivotTables = wsLASumPiv.PivotTables;
    sourceDataPT = String.Format("=LA_SUM!{0}", rngAllData.Address);
    int indPivTab = pivotTables.Add(sourceDataPT, "A2", "PivotTbl");
    PivotTable pivotTable = pivotTables[indPivTab];
    //Add pivot fields
    pivotTable.AddFieldToArea(PivotFieldType.Row, "Year");
    PivotField pfYear = pivotTable.RowFields[0]; pfYear.IsAutoSort = true;
    pivotTable.AddFieldToArea(PivotFieldType.Data, "Total Cas");
    PivotField TotCas = pivotTable.DataFields[0]; TotCas.DisplayName = "Total Casualties";
    pivotTable.AddFieldToArea(PivotFieldType.Data, "Cas per billion veh km");
    PivotField pfCasbilkm = pivotTable.DataFields[1];
    pfCasbilkm.DisplayName = "Casualties per billion veh km";
    wsLASumPiv.PivotTables[0].ColumnFields.Add(wsLASumPiv.PivotTables[0].DataField);
    pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight16;
    
    //Add these lines	
    pivotTable.RefreshData();
    pivotTable.CalculateData();

    // Build Pivot Chart
    int indChart = wsLASumPiv.Charts.Add(ChartType.Line, 8, 6, 41, 22);
    Chart chart = wsLASumPiv.Charts[indChart];
    chart.PivotSource = "LA_SUM_Pivot!PivotTbl";
    chart.PlotArea.Area.FillFormat.FillType = Aspose.Cells.Drawing.FillType.None;
    chart.Legend.Position = LegendPositionType.Bottom;

    //Add these lines
    chart.RefreshPivotData();
    chart.Calculate();

    // Adding TrendLine and more custom properties to reach the desired output
    chart.NSeries[0].PlotOnSecondAxis = true; // error
    Series s = chart.NSeries[0];  // error
    s.TrendLines.Add(TrendlineType.Linear);

    book.Save(path + "TrendlineOutput.xlsx");
}
5

Is working fine now. Thank you!

6

@Remus87,

Good to know that your issue is resolved by the suggested lines of code. Feel free to write us back if you need further help or have some other issue or queries, we will be happy to assist you soon.