PHPExcel Accounting Formats

Question

I'm working with PHPExcel and I'm trying to format a cell using Excel's built-in "Accounting" format. I'm aware of the Format Code:

PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE

But this simply formats to two decimal places and adds a $ in front of the number. The result I'm looking for is the right aligned cell with the $ on the left. $0 values should be listed as "-" and negative values should be $ (1.11)

As far as I can tell there are no other currency formats in the documentation (though I may have missed it, this documentation is horrendous). Am I looking in the wrong place? Can this be achieved with regular cell formatting or is Excel doing something unique for Accounting?

score 45 · Accepted Answer · edited Feb 21 '20 at 14:47

45

I reverse engineered the format code from an existing spreadsheet using PHPExcel and got this:

_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)

This is the code Excel places on the cell when you select the "Accounting" format... or click that "$" toolbar button in Excel 2007.

If you didn't need currency symbol :

->setFormatCode("_(* #,##0.00_);_(* \(#,##0.00\);_(* \"-\"??_);_(@_)");

edited Feb 21 '20 at 14:47

Budi Mulyo

384
5
22

answered Jun 08 '12 at 20:58

bobwienholt

17,420
3
40
48

This helped me a lot! Thanks! – Tomatrox Nov 04 '15 at 20:33
3

how do you implement this? – relipse Dec 13 '16 at 14:58
@relipse - `$objPHPExcel->getActiveSheet()->getStyle("A1")->getNumberFormat()->setFormatCode("_(\"$\"* #,##0.00_);_(\"$\"* $#,##0.00$;_(\"$\"* \"-\"??_);_(@_)");` – user8735467239 Jun 25 '17 at 06:41
Could we get a breakdown of the expression and what each character does/means? – Matthew Goheen Apr 15 '19 at 18:49
1

idk bout dat @MatthewGoheen, but i need without symbol and other answer still buggy for me, so i updated the answer like this `->getNumberFormat()->setFormatCode("_(* #,##0.00_);_(* $#,##0.00$;_(* \"-\"??_);_(@_)");` – Budi Mulyo Feb 21 '20 at 09:09

score 7 · Answer 2 · answered Jul 18 '14 at 13:42

7

$objPHPExcel->getActiveSheet()->getStyle('C1')->getNumberFormat()->setFormatCode("#,##0.00");

or use

$objPHPExcel->getActiveSheet()->getStyle('C1')->getNumberFormat()->setFormatCode("#.##0,00");

Set in getStyle CELL.

answered Jul 18 '14 at 13:42

Rogerio de Moraes

1,527
18
15

score 3 · Answer 3 · answered Apr 15 '11 at 06:43

3

There are no other pre-defined formats beyond those listed in PHPExcel_Style_NumberFormat, but you should be able to set the format code to any string that you could use when setting an MS Excel custom format...

e.g.

[green]$#,##0.00;[red]$(-#,##0.00)

As regards cell alignment, set this to right yourself, or don't set it at all.

answered Apr 15 '11 at 06:43

Mark Baker

209,507
32
346
385

I understand I can set the cell alignment and do conditional formatting on my end, but when MS Excel uses an 'accounting' format it aligns the currency sign ($, £, etc.) on the left and the rest of the cell on the right and handles the rest automagically. Is this not a format that can be set with PHPExcel? – Matt Brunmeier Apr 15 '11 at 16:42
@epalla Standard MS Excel format mask would be "_-£* #,##0.00_-;-£* #,##0.00_-;_-£* "-"??_-;_-@_-" (using £ as the currency symbol) or something like "_-[$€-2] * #,##0.00_-;-[$€-2] * #,##0.00_-;_-[$€-2] * "-"??_-;_-@_-" (using a €)... these are just standard MS Excel formats, which will work perfectly with the Excel5 and Excel2007 writers... though they won't look as good if you use CSV or HTML or PDF writers – Mark Baker Apr 15 '11 at 18:22

PHPExcel Accounting Formats

3 Answers3

Linked