Issue with assigning value to a percentage formatted cell Programmatically

[Malay]

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.

[Amjad_Sahi]

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.

[Malay]

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

[Malay]

Hi Amjad,

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

Regards,
Malay

[Amjad_Sahi]

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.

[Amjad_Sahi]

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.