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?
Answer
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\);_(* \"-\"??_);_(@_)");