You are here

public static function PHPExcel_Style_NumberFormat::toFormattedString in Loft Data Grids 6.2

Same name and namespace in other branches
  1. 7.2 vendor/phpoffice/phpexcel/Classes/PHPExcel/Style/NumberFormat.php \PHPExcel_Style_NumberFormat::toFormattedString()

* Convert a value in a pre-defined format to a PHP string * *

Parameters

mixed $value Value to format: * @param string $format Format code * @param array $callBack Callback function for additional formatting of string * @return string Formatted string

7 calls to PHPExcel_Style_NumberFormat::toFormattedString()
PHPExcel_Calculation_TextData::DOLLAR in vendor/phpoffice/phpexcel/Classes/PHPExcel/Calculation/TextData.php
* DOLLAR * * This function converts a number to text using currency format, with the decimals rounded to the specified place. * The format used is $#,##0.00_);($#,##0.00).. * *
PHPExcel_Calculation_TextData::TEXTFORMAT in vendor/phpoffice/phpexcel/Classes/PHPExcel/Calculation/TextData.php
* TEXTFORMAT * *
PHPExcel_Cell::getFormattedValue in vendor/phpoffice/phpexcel/Classes/PHPExcel/Cell.php
* Get cell value with formatting * *
PHPExcel_Chart_Renderer_jpgraph::_formatDataSetLabels in vendor/phpoffice/phpexcel/Classes/PHPExcel/Chart/Renderer/jpgraph.php
PHPExcel_Worksheet::calculateColumnWidths in vendor/phpoffice/phpexcel/Classes/PHPExcel/Worksheet.php
Calculate widths for auto-size columns

... See full list

File

vendor/phpoffice/phpexcel/Classes/PHPExcel/Style/NumberFormat.php, line 547

Class

PHPExcel_Style_NumberFormat
PHPExcel_Style_NumberFormat

Code

public static function toFormattedString($value = '0', $format = PHPExcel_Style_NumberFormat::FORMAT_GENERAL, $callBack = null) {

  // For now we do not treat strings although section 4 of a format code affects strings
  if (!is_numeric($value)) {
    return $value;
  }

  // For 'General' format code, we just pass the value although this is not entirely the way Excel does it,
  // it seems to round numbers to a total of 10 digits.
  if ($format === PHPExcel_Style_NumberFormat::FORMAT_GENERAL || $format === PHPExcel_Style_NumberFormat::FORMAT_TEXT) {
    return $value;
  }

  // Get the sections, there can be up to four sections
  $sections = explode(';', $format);

  // Fetch the relevant section depending on whether number is positive, negative, or zero?
  // Text not supported yet.
  // Here is how the sections apply to various values in Excel:
  //   1 section:   [POSITIVE/NEGATIVE/ZERO/TEXT]
  //   2 sections:  [POSITIVE/ZERO/TEXT] [NEGATIVE]
  //   3 sections:  [POSITIVE/TEXT] [NEGATIVE] [ZERO]
  //   4 sections:  [POSITIVE] [NEGATIVE] [ZERO] [TEXT]
  switch (count($sections)) {
    case 1:
      $format = $sections[0];
      break;
    case 2:
      $format = $value >= 0 ? $sections[0] : $sections[1];
      $value = abs($value);

      // Use the absolute value
      break;
    case 3:
      $format = $value > 0 ? $sections[0] : ($value < 0 ? $sections[1] : $sections[2]);
      $value = abs($value);

      // Use the absolute value
      break;
    case 4:
      $format = $value > 0 ? $sections[0] : ($value < 0 ? $sections[1] : $sections[2]);
      $value = abs($value);

      // Use the absolute value
      break;
    default:

      // something is wrong, just use first section
      $format = $sections[0];
      break;
  }

  // Save format with color information for later use below
  $formatColor = $format;

  // Strip color information
  $color_regex = '/^\\[[a-zA-Z]+\\]/';
  $format = preg_replace($color_regex, '', $format);

  // Let's begin inspecting the format and converting the value to a formatted string
  if (preg_match('/^(\\[\\$[A-Z]*-[0-9A-F]*\\])*[hmsdy]/i', $format)) {

    // datetime format
    self::_formatAsDate($value, $format);
  }
  else {
    if (preg_match('/%$/', $format)) {

      // % number format
      self::_formatAsPercentage($value, $format);
    }
    else {
      if ($format === self::FORMAT_CURRENCY_EUR_SIMPLE) {
        $value = 'EUR ' . sprintf('%1.2f', $value);
      }
      else {

        // In Excel formats, "_" is used to add spacing, which we can't do in HTML
        $format = preg_replace('/_./', '', $format);

        // Some non-number characters are escaped with \, which we don't need
        $format = preg_replace("/\\\\/", '', $format);

        //              Handle escaped characters, such as \" to display a literal " or \\ to display a literal \
        //              $format = preg_replace('/(?<!\\\\)\"/', '', $format);
        //				$format = str_replace(array('\\"', '*'), array('"', ''), $format);
        // Some non-number strings are quoted, so we'll get rid of the quotes, likewise any positional * symbols
        $format = str_replace(array(
          '"',
          '*',
        ), '', $format);

        // Find out if we need thousands separator
        // This is indicated by a comma enclosed by a digit placeholder:
        //		#,#   or   0,0
        $useThousands = preg_match('/(#,#|0,0)/', $format);
        if ($useThousands) {
          $format = preg_replace('/0,0/', '00', $format);
          $format = preg_replace('/#,#/', '##', $format);
        }

        // Scale thousands, millions,...
        // This is indicated by a number of commas after a digit placeholder:
        //		#,   or	0.0,,
        $scale = 1;

        // same as no scale
        $matches = array();
        if (preg_match('/(#|0)(,+)/', $format, $matches)) {
          $scale = pow(1000, strlen($matches[2]));

          // strip the commas
          $format = preg_replace('/0,+/', '0', $format);
          $format = preg_replace('/#,+/', '#', $format);
        }
        if (preg_match('/#?.*\\?\\/\\?/', $format, $m)) {

          //echo 'Format mask is fractional '.$format.' <br />';
          if ($value != (int) $value) {
            self::_formatAsFraction($value, $format);
          }
        }
        else {

          // Handle the number itself
          // scale number
          $value = $value / $scale;

          // Strip #
          $format = preg_replace('/\\#/', '0', $format);
          $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());
              $value = preg_replace($number_regex, $value, $format);
            }
            else {
              if (preg_match('/[0#]E[+-]0/i', $format)) {

                //	Scientific format
                $value = sprintf('%5.2E', $value);
              }
              elseif (preg_match('/0([^\\d\\.]+)0/', $format)) {
                $value = self::_complexNumberFormatMask($value, $format);
              }
              else {
                $sprintf_pattern = "%0{$minWidth}." . strlen($right) . "f";
                $value = sprintf($sprintf_pattern, $value);
                $value = preg_replace($number_regex, $value, $format);
              }
            }
          }
        }
        if (preg_match('/\\[\\$(.*)\\]/u', $format, $m)) {

          //	Currency or Accounting
          $currencyFormat = $m[0];
          $currencyCode = $m[1];
          list($currencyCode) = explode('-', $currencyCode);
          if ($currencyCode == '') {
            $currencyCode = PHPExcel_Shared_String::getCurrencyCode();
          }
          $value = preg_replace('/\\[\\$([^\\]]*)\\]/u', $currencyCode, $value);
        }
      }
    }
  }

  // Additional formatting provided by callback function
  if ($callBack !== null) {
    list($writerInstance, $function) = $callBack;
    $value = $writerInstance
      ->{$function}($value, $formatColor);
  }
  return $value;
}