Pivot table formatting into % and currency -$

[SakethDodda]

How to format Pivot Table data into percentage and Currency (Certain rows in percentage and certain rows in Currency-$).
Please provide suggestions for this issue

Here in the image rows marked red should be in % format and rest all rows should be in dollar ($) format

and here is the code snippet for this issue:

index= pivotTable.addFieldToArea(PivotFieldType.DATA,pivotElements.get(key));
pivotTable.getDataFields().get(index).setFunction(ConsolidationFunction.SUM);
//Setting DataField to text Format
pivotTable.getDataFields().get(index).setNumberFormat("$#,##0_);Red");

[Amjad_Sahi]

@SakethDodda,

Could you please zip and share your current Excel file (containing the pivot table as per the image) by Aspose.Cells API and a sample Excel file containing your desired pivot table formatted as per your custom needs. You can create/update your desired pivot table in MS Excel manually to share here. We will check and help you through.

[SakethDodda]

Hi,

Attached zip file is the same pivot table as shown in the image that was attached in the forum. And also I attached a sample excel sheet with our desired outputs. Our need is to make only two columns as percentage format as shown in the desired output zip file. Please help us in this issue.

Thanks,

Saketh.

State Reconciliation (1).zip (150 KB)

Desired Output.zip (8.7 KB)

[Amjad_Sahi]

@SakethDodda,

The pivot table is based on its source data in the sheet, so how come you could just apply different formatting for selected rows in data fields? You provided the output having static data which you have formatted using Format Cells dialog. Could you please share a sample Excel file having your desired formatted data in pivot table report (not in static data/table). We will check it soon.

[SakethDodda]

Here is the data you asked for in pivot table format with our desired outputs (Whole State apport factor and Marginal Tax rate rows should be in % format).

And can we connect through call in teams if its feasible for you, so that we can discuss in detailed without any discrepancies.

Regards!

Desired output State Reconciliation.zip (166 KB)

State Reconciliation (1).zip (150 KB)

[Amjad_Sahi]

@SakethDodda,

Thanks for providing the file containing your desired pivot table with percentage formatting for your selected rows.

See the following sample code segment with comments on how to format selected row (you first need to find the row based on your desired pivot row field label/text) for your reference. You may refer to the code segment and write your own code or add your code based on your custom needs:
e.g.
Sample code:

 
.......
        Worksheet worksheet = workbook.getWorksheets().get(0);
        PivotTable pivotTable = worksheet.getPivotTables().get(0);
        pivotTable.refreshData();
        pivotTable.calculateData();

        //Find the cell containing the row field text/label
        Cell cell = worksheet.getCells().find("State Apport. Factor",null);

        //Create the style with your desired formatting
        Style style = workbook.createStyle();
        style.setCustom("0.00%");
        style.getFont().setName("Calibri");
        style.getFont().setSize(11);

        //Get the row index
        int row = cell.getRow();
        System.out.println(row);

        //Get the cell area based on pivot table range
        CellArea area = pivotTable.getTableRange1();
        //Get the starting column index
        int start = area.StartColumn;

        //browse the relevant row upto last column in the pivot table report
        //format each cell in the row to set percentage numbers formatting
        for (int i = start; i <= area.EndColumn; i++)
        {

            pivotTable.format(row, i, style);

        }

        workbook.save("f:\\files\\out1.xlsx"); 

Hope, this helps a bit.

@SakethDodda

And can we connect through call in teams if its feasible for you, so that we can discuss in detailed without any discrepancies

We are sorry but generally, we don’t provide technical support via phone or net meetings. The best way to get help is via forums and we can assist you better via forums threads.