1: <?php
2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26:
27:
28:
29:
30: if (!defined('PHPEXCEL_ROOT')) {
31: 32: 33:
34: define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35: require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36: }
37:
38:
39: 40: 41: 42: 43: 44: 45:
46: class PHPExcel_Cell_AdvancedValueBinder extends PHPExcel_Cell_DefaultValueBinder implements PHPExcel_Cell_IValueBinder
47: {
48: 49: 50: 51: 52: 53: 54:
55: public function bindValue(PHPExcel_Cell $cell, $value = null)
56: {
57:
58: if (is_string($value)) {
59: $value = PHPExcel_Shared_String::SanitizeUTF8($value);
60: }
61:
62:
63: $dataType = parent::dataTypeForValue($value);
64:
65:
66: if ($dataType === PHPExcel_Cell_DataType::TYPE_STRING && !$value instanceof PHPExcel_RichText) {
67:
68: if ($value == PHPExcel_Calculation::getTRUE()) {
69: $cell->setValueExplicit( TRUE, PHPExcel_Cell_DataType::TYPE_BOOL);
70: return true;
71: } elseif($value == PHPExcel_Calculation::getFALSE()) {
72: $cell->setValueExplicit( FALSE, PHPExcel_Cell_DataType::TYPE_BOOL);
73: return true;
74: }
75:
76:
77: if (preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_NUMBER.'$/', $value)) {
78: $cell->setValueExplicit( (float) $value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
79: return true;
80: }
81:
82:
83: if (preg_match('/^([+-]?)\s*([0-9]+)\s?\/\s*([0-9]+)$/', $value, $matches)) {
84:
85: $value = $matches[2] / $matches[3];
86: if ($matches[1] == '-') $value = 0 - $value;
87: $cell->setValueExplicit( (float) $value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
88:
89: $cell->getWorksheet()->getStyle( $cell->getCoordinate() )
90: ->getNumberFormat()->setFormatCode( '??/??' );
91: return true;
92: } elseif (preg_match('/^([+-]?)([0-9]*) +([0-9]*)\s?\/\s*([0-9]*)$/', $value, $matches)) {
93:
94: $value = $matches[2] + ($matches[3] / $matches[4]);
95: if ($matches[1] == '-') $value = 0 - $value;
96: $cell->setValueExplicit( (float) $value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
97:
98: $cell->getWorksheet()->getStyle( $cell->getCoordinate() )
99: ->getNumberFormat()->setFormatCode( '# ??/??' );
100: return true;
101: }
102:
103:
104: if (preg_match('/^\-?[0-9]*\.?[0-9]*\s?\%$/', $value)) {
105:
106: $value = (float) str_replace('%', '', $value) / 100;
107: $cell->setValueExplicit( $value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
108:
109: $cell->getWorksheet()->getStyle( $cell->getCoordinate() )
110: ->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00 );
111: return true;
112: }
113:
114:
115: $currencyCode = PHPExcel_Shared_String::getCurrencyCode();
116: $decimalSeparator = PHPExcel_Shared_String::getDecimalSeparator();
117: $thousandsSeparator = PHPExcel_Shared_String::getThousandsSeparator();
118: if (preg_match('/^'.preg_quote($currencyCode).' *(\d{1,3}('.preg_quote($thousandsSeparator).'\d{3})*|(\d+))('.preg_quote($decimalSeparator).'\d{2})?$/', $value)) {
119:
120: $value = (float) trim(str_replace(array($currencyCode, $thousandsSeparator, $decimalSeparator), array('', '', '.'), $value));
121: $cell->setValueExplicit( $value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
122:
123: $cell->getWorksheet()->getStyle( $cell->getCoordinate() )
124: ->getNumberFormat()->setFormatCode(
125: str_replace('$', $currencyCode, PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE )
126: );
127: return true;
128: } elseif (preg_match('/^\$ *(\d{1,3}(\,\d{3})*|(\d+))(\.\d{2})?$/', $value)) {
129:
130: $value = (float) trim(str_replace(array('$',','), '', $value));
131: $cell->setValueExplicit( $value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
132:
133: $cell->getWorksheet()->getStyle( $cell->getCoordinate() )
134: ->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE );
135: return true;
136: }
137:
138:
139: if (preg_match('/^(\d|[0-1]\d|2[0-3]):[0-5]\d$/', $value)) {
140:
141: list($h, $m) = explode(':', $value);
142: $days = $h / 24 + $m / 1440;
143: $cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC);
144:
145: $cell->getWorksheet()->getStyle( $cell->getCoordinate() )
146: ->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3 );
147: return true;
148: }
149:
150:
151: if (preg_match('/^(\d|[0-1]\d|2[0-3]):[0-5]\d:[0-5]\d$/', $value)) {
152:
153: list($h, $m, $s) = explode(':', $value);
154: $days = $h / 24 + $m / 1440 + $s / 86400;
155:
156: $cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC);
157:
158: $cell->getWorksheet()->getStyle( $cell->getCoordinate() )
159: ->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4 );
160: return true;
161: }
162:
163:
164: if (($d = PHPExcel_Shared_Date::stringToExcel($value)) !== false) {
165:
166: $cell->setValueExplicit($d, PHPExcel_Cell_DataType::TYPE_NUMERIC);
167:
168: if (strpos($value, ':') !== false) {
169: $formatCode = 'yyyy-mm-dd h:mm';
170: } else {
171: $formatCode = 'yyyy-mm-dd';
172: }
173: $cell->getWorksheet()->getStyle( $cell->getCoordinate() )
174: ->getNumberFormat()->setFormatCode($formatCode);
175: return true;
176: }
177:
178:
179: if (strpos($value, "\n") !== FALSE) {
180: $value = PHPExcel_Shared_String::SanitizeUTF8($value);
181: $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
182:
183: $cell->getWorksheet()->getStyle( $cell->getCoordinate() )
184: ->getAlignment()->setWrapText(TRUE);
185: return true;
186: }
187: }
188:
189:
190: return parent::bindValue($cell, $value);
191: }
192: }
193: