Number styles seemingly ignored

Jun 22, 2009 at 7:55 PM

I'm passing in a style array to my custom function for adding rows; this works fine for pretty much anything style-wise I throw at it.

However, number styles seemingly are ignored by PHPExcel.  Am I doing something wrong?

Here's my style array, passed to applyFromArray():

array('numberFormat' => array('code' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD))
The only thing that shows up in the resuling XLSX file as "properly" formatted are dates, and then only because Excel seems smart enough to display them--everything else (mainly USD currency) shows up with the green triangle in the upper-left corner, warning me that the cell is formatted as "General" and not a number.

Is my syntax wrong somewhere in the style array?  Any help is appreciated.

(If more of the code is required, let me know; it's kinda long...)

Jun 23, 2009 at 10:51 PM

I think you need to use the correct case:

'numberformat' =>

instead of

'numberFormat' =>

for the array key.

Jun 24, 2009 at 12:40 PM

'numberformat' is actually what I have in the most recent code; it doesn't work that way either.

Jun 24, 2009 at 2:03 PM

In that case, it sounds like the values are not being stored as numbers. Can you show us some code where you are inserting the values?


Jun 24, 2009 at 2:08 PM

Sure.  Keep in mind that this is using my addRowToSheet function, at least for now--I may or may not switch to the fromArray() (or whatever it was) that was referenced in another thread.

$workingRowFields = array(
                        array('contents' => $position['title'] . " " . $position['posNum']),
                        array('contents' => $position['startDate'],                         'styles' => array('numberformat' => array('code' => PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2))),
                        array('contents' => '$' . $position['overallSalary'],               'styles' => array('numberformat' => array('code' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD))),
                        array('contents' => $position['percentOnThisAccount'] . "%",        'styles' => array('numberformat' => array('code' => PHPExcel_Style_NumberFormat::FORMAT_NUMBER))),
                        array('contents' => '$' . round($position['salaryOnThisAccount']),  'styles' => array('numberformat' => array('code' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD))),
                        array('contents' => '$' . round($position['accountHit']),           'styles' => array('numberformat' => array('code' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD))),
                    addRowToSheet($objPHPExcel, $workingRowFields, $currentRow);

All of the stuff in the $position[] array comes from a MySQL database, where the values are stored as follows:

startDate: date
overallSalary: float
percentOnThisAccount: int
salaryOnThisAccount: computed in PHP based on overallSalary, percentOnThisAccount, and some funky date computations (don't ask...took me a long time to figure that bit out!)

Let me know if you need anything more.

Jun 24, 2009 at 2:21 PM

It looks like you have a dollar sign that shouldn't be there.

Try to change this:

'$' . $position['overallSalary']

to this


(and similar places)


Jun 24, 2009 at 2:24 PM

Still no-go.  I'd tried that before too (and also tried removing the % sign in the percentOnThisAccount row).

The only thing that gets properly formatted (at least according to Excel, mind you) is the date.

Jun 24, 2009 at 2:36 PM

It is for sure that the %-sign and $-sign needs to be removed.

Can you try to run this:

$excel = new PHPExcel();
$sheet = $excel->getActiveSheet();

    'numberformat' => array(
        'code' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');

Work ok? :

Jun 24, 2009 at 2:45 PM

That worked...and your code gave me a clue.

I changed my 'contents' check in the addRowToSheet function FROM:

$ssObj->getActiveSheet()->setCellValueExplicit($cell, $element['contents']);

TO this:

All is well!  Thank you for your assistance.

Jun 24, 2009 at 3:18 PM

Just a note to others. Instead of this:


you may want to use this, which is slightly faster:

$sheet->getCell('A1')->setValueExplicit(123.45, PHPExcel_Cell_DataType::TYPE_NUMERIC);