[Summary view]

Differences Between: [Versions 400 and 401] [Versions 401 and 402] [Versions 401 and 403]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Style\NumberFormat;
   4  
   5  use PhpOffice\PhpSpreadsheet\Style\Color;
   6  use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
   7  
   8  class Formatter
   9  {
  10      private static function splitFormatCompare($value, $cond, $val, $dfcond, $dfval)
  11      {
  12          if (!$cond) {
  13              $cond = $dfcond;
  14              $val = $dfval;
  15          }
  16          switch ($cond) {
  17              case '>':
  18                  return $value > $val;
  19  
  20              case '<':
  21                  return $value < $val;
  22  
  23              case '<=':
  24                  return $value <= $val;
  25  
  26              case '<>':
  27                  return $value != $val;
  28  
  29              case '=':
  30                  return $value == $val;
  31          }
  32  
  33          return $value >= $val;
  34      }
  35  
  36      private static function splitFormat($sections, $value)
  37      {
  38          // Extract the relevant section depending on whether number is positive, negative, or zero?
  39          // Text not supported yet.
  40          // Here is how the sections apply to various values in Excel:
  41          //   1 section:   [POSITIVE/NEGATIVE/ZERO/TEXT]
  42          //   2 sections:  [POSITIVE/ZERO/TEXT] [NEGATIVE]
  43          //   3 sections:  [POSITIVE/TEXT] [NEGATIVE] [ZERO]
  44          //   4 sections:  [POSITIVE] [NEGATIVE] [ZERO] [TEXT]
  45          $cnt = count($sections);
  46          $color_regex = '/\\[(' . implode('|', Color::NAMED_COLORS) . ')\\]/mui';
  47          $cond_regex = '/\\[(>|>=|<|<=|=|<>)([+-]?\\d+([.]\\d+)?)\\]/';
  48          $colors = ['', '', '', '', ''];
  49          $condops = ['', '', '', '', ''];
  50          $condvals = [0, 0, 0, 0, 0];
  51          for ($idx = 0; $idx < $cnt; ++$idx) {
  52              if (preg_match($color_regex, $sections[$idx], $matches)) {
  53                  $colors[$idx] = $matches[0];
  54                  $sections[$idx] = (string) preg_replace($color_regex, '', $sections[$idx]);
  55              }
  56              if (preg_match($cond_regex, $sections[$idx], $matches)) {
  57                  $condops[$idx] = $matches[1];
  58                  $condvals[$idx] = $matches[2];
  59                  $sections[$idx] = (string) preg_replace($cond_regex, '', $sections[$idx]);
  60              }
  61          }
  62          $color = $colors[0];
  63          $format = $sections[0];
  64          $absval = $value;
  65          switch ($cnt) {
  66              case 2:
  67                  $absval = abs($value);
  68                  if (!self::splitFormatCompare($value, $condops[0], $condvals[0], '>=', 0)) {
  69                      $color = $colors[1];
  70                      $format = $sections[1];
  71                  }
  72  
  73                  break;
  74              case 3:
  75              case 4:
  76                  $absval = abs($value);
  77                  if (!self::splitFormatCompare($value, $condops[0], $condvals[0], '>', 0)) {
  78                      if (self::splitFormatCompare($value, $condops[1], $condvals[1], '<', 0)) {
  79                          $color = $colors[1];
  80                          $format = $sections[1];
  81                      } else {
  82                          $color = $colors[2];
  83                          $format = $sections[2];
  84                      }
  85                  }
  86  
  87                  break;
  88          }
  89  
  90          return [$color, $format, $absval];
  91      }
  92  
  93      /**
  94       * Convert a value in a pre-defined format to a PHP string.
  95       *
  96       * @param mixed $value Value to format
  97       * @param string $format Format code, see = NumberFormat::FORMAT_*
  98       * @param array $callBack Callback function for additional formatting of string
  99       *
 100       * @return string Formatted string
 101       */
 102      public static function toFormattedString($value, $format, $callBack = null)
 103      {
 104          // For now we do not treat strings although section 4 of a format code affects strings
 105          if (!is_numeric($value)) {
 106              return $value;
 107          }
 108  
 109          // For 'General' format code, we just pass the value although this is not entirely the way Excel does it,
 110          // it seems to round numbers to a total of 10 digits.
 111          if (($format === NumberFormat::FORMAT_GENERAL) || ($format === NumberFormat::FORMAT_TEXT)) {
 112              return $value;
 113          }
 114  
 115          // Ignore square-$-brackets prefix in format string, like "[$-411]ge.m.d", "[$-010419]0%", etc
 116          $format = (string) preg_replace('/^\[\$-[^\]]*\]/', '', $format);
 117  
 118          $format = (string) preg_replace_callback(
 119              '/(["])(?:(?=(\\\\?))\\2.)*?\\1/u',
 120              function ($matches) {
 121                  return str_replace('.', chr(0x00), $matches[0]);
 122              },
 123              $format
 124          );
 125  
 126          // Convert any other escaped characters to quoted strings, e.g. (\T to "T")
 127          $format = (string) preg_replace('/(\\\(((.)(?!((AM\/PM)|(A\/P))))|([^ ])))(?=(?:[^"]|"[^"]*")*$)/ui', '"$2}"', $format);
 128  
 129          // Get the sections, there can be up to four sections, separated with a semi-colon (but only if not a quoted literal)
 130          $sections = preg_split('/(;)(?=(?:[^"]|"[^"]*")*$)/u', $format);
 131  
 132          [$colors, $format, $value] = self::splitFormat($sections, $value);
 133  
 134          // In Excel formats, "_" is used to add spacing,
 135          //    The following character indicates the size of the spacing, which we can't do in HTML, so we just use a standard space
 136          $format = (string) preg_replace('/_.?/ui', ' ', $format);
 137  
 138          // Let's begin inspecting the format and converting the value to a formatted string
 139  
 140          //  Check for date/time characters (not inside quotes)
 141          if (preg_match('/(\[\$[A-Z]*-[0-9A-F]*\])*[hmsdy](?=(?:[^"]|"[^"]*")*$)/miu', $format, $matches)) {
 142              // datetime format
 143              $value = DateFormatter::format($value, $format);
 144          } else {
 145              if (substr($format, 0, 1) === '"' && substr($format, -1, 1) === '"' && substr_count($format, '"') === 2) {
 146                  $value = substr($format, 1, -1);
 147              } elseif (preg_match('/[0#, ]%/', $format)) {
 148                  // % number format
 149                  $value = PercentageFormatter::format($value, $format);
 150              } else {
 151                  $value = NumberFormatter::format($value, $format);
 152              }
 153          }
 154  
 155          // Additional formatting provided by callback function
 156          if ($callBack !== null) {
 157              [$writerInstance, $function] = $callBack;
 158              $value = $writerInstance->$function($value, $colors);
 159          }
 160  
 161          $value = str_replace(chr(0x00), '.', $value);
 162  
 163          return $value;
 164      }
 165  }