Spreadsheet Pivot Table Capabilities & Chart customiation at run time

Question

Good day,Due to the fact that the pivotGridControl is not serializable, and I am attempting to take a different approach to accomplish my goals be utilizing the spreadsheet control. I have not seen anything in the documentation about whether or not it is possible to include a pivot table in the spreadsheet, like in Excel, does this function currently exist?Also, how can the user manipulate all of the chart properties (chart type, series colors, axis details, etc) at run time after a chart has been added?

Thirumurugan Loganathan · Answer

Hi Travis,   
   
Please find the below details for asked queries.   
   




S.No   

Query   

Comments   
   


1   

Is it Possible to include a pivot table in the spreadsheet, like in Excel   
   

No, currently spreadsheet does not have support to include pivot table as Excel.   
   


2   

how can the user manipulate all of the chart properties (chart type, series colors, axis details, etc.) at run time after a chart has been added   
   

You can change all the chart properties at runtime by using ActiveGrid_loaded event of the spreadsheet. Please refer the below code sample for your reference.   
   




   
private void Spreadsheet_WorkbookLoaded(object sender, WorkbookLoadedEventArgsargs)   
{   
            spreadsheet.ActiveGrid.Loaded += ActiveGrid_Loaded;   
}   
   
private void ActiveGrid_Loaded(object sender, RoutedEventArgs e)   
{   
            var chart = spreadsheet.AddChart(spreadsheet.ActiveSheet);   
   
            //Given chart axis details.   
            object[] Y_values = new object[] { 200, 100, 100 };   
            object[] X_values = new object[] { "Total Income", "Expenses", "Profit" };   
   
            //Change chart type.   
            IChartSerie series = chart.Series.Add(ExcelChartType.Bar_Clustered);   
   
            //Change chart color.   
            series.SerieFormat.Fill.ForeColor = System.Drawing.Color.Green;   
   
            //Change chart's DataLabel color.   
            series.DataPoints.DefaultDataPoint.DataLabels.Color = ExcelKnownColors.Red;   
            series.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;   
   
            // Enters the X and Y values directly   
            series.EnteredDirectlyValues = Y_values;   
            series.EnteredDirectlyCategoryLabels = X_values;   
   
            var shape = chart as ShapeImpl;   
   
            // Re-Positioning Chart   
            shape.Top = 100;   
            shape.Left = 100;   
   
            //Re-sizing a Chart   
            shape.Height = 300;   
            shape.Width = 300;   
}   
   
   
Please refer the below link for further clarification.   
   
https://help.syncfusion.com/file-formats/xlsio/working-with-charts#chart-appearance-settings     
    
   
   
Sample: http://www.syncfusion.com/downloads/support/directtrac/134582/ze/ChartDemo-1329371285     
   
Regards,   
Thirumurugan