Using a currency code to format a number

[shmax]

Hi guys, I have a PHP application that uses the PEAR Spreadsheet_Excel_Writer package, and am having trouble with currency. Each row in the spreadsheet I'm trying to construct has a field representing a price in any one of a number of currencies. You guys don't need to know anything about PHP, but there is a step in there where I can set a number format encoding, for example "$##.###".

For me it's kind of difficult, because I need to be able to express any of the currencies of the world, and if I try simply replacing the $ in the string above with something else, say "₭", I wind up with garbage symbols in my cell.

So, is there something I can do with currency codes in the number format field? For example, let's say I want to format a custom currency field to use yen. I'd like to be able to do something like "##.#### JPY", and wind up with a cell that looks like "¥7500". I know that Excel knows how to handle currencies properly, putting the symbol on the right or left as needed, that sort of thing--I'd love to be able to take advantage of it.

Do you understand what I mean? Any ideas?

Thanks much!

Max

 

[lenze]

Hi and welcome to the board!!
Sorry, I don't have time to address this now, but have a look at custom style. i've posted on this before. Do a advanved search for "styles" with my name as Author. Maybe it will turn uo something, will check back latter
Good luck
lenze

 

[XLXRider]

I occasionally need ¥, ¢, or £... so here's what I do.
Format, currency, and for the ¥ symbol, for instance, I just replace the $ character in $##,### with the following keystrokes: alt0165. Hold the Alt key and then 0165 and you'll get the ¥. I've no occasion to use other currency types, so these are the only three that come readily to mind. I'm sure a little research would enlighten you, I think these are unicode for the symbols, like the degree sign where alt0176 yields °.

Oh, the ¢ is alt0162 and the £ is alt0163... and € is alt0128.

HTH,
XLXRider

 

[shmax]

Hi guys, thanks for the replies. There's no keystrokes involved here--the spreadsheet I'm creating is machine-generated. I do indeed have the unicode values for every known currency symbol, but the trick is getting those values from my PHP header (utf-8 encoded), through the PEAR Spreadsheet_Excel_Writer class, and ultimately into an automatically-generated file. Somewhere in there there's a bug--there are many little steps you have to get right when dealing with Unicode, and obviously I'm missing something. I suspect that the bug is in the PEAR class, which is only sparsely maintained. So, I thought that there might be something I could do with those Currency codes.

I've been playing around in Excel with the "custom" formatting text input line. I've discovered that if you pick one of the exotic currencies from the standard "currencies" list, you can then go back and choose "custom", and see what that currency format looks like. But they don't seem to do anything with the currency code--the actual utf-8 symbol is simply typed into the format string. I guess I've got no choice but to roll up my sleeves and see if I can figure where the bug is in the utf-8 pipeline...

Max

I occasionally need ¥, ¢, or £... so here's what I do.
Format, currency, and for the ¥ symbol, for instance, I just replace the $ character in $##,### with the following keystrokes: alt0165. Hold the Alt key and then 0165 and you'll get the ¥. I've no occasion to use other currency types, so these are the only three that come readily to mind. I'm sure a little research would enlighten you, I think these are unicode for the symbols, like the degree sign where alt0176 yields °.

Oh, the ¢ is alt0162 and the £ is alt0163... and € is alt0128.

HTH,
XLXRider

 

[lenze]

Is there a field in your SS that indicates the country for that record??
lenze

 

[shmax]

I could certainly add one. Would that help things?

Is there a field in your SS that indicates the country for that record??
lenze

 

[lenze]

I could certainly add one. Would that help things?

Yep, if you want to use code!!! First, create a custom style for each currency. Format>Styles. Assign a name for each style you create and use custom formatting to crreate the desired style. Say, for example, you have styles USA,ENG,JPN,etc. where USA is formatted "$#,##0.00", ENG is [$£-809]#,##0.00.",etc Now use something like this

Sub FixCurr()
Dim cl As Range
For Each cl In Range("$C$2:$C" & Range("$C$65536").End(xlUp).Row)
Select Case UCase(cl)
    Case "USA": Cells(cl.Row, "D").Style = "USA"
    Case "ENG": Cells(cl.Row, "D").Style = "ENG"
    Case "JPN": Cells(cl.Row, "D").Style = "JPN"
    'etc.
End Select
Next cl
End Sub

wher Column "C" has your country ID and your amounts are in column "D". Adjust as needed.

lenze

 

[shmax]

Hi Lenze, thanks for the idea, very creative. But I guess I should have made my situation more clear--I'm not actually touching Excel at all. I have a "Download Spreadsheet" link on my website. A user clicks on this button, a fancy PHP script executes, and the binary for an Excel spreadsheet is generated, downloaded, and saved to the user's computer. It's a competely automated process, and I can't make any assumptions about what styles the user has set up in Excel. Are styles saved out in the excel format, or are they stored in an external config file that lives somewhere in the Excel install directory? Either way, I don't think that the API I'm using to generate the Excel file provides hooks for it. I'll take a look. All I really have for formatting is the option to define a number format string, which is why I was asking about it earlier in this thread.

But if I'm understanding you correctly, each of the built-in currency formats in Excel are really just named styles, with each style being driven by a number format string? I had another look at the default styles available in Excel's currency list, and there are indeed listings for a variety of 3-letter currency codes (ie. USD, CAD, etc), but those styles simply USE the currency code instead of the currency symbol.

My brain just kicked in as I was writing this, and it occurred to me that I was on the right track a paragraph ago. I copied one of the format strings for an exotic currency, then grepped the Microsoft Office folder for it. No luck, unfortunately. I was hoping to find a text file with all of the format strings conveniently typed into it, perhaps with currency code comments. If I had that I could just parse it, extract the format strings, store 'em in my own db, and then use them when it comes time to generate the file. Any idea which config file might have those strings? Or is it *gulp* compiled into the binary?

Or am I making this more complicated than it needs to be?

Max

Yep, if you want to use code!!! First, create a custom style for each currency. Format>Styles. Assign a name for each style you create and use custom formatting to crreate the desired style. Say, for example, you have styles USA,ENG,JPN,etc. where USA is formatted "$#,##0.00", ENG is [$£-809]#,##0.00.",etc Now use something like this

Sub FixCurr()
Dim cl As Range
For Each cl In Range("$C$2:$C" & Range("$C$65536").End(xlUp).Row)
Select Case UCase(cl)
    Case "USA": Cells(cl.Row, "D").Style = "USA"
    Case "ENG": Cells(cl.Row, "D").Style = "ENG"
    Case "JPN": Cells(cl.Row, "D").Style = "JPN"
    'etc.
End Select
Next cl
End Sub

wher Column "C" has your country ID and your amounts are in column "D". Adjust as needed.

lenze