PHPExcel get cell currency format other than $ and EUR

Question

PHPExcel uses $ and EUR only:

const FORMAT_CURRENCY_USD_SIMPLE        = '"$"#,##0.00_-';
const FORMAT_CURRENCY_USD            = '$#,##0_-';
const FORMAT_CURRENCY_EUR_SIMPLE        = '[$EUR ]#,##0.00_-';

What I need is to get other currencies (I use "zł" for polish zloty). All currencies excep $ and EUR are marked as 'General'.

Follow-up question to SWilk's answer: How do I use custom format. I put new constants in NumberFormat.php:

const FORMAT_CURRENCY_PLN_1 = '_-* #,##0.00\ [$zł-415]_-';
const FORMAT_CURRENCY_PLN_2 = '\-* #,##0.00\ [$zł-415]_-';
const FORMAT_CURRENCY_PLN_3 = '_-* "-&quot';
const FORMAT_CURRENCY_PLN_4 = '??\ [$zł-415]_-;_-@_-';

is it ok? What else I have to do to read formats using

$objPHPExcel->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode();

Answer 1

Default constants are defined for Dollars and Euros only, but PHPExcel doesn't limit you to those constant constant values only. They're simply strings; and you can set any valid MS Excel format code as the numberFormat mask simply by setting it to the appropriate string value. It generally isn't a good idea to add your own new constants to numberFormat.php though, as you'll need to remember to add them whenever you upgrade to a new version of PHPExcel... it's better to define additional constants in your own code.

You can apply a currency format to cells using:

$objPHPExcel->getActiveSheet()
    ->getStyle('E4:E13')
    ->getNumberFormat()
    ->setFormatCode(
        '_-* #,##0.00\ [$zł-415]_-'
    );

Or, if you define a new constant of your own using

define('FORMAT_CURRENCY_PLN_1', '_-* #,##0.00\ [$zł-415]_-');

then you can apply it to your cells using

$objPHPExcel->getActiveSheet()
    ->getStyle('E4:E13')
    ->getNumberFormat()
    ->setFormatCode(
        FORMAT_CURRENCY_PLN_1
    );

Answer 2

I have saved a simple excel file with one cell formatted as currency cell denominated in PLN. Unzipped the .xlsx and checked the format. It is:

_-* #,##0.00\ [$zł-415]_-;\-* #,##0.00\ [$zł-415]_-;_-* "-"??\ [$zł-415]_-;_-@_-

Try using this format or writing your own based on this one.