Blog

Have you used the Spreadsheet_Excel_Writer PEAR package to output data to a spreadsheet? In this data that is being exported, have you needed to format the data? What about numbers, and then formatting further to a currency format. The currency formats for this data: one for Great British Pounds (£) and one for the Euro (€). The formatting needs to happen with those symbols. So here is the journey . . .

Since this post is not about the querying of the data, that is not going to be covered, but rather just the issues with formatting the currency symbols for Euro and GBP in the Writer. And since this is not about the Spreadsheet writer ins and outs, if you would like to know more about that, please see Package Information: Spreadsheet_Excel_Writer

First off, include the PEAR library:

require_once('Spreadsheet/Excel/Writer.php');

Set up the Writer, and set the formats up.

$workbook = new Spreadsheet_Excel_Writer($this->fileSrc);
$worksheet =& $workbook->addWorksheet('Data Info');
// Set the formats for text
$formatText =& $workbook->addFormat(array('Size' => 30));
// Set the formats for two numeric values for currency
$formatUKCurr =& $workbook->addFormat(array('Size' => 12));
$formatEUROCurr =& $workbook->addFormat(array('Size' => 12));
// now we need to format the numbers
$formatUKCurr->setNumFormat('#,##0.00');
$formatEUROCurr->setNumFormat('#,##0.00');
/**
All other writer code would go here.
**/

This sets up the spreadsheet library object, added a couple of formats, and then extended it. With the numeric formats, we extended it so that the number will show up in that decimal format. What this means, is that if a number, for example 50, is retrieved from the database, then it will show up in the spreadsheet as 50.00. However, we need to put in the currency symbol in that format.

So, one would think that it would be easy enough, because all we would need to do is:

$formatUKCurr->setNumFormat('£#,##0.00');
$formatEUROCurr->setNumFormat('€#,##0.00');

Right?
Wrong. Let’s say the GBP amount is 50, and the Euro amount is 23.5. When the spreadsheet is opened in Excel, what happens to that formatting is the following:
GBP: £ 50.00
Euro: € 23.50

Yikes, there is an extra character before the pound symbol, and the Euro symbol has been warped.
1. PHP, at least not until version 6, does not support UTF-8 natively. So this means that the character set that defines these symbols is incapable of being displayed properly in the current encode that PHP understands. Thus the extra characters
2. To get more detailed info on why this character encoding is a problem, see Fun with UTF-8, PHP and MySQL. This explains a good deal about UTF-8, ASCII, and ISO 8859-1.

So the first thing I tried was to force the headers to use UTF-8. That was a no go.

The next thing was to use the ICONV.

$pound = iconv("ISO-8859-1", "UTF-8", "£");
$formatUKCurr->setNumFormat($pound . '#,##0.00');

That was a no go as well.

I changed the font family of the destination cell. That also did not work.

So I took a walk to the kitchen to clear my head. I was clearly on the wrong track. I may as well try to put everything into an array, explode it, then implode it, then echo it, then die(). So I got back from the kitchen getting some water.

The thought came to me to try using the chr() function. It could not hurt. So I looked at the PHP manual for chr() to get the ASCII code list. The code for the pound symbol according to that site, is 156, so

$formatUKCurr->setNumFormat( chr(156) . '#,##0.00' );

Well, good news and bad news. First good news, no more additional characters. Bad news, the output for the GBP was :
œ 50.00

OK, close, very close. Now I went hunting for the correct code. It is 163, so

$formatUKCurr->setNumFormat( chr(163) . '#,##0.00' );

After a couple of searches for the Euro symbol, as it is a newer symbol and not in the regular lists, I found it could be 128. So I tested it out

$formatUKCurr->setNumFormat( chr(163) . '#,##0.00' );
$formatEUROCurr->setNumFormat(  chr(128) . '#,##0.00');

And the output:
£ 50.00
€ 23.50

SUCCESS!

So, if you are struggling with this, using the Spreadsheet_Excel_Writer formatting to get the currency symbols for the GBP and the Euro, that is the fix that I have found.