Pivot chart setPivotSource issue

Aspose.Cells Product Family
1

Hi,


I’m trying to create a pivot chart using the two pieces of code, below. I call doPivot and it correctly creates a pivot table named IF_Pivot, I confirmed the name by looking in the Pivot table properties.

The doCharting creates an empty chart on the Chart table with no data source defined. Seems like the chart.setPivotSource(“Pivot!IF_Pivot”); isn’t working… Can you please help me?

Thanks!

Perry

private static Boolean doPivot(WorksheetCollection ws)

{

Worksheet wsPivot = ws.get("Pivot");

Worksheet wsData = ws.get("Data");

if(wsData == null) // is there a data tab?

return false;

// so check to see if the pivot tab already exists. if it does, whack it and replace it with a new one

if(wsPivot != null) // it exists...

ws.removeAt("Pivot");

ws.add("Pivot");

wsPivot = ws.get("Pivot");


Cells cells = wsData.getCells();

int lastRow = wsData.getCells().getMaxDataRow()+1;


PivotTableCollection pivotTables = wsPivot.getPivotTables();

int index = pivotTables.add("=Data!A3:E" + lastRow, "B3", "IF_Pivot");

PivotTable pivotTable = pivotTables.get(index);

pivotTable.setRowGrand(false);

pivotTable.setColumnGrand(false);

pivotTable.addFieldToArea(PivotFieldType.ROW, 0);

pivotTable.addFieldToArea(PivotFieldType.COLUMN, 2);

pivotTable.addFieldToArea(PivotFieldType.DATA, 3);

pivotTable.addFieldToArea(PivotFieldType.DATA, 4);

return true;

}

private static Boolean doCharting(WorksheetCollection ws)

{

Worksheet wsChart = ws.get("Chart");

// so check to see if the chart tab already exists. if it does, whack it and replace it with a new one

if(wsChart != null) // it exists...

ws.removeAt("Chart");

ws.add("Chart");

wsChart = ws.get("Chart");


int chartIndex = wsChart.getCharts().add(ChartType.COLUMN, 0, 5, 28, 16);

Chart chart = wsChart.getCharts().get(chartIndex);

// Set the pivot chart data source

chart.setPivotSource("Pivot!IF_Pivot");

chart.setHidePivotFieldButtons(false);

return true;

}

2

Hi,


Your code segment looks Ok to me. Could you see the document for your complete reference on how to create PivotTable and PivotChart in two steps here:
http://www.aspose.com/docs/display/cellsjava/Create+Pivot+Tables+and+Pivot+Charts

If you still find the issue with Aspose.Cells for Java APIs, kindly do provide complete sample JAVA program (runnable) with template file (if any), so we could evaluate your issue precisely to consequently figure it out soon. Alternatively, you may only provide us the output Excel file (for step 1) that should contain your desired PivotTable.

Thank you.

3

Hi,


I’m attaching my Excel.

Thank you!
4

Hi,


Thanks for providing us template Excel file.

Please try our latest version/fix: Aspose.Cells for Java v8.8.1.3 (attached).

I have tested your scenario/ case a bit using the following sample code with your template file using v8.8.1.3, it works fine. The Pivot Chart is created fine in my added worksheet. I have used your provided template file and created a Pivot Chart in a new worksheet “PivotChart” based on your existing PivotTable in the “Pivot” sheet. I have also attached the output Excel file for your reference.
e.g
Sample code:

//Instantiating an Workbook object
Workbook workbook = new Workbook(“foo.xlsx”);
//Adding a new sheet
int sheetIndex = workbook.getWorksheets().add(SheetType.CHART);
Worksheet sheet3 = workbook.getWorksheets().get(sheetIndex);
//Naming the sheet
sheet3.setName(“PivotChart”);
//Adding a column chart
int chartIndex = sheet3.getCharts().add(ChartType.COLUMN, 0, 5, 28, 16);
Chart chart = sheet3.getCharts().get(chartIndex);
//Setting the pivot chart data source
chart.setPivotSource(“Pivot!IF_Pivot”);
chart.setHidePivotFieldButtons(false);
//Saving the Excel file
workbook.save(“out1pivotChart_test1.xlsx”);

Let us know if you still have any issue.

Thank you.
5

Hi,


Thanks for working on this, but the new build doesn’t seem to fix the issue. I re-ran my code with your new build and still have a blank chart. Sorry, it’s still not working.

I downloaded your example XL and the PivotChart is still empty, with no data source defined.

Perry
6

Hi again,


Is there any possibility that the issue is because I’m using Excel on a Mac, OS X El Capitan? It seems like the Mac build of Excel might have issues with pivot charts. Can you see data in your example pivot chart?

Thanks,

Perry
7

Hi,


Could you try to open the output Excel file into MS Excel on Windows if PivotChart is fine tuned in it. We will also try to perform the test (by opening the file) on Mac, OS X on our end.

Thank you.
8

Hello and thank you for your support!


This is definitely a Mac / Excel issue. Using Windows XL I can see both charts, my original and yours in the XL sheet you sent. Both charts appear empty on my Mac.

If you need any other information about this please let me know. I owe you one!

Thank you!

Perry
9

Hi Perry,


Thank you for the confirmation.

We need your help in figuring out the problem cause. Could you please manually create a Pivot Chart in Excel for MAC and provide it here for analysis? If you can, please provide us 2 spreadsheets as follow.

  • Load your foo.xlsx in Excel for MAC, and manually create a Pivot Chart based on the existing Pivot Table (which you created with Aspose.Cells APIs). Save the result in another file and share it here.
  • Create a new spreadsheet in Excel for MAC and manually create a Pivot Table as well as Pivot Chart based on similar data as of foo.xlsx, and share the resultant spreadsheet here for analysis.

Looking forward to your response.
10

Hi Perry,


This is to update you that we have investigated the matter discussed in this thread by installing the Excel 2016 for MAC on El Capitan. Unfortunately, Excel 2016 for MAC does not support creating the Pivot Charts like its predecessor, Excel for MAC 2011. However, if a spreadsheet already contains a Pivot Chart created with Excel 2013 (windows) the chart displays as regular chart in Excel 2016 for MAC but in case the Pivot Chart is created with Aspose.Cells for Java 8.8.2.3, the chart renders blank. We have logged an investigative ticket as CELLSJAVA-41851 to look further into this matter. As soon as we have any updates in this regard, we will post here for your kind reference.
11

Hi again,


This is to update you that we have logged further into the matter logged earlier as CELLSJAVA-41851. Please note, Excel application for Windows platform automatically refreshes the Pivot Chart’s data from the Pivot Table, however, Aspose.Cells APIs do not automatically refresh because it’s very complex. Moreover, as Excel 2016 for MAC does not support Pivot Chart therefore please call Chart.refreshPivotData method before saving the file.

See following snippet.

Java

//Instantiating an Workbook object
Workbook workbook = new Workbook(dir + “foo.xlsx”);
//Adding a column chart
int chartIndex = workbook.getWorksheets().get(“Pivot”).getCharts().add(ChartType.COLUMN, 10, 6, 28, 16);
Chart chart = workbook.getWorksheets().get(“Pivot”).getCharts().get(chartIndex);
//Setting the pivot chart data source
chart.setPivotSource(“Pivot!IF_Pivot”);
chart.setHidePivotFieldButtons(true);
chart.refreshPivotData();
//Saving the Excel file
workbook.save(dir + “output.xlsx”);

That said, there's still a bug of Chart.refreshPivotData that category data is not correct. We will look into this issue and provide the fix soon.
12
Hi Perry,

This is to update you that we have resolved the problem logged earlier as CELLSJAVA-41851. We will shortly share the fix here after ensuring the quality and incorporating other enhancements.
13

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for Java v8.8.2.6 and let us know your feedback.

14

Thank you, I did the download and will let you know ASAP.

Thanks again for working on this!

Perry

15

Hi, much better thank you, but I think there might be an issues with the way the X-Axis legend is rendering. Attaching a sample. Looks like both are put into the leftmost cell on the graph.



Thanks!

16

Hi Perry,


Thank you for your feedback.

Regarding your recent concerns, I am afraid, I do not understand them correctly. Could you please share a snapshot showing the comparison of same chart in Excel for Windows & Excel for MAC, highlighting the problematic areas?
17

The issues you have found earlier (filed as CELLSJAVA-41851) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.