Problem extending AdvancedValueBinder

Topics: Developer Forum
Mar 1, 2012 at 5:39 PM

I'm trying to extend AdvancdValueBinder to include two more formats.

The first one is for currency.  It probably isn't perfect, but it works for my purposes:

// Check for currency
if (preg_match('/^\$[0-9]{1,3}(,[0-9]{3})*(\.[0-9]+)?/', $value)) {
// Convert value to number
$value = str_replace('$', '', $value);
$cell->setValueExplicit( (float)str_replace(',', '', $value) , PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD );
return true; }

However, I'm having a problem with the second one. I want a number string from formatted with a + or - to display with the + or - (e.g. +21 or -5). The numbers are all 1 to 3 digits. I added this code to AdvancedValueBinder:

// Check for net score (+ or -)
if (preg_match('/^(\+|\-)[0-9]{1,3}/', $value)) {
// Convert value to number
$cell->setValueExplicit( (float)str_replace('+', '', $value) , PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat()->setFormatCode('+##0;-##0;0');
return true;

I don't know why it isn't working. I checked the preg_match and it matches the numbers I provide, and I used the custom format string in Excel 2007 and it displays with a + or -. When I check the generated Excel spreadsheet the cells where it should apply the custom format have a general number format.

Thanks is advance.