Please explain Region.COuntryCode = Canada

Aspose.Cells Product Family
1

Is this english or french… where is the list of all the associated cultures per…as en-CA and fr-CA are 2 very different things… but both are canadian…hence why the confusion with just one Canada code.


I also need assitance with the application of the custom format on a server that runs aspose and is in french, but has a session state variable to consider whether the user has clicked to change the language from french to english, the format code works for both languages on my pc, but when deployed to the server, it only works in english, not in frnehc, and the server as I said is in french…

It would need to work in both modes, and I am not sure how I can affect the cell values individually when generating the excel file to show the format as such.

###,###,##0.00 $

Help is much appreicated

Thank you for all help in advance…
2

Hi,

Thanks for your posting and considering Aspose.Cells.

Aspose.Cells is known to work well with any Regional setting. Once you will change the regional setting using the Control Panel, then it will have effect both on the Microsoft Excel and Aspose.Cells.

Could you please provide us some sample code to help us look into this issue further? We will investigate this issue and update you asap.

Thanks for your cooperation.

3

Thats not acceptable for my company, we can not start changing the culture of our clients computers just because you say so. What we need is a way of controling the ouput of a formatting method so that the ###,###,##0.00 comes out properly and the output can be properly used in a formula, which is what is happening now…the french version is coming out where it can not be used in calculations, it seems to think of itself as text, where as the english is ok…

I am assigning the values as double directly with the Cell.PutValue method, then assigning the cell formatting by using the setstyle() method.

For some reason this works correctly on my pc while I am debugging my webservice that uses Aspose to generate the excel file with values, and then I open the file on my pc, both french and english work fine…

When I deploy the webservice to the iis server (production) then I call it and open the file on my local pc, the file does not assign the format properly in french. Both the server and my machine are configured with same culture inherently…however I dont know if I need a special assignment (such as Settings.Region.COuntryCode) to affect the cell format directly. The documentation is not very good on the use of these methods/properties.

btw you still have not answered about canada region setting, is it french or english? you do not specify in your documentation the outcome of applying the region codes, (then why have documentation on it if you dont say what it does or how to use it?)

Canada has 2 formats, and when I see RegionCode.Canada, it should be either en-CA or fr-CA…please advise on this, and how region code affects the format at the cell level overall…a good explanation and example goes a long way.
4

Hi,

Thanks for using Aspose.Cells.

CountryCode enumeration is defined by ms excel and we cannot make it more accurate. To specify more accurate region/language settings, please use CultureInfo instead. For countrycode Canada, we use en-CA.

For your another issue of the custom formatting cannot work on the server, we are not sure what’s the exact scenario and problem. What’s the meaning of “only works in English, not in French”? Does that mean the it always give formatted result as English language even if you changed the cultureinfo to French? Or cells gave incorrect formatted result for French language? Please describe your problem with more details so that we can make further investigation. Before that, here are some hints for formatting feature may be helpful for you:

1) User does not need to change the host’s environment settings, instead he can specify the desired region/cultureinfo for formatting at Workbook level:

Workbook.Settings.Region=… or Workbook.Settings.CultureInfo=…

2) When specifying number formatting by Style.Custom, the custom string value must be in en-US format, cells component’s formatting module will change it to proper formatting pattern and do formatting according to the specified region/cultureinfo of the workbook.

3) To use the host’s environment settings for formatting, user can just set the region to Default or set the cultureinfo to CultureInfo.Default

5

Workbook.Settings.CultureInfo=


Great this I think is what I will need…

Here is the scenario…

I am using the vs project with aspose to generate the excel file with values as doubles in the cells. I apply the format (which is as you say en-US or ###,###,##0.00 $. This allows me to call the method within a session that can either be en-CA or fr-CA and push out the finished cell values correctly, but that can still be used inside the formulas (because they are not strings). When I run the code on my local machine (in debug) and I have my machine culture region settings as en-CA, the format works correctly in both languages (that is by selecting the english or frnehc hyperlink that changes the websites session culture).

When I deploy the exact same code, and call the production website from my local machine to generate the file, the same code now generates a proper english version, but the french version gets turned out a text in cell values…so they cant be used inside formulas.

This leads me to believe the machine with aspose generates a different version of a worksheet on the server side, vs. my local debug of the project, even though both machines (server and mine) have the same base region setting (fr-CA).

Mentioning the CultureInfo property makes sense, but what does not help is why there is so little documentation on this very important and very common issue, there should be atleast 10 different examples of how to control culture (from a winforms, from a website, from a service being called… etc) and using all differing methods you mentioned…that way people like me dont have to disturb people like you, :slight_smile:

I will get back to you with my result of using CultureInfo… althoujgh you did not really go into detail of what I should expect when I would call this method and how to use it (in detail), you just lightly mentioned it…so I guess I am expecting that I will be controlling the output (based on the format which should just be en-US or ###,###,##0.00 $) from being affected by the session culture or the local culture of the machine calling the application and only using the worksheets culture.

Am I correct atleast in thinking this?


ps - when you say the countrycodes are microsoft… i have the link for their codes

https://msdn.microsoft.com/en-us/goglobal/bb964664.aspx



6

Hi,

For using WorkbookSettings.Region/CultureInfo, what you need to do is just specifying the correct value for one of these properties, then Aspose.Cells component will use the specified regional settings instead of the local culture of the machine to do the formatting. If you have not specified the regional settings, then the local culture of the machine will take effect for formatting values.

We are still not very clear for your scenario, do you apply the format by setting style(Style.Custom) for cells or use formula such as =TEXT(value,format_text)? Above rules take effect for both cases. However, Style.Custom requires standard format pattern string(same with en-US region), TEXT formula requires regional specified format pattern string. Those different requirements are also same with what MS Excel saved in the Excel file. If you still get trouble with the regional settings for your application on server, please provide us your sample code and template file to show the problem so we can look into it.

For the url you provided, in fact it enumerates LCIDs which is corresponding to all CultureInfos(CultureInfo.LCID) and different from the enumeration of CountryCode which is defined in excel file format’s specification. As you have known now, CultureInfo should be more flexible and suitable for you than CountryCode.

Thank you.

7
The user opens the Excel file with all the values that should be formatted, and then manually adds a formula at xxx cell position to do some calculations....this is a manual edit of the Excel file after we have generated it....

On the production server, with English as the users local choice for culture for the website viewing, the format all is ok and allows for the formula to work as expected, but in french, it does not (as the cells are seen as text when you look inside the excel file, even though I added the values as double when generating)

I have used your idea of the Settings.CultureInfo = new CultureInfo("en-FR") etc.

This had no difference on the outcome... I am really perplexed!.

Here is my code as is...
//new part added
if (objCurrentReportDetail.language == "F")
objWorkSheet.Workbook.Settings.CultureInfo = new CultureInfo.CreateSpecificCulture("fr-CA");
else
objWorkSheet.Workbook.Settings.CultureInfo = new CultureInfo.CreateSpecificCulture("en-CA");


Style stlz = objWorkSheet.Cells[intRow, i].GetStyle();
stlz.CultureCustom = "###,###,##0.00 $";
objWorkSheet.Cells[intRow, i].SetStyle(stlz);
double val = 4.00;
objWorkSheet.Cells[intRow, i].PutValue(val);

This comes out OK on my local pc where I am running the project in debug mode, and can see the format works as described.

On the production server, which I can not access to debug, and can only confirm by the website itself, I do not get the desired output, yes when viewing in english it works, but when viewing in french it comes out unformatted and with a small green triangle in each cell that says the popup that this cell is text.

All I can confirm is that the server is in french Canada (control panel) and my local pc is french Canada region culture also, so I am not sure what Aspose uses to format the Cell value, if I use the code above I am expecting no matter what the setting locally, I would see it as formatted.



8

Hi,

Thanks for your posting and using Aspose.Cells.

In your code you are using Style.CultureCustom instead of Style.Custom property. As the property name denotes, CultureCustom is culture-dependent. So your code should be changed to:

style.Custom = “###,###,##0.00 $”; //for all regions

Or

style.CultureCustom = “### ### ##0,00 $”; //only for some regions such as fr-CA