Adding Pivot charts in a WorkSheet

Hi,

Is it possiable to add pivot charts in a work sheet using Aspose.

If yes, can you please tell how can we do that.

Thanks,

Suresh.

Hi Suresh,

Yes, it is quite possible. Please see the document/article for your reference:

http://www.aspose.com/documentation/java-components/aspose.cells-for-java/create-pivot-tables-and-pivot-charts.html

Thank you.

Hi,

thanku for ur reply, based on the code from the link which you specified,

i generated the the excel with pivot tables and pivot charts.

But in the pivot chart sheet i am not able to select any cell.

Can you please tell in the pivot chart sheet, how can i display a text (Suppose in the position (1,1))

Thank,

Suresh

Hi,

How could you select a cell or add the text in your desired cell (A1) in the pivot chart
sheet manually, if you could do in MS Excel, kindly post the Excel file here. I think even in MS Excel
you cannot directly insert text into the cells for the chart type sheet.

Thank you.

Hi Amjad,

I am attaching my Sample excel file(Sample_Report.xls).Which is our actulal requirement. we need to do in the same way.

You can find the another excel file attachment(Report.xls). Which is nothing but the excel which is generated based on Aspose, as per our requirement.

Please have the a look and find the difference and can you please provide us the solution.

thankas,

Suresh.

Hi Suresh,

Thanks for providing us the template files.

Well, you are actually adding Chart type sheet in which you are adding pivot charts as shown in your “Report.xls”. For your requirement, you need to add a simple sheet and not chart type sheet (in chart type sheet you cannot add any data into the cells or select the cells, so you cannot add your desired text into the A1 cell of the pivot chart sheet).

I think for your requirement, you may slightly update you code accordingly as follows:

e.g

//Adding a new sheet
Worksheet pvtChartSheet = workbook.getWorksheets().addSheet();
//Naming the sheet
pvtChartSheet.setName(“CIB Report”);
pvtChartSheet.setGridlinesVisible(false);
pvtChartSheet.getCells().getCell(“A1”).setValue(“Q1 2010 WATCHLIST TRENDS (RISQ/CIB PERIMETER)”);
//Adding a column chart
Chart chart = pvtChartSheet.getCharts().addChart(…);
//Setting the pivot chart data source
chart.setPivotSource(“PIVOT BL!PivotTable1”);
chart.hidePivotFieldButton(false);




And for your other issue regarding hiding Column Grand Totals, we have found the issue. It seems the code i.e. pivotTable.setColumnGrand(false); does not work. I have logged your issue into our issue tracking system with an issue id: CELLSJAVA-16340. We will figure it out soon.

Thank you.

Hi Amjad,

Thank u very much. I made changes in my code based on code which u sent. Its Working fine.

When i generate the report, i am facing another issue.

Suppose we have only record in the data sheet, and i try to generate the report. then i am getting folowing exception.

java.lang.IllegalArgumentException: Creating a PivotTable needs at least two rows of source data.

com.aspose.cells.bY.(Unknown Source)

com.aspose.cells.fX.a(Unknown Source)

com.aspose.cells.PivotTables.add(Unknown Source)

com.aspose.cells.PivotTables.add(Unknown Source)

Can u please tell me how to overcome this problem.( like even we have only one record in the data sheet it should generate the report)

Thanks,

Suresh.

Hi,

Well, MS Excel puts this restriction and you cannot create a PivotTable based on one row of data as source, you should have at least two rows of source data. You can confirm this error by manually try to create pivot table (based on only one row as source data) in MS Excel. Aspose.Cells for Java provides the same behavior as MS Excel.

Thank you.

Hi Amjad,

Thank u for u Reply.

I tried to create the Pivot table with one row as the source data in the Excel manually, I am able to generate the Pivot table and Pivot chart.

But same thing i am not able to do with Aspose. When i tried to generate the pivot tables and pivot charts with only row in the source data, i am getting the following Exception.

java.lang.IllegalArgumentException: Creating a PivotTable needs at least two rows of source data.

com.aspose.cells.bY.(Unknown Source)

com.aspose.cells.fX.a(Unknown Source)

com.aspose.cells.PivotTables.add(Unknown Source)

com.aspose.cells.PivotTables.add(Unknown Source)

For your referance i am sending you my Excel which is manually create with only one row as my source data.

Please have a look into the excel and please provide us the solution to overcome the current issue.

Thanks,

Suresh

Hi,

Well, both your pivot tables (in the template file) still have two rows as their data source for pivot table reports.
e.g
i) Raw ABC!A1:D2 - for first pivot table. - two rows in total
ii) Raw XYZ!A1:D2 - for second pivot table. - two rows in total

Using the Aspose.Cells for Java API, you can use e.g
int index = pivotTables.add("=Raw ABC!A1:D2", “B3”, “PivotTable1”);


Kindly let us know if you still find any issue, we will check it

Thank you.