Free Support Forum - aspose.com

%of-%of Row-%of ParentRowTotal-%ParentTotal

Hi team,


%of, %of Row, %of ParentRowTotal, %ParentTotal not working in pivot excel output


below is the sample code to apply format

pivotField.setDataDisplayFormat(Integer.parseInt((String)EISRSCAsposeConstants.fieldDisplayFMT.get(subTotType[1])));


In EISRSCAsposeConstants.java file
----------------------------------------------

public class EISRSCAsposeConstants {

public static final Hashtable fieldDisplayFMT = getFiledDisplayFMT();

private static Hashtable getFiledDisplayFMT()
{
Hashtable fieldDisplayFMT = new Hashtable();
fieldDisplayFMT.put(“xlNormal”,String.valueOf(PivotFieldDataDisplayFormat.NORMAL));
fieldDisplayFMT.put(“xlPercentOfRow”,String.valueOf(PivotFieldDataDisplayFormat.PERCENTAGE_OF_ROW));
fieldDisplayFMT.put(“xlPercentOf”,String.valueOf(PivotFieldDataDisplayFormat.PERCENTAGE_OF));
fieldDisplayFMT.put(“xlPercentOfParentRowTotal”,String.valueOf(PivotFieldDataDisplayFormat.PERCENTAGE_OF_PARENT_ROW_TOTAL));
fieldDisplayFMT.put(“xlPercentOfParentTotal”,String.valueOf(PivotFieldDataDisplayFormat.PERCENTAGE_OF_PARENT_TOTAL));
return fieldDisplayFMT;
}



i tried it in latest aspose-cells-8.8-java

can you please help me , how can i fix it.


Thanks
Anjaneyulu

Hi,

Thanks for your posting and using Aspose.Cells.

Please provide us your expected output excel file which you want to create with Aspose.Cells APIs. You can create your expected output excel file manually using Microsoft Excel and post it here for our reference. We will then look into it using Aspose.Cells APIs and provide you a sample code to achieve the same thing via Aspose.Cells APIs. Thanks for your cooperation in this regard.

Hi team,

please find the some sample attached screen shots and excel sheet for your reference.

you can also reffer below url to set % of parent total

http://www.excel-university.com/pivottable-percentage-of-parent-total/



Hi Shakeel,



It would be great if you can test and provide us the sample JAVA program with %of ParentTotal in Pivot Excel ,as i don’t have any sample Java program for Pivot Excel
generation.


Thanks in Advance.

Anjaneyulu.T

Hi,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue by executing the following sample code with the given sample excel file using the latest version:
Aspose.Cells for Java v8.8.2.7 and found that although Aspose.Cells sets “Show Values As” correctly but it does not calculate them or refresh them correctly.

I have attached the sample excel file, actual output excel file, expected output excel file and their screenshots for a reference.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-41879 - %of, %of Row, %of ParentRowTotal, %ParentTotal etc. are not working in pivot excel output

Java
//load sample excel
Workbook wb = new Workbook(dirPath + “sample.xlsx”);

//Access first pivot table in first worksheet
PivotTable pt = wb.getWorksheets().get(0).getPivotTables().get(0);

//Set % of Grand Total
pt.getDataFields().get(0).setDataDisplayFormat(PivotFieldDataDisplayFormat.PERCENTAGE_OF_TOTAL);

//Refresh and calculate data
pt.refreshData();
pt.calculateData();

//save the workbook
wb.save(dirPath + “actual-output.xlsx”);

Hi Team,

Can you please update for the above issue, we are waiting for the fix.

Thanks & regards
Anjaneyulu.

Hi,

Thanks for your posting and using Aspose.Cells.

We are afraid there is no update for you at this moment regarding this issue. However, we have logged your comment in our database against this issue and requested the product team to provide some fix or ETA for this issue. Once there is some news for you, we will update you asap by posting in this thread.

Hi,


Thanks for using Aspose.Cells.

We are afraid, we are unable to fix this issue because of its complexity and we have no plan to support it for now. However, your issue is still open, once we will have any other news for you, we will let you know asap.

@Anjaneyulu2689,
This is to inform you that we have fixed your issue (logged earlier as “CELLSJAVA-41879”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

The issues you have found earlier (filed as CELLSJAVA-41879) have been fixed in Aspose.Cells for Java v20.1. This message was posted using Bugs notification tool by ahsaniqbalsidiqui