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...
 
 bindValue (Cell $cell, $value)
 Bind value to a cell. More...
 
 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

Reimplemented from PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder.

Definition at line 21 of file AdvancedValueBinder.php.

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\Calculation\Calculation\getTRUE
static getTRUE()
Return the locale-specific translation of TRUE.
Definition: Calculation.php:2834
PhpOffice\PhpSpreadsheet\Calculation\Calculation\getFALSE
static getFALSE()
Return the locale-specific translation of FALSE.
Definition: Calculation.php:2844
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
PhpOffice\PhpSpreadsheet\Shared\StringHelper\getCurrencyCode
static getCurrencyCode()
Get the currency code.
Definition: StringHelper.php:650
PhpOffice\PhpSpreadsheet\Shared\StringHelper\sanitizeUTF8
static sanitizeUTF8($value)
Try to sanitize UTF8, stripping invalid byte sequences.
Definition: StringHelper.php:337
$d
for( $i=6;$i< 13;$i++) for($i=1; $i< 13; $i++) $d
Definition: date.php:296

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.

+ 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.

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 }

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().

+ 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.

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 }

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().

+ 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.

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 }

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().

+ 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.

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 }

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().

+ 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.

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 }

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().

+ 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: