/** * @dataProvider binderProvider */ public function testBindValue($value) { $this->createCellStub(); $binder = new PHPExcel_Cell_DefaultValueBinder(); $result = $binder->bindValue($this->cellStub, $value); $this->assertTrue($result); }
/** * Bind value to a cell, preserving possible leading zeros * See http://stackoverflow.com/questions/12457610/reading-numbers-as-text-format-with-phpexcel * * @param PHPExcel_Cell $cell Cell to bind value to * @param mixed $value Value to bind in cell * @return boolean */ public function bindValue(PHPExcel_Cell $cell, $value = null) { // sanitize UTF-8 strings if (is_string($value)) { $value = PHPExcel_Shared_String::SanitizeUTF8($value); } // Preserve numeric string, including leading zeros, if it is a text format $format = $cell->getStyle()->getNumberFormat()->getFormatCode(); if ($format == PHPExcel_Style_NumberFormat::FORMAT_TEXT) { $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING); return true; } // Not bound yet? Use default value parent... return parent::bindValue($cell, $value); }
/** * DataType for value * * @deprecated Replaced by PHPExcel_Cell_IValueBinder infrastructure * @param mixed $pValue * @return int */ public static function dataTypeForValue($pValue = null) { return PHPExcel_Cell_DefaultValueBinder::dataTypeForValue($pValue); }
/** * Bind value to a cell * * @param PHPExcel_Cell $cell Cell to bind value to * @param mixed $value Value to bind in cell * @return boolean */ public function bindValue(PHPExcel_Cell $cell, $value = null) { // sanitize UTF-8 strings if (is_string($value)) { $value = PHPExcel_Shared_String::SanitizeUTF8($value); } // Find out data type $dataType = parent::dataTypeForValue($value); // Style logic - strings if ($dataType === PHPExcel_Cell_DataType::TYPE_STRING && !$value instanceof PHPExcel_RichText) { // Check for percentage if (preg_match('/^\\-?[0-9]*\\.?[0-9]*\\s?\\%$/', $value)) { // Convert value to number $cell->setValueExplicit((double) str_replace('%', '', $value) / 100, PHPExcel_Cell_DataType::TYPE_NUMERIC); // Set style $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE); return true; } // Check for time e.g. '9:45', '09:45' if (preg_match('/^(\\d|[0-1]\\d|2[0-3]):[0-5]\\d$/', $value)) { list($h, $m) = explode(':', $value); $days = $h / 24 + $m / 1440; // Convert value to number $cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC); // Set style $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3); return true; } // Check for date if (strtotime($value) !== false) { // make sure we have UTC for the sake of strtotime $saveTimeZone = date_default_timezone_get(); date_default_timezone_set('UTC'); // Convert value to Excel date $cell->setValueExplicit(PHPExcel_Shared_Date::PHPToExcel(strtotime($value)), PHPExcel_Cell_DataType::TYPE_NUMERIC); // Set style $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2); // restore original value for timezone date_default_timezone_set($saveTimeZone); return true; } } // Style logic - Numbers if ($dataType === PHPExcel_Cell_DataType::TYPE_NUMERIC) { // Leading zeroes? if (preg_match('/^\\-?[0]+[0-9]*\\.?[0-9]*$/', $value)) { // Convert value to string $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING); // Set style $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); return true; } } // Not bound yet? Use parent... return parent::bindValue($cell, $value); }
/** * Bind value to a cell * * @param PHPExcel_Cell $cell Cell to bind value to * @param mixed $value Value to bind in cell * @return boolean */ public function bindValue(PHPExcel_Cell $cell, $value = null) { // sanitize UTF-8 strings if (is_string($value)) { $value = PHPExcel_Shared_String::SanitizeUTF8($value); } // Find out data type $dataType = parent::dataTypeForValue($value); // Style logic - strings if ($dataType === PHPExcel_Cell_DataType::TYPE_STRING && !$value instanceof PHPExcel_RichText) { // Check for percentage if (preg_match('/^\\-?[0-9]*\\.?[0-9]*\\s?\\%$/', $value)) { // Convert value to number $cell->setValueExplicit((double) str_replace('%', '', $value) / 100, PHPExcel_Cell_DataType::TYPE_NUMERIC); // Set style $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE); return true; } // Check for time without seconds e.g. '9:45', '09:45' if (preg_match('/^(\\d|[0-1]\\d|2[0-3]):[0-5]\\d$/', $value)) { list($h, $m) = explode(':', $value); $days = $h / 24 + $m / 1440; // Convert value to number $cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC); // Set style $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3); return true; } // Check for time with seconds '9:45:59', '09:45:59' if (preg_match('/^(\\d|[0-1]\\d|2[0-3]):[0-5]\\d:[0-5]\\d$/', $value)) { list($h, $m, $s) = explode(':', $value); $days = $h / 24 + $m / 1440 + $s / 86400; // Convert value to number $cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC); // Set style $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4); return true; } // Check for datetime, e.g. '2008-12-31', '2008-12-31 15:59', '2008-12-31 15:59:10' if (($v = PHPExcel_Shared_Date::stringToExcel($value)) !== false) { // Convert value to Excel date $cell->setValueExplicit($v, PHPExcel_Cell_DataType::TYPE_NUMERIC); // Set style. Either there is a time part or not. Look for ':' if (strpos($value, ':') !== false) { $formatCode = 'yyyy-mm-dd h:mm'; } else { $formatCode = 'yyyy-mm-dd'; } $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode($formatCode); return true; } // Check for newline character "\n" if (strpos($value, "\n") !== false) { $value = PHPExcel_Shared_String::SanitizeUTF8($value); $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING); // Set style $cell->getParent()->getStyle($cell->getCoordinate())->getAlignment()->setWrapText(true); return true; } } // Not bound yet? Use parent... return parent::bindValue($cell, $value); }
/** * Bind value to a cell * * @param PHPExcel_Cell $cell Cell to bind value to * @param mixed $value Value to bind in cell * @return boolean */ public function bindValue(PHPExcel_Cell $cell, $value = null) { // sanitize UTF-8 strings if (is_string($value)) { $value = PHPExcel_Shared_String::SanitizeUTF8($value); } // Find out data type $dataType = parent::dataTypeForValue($value); // Style logic - strings if ($dataType === PHPExcel_Cell_DataType::TYPE_STRING && !$value instanceof PHPExcel_RichText) { // Test for booleans using locale-setting if ($value == PHPExcel_Calculation::getTRUE()) { $cell->setValueExplicit(TRUE, PHPExcel_Cell_DataType::TYPE_BOOL); return true; } elseif ($value == PHPExcel_Calculation::getFALSE()) { $cell->setValueExplicit(FALSE, PHPExcel_Cell_DataType::TYPE_BOOL); return true; } // Check for number in scientific format if (preg_match('/^' . PHPExcel_Calculation::CALCULATION_REGEXP_NUMBER . '$/', $value)) { $cell->setValueExplicit((double) $value, PHPExcel_Cell_DataType::TYPE_NUMERIC); return true; } // Check for fraction if (preg_match('/^([+-]?) *([0-9]*)\\s?\\/\\s*([0-9]*)$/', $value, $matches)) { // Convert value to number $value = $matches[2] / $matches[3]; if ($matches[1] == '-') { $value = 0 - $value; } $cell->setValueExplicit((double) $value, PHPExcel_Cell_DataType::TYPE_NUMERIC); // Set style $cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode('??/??'); return true; } elseif (preg_match('/^([+-]?)([0-9]*) +([0-9]*)\\s?\\/\\s*([0-9]*)$/', $value, $matches)) { // Convert value to number $value = $matches[2] + $matches[3] / $matches[4]; if ($matches[1] == '-') { $value = 0 - $value; } $cell->setValueExplicit((double) $value, PHPExcel_Cell_DataType::TYPE_NUMERIC); // Set style $cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode('# ??/??'); return true; } // Check for percentage if (preg_match('/^\\-?[0-9]*\\.?[0-9]*\\s?\\%$/', $value)) { // Convert value to number $value = (double) str_replace('%', '', $value) / 100; $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_NUMERIC); // Set style $cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00); return true; } // Check for currency $currencyCode = PHPExcel_Shared_String::getCurrencyCode(); $decimalSeparator = PHPExcel_Shared_String::getDecimalSeparator(); $thousandsSeparator = PHPExcel_Shared_String::getThousandsSeparator(); if (preg_match('/^' . preg_quote($currencyCode) . ' *(\\d{1,3}(' . preg_quote($thousandsSeparator) . '\\d{3})*|(\\d+))(' . preg_quote($decimalSeparator) . '\\d{2})?$/', $value)) { // Convert value to number $value = (double) trim(str_replace(array($currencyCode, $thousandsSeparator, $decimalSeparator), array('', '', '.'), $value)); $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_NUMERIC); // Set style $cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(str_replace('$', $currencyCode, PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE)); return true; } elseif (preg_match('/^\\$ *(\\d{1,3}(\\,\\d{3})*|(\\d+))(\\.\\d{2})?$/', $value)) { // Convert value to number $value = (double) trim(str_replace(array('$', ','), '', $value)); $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_NUMERIC); // Set style $cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE); return true; } // Check for time without seconds e.g. '9:45', '09:45' if (preg_match('/^(\\d|[0-1]\\d|2[0-3]):[0-5]\\d$/', $value)) { // Convert value to number list($h, $m) = explode(':', $value); $days = $h / 24 + $m / 1440; $cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC); // Set style $cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3); return true; } // Check for time with seconds '9:45:59', '09:45:59' if (preg_match('/^(\\d|[0-1]\\d|2[0-3]):[0-5]\\d:[0-5]\\d$/', $value)) { // Convert value to number list($h, $m, $s) = explode(':', $value); $days = $h / 24 + $m / 1440 + $s / 86400; // Convert value to number $cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC); // Set style $cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4); return true; } // Check for datetime, e.g. '2008-12-31', '2008-12-31 15:59', '2008-12-31 15:59:10' if (($d = PHPExcel_Shared_Date::stringToExcel($value)) !== false) { // Convert value to number $cell->setValueExplicit($d, PHPExcel_Cell_DataType::TYPE_NUMERIC); // Determine style. Either there is a time part or not. Look for ':' if (strpos($value, ':') !== false) { $formatCode = 'yyyy-mm-dd h:mm'; } else { $formatCode = 'yyyy-mm-dd'; } $cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode($formatCode); return true; } // Check for newline character "\n" if (strpos($value, "\n") !== FALSE) { $value = PHPExcel_Shared_String::SanitizeUTF8($value); $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING); // Set style $cell->getWorksheet()->getStyle($cell->getCoordinate())->getAlignment()->setWrapText(TRUE); return true; } } // Not bound yet? Use parent... return parent::bindValue($cell, $value); }
protected function _EXCEL_SET($elem) { list($excel, $pos) = $this->getStruct('EXCEL'); $value = $elem[SIGN_CDATA]; ($coords = $elem['COORDS']) === '' || $this->getExcelCoords($excel, $pos, $coords); switch ($type = $elem['TYPE']) { case 'text': $exceltype = \PHPExcel_Cell_DataType::TYPE_STRING; break; case 'bool': $exceltype = \PHPExcel_Cell_DataType::TYPE_BOOL; break; case 'numeric': case 'date': $date = $type === 'date'; if (is_numeric($value) || $date && ($value = parseTime($value)) !== null) { $exceltype = \PHPExcel_Cell_DataType::TYPE_NUMERIC; $date and $value = \PHPExcel_Shared_Date::PHPToExcel($value); break; } case 'null': $exceltype = \PHPExcel_Cell_DataType::TYPE_NULL; $value = ''; break; default: // auto $exceltype = \PHPExcel_Cell_DefaultValueBinder::dataTypeForValue($value); } list($sheet, $row, $col) = $pos; $row++; $sheet = $excel->getSheet($sheet); $sheet->getCellByColumnAndRow($col, $row)->setValueExplicit($value, $exceltype); ($format = $elem['FORMAT']) === null || $sheet->getStyleByColumnAndRow($col, $row)->getNumberFormat()->setFormatCode($format); }
/** * Bind value to a cell * * @param PHPExcel_Cell $cell Cell to bind value to * @param mixed $value Value to bind in cell * @return boolean */ public function bindValue(PHPExcel_Cell $cell, $value = null) { // Find out data type $dataType = parent::dataTypeForValue($value); // Style logic - strings if ($dataType === PHPExcel_Cell_DataType::TYPE_STRING && !$value instanceof PHPExcel_RichText) { // Check for percentage if (preg_match('/^\\-?[0-9]*\\.?[0-9]*\\s?\\%$/', $value)) { // Convert value to number $cell->setValueExplicit((double) str_replace('%', '', $value) / 100, PHPExcel_Cell_DataType::TYPE_NUMERIC); // Set style $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE); return true; } // Check for date if (strtotime($value) !== false) { // Convert value to Excel date $cell->setValueExplicit(PHPExcel_Shared_Date::PHPToExcel(strtotime($value)), PHPExcel_Cell_DataType::TYPE_NUMERIC); // Set style $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2); return true; } } // Style logic - Numbers if ($dataType === PHPExcel_Cell_DataType::TYPE_NUMERIC) { // Leading zeroes? if (preg_match('/^\\-?[0]+[0-9]*\\.?[0-9]*$/', $value)) { // Convert value to string $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING); // Set style $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); return true; } } // Not bound yet? Use parent... return parent::bindValue($cell, $value); }