[Summary view]

Differences Between: [Versions 310 and 401] [Versions 311 and 401] [Versions 39 and 401] [Versions 400 and 401] [Versions 401 and 402] [Versions 401 and 403]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Cell;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
   6  use PhpOffice\PhpSpreadsheet\RichText\RichText;
   7  use PhpOffice\PhpSpreadsheet\Shared\Date;
   8  use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
   9  use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
  10  
  11  class AdvancedValueBinder extends DefaultValueBinder implements IValueBinder
  12  {
  13      /**
  14       * Bind value to a cell.
  15       *
  16       * @param Cell $cell Cell to bind value to
  17       * @param mixed $value Value to bind in cell
  18       *
  19       * @return bool
  20       */
  21      public function bindValue(Cell $cell, $value = null)
  22      {
  23          if ($value === null) {
  24              return parent::bindValue($cell, $value);
  25          } elseif (is_string($value)) {
  26              // sanitize UTF-8 strings
  27              $value = StringHelper::sanitizeUTF8($value);
  28          }
  29  
  30          // Find out data type
  31          $dataType = parent::dataTypeForValue($value);
  32  
  33          // Style logic - strings
  34          if ($dataType === DataType::TYPE_STRING && !$value instanceof RichText) {
  35              //    Test for booleans using locale-setting
  36              if ($value == Calculation::getTRUE()) {
  37                  $cell->setValueExplicit(true, DataType::TYPE_BOOL);
  38  
  39                  return true;
  40              } elseif ($value == Calculation::getFALSE()) {
  41                  $cell->setValueExplicit(false, DataType::TYPE_BOOL);
  42  
  43                  return true;
  44              }
  45  
  46              // Check for fractions
  47              if (preg_match('/^([+-]?)\s*(\d+)\s?\/\s*(\d+)$/', $value, $matches)) {
  48                  return $this->setProperFraction($matches, $cell);
  49              } elseif (preg_match('/^([+-]?)(\d*) +(\d*)\s?\/\s*(\d*)$/', $value, $matches)) {
  50                  return $this->setImproperFraction($matches, $cell);
  51              }
  52  
  53              // Check for percentage
  54              if (preg_match('/^\-?\d*\.?\d*\s?\%$/', $value)) {
  55                  return $this->setPercentage($value, $cell);
  56              }
  57  
  58              // Check for currency
  59              $currencyCode = StringHelper::getCurrencyCode();
  60              $decimalSeparator = StringHelper::getDecimalSeparator();
  61              $thousandsSeparator = StringHelper::getThousandsSeparator();
  62              if (preg_match('/^' . preg_quote($currencyCode, '/') . ' *(\d{1,3}(' . preg_quote($thousandsSeparator, '/') . '\d{3})*|(\d+))(' . preg_quote($decimalSeparator, '/') . '\d{2})?$/', $value)) {
  63                  // Convert value to number
  64                  $value = (float) trim(str_replace([$currencyCode, $thousandsSeparator, $decimalSeparator], ['', '', '.'], $value));
  65                  $cell->setValueExplicit($value, DataType::TYPE_NUMERIC);
  66                  // Set style
  67                  $cell->getWorksheet()->getStyle($cell->getCoordinate())
  68                      ->getNumberFormat()->setFormatCode(
  69                          str_replace('$', $currencyCode, NumberFormat::FORMAT_CURRENCY_USD_SIMPLE)
  70                      );
  71  
  72                  return true;
  73              } elseif (preg_match('/^\$ *(\d{1,3}(\,\d{3})*|(\d+))(\.\d{2})?$/', $value)) {
  74                  // Convert value to number
  75                  $value = (float) trim(str_replace(['$', ','], '', $value));
  76                  $cell->setValueExplicit($value, DataType::TYPE_NUMERIC);
  77                  // Set style
  78                  $cell->getWorksheet()->getStyle($cell->getCoordinate())
  79                      ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
  80  
  81                  return true;
  82              }
  83  
  84              // Check for time without seconds e.g. '9:45', '09:45'
  85              if (preg_match('/^(\d|[0-1]\d|2[0-3]):[0-5]\d$/', $value)) {
  86                  return $this->setTimeHoursMinutes($value, $cell);
  87              }
  88  
  89              // Check for time with seconds '9:45:59', '09:45:59'
  90              if (preg_match('/^(\d|[0-1]\d|2[0-3]):[0-5]\d:[0-5]\d$/', $value)) {
  91                  return $this->setTimeHoursMinutesSeconds($value, $cell);
  92              }
  93  
  94              // Check for datetime, e.g. '2008-12-31', '2008-12-31 15:59', '2008-12-31 15:59:10'
  95              if (($d = Date::stringToExcel($value)) !== false) {
  96                  // Convert value to number
  97                  $cell->setValueExplicit($d, DataType::TYPE_NUMERIC);
  98                  // Determine style. Either there is a time part or not. Look for ':'
  99                  if (strpos($value, ':') !== false) {
 100                      $formatCode = 'yyyy-mm-dd h:mm';
 101                  } else {
 102                      $formatCode = 'yyyy-mm-dd';
 103                  }
 104                  $cell->getWorksheet()->getStyle($cell->getCoordinate())
 105                      ->getNumberFormat()->setFormatCode($formatCode);
 106  
 107                  return true;
 108              }
 109  
 110              // Check for newline character "\n"
 111              if (strpos($value, "\n") !== false) {
 112                  $cell->setValueExplicit($value, DataType::TYPE_STRING);
 113                  // Set style
 114                  $cell->getWorksheet()->getStyle($cell->getCoordinate())
 115                      ->getAlignment()->setWrapText(true);
 116  
 117                  return true;
 118              }
 119          }
 120  
 121          // Not bound yet? Use parent...
 122          return parent::bindValue($cell, $value);
 123      }
 124  
 125      protected function setImproperFraction(array $matches, Cell $cell): bool
 126      {
 127          // Convert value to number
 128          $value = $matches[2] + ($matches[3] / $matches[4]);
 129          if ($matches[1] === '-') {
 130              $value = 0 - $value;
 131          }
 132          $cell->setValueExplicit((float) $value, DataType::TYPE_NUMERIC);
 133  
 134          // Build the number format mask based on the size of the matched values
 135          $dividend = str_repeat('?', strlen($matches[3]));
 136          $divisor = str_repeat('?', strlen($matches[4]));
 137          $fractionMask = "# {$dividend}/{$divisor}";
 138          // Set style
 139          $cell->getWorksheet()->getStyle($cell->getCoordinate())
 140              ->getNumberFormat()->setFormatCode($fractionMask);
 141  
 142          return true;
 143      }
 144  
 145      protected function setProperFraction(array $matches, Cell $cell): bool
 146      {
 147          // Convert value to number
 148          $value = $matches[2] / $matches[3];
 149          if ($matches[1] === '-') {
 150              $value = 0 - $value;
 151          }
 152          $cell->setValueExplicit((float) $value, DataType::TYPE_NUMERIC);
 153  
 154          // Build the number format mask based on the size of the matched values
 155          $dividend = str_repeat('?', strlen($matches[2]));
 156          $divisor = str_repeat('?', strlen($matches[3]));
 157          $fractionMask = "{$dividend}/{$divisor}";
 158          // Set style
 159          $cell->getWorksheet()->getStyle($cell->getCoordinate())
 160              ->getNumberFormat()->setFormatCode($fractionMask);
 161  
 162          return true;
 163      }
 164  
 165      protected function setPercentage(string $value, Cell $cell): bool
 166      {
 167          // Convert value to number
 168          $value = ((float) str_replace('%', '', $value)) / 100;
 169          $cell->setValueExplicit($value, DataType::TYPE_NUMERIC);
 170  
 171          // Set style
 172          $cell->getWorksheet()->getStyle($cell->getCoordinate())
 173              ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_PERCENTAGE_00);
 174  
 175          return true;
 176      }
 177  
 178      protected function setTimeHoursMinutes(string $value, Cell $cell): bool
 179      {
 180          // Convert value to number
 181          [$hours, $minutes] = explode(':', $value);
 182          $hours = (int) $hours;
 183          $minutes = (int) $minutes;
 184          $days = ($hours / 24) + ($minutes / 1440);
 185          $cell->setValueExplicit($days, DataType::TYPE_NUMERIC);
 186  
 187          // Set style
 188          $cell->getWorksheet()->getStyle($cell->getCoordinate())
 189              ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_TIME3);
 190  
 191          return true;
 192      }
 193  
 194      protected function setTimeHoursMinutesSeconds(string $value, Cell $cell): bool
 195      {
 196          // Convert value to number
 197          [$hours, $minutes, $seconds] = explode(':', $value);
 198          $hours = (int) $hours;
 199          $minutes = (int) $minutes;
 200          $seconds = (int) $seconds;
 201          $days = ($hours / 24) + ($minutes / 1440) + ($seconds / 86400);
 202          $cell->setValueExplicit($days, DataType::TYPE_NUMERIC);
 203  
 204          // Set style
 205          $cell->getWorksheet()->getStyle($cell->getCoordinate())
 206              ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_TIME4);
 207  
 208          return true;
 209      }
 210  }