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.
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.
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.
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.