5 * Copyright (c) 2006 - 2014 PHPExcel
7 * This library is free software; you can redistribute it and/or
8 * modify it under the terms of the GNU Lesser General Public
9 * License as published by the Free Software Foundation; either
10 * version 2.1 of the License, or (at your option) any later version.
12 * This library is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15 * Lesser General Public License for more details.
17 * You should have received a copy of the GNU Lesser General Public
18 * License along with this library; if not, write to the Free Software
19 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
22 * @package PHPExcel_Cell
23 * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
24 * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25 * @version ##VERSION##, ##DATE##
29 /** PHPExcel root directory */
30 if (!defined('PHPEXCEL_ROOT')) {
34 define('PHPEXCEL_ROOT', dirname(__FILE__
) . '/../../');
35 require(PHPEXCEL_ROOT
. 'PHPExcel/Autoloader.php');
40 * PHPExcel_Cell_AdvancedValueBinder
43 * @package PHPExcel_Cell
44 * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
46 class PHPExcel_Cell_AdvancedValueBinder
extends PHPExcel_Cell_DefaultValueBinder
implements PHPExcel_Cell_IValueBinder
49 * Bind value to a cell
51 * @param PHPExcel_Cell $cell Cell to bind value to
52 * @param mixed $value Value to bind in cell
55 public function bindValue(PHPExcel_Cell
$cell, $value = null)
57 // sanitize UTF-8 strings
58 if (is_string($value)) {
59 $value = PHPExcel_Shared_String
::SanitizeUTF8($value);
63 $dataType = parent
::dataTypeForValue($value);
65 // Style logic - strings
66 if ($dataType === PHPExcel_Cell_DataType
::TYPE_STRING
&& !$value instanceof PHPExcel_RichText
) {
67 // Test for booleans using locale-setting
68 if ($value == PHPExcel_Calculation
::getTRUE()) {
69 $cell->setValueExplicit( TRUE, PHPExcel_Cell_DataType
::TYPE_BOOL
);
71 } elseif($value == PHPExcel_Calculation
::getFALSE()) {
72 $cell->setValueExplicit( FALSE, PHPExcel_Cell_DataType
::TYPE_BOOL
);
76 // Check for number in scientific format
77 if (preg_match('/^'.PHPExcel_Calculation
::CALCULATION_REGEXP_NUMBER
.'$/', $value)) {
78 $cell->setValueExplicit( (float) $value, PHPExcel_Cell_DataType
::TYPE_NUMERIC
);
83 if (preg_match('/^([+-]?)\s*([0-9]+)\s?\/\s*([0-9]+)$/', $value, $matches)) {
84 // Convert value to number
85 $value = $matches[2] / $matches[3];
86 if ($matches[1] == '-') $value = 0 - $value;
87 $cell->setValueExplicit( (float) $value, PHPExcel_Cell_DataType
::TYPE_NUMERIC
);
89 $cell->getWorksheet()->getStyle( $cell->getCoordinate() )
90 ->getNumberFormat()->setFormatCode( '??/??' );
92 } elseif (preg_match('/^([+-]?)([0-9]*) +([0-9]*)\s?\/\s*([0-9]*)$/', $value, $matches)) {
93 // Convert value to number
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
);
98 $cell->getWorksheet()->getStyle( $cell->getCoordinate() )
99 ->getNumberFormat()->setFormatCode( '# ??/??' );
103 // Check for percentage
104 if (preg_match('/^\-?[0-9]*\.?[0-9]*\s?\%$/', $value)) {
105 // Convert value to number
106 $value = (float) str_replace('%', '', $value) / 100;
107 $cell->setValueExplicit( $value, PHPExcel_Cell_DataType
::TYPE_NUMERIC
);
109 $cell->getWorksheet()->getStyle( $cell->getCoordinate() )
110 ->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat
::FORMAT_PERCENTAGE_00
);
114 // Check for currency
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 // Convert value to number
120 $value = (float) trim(str_replace(array($currencyCode, $thousandsSeparator, $decimalSeparator), array('', '', '.'), $value));
121 $cell->setValueExplicit( $value, PHPExcel_Cell_DataType
::TYPE_NUMERIC
);
123 $cell->getWorksheet()->getStyle( $cell->getCoordinate() )
124 ->getNumberFormat()->setFormatCode(
125 str_replace('$', $currencyCode, PHPExcel_Style_NumberFormat
::FORMAT_CURRENCY_USD_SIMPLE
)
128 } elseif (preg_match('/^\$ *(\d{1,3}(\,\d{3})*|(\d+))(\.\d{2})?$/', $value)) {
129 // Convert value to number
130 $value = (float) trim(str_replace(array('$',','), '', $value));
131 $cell->setValueExplicit( $value, PHPExcel_Cell_DataType
::TYPE_NUMERIC
);
133 $cell->getWorksheet()->getStyle( $cell->getCoordinate() )
134 ->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat
::FORMAT_CURRENCY_USD_SIMPLE
);
138 // Check for time without seconds e.g. '9:45', '09:45'
139 if (preg_match('/^(\d|[0-1]\d|2[0-3]):[0-5]\d$/', $value)) {
140 // Convert value to number
141 list($h, $m) = explode(':', $value);
142 $days = $h / 24 +
$m / 1440;
143 $cell->setValueExplicit($days, PHPExcel_Cell_DataType
::TYPE_NUMERIC
);
145 $cell->getWorksheet()->getStyle( $cell->getCoordinate() )
146 ->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat
::FORMAT_DATE_TIME3
);
150 // Check for time with seconds '9:45:59', '09:45:59'
151 if (preg_match('/^(\d|[0-1]\d|2[0-3]):[0-5]\d:[0-5]\d$/', $value)) {
152 // Convert value to number
153 list($h, $m, $s) = explode(':', $value);
154 $days = $h / 24 +
$m / 1440 +
$s / 86400;
155 // Convert value to number
156 $cell->setValueExplicit($days, PHPExcel_Cell_DataType
::TYPE_NUMERIC
);
158 $cell->getWorksheet()->getStyle( $cell->getCoordinate() )
159 ->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat
::FORMAT_DATE_TIME4
);
163 // Check for datetime, e.g. '2008-12-31', '2008-12-31 15:59', '2008-12-31 15:59:10'
164 if (($d = PHPExcel_Shared_Date
::stringToExcel($value)) !== false) {
165 // Convert value to number
166 $cell->setValueExplicit($d, PHPExcel_Cell_DataType
::TYPE_NUMERIC
);
167 // Determine style. Either there is a time part or not. Look for ':'
168 if (strpos($value, ':') !== false) {
169 $formatCode = 'yyyy-mm-dd h:mm';
171 $formatCode = 'yyyy-mm-dd';
173 $cell->getWorksheet()->getStyle( $cell->getCoordinate() )
174 ->getNumberFormat()->setFormatCode($formatCode);
178 // Check for newline character "\n"
179 if (strpos($value, "\n") !== FALSE) {
180 $value = PHPExcel_Shared_String
::SanitizeUTF8($value);
181 $cell->setValueExplicit($value, PHPExcel_Cell_DataType
::TYPE_STRING
);
183 $cell->getWorksheet()->getStyle( $cell->getCoordinate() )
184 ->getAlignment()->setWrapText(TRUE);
189 // Not bound yet? Use parent...
190 return parent
::bindValue($cell, $value);