ILIAS  release_5-4 Revision v5.4.26-12-gabc799a52e6
Public Member Functions | Protected Member Functions
PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder Class Reference
+ Inheritance diagram for PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder:
+ Collaboration diagram for PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder:

Public Member Functions

 bindValue (Cell $cell, $value=null)
 Bind value to a cell. More...
 
- Public Member Functions inherited from PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder
 bindValue (Cell $cell, $value)
 Bind value to a cell. More...
 

Protected Member Functions

 setImproperFraction (array $matches, Cell $cell)
 
 setProperFraction (array $matches, Cell $cell)
 
 setPercentage (string $value, Cell $cell)
 
 setTimeHoursMinutes (string $value, Cell $cell)
 
 setTimeHoursMinutesSeconds (string $value, Cell $cell)
 

Additional Inherited Members

- Static Public Member Functions inherited from PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder
static dataTypeForValue ($value)
 DataType for value. More...
 

Detailed Description

Definition at line 11 of file AdvancedValueBinder.php.

Member Function Documentation

◆ bindValue()

PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder::bindValue ( Cell  $cell,
  $value = null 
)

Bind value to a cell.

Parameters
Cell$cellCell to bind value to
mixed$valueValue to bind in cell
Returns
bool

Implements PhpOffice\PhpSpreadsheet\Cell\IValueBinder.

Definition at line 21 of file AdvancedValueBinder.php.

References $d, PhpOffice\PhpSpreadsheet\Style\NumberFormat\FORMAT_CURRENCY_USD_SIMPLE, PhpOffice\PhpSpreadsheet\Cell\Cell\getCoordinate(), PhpOffice\PhpSpreadsheet\Shared\StringHelper\getCurrencyCode(), PhpOffice\PhpSpreadsheet\Shared\StringHelper\getDecimalSeparator(), PhpOffice\PhpSpreadsheet\Calculation\Calculation\getFALSE(), PhpOffice\PhpSpreadsheet\Shared\StringHelper\getThousandsSeparator(), PhpOffice\PhpSpreadsheet\Calculation\Calculation\getTRUE(), PhpOffice\PhpSpreadsheet\Cell\Cell\getWorksheet(), PhpOffice\PhpSpreadsheet\Shared\StringHelper\sanitizeUTF8(), PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder\setImproperFraction(), PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder\setPercentage(), PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder\setProperFraction(), PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder\setTimeHoursMinutes(), PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder\setTimeHoursMinutesSeconds(), PhpOffice\PhpSpreadsheet\Cell\Cell\setValueExplicit(), PhpOffice\PhpSpreadsheet\Shared\Date\stringToExcel(), PhpOffice\PhpSpreadsheet\Cell\DataType\TYPE_BOOL, PhpOffice\PhpSpreadsheet\Cell\DataType\TYPE_NUMERIC, and PhpOffice\PhpSpreadsheet\Cell\DataType\TYPE_STRING.

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  }
PhpOffice\PhpSpreadsheet\Shared\StringHelper\sanitizeUTF8
static sanitizeUTF8($value)
Try to sanitize UTF8, stripping invalid byte sequences.
Definition: StringHelper.php:337
PhpOffice\PhpSpreadsheet\Shared\StringHelper\getCurrencyCode
static getCurrencyCode()
Get the currency code.
Definition: StringHelper.php:650
PhpOffice\PhpSpreadsheet\Calculation\Calculation\getFALSE
static getFALSE()
Return the locale-specific translation of FALSE.
Definition: Calculation.php:2844
PhpOffice\PhpSpreadsheet\Calculation\Calculation\getTRUE
static getTRUE()
Return the locale-specific translation of TRUE.
Definition: Calculation.php:2834
PhpOffice\PhpSpreadsheet\Shared\Date\stringToExcel
static stringToExcel($dateValue)
Convert a date/time string to Excel time.
Definition: Date.php:441
PhpOffice\PhpSpreadsheet\Shared\StringHelper\getThousandsSeparator
static getThousandsSeparator()
Get the thousands separator.
Definition: StringHelper.php:617
PhpOffice\PhpSpreadsheet\Shared\StringHelper\getDecimalSeparator
static getDecimalSeparator()
Get the decimal separator.
Definition: StringHelper.php:584
$d
for($i=6; $i< 13; $i++) for($i=1; $i< 13; $i++) $d
Definition: date.php:296
+ Here is the call graph for this function:

◆ setImproperFraction()

PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder::setImproperFraction ( array  $matches,
Cell  $cell 
)
protected

Definition at line 125 of file AdvancedValueBinder.php.

References PhpOffice\PhpSpreadsheet\Cell\Cell\getCoordinate(), PhpOffice\PhpSpreadsheet\Cell\Cell\getWorksheet(), PhpOffice\PhpSpreadsheet\Cell\Cell\setValueExplicit(), and PhpOffice\PhpSpreadsheet\Cell\DataType\TYPE_NUMERIC.

Referenced by PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder\bindValue().

125  : 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  }
+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ setPercentage()

PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder::setPercentage ( string  $value,
Cell  $cell 
)
protected

Definition at line 165 of file AdvancedValueBinder.php.

References PhpOffice\PhpSpreadsheet\Style\NumberFormat\FORMAT_PERCENTAGE_00, PhpOffice\PhpSpreadsheet\Cell\Cell\getCoordinate(), PhpOffice\PhpSpreadsheet\Cell\Cell\getWorksheet(), PhpOffice\PhpSpreadsheet\Cell\Cell\setValueExplicit(), and PhpOffice\PhpSpreadsheet\Cell\DataType\TYPE_NUMERIC.

Referenced by PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder\bindValue().

165  : 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  }
+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ setProperFraction()

PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder::setProperFraction ( array  $matches,
Cell  $cell 
)
protected

Definition at line 145 of file AdvancedValueBinder.php.

References PhpOffice\PhpSpreadsheet\Cell\Cell\getCoordinate(), PhpOffice\PhpSpreadsheet\Cell\Cell\getWorksheet(), PhpOffice\PhpSpreadsheet\Cell\Cell\setValueExplicit(), and PhpOffice\PhpSpreadsheet\Cell\DataType\TYPE_NUMERIC.

Referenced by PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder\bindValue().

145  : 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  }
+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ setTimeHoursMinutes()

PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder::setTimeHoursMinutes ( string  $value,
Cell  $cell 
)
protected

Definition at line 178 of file AdvancedValueBinder.php.

References PhpOffice\PhpSpreadsheet\Style\NumberFormat\FORMAT_DATE_TIME3, PhpOffice\PhpSpreadsheet\Cell\Cell\getCoordinate(), PhpOffice\PhpSpreadsheet\Cell\Cell\getWorksheet(), PhpOffice\PhpSpreadsheet\Cell\Cell\setValueExplicit(), and PhpOffice\PhpSpreadsheet\Cell\DataType\TYPE_NUMERIC.

Referenced by PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder\bindValue().

178  : bool
179  {
180  // Convert value to number
181  [$hours, $minutes] = explode(':', $value);
182  $days = ($hours / 24) + ($minutes / 1440);
183  $cell->setValueExplicit($days, DataType::TYPE_NUMERIC);
184 
185  // Set style
186  $cell->getWorksheet()->getStyle($cell->getCoordinate())
187  ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_TIME3);
188 
189  return true;
190  }
+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ setTimeHoursMinutesSeconds()

PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder::setTimeHoursMinutesSeconds ( string  $value,
Cell  $cell 
)
protected

Definition at line 192 of file AdvancedValueBinder.php.

References PhpOffice\PhpSpreadsheet\Style\NumberFormat\FORMAT_DATE_TIME4, PhpOffice\PhpSpreadsheet\Cell\Cell\getCoordinate(), PhpOffice\PhpSpreadsheet\Cell\Cell\getWorksheet(), PhpOffice\PhpSpreadsheet\Cell\Cell\setValueExplicit(), and PhpOffice\PhpSpreadsheet\Cell\DataType\TYPE_NUMERIC.

Referenced by PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder\bindValue().

192  : bool
193  {
194  // Convert value to number
195  [$hours, $minutes, $seconds] = explode(':', $value);
196  $days = ($hours / 24) + ($minutes / 1440) + ($seconds / 86400);
197  $cell->setValueExplicit($days, DataType::TYPE_NUMERIC);
198 
199  // Set style
200  $cell->getWorksheet()->getStyle($cell->getCoordinate())
201  ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_TIME4);
202 
203  return true;
204  }
+ Here is the call graph for this function:
+ Here is the caller graph for this function:
The documentation for this class was generated from the following file: