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