Free Support Forum - aspose.com

Issue with assigning value to a percentage formatted cell Programmatically

Hi,

We are using Aspose for one of our reporting application. I am facing some issues while assigning value to a Cell which is formatted to have percentage value.

Please find the sample Excel and Java Program simulating the issue for your reference. I will put up my scenario based on the "PercentageTester.xls" file attached in the mail.

Scenario

1. Column D & E are formatted to have percentage value upto 4 decimal points.

------ Now lets capitalize on the cell E4 on the sheet named test -------

2.a. I manually go and change the value of the cell E4 to 5. The cell displays the value as 5.0000%.

2.b. I programmatically** update the value of the of the cell E4 to 5. The cell*** displays the cell value as 500.0000%.

Question

I think this might be an issue with Aspose. If I am not wrong then the right way is the way Ms Excel is behaving and in-fact thats what our requirement is.

Please guide me through. Let me know if you need more clarification.

Regards,
Malay

Markers

** - The java programmed is supplied in the attachment.
*** - The output file is supplied in the attachment.

Hi,

I have tested your template files and code.

Well, since you have already applied formattings i.e. currency "0.0000%" to C4:E23 cells in your template file "PercentageTester.xls". But you do have formula into E4:E23 cells. e.g..," =IF(C4=0,0,C4/totalrange*100)/100" etc. So if you delete the formula first and put 5 into E4 cell it will be 500.0000% based on the formatting set on the cell as Aspose.Cells calculates it fine.

To authenticate and verify what I am saying in MS Excel manually, kindly select E4 cell, Press F2, now remove the formula (selecting by mouse or so.) i.e."=IF(C4=0,0,C4/totalrange*100)/100" from the cell. Now, enter 5 value and then press enter. You will see 500.0000% result same as Aspose.Cells's result.

Thank you.

Hi Amjad,

Thanks a lot for that quick reply.

I could simulate your suggestion and you are absolutely right. I manually deleted the formula first and then entered value 5 in E4, as rightly pointed by you, the value changed to 500.0000%.

Now, I think we can say that the MS Excel behaves in 2 different ways based on the action. They are:

1. The focus was on E4 and a value 5 was entered without focussing the content. (i.e. pressing F2 key and taking the cursor into the cell.)

2. The focus was on E4 and a value 5 was entered after focusing the content. (Pressing F2 key and manually deleting the existing value (i.e. a formula in this case) first and then entering the new value.)

I am now failing to understand the difference between the two. Because, in either case, the formatting is preserved.

But, I am now even more curious to know whether we can replicate the scenario 1 from MS Excel using any API in aspose.Cell class. I assume there might be some way in Aspose too.

Please suggest.

Regards,
Malay

Hi Amjad,

Any update on this issue will be highly appreciated. We are kind of stuck at this place now.

Regards,
Malay

Hi,

I think as a workaround you may try to divide the number by 100 while puting the values into those cells. You may modify your code like:

Cells cells = workbook.getWorksheets().getSheet("test").getCells();
Cell cell = cells.getCell("E4");
System.out.println(cell.getStringValue());
double v = 5 / 100f;
cell.setValue(v);

Thank you.

Hi,

Thanks for considering Aspose.

Please try the attached version (1.9.4.5).

This version provides two new methods:

Style.isPercent() and Cell.isPercentStyle() .....used to check whether a cell is set to percentage style. So you may check the style of a cell if not sure and then set proper value for the cell.

Sample code:

Workbook workbook = new Workbook();
workbook.open("E:\\Files\\MyBook.xls");
Cells cells = workbook.getWorksheets().getSheet(0).getCells();
Cell cell = cells.getCell(0,0);
System.out.println(cell.isPercentStyle());
Style style = cell.getStyle();
System.out.println(style.isPercent());

Thank you.