How to apply culture to numeric cell value using Aspose.Cells

Aspose.Cells Product Family
1

Hi Team,

Please suggest how to apply culture to cell value. I am creating report for the multiple countries, in my case country will be decided dynamically.

For example I am generating report the France, so in this case I want to apply the French culture to the cell. I want to display currency and percentage value with their symbol up to 2 decimal places. In case of French currency symbol is being shown at last i.e. 100,00 € or percentage value will shown as 100,00 %.

So please suggest how to apply formatting to cell based upon the culture.

Thanks
Kapil

2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We are pleased to let you know that Aspose.Cells support the Regional Settings, please use the Workbook.Settings.Region property for your needs.

Please check the following code. In this, we have illustrated you how to use Aspose.Cells APIs and put cell values - Percentage and Currency - cell values in French Settings.

Then, it saves the workbook both in Xlsx and Pdf format.

Please check the following code. I have attached output pdf and screenshot for your reference.

C#


Workbook workbook = new Workbook();


workbook.Settings.Region = CountryCode.France;


Worksheet worksheet = workbook.Worksheets[0];


Cell cell = worksheet.Cells[“A1”];

cell.PutValue(123);


Style style = cell.GetStyle();

style.Number = 10;

cell.SetStyle(style);



cell = worksheet.Cells[“A2”];

cell.PutValue(123);


style = cell.GetStyle();

style.Number = 44;

cell.SetStyle(style);


workbook.Save(“output.pdf”, SaveFormat.Pdf);

workbook.Save(“output.xlsx”, SaveFormat.Xlsx);

Screenshot:
3

Hi Team,

Thanks for reply.

I used the code as suggested by you but it’s not working. I have set the Country code as France, but in when I generated the report its shows $ symbol. Even file attached by you in post shows the $ symbol. In case of France it should show the € symbol and in France culture Comma(,) is instead of decimal(.) i.e.1.00% in France will be written as 1,00%. I want to display same value in excel as shown in PDF file attached by you. Not sure how PDF attached by you is showing correct culture, but excel file is showing culture.

Also suggest how I can set the decimal places currently it is showing only up to 2 decimal places, in my case I want to show cell value up to 3 decimal places. One more thing CountryCode class not contains the all countries list.

Please suggest.

Thanks

Kapil

4

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

If you will save the Workbook object into Pdf, then Aspose.Cells will create all these Percentage and Currency - French Settings for you.

However, for Xlsx, you will have to change Regional Settings to France/French from Control Panel. Once, you will change it, Ms-Excel will show you correct formatting.

You can set Date and Number Formats using Style object, please check the following article for detail reference.


If you want to change Regional Settings on Cell Level (Earlier we set it on Workbook Level), then you will have to set these properties with proper values.

  • Style.Custom
  • Style.CultureCustom

First, you should set it using Ms-Excel in some template file, then you should load that template file and observer the values in Debug Window or Watches and it will give you idea, what you need to set in these properties.

When you will set on Cell Level, you will not have to change your Control Panel settings.

5

Hi team,

Thanks for the reply. I want to apply culture at cell level, I already tried Style.custom, but this method also not showing the proper culture. Can u please give me one example using the style.custom or style.culturecustom

As I am generating report for multiple countries, so please suggest me how use style.custom or style.culturecustom.

One more question, can I change the regional settings in .net class.

Thanks Kapil

6

Hi,

Thanks for your posting and email.

We are just working on your issue. We will get back to you asap.

7

Hi,

Thanks for your posting and email.

Please check the following code. It reads the source file and observe the Style object for cell A1. The cell A1 contains a 123 number in French Currency format.

It then creates a new cell A2 and new Style object with French Currency format and insert inside it a new value 456.

I have attached a source and output files generated by this code and a screenshot illustrating everything for your reference.

Besides, you can change CultureSettings using following .NET APIs, but I think,
Ms-Excel will not change the display formats unless you change the
CultureSettings from Control Panel.

  • System.Threading.Thread.CurrentThread.CurrentCulture
  • System.Threading.Thread.CurrentThread.CurrentUICulture


C#


string filePath = “F:\Shak-Data-RW\Downloads\source.xlsx”;

Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


Cell cellA1 = worksheet.Cells[“A1”];

Style style = cellA1.GetStyle();


//Access and analyze the existing custom and culture properties in Debugging

//from your existing file for a reference

//From there you will find how to set these properties

string custom = style.Custom;

string cultureCustom = style.CultureCustom;


//Now add the value in cell with your new style object

//you can also use the existing style object.

Style newStyle = workbook.CreateStyle();

newStyle.CultureCustom = “#,##0.000\ [$€-40C]”;

newStyle.Custom = “#,##0.000\ [$€-40C]”;


//Put value in cell A2 and set its style

Cell cellA2 = worksheet.Cells[“A2”];

cellA2.PutValue(456);

cellA2.SetStyle(newStyle);


workbook.Save(filePath + “.out.xlsx”, SaveFormat.Xlsx);

Screenshot:
8

HI ,

Thanks for reply.

Above solution is working fine for the french culture. Please suggest how I can handle multiple countries cultures by usng style.Custon or style.cultureCustom in single line. The above code will work only for french, as i am generating report for multiple coutries, how can i handle multple countries culture in single line code.

Please suggest it will be great help.

Thanks

Kapil

9

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Well, I think, it is not possible. Because Ms-Excel will load numeric dates and values based on the Control Panel Settings.

However, if you want to generate PDF from it, then you can programmatically change it as discussed in the previous posts.