Applying NumberFormat via Shared Style Not Working

Topics: User Forum
Jul 9, 2011 at 2:19 PM
Edited Jul 9, 2011 at 2:21 PM

I'm having the hardest time getting number styles to apply through a shared style.  Seems that no matter what I try, the number formatting simply doesn't come through.

The sample code below demonstrates my futility.  I try type casting to see if that's the culprit (it's not), I try different syntax variants in the shared styles (it's not)...

I simply can't get this to work.  The only way to do it seems to be applying the style directly to the sell which rather defeats the purpose of applying a shared style.

Can anyone shed some light?  Searches for bugs or threads on this topic have come up empty.  Many thanks in advance. 


/** PHPExcel */
require_once '../Classes/PHPExcel.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
 ->setLastModifiedBy("Maarten Balliauw")
 ->setTitle("Office 2007 XLSX Test Document")
 ->setSubject("Office 2007 XLSX Test Document")
 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
 ->setKeywords("office 2007 openxml php")
 ->setCategory("Test result file");

// Add some data

$sharedStyle1 = new PHPExcel_Style();
$sharedStyle2 = new PHPExcel_Style();
$sharedStyle3 = new PHPExcel_Style();
$sharedStyle4 = new PHPExcel_Style();

	array('numberFormat'   =>   array(
		'code'    => '#,##0.00;[Red](#,##0.00)'

	array('numberFormat'   =>   array(
		'formatCode'    => '#,##0.00;[Red](#,##0.00)'

	array('numberFormat'   =>   array(
		'format'    => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD

	array('numberFormat'   =>   array(
		'format'    => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD

$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A1:A4");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle2, "B1:B4");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle3, "C1:C4");
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle4, "D1:D4");

// Attempts at type casting to see if it's the culprit.  It's not.
	->setCellValue('A1', (double) -1.23)
	->setCellValue('A2', (double) -1.23)
	->setCellValue('A3', (double) -1.23)
	->setCellValue('A4', (double) -1.23)
	->setCellValue('B1', (float) -2)
	->setCellValue('B2', (float) -2)
	->setCellValue('B3', (float) -2)
	->setCellValue('B4', (float) -2)
	->setCellValue('C1', (real) -5)
	->setCellValue('C2', (real) -5)
	->setCellValue('C3', (real) -5)
	->setCellValue('C4', (real) -5)
	->setCellValue('D1', '-8.1')
	->setCellValue('D2', '-8.1')
	->setCellValue('D3', '-8.1')
	->setCellValue('D4', '-8.1');

$objPHPExcel->getActiveSheet()->setCellValue('F1', '-34.7');
$objPHPExcel->getActiveSheet()->setCellValue('F2', -34.7);

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="numbersample.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');