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;-&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();

score 7 · Answer 1 · answered Oct 01 '13 at 19:39

7

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
    );

answered Oct 01 '13 at 19:39

Mark Baker

209,507
32
346
385

Thank you very much! But I need to getFormatCode, not set it. I have such setting in excel file and I need to put currency suffix in all cells with FORMAT_CURRENCY_PLN_1. – Marek Oct 01 '13 at 22:43
If you're reading a currency format mask that contains Złote from a file that you've loaded, then that's what you should see... unless you've loaded the file with readDataOnly set to true – Mark Baker Oct 01 '13 at 22:45
Thanks, what does the -415 stand for? Do I for some reason need to change it for other currency? Tried replacing zł with Kč (czech) and it seems to work well – Fanky Apr 27 '16 at 13:20
Found the codes like [$zł-415] it in Excel - cell format -> number – Fanky Apr 27 '16 at 13:31

score 3 · Answer 2 · answered Oct 01 '13 at 08:53

3

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]_-;_-* &quot;-&quot;??\ [$zł-415]_-;_-@_-

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

answered Oct 01 '13 at 08:53

SWilk

3,261
8
30
51

Thank you. But I don't know how to use it. I put new CONSTants in NumberFormat.php: – Marek Oct 01 '13 at 09:12

score 1 · Answer 3 · answered Dec 01 '15 at 18:36

for UK Pound (Sterling) phpExcel

 $numformat= 'Â£#,##0_-';
 $activeSheet->getStyle($cell)->getNumberFormat()->setFormatCode($numformat);
 $activeSheet->setCellValue($cell,$val4);

I found out the correct value by unzipping an xlsx with a cell formatted as currency £ (or a cell with a £ entered into to it: seemed to be the same formatting)

PHPExcel get cell currency format other than $ and EUR

3 Answers3