1<?php 2 3/** PHPExcel root directory */ 4if (!defined('PHPEXCEL_ROOT')) { 5 /** 6 * @ignore 7 */ 8 define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../'); 9 require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php'); 10} 11 12/** 13 * PHPExcel_Cell_AdvancedValueBinder 14 * 15 * Copyright (c) 2006 - 2015 PHPExcel 16 * 17 * This library is free software; you can redistribute it and/or 18 * modify it under the terms of the GNU Lesser General Public 19 * License as published by the Free Software Foundation; either 20 * version 2.1 of the License, or (at your option) any later version. 21 * 22 * This library is distributed in the hope that it will be useful, 23 * but WITHOUT ANY WARRANTY; without even the implied warranty of 24 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 25 * Lesser General Public License for more details. 26 * 27 * You should have received a copy of the GNU Lesser General Public 28 * License along with this library; if not, write to the Free Software 29 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA 30 * 31 * @category PHPExcel 32 * @package PHPExcel_Cell 33 * @copyright Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel) 34 * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL 35 * @version ##VERSION##, ##DATE## 36 */ 37class PHPExcel_Cell_AdvancedValueBinder extends PHPExcel_Cell_DefaultValueBinder implements PHPExcel_Cell_IValueBinder 38{ 39 /** 40 * Bind value to a cell 41 * 42 * @param PHPExcel_Cell $cell Cell to bind value to 43 * @param mixed $value Value to bind in cell 44 * @return boolean 45 */ 46 public function bindValue(PHPExcel_Cell $cell, $value = null) 47 { 48 // sanitize UTF-8 strings 49 if (is_string($value)) { 50 $value = PHPExcel_Shared_String::SanitizeUTF8($value); 51 } 52 53 // Find out data type 54 $dataType = parent::dataTypeForValue($value); 55 56 // Style logic - strings 57 if ($dataType === PHPExcel_Cell_DataType::TYPE_STRING && !$value instanceof PHPExcel_RichText) { 58 // Test for booleans using locale-setting 59 if ($value == PHPExcel_Calculation::getTRUE()) { 60 $cell->setValueExplicit(true, PHPExcel_Cell_DataType::TYPE_BOOL); 61 return true; 62 } elseif ($value == PHPExcel_Calculation::getFALSE()) { 63 $cell->setValueExplicit(false, PHPExcel_Cell_DataType::TYPE_BOOL); 64 return true; 65 } 66 67 // Check for number in scientific format 68 if (preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_NUMBER.'$/', $value)) { 69 $cell->setValueExplicit((float) $value, PHPExcel_Cell_DataType::TYPE_NUMERIC); 70 return true; 71 } 72 73 // Check for fraction 74 if (preg_match('/^([+-]?)\s*([0-9]+)\s?\/\s*([0-9]+)$/', $value, $matches)) { 75 // Convert value to number 76 $value = $matches[2] / $matches[3]; 77 if ($matches[1] == '-') { 78 $value = 0 - $value; 79 } 80 $cell->setValueExplicit((float) $value, PHPExcel_Cell_DataType::TYPE_NUMERIC); 81 // Set style 82 $cell->getWorksheet()->getStyle($cell->getCoordinate()) 83 ->getNumberFormat()->setFormatCode('??/??'); 84 return true; 85 } elseif (preg_match('/^([+-]?)([0-9]*) +([0-9]*)\s?\/\s*([0-9]*)$/', $value, $matches)) { 86 // Convert value to number 87 $value = $matches[2] + ($matches[3] / $matches[4]); 88 if ($matches[1] == '-') { 89 $value = 0 - $value; 90 } 91 $cell->setValueExplicit((float) $value, PHPExcel_Cell_DataType::TYPE_NUMERIC); 92 // Set style 93 $cell->getWorksheet()->getStyle($cell->getCoordinate()) 94 ->getNumberFormat()->setFormatCode('# ??/??'); 95 return true; 96 } 97 98 // Check for percentage 99 if (preg_match('/^\-?[0-9]*\.?[0-9]*\s?\%$/', $value)) { 100 // Convert value to number 101 $value = (float) str_replace('%', '', $value) / 100; 102 $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_NUMERIC); 103 // Set style 104 $cell->getWorksheet()->getStyle($cell->getCoordinate()) 105 ->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00); 106 return true; 107 } 108 109 // Check for currency 110 $currencyCode = PHPExcel_Shared_String::getCurrencyCode(); 111 $decimalSeparator = PHPExcel_Shared_String::getDecimalSeparator(); 112 $thousandsSeparator = PHPExcel_Shared_String::getThousandsSeparator(); 113 if (preg_match('/^'.preg_quote($currencyCode).' *(\d{1,3}('.preg_quote($thousandsSeparator).'\d{3})*|(\d+))('.preg_quote($decimalSeparator).'\d{2})?$/', $value)) { 114 // Convert value to number 115 $value = (float) trim(str_replace(array($currencyCode, $thousandsSeparator, $decimalSeparator), array('', '', '.'), $value)); 116 $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_NUMERIC); 117 // Set style 118 $cell->getWorksheet()->getStyle($cell->getCoordinate()) 119 ->getNumberFormat()->setFormatCode( 120 str_replace('$', $currencyCode, PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE) 121 ); 122 return true; 123 } elseif (preg_match('/^\$ *(\d{1,3}(\,\d{3})*|(\d+))(\.\d{2})?$/', $value)) { 124 // Convert value to number 125 $value = (float) trim(str_replace(array('$',','), '', $value)); 126 $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_NUMERIC); 127 // Set style 128 $cell->getWorksheet()->getStyle($cell->getCoordinate()) 129 ->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE); 130 return true; 131 } 132 133 // Check for time without seconds e.g. '9:45', '09:45' 134 if (preg_match('/^(\d|[0-1]\d|2[0-3]):[0-5]\d$/', $value)) { 135 // Convert value to number 136 list($h, $m) = explode(':', $value); 137 $days = $h / 24 + $m / 1440; 138 $cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC); 139 // Set style 140 $cell->getWorksheet()->getStyle($cell->getCoordinate()) 141 ->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3); 142 return true; 143 } 144 145 // Check for time with seconds '9:45:59', '09:45:59' 146 if (preg_match('/^(\d|[0-1]\d|2[0-3]):[0-5]\d:[0-5]\d$/', $value)) { 147 // Convert value to number 148 list($h, $m, $s) = explode(':', $value); 149 $days = $h / 24 + $m / 1440 + $s / 86400; 150 // Convert value to number 151 $cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC); 152 // Set style 153 $cell->getWorksheet()->getStyle($cell->getCoordinate()) 154 ->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4); 155 return true; 156 } 157 158 // Check for datetime, e.g. '2008-12-31', '2008-12-31 15:59', '2008-12-31 15:59:10' 159 if (($d = PHPExcel_Shared_Date::stringToExcel($value)) !== false) { 160 // Convert value to number 161 $cell->setValueExplicit($d, PHPExcel_Cell_DataType::TYPE_NUMERIC); 162 // Determine style. Either there is a time part or not. Look for ':' 163 if (strpos($value, ':') !== false) { 164 $formatCode = 'yyyy-mm-dd h:mm'; 165 } else { 166 $formatCode = 'yyyy-mm-dd'; 167 } 168 $cell->getWorksheet()->getStyle($cell->getCoordinate()) 169 ->getNumberFormat()->setFormatCode($formatCode); 170 return true; 171 } 172 173 // Check for newline character "\n" 174 if (strpos($value, "\n") !== false) { 175 $value = PHPExcel_Shared_String::SanitizeUTF8($value); 176 $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING); 177 // Set style 178 $cell->getWorksheet()->getStyle($cell->getCoordinate()) 179 ->getAlignment()->setWrapText(true); 180 return true; 181 } 182 } 183 184 // Not bound yet? Use parent... 185 return parent::bindValue($cell, $value); 186 } 187} 188