PHPExcel gets cell currency format other than $ and euro

Question

PHPExcel uses only $ and EUR:

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

I need to get other currencies (I use "zł" for Polish zloty). All currencies exceed $ and EUR, designated as "General".

The next question for SWilk's answer is: how to use a custom format. I introduced 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]_-;_-@_-'; 

this is normal? What else should I do to read formats using

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

Answer 1

Default constants are defined only for dollars and euros, but PHPExcel does not limit you to just these constant constants. They are just strings; and you can set any valid MS Excel format code as a numberFormat mask by simply setting it to the corresponding string value. It is generally not recommended to add your own constants to numberFormat.php, although you need to remember to add them each time you upgrade to a new version of PHPExcel ... it is better to define additional constants in your own code.

You can apply the currency format to cells using:

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

Or, if you define a new constant 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 saved a simple excel file with one cell formatted as a currency cell nominated in PLN. Unzip the .xlsx and check the format. It:

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

Try using this format or write your own based on this.

Answer 3

for pound sterling phpExcel

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

I found the correct value by unpacking xlsx with a cell formatted as £ currency (or a cell with £ entered into it: it seemed that this was the same formatting)