[Summary view]

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

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Calculation\TextData;
   4  
   5  use DateTimeInterface;
   6  use PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled;
   7  use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
   8  use PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
   9  use PhpOffice\PhpSpreadsheet\Calculation\Exception as CalcExp;
  10  use PhpOffice\PhpSpreadsheet\Calculation\Functions;
  11  use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
  12  use PhpOffice\PhpSpreadsheet\Calculation\MathTrig;
  13  use PhpOffice\PhpSpreadsheet\RichText\RichText;
  14  use PhpOffice\PhpSpreadsheet\Shared\Date;
  15  use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
  16  use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
  17  
  18  class Format
  19  {
  20      use ArrayEnabled;
  21  
  22      /**
  23       * DOLLAR.
  24       *
  25       * This function converts a number to text using currency format, with the decimals rounded to the specified place.
  26       * The format used is $#,##0.00_);($#,##0.00)..
  27       *
  28       * @param mixed $value The value to format
  29       *                         Or can be an array of values
  30       * @param mixed $decimals The number of digits to display to the right of the decimal point (as an integer).
  31       *                            If decimals is negative, number is rounded to the left of the decimal point.
  32       *                            If you omit decimals, it is assumed to be 2
  33       *                         Or can be an array of values
  34       *
  35       * @return array|string
  36       *         If an array of values is passed for either of the arguments, then the returned result
  37       *            will also be an array with matching dimensions
  38       */
  39      public static function DOLLAR($value = 0, $decimals = 2)
  40      {
  41          if (is_array($value) || is_array($decimals)) {
  42              return self::evaluateArrayArguments([self::class, __FUNCTION__], $value, $decimals);
  43          }
  44  
  45          try {
  46              $value = Helpers::extractFloat($value);
  47              $decimals = Helpers::extractInt($decimals, -100, 0, true);
  48          } catch (CalcExp $e) {
  49              return $e->getMessage();
  50          }
  51  
  52          $mask = '$#,##0';
  53          if ($decimals > 0) {
  54              $mask .= '.' . str_repeat('0', $decimals);
  55          } else {
  56              $round = 10 ** abs($decimals);
  57              if ($value < 0) {
  58                  $round = 0 - $round;
  59              }
  60              $value = MathTrig\Round::multiple($value, $round);
  61          }
  62          $mask = "{$mask};-{$mask}";
  63  
  64          return NumberFormat::toFormattedString($value, $mask);
  65      }
  66  
  67      /**
  68       * FIXED.
  69       *
  70       * @param mixed $value The value to format
  71       *                         Or can be an array of values
  72       * @param mixed $decimals Integer value for the number of decimal places that should be formatted
  73       *                         Or can be an array of values
  74       * @param mixed $noCommas Boolean value indicating whether the value should have thousands separators or not
  75       *                         Or can be an array of values
  76       *
  77       * @return array|string
  78       *         If an array of values is passed for either of the arguments, then the returned result
  79       *            will also be an array with matching dimensions
  80       */
  81      public static function FIXEDFORMAT($value, $decimals = 2, $noCommas = false)
  82      {
  83          if (is_array($value) || is_array($decimals) || is_array($noCommas)) {
  84              return self::evaluateArrayArguments([self::class, __FUNCTION__], $value, $decimals, $noCommas);
  85          }
  86  
  87          try {
  88              $value = Helpers::extractFloat($value);
  89              $decimals = Helpers::extractInt($decimals, -100, 0, true);
  90          } catch (CalcExp $e) {
  91              return $e->getMessage();
  92          }
  93  
  94          $valueResult = round($value, $decimals);
  95          if ($decimals < 0) {
  96              $decimals = 0;
  97          }
  98          if ($noCommas === false) {
  99              $valueResult = number_format(
 100                  $valueResult,
 101                  $decimals,
 102                  StringHelper::getDecimalSeparator(),
 103                  StringHelper::getThousandsSeparator()
 104              );
 105          }
 106  
 107          return (string) $valueResult;
 108      }
 109  
 110      /**
 111       * TEXT.
 112       *
 113       * @param mixed $value The value to format
 114       *                         Or can be an array of values
 115       * @param mixed $format A string with the Format mask that should be used
 116       *                         Or can be an array of values
 117       *
 118       * @return array|string
 119       *         If an array of values is passed for either of the arguments, then the returned result
 120       *            will also be an array with matching dimensions
 121       */
 122      public static function TEXTFORMAT($value, $format)
 123      {
 124          if (is_array($value) || is_array($format)) {
 125              return self::evaluateArrayArguments([self::class, __FUNCTION__], $value, $format);
 126          }
 127  
 128          $value = Helpers::extractString($value);
 129          $format = Helpers::extractString($format);
 130  
 131          if (!is_numeric($value) && Date::isDateTimeFormatCode($format)) {
 132              $value = DateTimeExcel\DateValue::fromString($value);
 133          }
 134  
 135          return (string) NumberFormat::toFormattedString($value, $format);
 136      }
 137  
 138      /**
 139       * @param mixed $value Value to check
 140       *
 141       * @return mixed
 142       */
 143      private static function convertValue($value)
 144      {
 145          $value = $value ?? 0;
 146          if (is_bool($value)) {
 147              if (Functions::getCompatibilityMode() === Functions::COMPATIBILITY_OPENOFFICE) {
 148                  $value = (int) $value;
 149              } else {
 150                  throw new CalcExp(ExcelError::VALUE());
 151              }
 152          }
 153  
 154          return $value;
 155      }
 156  
 157      /**
 158       * VALUE.
 159       *
 160       * @param mixed $value Value to check
 161       *                         Or can be an array of values
 162       *
 163       * @return array|DateTimeInterface|float|int|string A string if arguments are invalid
 164       *         If an array of values is passed for the argument, then the returned result
 165       *            will also be an array with matching dimensions
 166       */
 167      public static function VALUE($value = '')
 168      {
 169          if (is_array($value)) {
 170              return self::evaluateSingleArgumentArray([self::class, __FUNCTION__], $value);
 171          }
 172  
 173          try {
 174              $value = self::convertValue($value);
 175          } catch (CalcExp $e) {
 176              return $e->getMessage();
 177          }
 178          if (!is_numeric($value)) {
 179              $numberValue = str_replace(
 180                  StringHelper::getThousandsSeparator(),
 181                  '',
 182                  trim($value, " \t\n\r\0\x0B" . StringHelper::getCurrencyCode())
 183              );
 184              if (is_numeric($numberValue)) {
 185                  return (float) $numberValue;
 186              }
 187  
 188              $dateSetting = Functions::getReturnDateType();
 189              Functions::setReturnDateType(Functions::RETURNDATE_EXCEL);
 190  
 191              if (strpos($value, ':') !== false) {
 192                  $timeValue = Functions::scalar(DateTimeExcel\TimeValue::fromString($value));
 193                  if ($timeValue !== ExcelError::VALUE()) {
 194                      Functions::setReturnDateType($dateSetting);
 195  
 196                      return $timeValue;
 197                  }
 198              }
 199              $dateValue = Functions::scalar(DateTimeExcel\DateValue::fromString($value));
 200              if ($dateValue !== ExcelError::VALUE()) {
 201                  Functions::setReturnDateType($dateSetting);
 202  
 203                  return $dateValue;
 204              }
 205              Functions::setReturnDateType($dateSetting);
 206  
 207              return ExcelError::VALUE();
 208          }
 209  
 210          return (float) $value;
 211      }
 212  
 213      /**
 214       * TEXT.
 215       *
 216       * @param mixed $value The value to format
 217       *                         Or can be an array of values
 218       * @param mixed $format
 219       *
 220       * @return array|string
 221       *         If an array of values is passed for either of the arguments, then the returned result
 222       *            will also be an array with matching dimensions
 223       */
 224      public static function valueToText($value, $format = false)
 225      {
 226          if (is_array($value) || is_array($format)) {
 227              return self::evaluateArrayArguments([self::class, __FUNCTION__], $value, $format);
 228          }
 229  
 230          $format = (bool) $format;
 231  
 232          if (is_object($value) && $value instanceof RichText) {
 233              $value = $value->getPlainText();
 234          }
 235          if (is_string($value)) {
 236              $value = ($format === true) ? Calculation::wrapResult($value) : $value;
 237              $value = str_replace("\n", '', $value);
 238          } elseif (is_bool($value)) {
 239              $value = Calculation::$localeBoolean[$value === true ? 'TRUE' : 'FALSE'];
 240          }
 241  
 242          return (string) $value;
 243      }
 244  
 245      /**
 246       * @param mixed $decimalSeparator
 247       */
 248      private static function getDecimalSeparator($decimalSeparator): string
 249      {
 250          return empty($decimalSeparator) ? StringHelper::getDecimalSeparator() : (string) $decimalSeparator;
 251      }
 252  
 253      /**
 254       * @param mixed $groupSeparator
 255       */
 256      private static function getGroupSeparator($groupSeparator): string
 257      {
 258          return empty($groupSeparator) ? StringHelper::getThousandsSeparator() : (string) $groupSeparator;
 259      }
 260  
 261      /**
 262       * NUMBERVALUE.
 263       *
 264       * @param mixed $value The value to format
 265       *                         Or can be an array of values
 266       * @param mixed $decimalSeparator A string with the decimal separator to use, defaults to locale defined value
 267       *                         Or can be an array of values
 268       * @param mixed $groupSeparator A string with the group/thousands separator to use, defaults to locale defined value
 269       *                         Or can be an array of values
 270       *
 271       * @return array|float|string
 272       */
 273      public static function NUMBERVALUE($value = '', $decimalSeparator = null, $groupSeparator = null)
 274      {
 275          if (is_array($value) || is_array($decimalSeparator) || is_array($groupSeparator)) {
 276              return self::evaluateArrayArguments([self::class, __FUNCTION__], $value, $decimalSeparator, $groupSeparator);
 277          }
 278  
 279          try {
 280              $value = self::convertValue($value);
 281              $decimalSeparator = self::getDecimalSeparator($decimalSeparator);
 282              $groupSeparator = self::getGroupSeparator($groupSeparator);
 283          } catch (CalcExp $e) {
 284              return $e->getMessage();
 285          }
 286  
 287          if (!is_numeric($value)) {
 288              $decimalPositions = preg_match_all('/' . preg_quote($decimalSeparator) . '/', $value, $matches, PREG_OFFSET_CAPTURE);
 289              if ($decimalPositions > 1) {
 290                  return ExcelError::VALUE();
 291              }
 292              $decimalOffset = array_pop($matches[0])[1];
 293              if (strpos($value, $groupSeparator, $decimalOffset) !== false) {
 294                  return ExcelError::VALUE();
 295              }
 296  
 297              $value = str_replace([$groupSeparator, $decimalSeparator], ['', '.'], $value);
 298  
 299              // Handle the special case of trailing % signs
 300              $percentageString = rtrim($value, '%');
 301              if (!is_numeric($percentageString)) {
 302                  return ExcelError::VALUE();
 303              }
 304  
 305              $percentageAdjustment = strlen($value) - strlen($percentageString);
 306              if ($percentageAdjustment) {
 307                  $value = (float) $percentageString;
 308                  $value /= 10 ** ($percentageAdjustment * 2);
 309              }
 310          }
 311  
 312          return is_array($value) ? ExcelError::VALUE() : (float) $value;
 313      }
 314  }