I would like to be able to show the data labels on a chart that I’ve set the pivot source for. I know that you can usually do this via the Chart.NSeries[0].DataLabels.ShowValue property. However, because the chart is a PivotChart (Chart.PivotSource = “”), my NSeries collection is empty. How can I control the look and feel of the series on my PivotChart?
Hi,
I am afraid, the feature is not supported yet.
We have recently supported to manipulate pivot tables in v5.2.0 of the product, so now we can start working on your feature (manipulating pivot charts). We already added the feature into our issue tracking system with an id: CELLSNET-16205. Once we have any update about it, we will let you know.
Thank you.
Hi,
Do we now have any way to display Data labels on pivot charts?
Thanks,
Priya
Hi,
I think manipulating pivot charts feature is not completely supported yet. I have tested your scenario a bit with latest version v7.0.4.x and it gives me error on the manipulating chart’s line of code. I have logged a ticket with an id: CELLSNET-40249. Our concerned developer will look into it soon. If we have any update on it, we will let you know here.
Thank you.
Hi,
Please change your sample code as following.
See the highlighted line.
C#
Workbook workbook = new Workbook("e:\\test2\\my_pvtchart.xls");
PivotTable pvt = workbook.Worksheets[0].PivotTables[0];
pvt.RefreshData();
pvt.CalculateData();
Chart chart = workbook.Worksheets[1].Charts[0];
chart.RefreshPivotData();//add this code
chart.NSeries[0].DataLabels.ShowValue = true; //Error
workbook.Save("e:\\test2\\gadddosdfasutput.xls");
PivotTable pvt = workbook.Worksheets[0].PivotTables[0];
pvt.RefreshData();
pvt.CalculateData();
Chart chart = workbook.Worksheets[1].Charts[0];
chart.RefreshPivotData();//add this code
chart.NSeries[0].DataLabels.ShowValue = true; //Error
workbook.Save("e:\\test2\\gadddosdfasutput.xls");
Hi,
Here are the complete code segments for your reference.
1) Creating pivot table and pivot chart in the output file.
//Instantiating an Workbook object
Workbook workbook = new Workbook();
//Obtaining the reference of the newly added worksheet
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;
//Setting the value to the cells
Aspose.Cells.Cell cell = cells[“A1”];
cell.PutValue(“Sport”);
cell = cells[“B1”];
cell.PutValue(“Quarter”);
cell = cells[“C1”];
cell.PutValue(“Sales”);
cell = cells[“A2”];
cell.PutValue(“Golf”);
cell = cells[“A3”];
cell.PutValue(“Golf”);
cell = cells[“A4”];
cell.PutValue(“Tennis”);
cell = cells[“A5”];
cell.PutValue(“Tennis”);
cell = cells[“A6”];
cell.PutValue(“Tennis”);
cell = cells[“A7”];
cell.PutValue(“Tennis”);
cell = cells[“A8”];
cell.PutValue(“Golf”);
cell = cells[“B2”];
cell.PutValue(“Qtr3”);
cell = cells[“B3”];
cell.PutValue(“Qtr4”);
cell = cells[“B4”];
cell.PutValue(“Qtr3”);
cell = cells[“B5”];
cell.PutValue(“Qtr4”);
cell = cells[“B6”];
cell.PutValue(“Qtr3”);
cell = cells[“B7”];
cell.PutValue(“Qtr4”);
cell = cells[“B8”];
cell.PutValue(“Qtr3”);
cell = cells[“C2”];
cell.PutValue(1500);
cell = cells[“C3”];
cell.PutValue(2000);
cell = cells[“C4”];
cell.PutValue(600);
cell = cells[“C5”];
cell.PutValue(1500);
cell = cells[“C6”];
cell.PutValue(4070);
cell = cells[“C7”];
cell.PutValue(5000);
cell = cells[“C8”];
cell.PutValue(6430);
PivotTableCollection pivotTables = sheet.PivotTables;
//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=A1:C8", “E20”, “PivotTable1”);
//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables[index];
//Draging the first field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 0);
//Draging the second field to the column area.
pivotTable.AddFieldToArea(PivotFieldType.Column, 1);
//Draging the third field to the data area.
pivotTable.AddFieldToArea(PivotFieldType.Data, 2);
index = workbook.Worksheets[workbook.Worksheets.Add(SheetType.Chart)].Charts.Add(ChartType.Bar, 0, 5, 20, 15);
workbook.Worksheets[1].Charts[index].PivotSource = “Sheet1!PivotTable1”;
workbook.Worksheets[1].Charts[index].HidePivotFieldButtons = false;
//Saving the Excel file
workbook.Save(@“e:\test2\my_pvtchart.xls”);
2) Manipulating pivot chart and showing data labels in the chart.
Workbook workbook = new Workbook(“e:\test2\my_pvtchart.xls”);
PivotTable pvt = workbook.Worksheets[0].PivotTables[0];
pvt.RefreshData();
pvt.CalculateData();
Chart chart = workbook.Worksheets[1].Charts[0];
chart.RefreshPivotData();
chart.NSeries[0].DataLabels.ShowValue = true; //OK
workbook.Save(“e:\test2\output.xls”);
Thank you.
Hi,
For this I need to first save the excel with pivot chart and then open it to use its DataLabel.ShowValue property, can’t this be done without saving the file, as my requirement is to use a template and create the resultant file on the fly?
Thanks,
Priya
Hi,
Well, it works fine (show data labels for the pivot chart on the fly). See the updated code below, I have created the pivot table and then then pivot chart on the fly to make the data labels for the first series of the pivot chart shown, finally I generated the file to the disk.
//Instantiating an Workbook object
Workbook workbook = new Workbook();
//Obtaining the reference of the newly added worksheet
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;
//Setting the value to the cells
Aspose.Cells.Cell cell = cells[“A1”];
cell.PutValue(“Sport”);
cell = cells[“B1”];
cell.PutValue(“Quarter”);
cell = cells[“C1”];
cell.PutValue(“Sales”);
cell = cells[“A2”];
cell.PutValue(“Golf”);
cell = cells[“A3”];
cell.PutValue(“Golf”);
cell = cells[“A4”];
cell.PutValue(“Tennis”);
cell = cells[“A5”];
cell.PutValue(“Tennis”);
cell = cells[“A6”];
cell.PutValue(“Tennis”);
cell = cells[“A7”];
cell.PutValue(“Tennis”);
cell = cells[“A8”];
cell.PutValue(“Golf”);
cell = cells[“B2”];
cell.PutValue(“Qtr3”);
cell = cells[“B3”];
cell.PutValue(“Qtr4”);
cell = cells[“B4”];
cell.PutValue(“Qtr3”);
cell = cells[“B5”];
cell.PutValue(“Qtr4”);
cell = cells[“B6”];
cell.PutValue(“Qtr3”);
cell = cells[“B7”];
cell.PutValue(“Qtr4”);
cell = cells[“B8”];
cell.PutValue(“Qtr3”);
cell = cells[“C2”];
cell.PutValue(1500);
cell = cells[“C3”];
cell.PutValue(2000);
cell = cells[“C4”];
cell.PutValue(600);
cell = cells[“C5”];
cell.PutValue(1500);
cell = cells[“C6”];
cell.PutValue(4070);
cell = cells[“C7”];
cell.PutValue(5000);
cell = cells[“C8”];
cell.PutValue(6430);
PivotTableCollection pivotTables = sheet.PivotTables;
//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=A1:C8", “E20”, “PivotTable1”);
//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables[index];
//Draging the first field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 0);
//Draging the second field to the column area.
pivotTable.AddFieldToArea(PivotFieldType.Column, 1);
//Draging the third field to the data area.
pivotTable.AddFieldToArea(PivotFieldType.Data, 2);
index = workbook.Worksheets[workbook.Worksheets.Add(SheetType.Chart)].Charts.Add(ChartType.Bar, 0, 5, 20, 15);
workbook.Worksheets[1].Charts[index].PivotSource = “Sheet1!PivotTable1”;
workbook.Worksheets[1].Charts[index].HidePivotFieldButtons = false;
Chart chart = workbook.Worksheets[1].Charts[index];
chart.RefreshPivotData();
chart.NSeries[0].DataLabels.ShowValue = true;
//Saving the Excel file
workbook.Save(@“e:\test2\my_pvtchart.xls”);