Query on Pivot Charts

Aspose.Cells Product Family
1
Hi,

I am trying to generate Pivot Chart from PIvot Table and my Pivot Table looks like -
TestName/Operation Passed Failed Warnings Comparison
getPatientProfile01_Src=QL_LOB=Alllines_Td_01 0 1 0 0
getPatientProfile01_Src=QL_LOB=Alllines_Td_02 0 0 1 0
getPatientProfile01_Src=QL_LOB=Alllines_Td_03 0 1 0 0
Grand Total 0 2 1 0

When generating the graph, its not working as expected.

I need Pass.Fail, Warn and Comparison on X-Axis and Grand Total as Legent.

I want my graph to have grand total of Pass, Fail, Warn and Comp

I want to do with Aspose Java , can you please help me as how to achieve this.

Thanks in Advance

2

Hi Rishi,


Thank you for contacting Aspose support.

Please provide us the sample spreadsheet containing your desired results that you may manually create using the MS Excel application. Doing so will enable us to understand your requirements precisely, and we will be able to replicate your requirements using Aspose.Cells for Java API.

Looking forward to your kind response.
3

Hi Babar,


Please find attached the Final Report. Using Aspose API, i have generated Data, PivotTable and PivotChart Worksheet at the run time. But the PivotChart generated as per my code is not the requirement i have.

My expected Pivot Chart, should be as given in the “Expected Graph” worksheet. Can you please help me in changing the axis or what ever so is required. My Graph should be exactly as given in the “Expected Graph” worksheet.

Thanks in Advance




4

Hi Rishi,


Thank you for providing the desired results.

By checking your provided spreadsheet, I have noticed that you wish to switch the Legend Entries (Series) with Horizontal Axis Labels (Category). If your requirement was to create an ordinary chart (not a Pivot Chart) then switching can be done while adding the series using the NSeriese.add method as elaborated below,

public int add(java.lang.String area, boolean isVertical)

The second Boolean parameter to the add method specifies whether to plot the series from a range of cell values by row or by column.

Unfortunately, there aren’t any APIs available to accomplish the same when chart is based on a Pivot Table because in that case the chart data source is set through the method setPivotSource. Moreover, your requirement may not be accomplished using MS Excel application as well. Reason being, when I set the chart data source to be the Pivot Table, I was unable to switch the rows to columns in MS Excel application. Please check the attached snapshot for your reference.
5

Hi Babar,


Thanks for the detailed email.

Actually my requirement was to built pivot chart from pivot table and switch the Horizontal Axis with Legend. Yaah even i noticed the switch option is disabled by MS Excel itself

What do you mean by, if the requirement was to create an Ordinary Chart (not a Pivot Chart)?

I would like to know more details on this. For Sure the table is going to be Pivot Table, can you please let me know as how to generate Ordinary Chart from Pivot Table ? Can you please provide any links for this for reference

Thanks for the help so far …

6

Hi Babar,


I tried normal charts and its working. Thanks for your inputs!

Thanks,
Rishi
7

Hi Babar,


Is there a way in Aspose, to delete the Worksheet based on the worksheet name starting with “Evaluation…”.

The reason behind this is, when ever i try saving the Excel using Aspose, it generates, “Evaluation Warning” by default and the since am saving thrice for different requirements, it gets me 3 “Evaluation Warning” worksheet. So, is there a way to avoid generating “Evaluation Warning” worksheet by default (or) if not, how could i delete them manaully.

Thanks in Advance
8

Hi Rishi,


First of all, please accept my sincere apology for a delayed response. Good to know that you have figure out the problem by switching to normal charts.

Regarding your recent inquiry, Evaluation worksheets are added because you are probably using the Aspose.Cells APIs in evaluation mode, that is; without setting a valid license. Unfortunately, when you delete these evaluation worksheets dynamically and save the results on the disc, there will be another evaluation worksheet, the Aspose.Cells APIs are designed in that way. Although you can delete them manually in MS Excel application by right clicking on the worksheet tabs, and select Delete from the menu.

In order to avoid the evaluation messages while working with Aspose.Cells APIs, we would suggest you to get a 30 day temporary license in case you are evaluating the product and wish to test the product at it’s full capacity. Please follow the instructions provided on the below provided link to request a temporary license. Upon reviewing your request, we will send out the temporary license file via an email.
http://www.aspose.com/corporate/purchase/temporary-license.aspx

9

No problem Babar. Thats fine.


Regarding, “Evaluation Worksheet” Query - Generating one or two “Evaluation” sheet is fine for me but since am saving the Worksheet in many places, am getting too many worksheet generated. Thats the only concern. So, it would be nice to know if you have any option to delete worksheet based on the Worksheet name like ‘%Evaluatio…%’ (or) Worksheet starting with ‘%Evaluatio’.

Also one more query i have while working on the normal charts. You can refer to the XL sheet, i attached before. In the PivotTable worksheet, i have Grand Total from B8:E8.

While generating graph, for time being (meaning working on POC) i gave something like this -

chart.getNSeries().add(“PivotTable!B8:E8”, false);

But in real time, i will not have control on the Pivot Table Data, especially the Grand Total, Rows and Column range be different, its not static and fixed.

While working on charts, can you please let me as how to pull the Grand Total, Rows range dynamically and add them to Nseries.add method.

For Example, How to get chart.getNSeries().add("PivotTable!B8:E8) where 8 should be dynamic

Thanks in Advance for your help so far
10

Hi Rishi,


Thank you for writing back.

For the first part of your inquiry, you may use the Worksheet.removeAt method to delete any worksheet that matches any specific criteria. Below provided code demonstrate the usage. Please modify it according to your need.

Java

Workbook book = new Workbook(myDir + “FinalReport.xls”);
for(int i=0; i<book.getWorksheets().getCount();i++)
{
String name = book.getWorksheets().get(i).getName();
if(name.toLowerCase().contains(“evaluation”))
{
book.getWorksheets().removeAt(name);
System.out.println(name);
}
}
book.save(myDir + “out.xls”);

Regarding the dynamic chart data referencing, I think you may use the Cells.getMaxDataRow and Cells.getMaxDataColoumn methods to get the row & column indexes that contains any data (non-empty). Please check the below provided code snippet for your reference.

Java
Workbook book = new Workbook(myDir + "input.xls"); //Get the cell collection of worksheet containing the chart data Cells cells = book.getWorksheets().get(0).getCells(); //Get the maximum number of row containing the data int maxRow = cells.getMaxDataRow(); //Get the maximum number of column containing the data int maxCol = cells.getMaxDataColumn(); //Insert a new chart int index = book.getWorksheets().get(0).getCharts().add(ChartType.COLUMN,5,0,15,5); //Get the instance of newly inserted chart Chart chart = book.getWorksheets().get(0).getCharts().get(index); //Set the chart series using the MaxDataRow and MaxDataColumn chart.getNSeries().add("Data!B2:" + CellsHelper.columnIndexToName(maxCol) + (maxRow + 1), true); //Save workbook book.save(myDir + "out.xls");

Please note, the above approach requires the starting point of the data, for instance I have hard coded that the data starts from B2.

Please feel free to write back in case you need our further assistance.
11

Hi Babar,


On the Delete, i tried, its working, perfect ! Thanks.

On the Dynamic Chart reference, i know to get the maxDataRow and maxDataColumn from “Data” Workseet. But my question here is, how to get that from “PivotTable” worksheet (please refer attached screenshot). I want the Grand Total, Data Range i.e; B8:E8 which is the last Row of the Pivot Table

Again, i repeat my query is how get the last row count of a generated Pivot Table

Thanks in Advance. Once again Thanks so much for your help so far
12

Hi Rishi,


Thank you for writing back.

Please note, you can use the same logic (as shared in my previous post) to dynamically assign the chart data from PivotTable’s Grand Total row. Please check the below modified code for your reference.

Java

Workbook book = new Workbook(myDir + “input.xls”);
//Get the cell collection of worksheet containing the chart data
Cells cells = book.getWorksheets().get(“PivotTable”).getCells();
//Get the maximum number of row containing the data
int maxRow = cells.getMaxDataRow();
//Get the maximum number of column containing the data
int maxCol = cells.getMaxDataColumn();
//Insert a new chart
int index = book.getWorksheets().get(“PivotTable”).getCharts().add(ChartType.COLUMN,5,0,15,5);
//Get the instance of newly inserted chart
Chart chart = book.getWorksheets().get(“PivotTable”).getCharts().get(index);
//Set the chart series using the MaxDataRow and MaxDataColumn
chart.getNSeries().add(“PivotTable!B5:” + CellsHelper.columnIndexToName(maxCol) + (maxRow + 1), true);
//Save workbook
book.save(myDir + “out.xls”);

Please let me know if I can be of further assistance to you.
13

Hi Babar,


I already tried this but for some reasons always it return the Row and Column count as 1 respectively.

Not sure if we have any other options. Did you try in your system ?

Thanks for the help
14

Hi Rishi,


I first tried the code snippet on my side then provided it you for testing. Although I didn’t mention that I am using the latest version of Aspose.Cells for Java 8.0.2 for evaluation of this scenario. Could you please give the latest version a try on your end?

Please find attachment for input and output files.

15

Hi Babar,


I guess that could be the potential issue. This is the version am using, aspose-cells-7.7.2.jar. May be i will find some time, change my JDK version (Not exactly sure if i can, the current one is based on the project requirement).

Once i udpate JDK and Aspose Jar, i will keep you posted/updated.

Thanks for the help so far

Thanks,
Rishi

16

Hi Rishi,


Thank you for writing back.

Before you convert your complete project to accommodate the latest version of Aspose.Cells for Java API, it would be more appropriate that you should first create a simple application using the latest Jars just to see if it makes any difference in your particular scenario. Once you are satisfied with the outcome, you may move forward to upgrade your complete project.