Aspose pivot chart

Aspose.Cells Product Family
1

(a)We would like to sort the data of the pivot chart ie. On the attached chart the Employee field names to appear in alphabetical order.

(b)We would also like the values of the average quarter to appear on the chart above every point.( see the attached image : chartWithvaluesOverPoints)

Ty.

2

Hi,


Thanks for your posting and using Aspose.Cells.

Please modify your output excel file using Microsoft Excel and attach the expected output excel file so that we could look into this issue and provide you a sample code to achieve it using Aspose.Cells APIs. Thanks for your cooperation in this regard and have a good day.

3

attaching the output file.

4

Hi,


Thanks for using Aspose.Cells.

Please see the following sample code which should fulfill your needs. The code first sorts the pivot row field and then it refreshes the chart pivot data.

Please read the comments inside the code. Please also check the source excel file used in this code and output excel file and the output pdf file generated by the code. I have also attached the screenshot that shows how the chart pivot data has been sorted in ascending order.

Java

//Open your workbook
Workbook wb = new Workbook(dirPath + “s.xls”);
//Access pivot table worksheet
Worksheet ws = wb.getWorksheets().get(“PivotchartName”);

//Sort first row field in ascending order and refersh and calculate data of pivot table
PivotTable pt = ws.getPivotTables().get(0);
pt.getRowFields().get(0).setAutoSort(true);
pt.getRowFields().get(0).setAscendSort(true);
pt.refreshData();
pt.calculateData();

//Access pivot chart sheet and access chart and refresh its pivot data
ws = wb.getWorksheets().get(“chartName”);
Chart ch = ws.getCharts().get(0);
ch.refreshPivotData();

//Save the workbook in xls format
wb.save(dirPath + “o.xls”);

//Save the workbook in pdf format
PdfSaveOptions opts = new PdfSaveOptions();
opts.setOnePagePerSheet(true);
wb.save(dirPath + “o.pdf”, opts);
5

Hey ,


Thanks for the solution ,

can you help me with the following issue as well…

(b)We would also like the values of the average quarter to appear on the chart above every point.( see the attached image : chartWithvaluesOverPoints)
6

Hi,


Thanks for your posting and using Aspose.Cells.

Please see the following sample code, its source excel file and the output excel file. Please also check the comments inside the code. It should fix your issue. Let us know your feedback.

Java

//Load your workbook and access chart worksheet
Workbook wb = new Workbook(dirPath + “s.xls”);
Worksheet ws = wb.getWorksheets().get(“chartName”);
//Access chart
Chart ch = ws.getCharts().get(0);

//Do this thing for all series one by one
//I am doing it for the first series only
DataLabels lbls= ch.getNSeries().get(0).getDataLabels();
lbls.setShowValue(true);
lbls.setPosition(LabelPositionType.RIGHT);

wb.save(dirPath + “s_out.xls”);
7

Nope. Not working.
We are getting a “java.lang.IndexOutOfBoundsException: Index: 0, Size: 0”
We are creating a PivotChart and then setting a PivotSource. So the Nseries list contains zero elements.
I also send you the code.
Home.txt (java file)
They should look in the last method createPivotChart, /* Nseries List contains zero elements *

Ty.

8

Hi,


Thanks for your posting and using Aspose.Cells.

Please call Chart.refreshPivotData() before you access your series. So modify your code like this.

// Setting the pivot chart data source
chart.setPivotSource(pivotTableName + “!” + pivotTableName + “1”);
chart.setHidePivotFieldButtons(false);

chart.refreshPivotData();

DataLabels lbls= ch.getNSeries().get(0).getDataLabels();