[Summary view]

Differences Between: [Versions 400 and 403] [Versions 401 and 403] [Versions 402 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) + DateTimeExcel\TimeValue::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, bool $spacesMeanZero = false)
 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          if (is_string($value)) {
 154              $value = trim($value);
 155              if ($spacesMeanZero && $value === '') {
 156                  $value = 0;
 157              }
 158          }
 159  
 160          return $value;
 161      }
 162  
 163      /**
 164       * VALUE.
 165       *
 166       * @param mixed $value Value to check
 167       *                         Or can be an array of values
 168       *
 169       * @return array|DateTimeInterface|float|int|string A string if arguments are invalid
 170       *         If an array of values is passed for the argument, then the returned result
 171       *            will also be an array with matching dimensions
 172       */
 173      public static function VALUE($value = '')
 174      {
 175          if (is_array($value)) {
 176              return self::evaluateSingleArgumentArray([self::class, __FUNCTION__], $value);
 177          }
 178  
 179          try {
 180              $value = self::convertValue($value);
 181          } catch (CalcExp $e) {
 182              return $e->getMessage();
 183          }
 184          if (!is_numeric($value)) {
 185              $numberValue = str_replace(
 186                  StringHelper::getThousandsSeparator(),
 187                  '',
 188                  trim($value, " \t\n\r\0\x0B" . StringHelper::getCurrencyCode())
 189              );
 190              if ($numberValue === '') {
 191                  return ExcelError::VALUE();
 192              }
 193              if (is_numeric($numberValue)) {
 194                  return (float) $numberValue;
 195              }
 196  
 197              $dateSetting = Functions::getReturnDateType();
 198              Functions::setReturnDateType(Functions::RETURNDATE_EXCEL);
 199  
 200              if (strpos($value, ':') !== false) {
 201                  $timeValue = Functions::scalar(DateTimeExcel\TimeValue::fromString($value));
 202                  if ($timeValue !== ExcelError::VALUE()) {
 203                      Functions::setReturnDateType($dateSetting);
 204  
 205                      return $timeValue;
 206                  }
 207              }
 208              $dateValue = Functions::scalar(DateTimeExcel\DateValue::fromString($value));
 209              if ($dateValue !== ExcelError::VALUE()) {
 210                  Functions::setReturnDateType($dateSetting);
 211  
 212                  return $dateValue;
 213              }
 214              Functions::setReturnDateType($dateSetting);
 215  
 216              return ExcelError::VALUE();
 217          }
 218  
 219          return (float) $value;
 220      }
 221  
 222      /**
 223       * TEXT.
 224       *
 225       * @param mixed $value The value to format
 226       *                         Or can be an array of values
 227       * @param mixed $format
 228       *
 229       * @return array|string
 230       *         If an array of values is passed for either of the arguments, then the returned result
 231       *            will also be an array with matching dimensions
 232       */
 233      public static function valueToText($value, $format = false)
 234      {
 235          if (is_array($value) || is_array($format)) {
 236              return self::evaluateArrayArguments([self::class, __FUNCTION__], $value, $format);
 237          }
 238  
 239          $format = (bool) $format;
 240  
 241          if (is_object($value) && $value instanceof RichText) {
 242              $value = $value->getPlainText();
 243          }
 244          if (is_string($value)) {
 245              $value = ($format === true) ? Calculation::wrapResult($value) : $value;
 246              $value = str_replace("\n", '', $value);
 247          } elseif (is_bool($value)) {
 248              $value = Calculation::getLocaleBoolean($value ? 'TRUE' : 'FALSE');
 249          }
 250  
 251          return (string) $value;
 252      }
 253  
 254      /**
 255       * @param mixed $decimalSeparator
 256       */
 257      private static function getDecimalSeparator($decimalSeparator): string
 258      {
 259          return empty($decimalSeparator) ? StringHelper::getDecimalSeparator() : (string) $decimalSeparator;
 260      }
 261  
 262      /**
 263       * @param mixed $groupSeparator
 264       */
 265      private static function getGroupSeparator($groupSeparator): string
 266      {
 267          return empty($groupSeparator) ? StringHelper::getThousandsSeparator() : (string) $groupSeparator;
 268      }
 269  
 270      /**
 271       * NUMBERVALUE.
 272       *
 273       * @param mixed $value The value to format
 274       *                         Or can be an array of values
 275       * @param mixed $decimalSeparator A string with the decimal separator to use, defaults to locale defined value
 276       *                         Or can be an array of values
 277       * @param mixed $groupSeparator A string with the group/thousands separator to use, defaults to locale defined value
 278       *                         Or can be an array of values
 279       *
 280       * @return array|float|string
 281       */
 282      public static function NUMBERVALUE($value = '', $decimalSeparator = null, $groupSeparator = null)
 283      {
 284          if (is_array($value) || is_array($decimalSeparator) || is_array($groupSeparator)) {
 285              return self::evaluateArrayArguments([self::class, __FUNCTION__], $value, $decimalSeparator, $groupSeparator);
 286          }
 287  
 288          try {
 289              $value = self::convertValue($value, true);
 290              $decimalSeparator = self::getDecimalSeparator($decimalSeparator);
 291              $groupSeparator = self::getGroupSeparator($groupSeparator);
 292          } catch (CalcExp $e) {
 293              return $e->getMessage();
 294          }
 295  
 296          if (!is_numeric($value)) {
 297              $decimalPositions = preg_match_all('/' . preg_quote($decimalSeparator, '/') . '/', $value, $matches, PREG_OFFSET_CAPTURE);
 298              if ($decimalPositions > 1) {
 299                  return ExcelError::VALUE();
 300              }
 301              $decimalOffset = array_pop($matches[0])[1] ?? null;
 302              if ($decimalOffset === null || strpos($value, $groupSeparator, $decimalOffset) !== false) {
 303                  return ExcelError::VALUE();
 304              }
 305  
 306              $value = str_replace([$groupSeparator, $decimalSeparator], ['', '.'], $value);
 307  
 308              // Handle the special case of trailing % signs
 309              $percentageString = rtrim($value, '%');
 310              if (!is_numeric($percentageString)) {
 311                  return ExcelError::VALUE();
 312              }
 313  
 314              $percentageAdjustment = strlen($value) - strlen($percentageString);
 315              if ($percentageAdjustment) {
 316                  $value = (float) $percentageString;
 317                  $value /= 10 ** ($percentageAdjustment * 2);
 318              }
 319          }
 320  
 321          return is_array($value) ? ExcelError::VALUE() : (float) $value;
 322      }
 323  }