Custom Number format in Pivot

Aspose.Cells Product Family
1

Hi Team,

In Pivot table we useing setnumber format for replace Rank id’s with rank Values.

this is my code ::

if(dataValues.get(i) == 12){

int indexFormat = pivotTable.addFieldToArea(PivotFieldType.DATA, dataValues.get(i));//getting data from dynamic excel
pivotTable.getDataFields().get(indexFormat).setFunction(opearatorFunction(“max”));
pivotTable.getDataFields().get(indexFormat).setNumberFormat("[=1]1-3;[=2]4-7;8+;"); //;[=184]8-10;[=185]11+;replaceing rankIds with rank Value
pivotTable.setSubtotalHiddenPageItems(true);

}


Here Problems is::

we able to set and replace only 3 rank only. not supporting more then 3 ranks in setnumber function, And we unable to use more then one setnumber format also.

But we have totally SIX ranks , 6 need to set as per ID.

This functionality working in manually excel pivot. How to get in code for support and set 6 different ranks with rank value.

Please find attached document for formula using manually pivot table.

Thank you.




2

Hi,


Thanks for the template files and screenshots.

As we told you in other threads, MS Excel does not support more than 3 ranks while applying numbers formattings. Aspose.Cells APIs follows MS Excel’s guidelines and specifications in its processes therefore it cannot support more than 3 ranks as well. If you could check the formatting string for the Rank column in “Manually+formulla+Piovt+table.xlsx” file, it is shown as: [=1]“1-3”;[=2]“4-7”;“8+”; which is also 3 ranks in total. When I tried to add more ranks manually in MS Excel while changing the numbers formatting string, MS Excel does not allow me to do that. So, Aspose.Cells can only mimic MS Excel’s behavior.

Thank you.
3

Hi ,

I agree with you. setNumberformat not support more then 3 ranks and we can use only one setNumberformat function only.

As per our previous discussion you told me if we create manually pivot excel as per requirement based on that you can provide solution . if you notice attached screenshot , Our BA team create pivot excel for all rank by using multiple formula rank .so, My concern is I also need to use all formulas as per screen shot else any alternate solution for support to all ranks format .So, Please let me know if they have any solution .

we created in manually pivot through conditional format. If they have any option like conditional format please let us know.


Thank you.


4

Hi,


Well, your screenshot (Custom Number format in Pivot) does not match with your file “Manually+formulla+Piovt+table.xlsx”. If you could check the formatting string for the Rank column in “Manually+formulla+Piovt+table.xlsx” file, it is shown as: [=1]“1-3”;[=2]“4-7”;“8+”; which is also 3 ranks in total. There is no conditionally formatting applied in the cells either. See the screenshot for your reference:
http://prntscr.com/ehf13u

Please provide your expected file in which you could manually set more than 3 ranks as numbers formatting in PivotTable report, we will check it soon.

Thank you.

5

Hi ,

Please find attached conditional format pivot excel and screen shot for your reference.

Thank you.

6
Hi,

I think you did not check my previous reply and again attached the same Excel file with same screenshot. I will paste my reply again here for your reference:

,
Well, your screenshot (https://forum.aspose.com/t/19551) does not match with your file "Manually+formulla+Piovt+table.xlsx". If you could check the formatting string for the Rank column in "Manually+formulla+Piovt+table.xlsx" file, it is shown as: [=1]"1-3";[=2]"4-7";"8+"; which is also 3 ranks in total. There is no conditionally formatting applied in the cells either. See the screenshot for your reference:

http://prntscr.com/ehf13u


Please provide your expected file in which you could manually set more than 3 ranks as numbers formatting in PivotTable report, we will check it soon.

The screenshot should match with your attached file and also it should demonstrate your requirements a bit, so we could understand you correctly and help you through.

Thank you.
7

HI Team,

I think something issue in previous pivot file , Please find attached pivot for reference as per screenshot.

Thank you.
8

Hi,


Thanks for the template file.

Yes, this file matches to your shared screenshot (Custom Number format in Pivot) but if you could check the formatting string for the Rank column, it (formatting string) is shown as: [=1]“1-3”;[=2]“4-7”;“8+”; which is 3 ranks in total, so this file does not exhibit more than 3 ranks as numbers formatting either. You may use setNumberformat to apply the formatting string without any problem. Do you find any issue while applying this formatting? If true, kindly provide more details, sample code and screenshot, so we could reproduce the issue on our end.

Thank you.

9

Hi,

I tried in different cases like below code.
My code for Dynamic rank column with setnumber format function ::

if(dataValues.get(i) == 12){//Rank
///pivotTable.addFieldToArea(PivotFieldType.DATA, dataValues.get(i));
int indexFormat = pivotTable.addFieldToArea(PivotFieldType.DATA, dataValues.get(i));
pivotTable.getDataFields().get(indexFormat).setFunction(opearatorFunction(“max”));
pivotTable.getDataFields().get(indexFormat).setNumberFormat("[=1]1-3;[=2]4-7;8+;"); //;[=184]8-10;[=185]11+;
// pivotTable.getDataFields().get(indexFormat).setNumberFormat("[=1]1-3;[=2]4-7;[=3]8+;[=4]NR;[=5]NA;[=3]8+;[=2]4-7;[=1]1-3"); //;[=184]8-10;[=185]11+;
/* pivotTable.getDataFields().get(indexFormat).setNumberFormat("[=5]NA");
pivotTable.getDataFields().get(indexFormat).setNumberFormat("[=4]NR");
pivotTable.getDataFields().get(indexFormat).setNumberFormat("[=3]8+");
pivotTable.getDataFields().get(indexFormat).setNumberFormat("[=2]4-7");
pivotTable.getDataFields().get(indexFormat).setNumberFormat("[=1]1-3"); */
}

Its not working, its showing IDs as it is with out format if i use more then 3 ranks in setnumber format else use more then one setnumber format function.

In previous attached manually pivot they used conditional format for support all ranks.

Please provide any solution for support all ranks as per manual pivot .


Thank you.


10

Hi,


I have used your file “All+Ranks+as+per+SH.xlsx” and apply the custom number formatting (as per your desired file’s applied formatting (in the screenshot), i.e., “[=1]“1-3”;[=2]“4-7”;“8+”;”) to the Rank pivot data field and it works fine, see the sample code for your reference:
e.g
Sample code:

Workbook workbook = new Workbook(“All+Ranks+as+per+SH.xlsx”);
Worksheet worksheet = workbook.getWorksheets().get(1);
PivotTable pivot = worksheet.getPivotTables().get(0);
pivot.getDataFields().get(3).setNumberFormat("[=1]“1-3”;[=2]“4-7”;“8+”;");
workbook.save(“out1All+Ranks+as+per+SH1.xlsx”);

The output file is also attached.
For conditional formatting in PivotTable report, please try using PivotTable.getPivotFormatConditions to get and PivotFormatConditionCollection, so you may use FormatCondition to add conditions accordingly.

Thank you.
11

Hi,

I check attached pivot sheet, it also supporting up to 3 rank only, But my requirement to replace many ranks (at least 6) based on that ID. while using setNumber format we not able to replace all ranks.



In above I share manually create pivot for support all ranks replaced based on ID. Can u please share code for conditional format for support all ranks else any solutions for support all ranks as per discussion.

Thank you.

12

Hi,


We have told you several times that MS Excel does not support more than 3 ranks as numbers formatting on Pivot field. So, Aspose.Cells cannot support more than 3 ranks in count.

Regarding conditional formatting to be applied on the cells, please see last part of my previous reply. I will also further evaluate it on my end.

Thank you.