Facing Issue in percentage formatting

Aspose.Cells Product Family
1

Hi

I am trying to export excel where I am getting some data in decimal format and I need to convert that data in percentage format I am using below method in .Net Core,

private static void FormatPercentCell(Aspose.Cells.Cell cell, decimal percentValue)
{
cell.PutValue(percentValue / 100);
var style = cell.GetStyle();
style.Custom = “0.00%”;
cell.SetStyle(style);
}

This percentage formatting works fine for all regions except Sweden region and when there is negative value in percentage.

If my percentValue is -100 it show -1 in exported excel if percentValue is -30 it shows -.30 in exported excel for sweden region.
But for other region like china for -100 it will show -100.00 % and for -30 it will show -30.00 %

why this is only creating issue with Sweden region ? Can someone please help me here ?

I also tried

style.Number= 10;

In above code but it doesnt work.

2

@abhijeetphophaliya

Cause

The issue you’re encountering with percentage formatting in Excel for the Sweden region is likely due to the way Excel handles number formats based on regional settings. In some regions, negative percentages may be displayed differently, which can lead to unexpected results when exporting data.

Solution

To ensure consistent percentage formatting across different regions, including Sweden, you can implement the following adjustments:

  1. Use Built-in Number Formats: Instead of setting a custom format, use the built-in number format for percentages. This will help maintain consistency across different locales.

  2. Set the Number Format Directly: You can set the number format directly using the style.Number property to ensure it applies the correct percentage format.

Code

Here’s an updated version of your method that uses the built-in number format for percentages:

private static void FormatPercentCell(Aspose.Cells.Cell cell, decimal percentValue)
{
    cell.PutValue(percentValue / 100);
    var style = cell.GetStyle();
    style.Number = 10; // Use built-in percentage format
    cell.SetStyle(style);
}

Next Steps

  1. Test the updated method with various percentage values, including negative ones, to confirm that the formatting issue is resolved.
  2. If the problem persists, check the regional settings of your Excel application to ensure they align with your expectations.

Please implement these changes and let me know if you need further assistance!

3

@abhijeetphophaliya
Could you post a generated file and a file created by MS Excel ? We will check them soon.

4

Even after trying style.Number = 10; its not working for Sweden region.

image.png (3.3 KB)

5

image.png (3.3 KB)

6

@abhijeetphophaliya
By testing on the latest version v25.6 using the following sample code, we can obtain the correct results.

Workbook workbook = new Workbook();
workbook.Settings.LanguageCode = CountryCode.Sweden;
workbook.Settings.Region = CountryCode.Sweden;

Cells cells = workbook.Worksheets[0].Cells;
FormatPercentCell(cells["A1"], 100);
FormatPercentCell(cells["A2"], -100);
FormatPercentCell(cells["A3"], 30);
FormatPercentCell(cells["A4"], -30);

Console.WriteLine(cells["A1"].DisplayStringValue); 
Console.WriteLine(cells["A2"].DisplayStringValue);
Console.WriteLine(cells["A3"].DisplayStringValue);
Console.WriteLine(cells["A4"].DisplayStringValue);

private static void FormatPercentCell(Aspose.Cells.Cell cell, decimal percentValue)
{
    cell.PutValue(percentValue / 100);
    var style = cell.GetStyle();
    style.Number = 10; // Use built-in percentage format
    cell.SetStyle(style);
}

The output:

100,00%
-100,00%
30,00%
-30,00%

We recommend you to kindly try using our latest version: Aspose.Cells for .NET v25.6.

If you still find the issue, kindly do share your complete sample (runnable) code and template Excel file (if any) to reproduce the issue on our end, we will check it soon.

7

Also One more observation when I export excel in sweden region and try to open it I am getting

image.png (53.7 KB)

Not sure why but works fine with other region

8

@abhijeetphophaliya,

We request you to kindly post us your generated Excel file by Aspose.Cells and an Excel file (having your desired results) created by MS Excel manually. Also, give us your exact code to produce such an Excel file which prompts an error message when opening it into MS Excel. Please note, screenshots might not help much to evaluate your issue precisely so do the needful as also requested earlier. We will check your issue soon.

PS. please zip the Excel files prior attaching here.