Bug Report: Cell format style \(000") "000-0000 for phone numbers does not display correctly.

Topics: Developer Forum, User Forum
Jul 10, 2012 at 8:20 PM
Edited Jul 11, 2012 at 3:09 AM

PHPExcel Info:

I've verified this bug for PHPExcel versions 1.7.6 and 1.7.7 with spreadsheet readers for xls, xlsx, and ods. My code uses the getFormattedValue() method to pull the data into PHP.

Description:

Any cell that contains a 10 digit phone number with the format code below will not get the expected formatted value when pulling the data with the getFormattedValue() method.

Formatting Code:

\(000") "000-0000

Examples:

Open Document Format: 5555555555 formats as 1260588259 instead of (555) 555-5555

Excel 5: 5555555555 formats as (5555555555) 5555555555-5555555555 instead of (555) 555-5555

Excel 2007: 5555555555 formats as (5555555555) 5555555555-5555555555 instead of (555) 555-5555

 

Mark,

 Let me know when you get this fixed and what code I need to change in order to fix 1.7.6 which I will need to manually change myself.

Thanks.

- Christopher Mullins

Jul 12, 2012 at 5:51 AM
Edited Jul 12, 2012 at 6:00 AM

Here's a solution that correctly returns the formatted value for excel files. This code still does not fix the Open document file using the same format.

File modified: PHPExcel/Style/NumberFormat.php

Method modified: toFormattedString()

Line number where following code is placed: 683

 

// Begin insert of new code
if ($format == '(000) 000-0000') { // phone number format
  $areacode = substr("{$value}", 0, 3);
  $exchange = substr("{$value}", 3, 3);
  $number   = substr("{$value}", 6);
  $value = "({$areacode}) {$exchange}-{$number}";
} else {
// End insert of new code
  // Begin indent of existing code
  $n = "/\[[^\]]+\]/";
  $m = preg_replace($n, '', $format);
  $number_regex = "/(0+)(\.?)(0*)/";
  if (preg_match($number_regex, $m, $matches)) {
    $left = $matches[1];
    $dec = $matches[2];
    $right = $matches[3];

    // minimun width of formatted number (including dot)
    $minWidth = strlen($left) + strlen($dec) + strlen($right);

    if ($useThousands) {
      $value = number_format(
        $value
        , strlen($right)
        , PHPExcel_Shared_String::getDecimalSeparator()
        , PHPExcel_Shared_String::getThousandsSeparator()
      );
    } else {
      $sprintf_pattern = "%0$minWidth." . strlen($right) . "f";
      $value = sprintf($sprintf_pattern, $value);
    }

    $value = preg_replace($number_regex, $value, $format);
  }
  // End indent of existing code
// Begin insert of new code
}

 

// End insert of new code

 

- Christpher Mullins
Sep 21, 2012 at 3:42 PM
Edited Sep 21, 2012 at 3:43 PM

For the OpenDocument issue, see this:

Some Non-Excel Readers and Big Numbers