Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsFree MagazinesWhite PapersSubmit Content
Discussion GroupsASP.NETWindows FormsLanguages.NET FrameworkVisual Studio.NET
Articles.NET FrameworkASP.NETToolsWindows Forms
.NET DirectoryOpen Source ProjectsUser GroupsWeb Resources
Related Topics
Visual Basic 6SQL ServerMS AccessOther DB ProductsMS Server ProductsMore Topics ...

.NET Forum / .NET Framework / Internationalization / November 2005

Tip: Looking for answers? Try searching our database.

Net Currency formatting not understood in Excel

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Henrik - 25 Oct 2005 15:41 GMT
Hello all.

We have a problem in that when we want to display our decimal values we sometimes format them as a currency string or a number string, depending on if the number symbolizes a monetary value, or just a number.

We live and work in Sweden, and the default regional setting for Sweden are used.
Our webpages deliver regional formatting using one of thre methods.
 1.. A requested culture, specified in the querystring.
 2.. If not supplied, takes the browsers language.
 3.. If No languages available in browser, then swedish language (sv-se)
The language is the set using
System.Globalization.CultureInfo _RequestedCulture _RequestedCulture = System.Globalization.CultureInfo.CreateSpecificCulture(sCultureName);

We then get our formatted strings using for example
    DecimalValue.ToString("C2", _RequestedCulture)  
if a currency value with two decimals, or in case of a numeric value with two decimals
    DecimalValue.ToString("N2", _RequestedCulture)  

The problem is that the output value in the case of currency format is not recognized by Excel, when using swdish formatting (sv-se)

Regional Settings report the following sample formatting for the number 123456789.00:

Number:    123 456 789,00    (space as group separator, komma as decimal separator)
Currency:  123.456.789,00 kr  (dot as group separator, komma as decimal separator  and finally the trailing currency symbol)

This is the standard regional setting for Sweden.
And this is also the formatting that .Net Framework delivers.

The problem is that Excel does not understand that 123.456.789,00 kr is a number (or a currency).
It does however understand numberformat + " kr", that is 123 456 789,00 kr, as a valid currency

This leads me to question if
a) One perhaps should not use currency formatting, or
b) There is a problem in Excel.

It seems a bit strange to me however, that the formatting that the operating system is reporting as the standard default currency format, is not used / valid in Excel.
And I do use Office 2003 Pro with SP2 (SP 2 was released just 3 weeks or so ago).

Any thoughts, anyone?
Garrett McGowan[MSFT] - 26 Oct 2005 01:47 GMT
Hi Henrik. I've struggled with this myself. I think the root cause of this
behavior is that Excel expects all values to be passed in the English
(United States) format. Once it has the value, Excel then formats it based
on the Windows user locale setting. So doing the right thing in your
.NET-based code can result in seemingly broken formatting in Excel.

I'm afraid that your best bet might indeed be to avoid currency formatting
in Excel.

Garrett McGowan [MSFT Developer International]

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
--------------------
>From: "Henrik" <henrik_the_boss@hotmail.com>
>Subject: Net Currency formatting not understood in Excel
[quoted text clipped - 3 lines]
>Hello all.
>We have a problem in that when we want to display our decimal values we sometimes format them as a currency string or a number string, depending on
if the number symbolizes a monetary value, or just a number.
>We live and work in Sweden, and the default regional setting for Sweden are used.
>Our webpages deliver regional formatting using one of thre methods.
[quoted text clipped - 19 lines]
>b) There is a problem in Excel.
>It seems a bit strange to me however, that the formatting that the operating system is reporting as the standard default currency format, is
not used / valid in Excel.
>And I do use Office 2003 Pro with SP2 (SP 2 was released just 3 weeks or so ago).
>Any thoughts, anyone?
Henrik - 26 Oct 2005 11:24 GMT
Hello Garret, and thanks for your response.

I had pretty much come to same conclusion myself, but wanted a second opinion.
The problem is that the "correct" way of writing currency values in Sweden is not much used, which compounded the problem.

The predominant way is unfortunately (at least in this aspect) the "Microsoft Excel Way".
That is, a currency value is mostly shown as "Number Format + currency label", that is, for example 9 500 kr (instead of 9.500 kr as it should be).

But since I was unsure if this idiosyncrocy of using Number Format  to display currency is a mostly loalized Swedish behaviour or not, I thought I should at least ask around.

On pages where we explicitely write out the currency symbol, I have been formatting currency values as currencies, ie. "1.234,50 kr"

On some pages or forms, a currency symbold (kr usually) is sometimes not shown.
Either because there is a lack of space, a design decision, or just because it is self evident and thus a bit silly to explicitely write it out.
Is it then wrong to use a currency formatting if no symbol is shown?
That is, is "1.234,50" a valid currency? Or should it be written as "1 234,50" since kr is not shown?

My approach HAS been to use currency formatting even so, but now as I have begin to investigate the matter, I have found that most localized settings use the SAME group digit symbol for currencies as the one used for general numbers. So that seems to indicate that this is of rather limited use, and perhaps even wrong.

My greatest fear was that, since Excel could not understand the formatted value, it was not a valid number. But a quick test confirmed that .NET could parse back the formatted string value to a decimal value, although I had to use both NumberStyles and IFormatProvied in some cases:
   decimal SomeNumber;
   SomeNumber = decimal.Parse("123 345,00", System.Globalization.NumberStyles.Currency);
   SomeNumber = decimal.Parse("123.345,00 kr", System.Globalization.NumberStyles.Currency, _RequestedCulture);
   SomeNumber = decimal.Parse("123.345,00", System.Globalization.NumberStyles.Currency);

Any thoughts on xml formats?
Is it best to use just plain number formats here, as the below sample?

One generally DON'T want to use the requested cultures monetary symbol here.
A value and it's currency is fixed in "kr" (swedish krona, also known as SEK internationally, just as the american dollar is known as USD internationally), even though en-US has been requsted.
Of course "1.000.000,00" kr is not equal to "$ 1,000,000.00", since there is an exchange rate between the two.

<?xml version="1.0" encoding="iso-8859-1" ?>
<Instrument>
   <InstrumentID>SE0000123456</InstrumentID>
   <NumberOfStocks>22 500 000</NumberOfStocks>
   <TradingLot>1 000</TradingLot>
   <Prices>
       <AskVolume>5 000</AskVolume>
       <AskPrice>13,45</AskPrice>
       <BidVolume>12 000</BidVolume>
       <BidPrice>12,45</BidPrice>
       <LatestQuote>13,50</LatestQuote>
       <NextToLastQuote>13,20</NextToLastQuote>
       <QuoteDate>2005-10-26</QuoteDate>
       <NoOfTrades>484</NoOfTrades>
       <TotalTradedValue>7 458 334,70</TotalTradedValue>
       <TotalTradedVolume>554 450</TotalTradedVolume>
   </Prices>
   <FormatHelp>
       <Currency>SEK</Currency>
       <NumberDecimalSeparator>,</NumberDecimalSeparator>
       <NumberGroupSeparator> </NumberGroupSeparator>
       <CurrentCultureName>sv-SE</CurrentCultureName>
       <LCID>1053</LCID>
   </FormatHelp>
</Instrument>

Or should one parse the values as currencies if they ARE currencies and pass along currency format help?

<?xml version="1.0" encoding="iso-8859-1" ?>
<Instrument>
   <InstrumentID>SE0000123456</InstrumentID>
   <Ticker>TICK 1</Ticker>
   <NumberOfStocks>22 500 000</NumberOfStocks>
   <TradingLot>1 000</TradingLot>
   <Prices>
       <AskVolume>5 000</AskVolume>
       <AskPrice>13,45</AskPrice>
       <BidVolume>12 000</BidVolume>
       <BidPrice>12,45</BidPrice>
       <LatestQuote>13,50</LatestQuote>
       <NextToLastQuote>13,20</NextToLastQuote>
       <QuoteDate>2005-10-26</QuoteDate>
       <NoOfTrades>484</NoOfTrades>
       <TotalTradedValue>7.458.334,70</TotalTradedValue>
       <TotalTradedVolume>554 450</TotalTradedVolume>
   </Prices>
   <FormatHelp>
       <Currency>SEK</Currency>
       <NumberDecimalSeparator>,</NumberDecimalSeparator>
       <NumberGroupSeparator> </NumberGroupSeparator>
       <CurrencyDecimalSeparator>,</CurrencyDecimalSeparator>
       <CurrencyGroupSeparator>.</CurrencyGroupSeparator>
       <CurrentCultureName>sv-SE</CurrentCultureName>
       <LCID>1053</LCID>
   </FormatHelp>
</Instrument>

This might be a bit confusing, especially if the currency value is so low that no grouping digits are shown, and a currency symbol is NOT displayed.
How can one then know if a value is a number or a currency (without resorting to xml attributes showing the type).
If one tries to figure then out without the benefit of documentation or so, then all the values in bold below can then be either type of value, which mught be kind of confusing, eh?
 
<?xml version="1.0" encoding="iso-8859-1" ?>
<Instrument>
   <InstrumentID>SE0000123456</InstrumentID>
   <Ticker>TICK 1</Ticker>
   <NumberOfStocks>22 500 000</NumberOfStocks>
   <TradingLot>70</TradingLot>
   <Prices>
       <AskVolume>500</AskVolume>
       <AskPrice>13,45</AskPrice>
       <BidVolume>700</BidVolume>
       <BidPrice>12,45</BidPrice>
       <LatestQuote>13,50</LatestQuote>
       <NextToLastQuote>13,20</NextToLastQuote>
       <QuoteDate>2005-10-26</QuoteDate>
       <NoOfTrades>484</NoOfTrades>
       <TotalTradedValue>970</TotalTradedValue>
       <TotalTradedVolume>70</TotalTradedVolume>
   </Prices>
   <FormatHelp>
       <Currency>SEK</Currency>
       <NumberDecimalSeparator>,</NumberDecimalSeparator>
       <NumberGroupSeparator> </NumberGroupSeparator>
       <CurrencyDecimalSeparator>,</CurrencyDecimalSeparator>
       <CurrencyGroupSeparator>.</CurrencyGroupSeparator>
       <CurrentCultureName>sv-SE</CurrentCultureName>
       <LCID>1053</LCID>
   </FormatHelp>
</Instrument>

> Hi Henrik. I've struggled with this myself. I think the root cause of this
> behavior is that Excel expects all values to be passed in the English
[quoted text clipped - 57 lines]
> so ago).
>>Any thoughts, anyone?
Garrett McGowan[MSFT] - 03 Nov 2005 19:30 GMT
Thank you for the detailed information Henrik. I've started an internal
thread with the Excel team regarding the apparent formatting issue. I don't
know yet though whether this behavior is truly unexpected or if there's a
historical reason for the discrepency. I'll keep you updated.

Garrett McGowan [MSFT Developer International]

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
--------------------
>From: "Henrik" <henrik_the_boss@hotmail.com>
>Subject: Re: Net Currency formatting not understood in Excel
[quoted text clipped - 7 lines]
>That is, a currency value is mostly shown as "Number Format + currency label", that is, for example 9 500 kr (instead of 9.500 kr as it should be).
>But since I was unsure if this idiosyncrocy of using Number Format  to display currency is a mostly loalized Swedish behaviour or not, I thought I
should at least ask around.
>On pages where we explicitely write out the currency symbol, I have been formatting currency values as currencies, ie. "1.234,50 kr"
>On some pages or forms, a currency symbold (kr usually) is sometimes not shown.
>Either because there is a lack of space, a design decision, or just because it is self evident and thus a bit silly to explicitely write it
out.
>Is it then wrong to use a currency formatting if no symbol is shown?
>That is, is "1.234,50" a valid currency? Or should it be written as "1 234,50" since kr is not shown?
>My approach HAS been to use currency formatting even so, but now as I have begin to investigate the matter, I have found that most localized settings
use the SAME group digit symbol for currencies as the one used for general
numbers. So that seems to indicate that this is of rather limited use, and
perhaps even wrong.
>My greatest fear was that, since Excel could not understand the formatted value, it was not a valid number. But a quick test confirmed that .NET
could parse back the formatted string value to a decimal value, although I
had to use both NumberStyles and IFormatProvied in some cases:
>    decimal SomeNumber;
>    SomeNumber = decimal.Parse("123 345,00", System.Globalization.NumberStyles.Currency);
[quoted text clipped - 4 lines]
>One generally DON'T want to use the requested cultures monetary symbol here.
>A value and it's currency is fixed in "kr" (swedish krona, also known as SEK internationally, just as the american dollar is known as USD
internationally), even though en-US has been requsted.
>Of course "1.000.000,00" kr is not equal to "$ 1,000,000.00", since there is an exchange rate between the two.
><?xml version="1.0" encoding="iso-8859-1" ?>
[quoted text clipped - 54 lines]
>How can one then know if a value is a number or a currency (without resorting to xml attributes showing the type).
>If one tries to figure then out without the benefit of documentation or so, then all the values in bold below can then be either type of value,
which mught be kind of confusing, eh?
>  
><?xml version="1.0" encoding="iso-8859-1" ?>
[quoted text clipped - 86 lines]
>> so ago).
>>>Any thoughts, anyone?

Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.