Cell.putValue() does not format %

Aspose.Cells Product Family
1

Hi,
I have logged this ticket before, cannot seem to find it in the forum so am logging this again.
We are currently using Aspose cells 7.2.1.3 (upgraded from 7.0).

When using a template, if i insert a value of say 0.35 in a cell which is formatted as %, instead of getting an output of 35%, i am getting 35000.0%. This was not happening in the previous versions, there seems to be an issue with how the % value is being calculated.
I am using cell.putValue(“0.35”,True) to get this result.

Can you please look into this and either provide an alternate way to do this, or log it as a bug to be resolved.
Please let me know.

thanks.

2

Hi,


Please download and try our latest fix/version: Aspose.Cells for .NET v7.5.1.3
I have tested using the following code as per your scenario, it works fine.

Sample code:


Workbook workbook = new Workbook();
Worksheet sheet1 = workbook.Worksheets[0];
Cells cells = sheet1.Cells;
cells[“A1”].PutValue(“0.35”, true);
//Set the percent style to a cell.
Style style = workbook.Styles[workbook.Styles.Add()];
style.Custom = “0%”;
cells[“A1”].SetStyle(style);
workbook.Save(“e:\test2\outstylepercent.xlsx”);


I have also attached the output Excel file for your reference here.

If you still have the issue, kindly paste your sample code (same as mine) and attach your template Excel file, we will check your issue soon.


Thank you.

3

Amjad,
thanks for the response.

I had previously tested with this new version, but i re-downloaded 7.5.1.3 and tested again. Same problem.
So the issue is not with a value of say “0.35” but with an additional decimal place like “0.355” which should be rendered as 35.5% but comes out as 35500 %.

Can you use values like 0.355 or 0.445 and retest?

thanks

4

Hi,


I tested the value 0.355 and it works fine again. I still could not find any issue at all, it works as per MS Excel.
See the attached output file for reference.

Thank you.
5

Yes, thanks i see that it works fine in your output.

Here is the thing - i am using a template instead of setting the % style in code? does that make a difference ?
I have attached a template file, If you insert a value of ‘0.355’ in cell E33 (which is formatted for percentage), you will be able to replicate the issue.
I am also attaching my output.

thanks.

6

Hi,

Thanks for using Aspose.Cells.

In your template file the saved locale is de-DE. For this locale the default decimal separator is ‘,’ and ‘.’ is the default group separator. So when you input “0.355” it will be parsed as 355 formatted by pattern “0,000”.

To get the result you expected, you need to input “0,35”, "0,355” according with the locale settings saved in the original template file, or you can reset the workbook’s locale to another one whose default decimal separator is ‘.’ such as en-US then use values “0.35”, "0.355”… Code example:

C#


Workbook wb = new Workbook(“PrecentageIssue.xls”);

wb.Settings.Region = CountryCode.USA;

Cell cell = cells[“E33”];

cell.PutValue(“0.355”, true);

Console.WriteLine(cell.Value + “:” + cell.DisplayStringValue); //output “0.35:35.5%” as expected

7

Thanks for the clarification on the locale issue.
But that is exactly the issue i started talking about earlier. All we did was upgrade from a previous version of aspose cells. In this example (when using the earlier version of cells), we still had the same template file (saved in de-DE), we had the same value input (0.355) - the output used to be 35.5% and the output excel file still used to be saved in de-DE format (because that was the format of the original template file).

This behavior has changed inside the aspose cells code. While changing the input value/the workbook region to US will work for this set up, this will not work in dynamic scenario like before.
Please advise.

thanks.

8

The version that worked fine was 7.1.2.0 for your reference.

9

Hi,

According to many other users’ requirement, from V7.2 we have rebuilt our formatting and parser model to support to parse/format values according to user specified locale. So from this version the saved region(locale) settings in template file will take effect for inputing/outputing values. If you need to keep the original region setting when re-save the workbook, we think you can keep the original region just after the template file be loaded, then change the region to USA and do inputing/outputing, then reset the region back to the original one and re-save the workbook:


Workbook wb = new Workbook(…);

CountryCode origRegion = wb.Settings.Region;

wb.Settings.Region = CountryCode.USA;

cell.PutValue(…);

…

wb.Settings.Region = origRegion;

wb.Save(…);