/** * Convert a value in a pre-defined format to a PHP string * * @param mixed $value Value to format * @param string $format Format code * @param array $callBack Callback function for additional formatting of string * @return string Formatted string */ public static function toFormattedString($value = '', $format = '', $callBack = null) { // For now we do not treat strings although section 4 of a format code affects strings if (!is_numeric($value)) { return $value; } // For 'General' format code, we just pass the value although this is not entirely the way Excel does it, // it seems to round numbers to a total of 10 digits. if ($format === PHPExcel_Style_NumberFormat::FORMAT_GENERAL || $format === PHPExcel_Style_NumberFormat::FORMAT_TEXT) { return $value; } // Get the sections, there can be up to four sections $sections = explode(';', $format); // Fetch the relevant section depending on whether number is positive, negative, or zero? // Text not supported yet. // Here is how the sections apply to various values in Excel: // 1 section: [POSITIVE/NEGATIVE/ZERO/TEXT] // 2 sections: [POSITIVE/ZERO/TEXT] [NEGATIVE] // 3 sections: [POSITIVE/TEXT] [NEGATIVE] [ZERO] // 4 sections: [POSITIVE] [NEGATIVE] [ZERO] [TEXT] switch (count($sections)) { case 1: $format = $sections[0]; break; case 2: $format = $value >= 0 ? $sections[0] : $sections[1]; $value = abs($value); // Use the absolute value break; case 3: $format = $value > 0 ? $sections[0] : ($value < 0 ? $sections[1] : $sections[2]); $value = abs($value); // Use the absolute value break; case 4: $format = $value > 0 ? $sections[0] : ($value < 0 ? $sections[1] : $sections[2]); $value = abs($value); // Use the absolute value break; default: // something is wrong, just use first section $format = $sections[0]; break; } // Save format with color information for later use below $formatColor = $format; // Strip color information $color_regex = '/^\\[[a-zA-Z]+\\]/'; $format = preg_replace($color_regex, '', $format); // Let's begin inspecting the format and converting the value to a formatted string if (preg_match('/^(\\[\\$[A-Z]*-[0-9A-F]*\\])*[hmsdy]/i', $format)) { // datetime format // dvc: convert Excel formats to PHP date formats // strip off first part containing e.g. [$-F800] or [$USD-409] // general syntax: [$<Currency string>-<language info>] // language info is in hexadecimal $format = preg_replace('/^(\\[\\$[A-Z]*-[0-9A-F]*\\])/i', '', $format); // OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case $format = strtolower($format); $format = strtr($format, self::$_dateFormatReplacements); if (!strpos($format, 'A')) { // 24-hour time format $format = strtr($format, self::$_dateFormatReplacements24); } else { // 12-hour time format $format = strtr($format, self::$_dateFormatReplacements12); } $dateObj = PHPExcel_Shared_Date::ExcelToPHPObject($value); $value = $dateObj->format($format); } else { if (preg_match('/%$/', $format)) { // % number format if ($format === self::FORMAT_PERCENTAGE) { $value = round(100 * $value, 0) . '%'; } else { if (preg_match('/\\.[#0]+/i', $format, $m)) { $s = substr($m[0], 0, 1) . (strlen($m[0]) - 1); $format = str_replace($m[0], $s, $format); } if (preg_match('/^[#0]+/', $format, $m)) { $format = str_replace($m[0], strlen($m[0]), $format); } $format = '%' . str_replace('%', 'f%%', $format); $value = sprintf($format, 100 * $value); } } else { if ($format === self::FORMAT_CURRENCY_EUR_SIMPLE) { $value = 'EUR ' . sprintf('%1.2f', $value); } else { // In Excel formats, "_" is used to add spacing, which we can't do in HTML $format = preg_replace('/_./', '', $format); // Some non-number characters are escaped with \, which we don't need $format = preg_replace("/\\\\/", '', $format); // Some non-number strings are quoted, so we'll get rid of the quotes, likewise any positional * symbols $format = str_replace(array('"', '*'), '', $format); // Find out if we need thousands separator // This is indicated by a comma enclosed by a digit placeholder: // #,# or 0,0 $useThousands = preg_match('/(#,#|0,0)/', $format); if ($useThousands) { $format = preg_replace('/0,0/', '00', $format); $format = preg_replace('/#,#/', '##', $format); } // Scale thousands, millions,... // This is indicated by a number of commas after a digit placeholder: // #, or 0.0,, $scale = 1; // same as no scale $matches = array(); if (preg_match('/(#|0)(,+)/', $format, $matches)) { $scale = pow(1000, strlen($matches[2])); // strip the commas $format = preg_replace('/0,+/', '0', $format); $format = preg_replace('/#,+/', '#', $format); } if (preg_match('/#?.*\\?\\/\\?/', $format, $m)) { //echo 'Format mask is fractional '.$format.' <br />'; if ($value != (int) $value) { $sign = $value < 0 ? '-' : ''; $integerPart = floor(abs($value)); $decimalPart = trim(fmod(abs($value), 1), '0.'); $decimalLength = strlen($decimalPart); $decimalDivisor = pow(10, $decimalLength); $GCD = PHPExcel_Calculation_MathTrig::GCD($decimalPart, $decimalDivisor); $adjustedDecimalPart = $decimalPart / $GCD; $adjustedDecimalDivisor = $decimalDivisor / $GCD; if (strpos($format, '0') !== false || strpos($format, '#') !== false || substr($format, 0, 3) == '? ?') { if ($integerPart == 0) { $integerPart = ''; } $value = "{$sign}{$integerPart} {$adjustedDecimalPart}/{$adjustedDecimalDivisor}"; } else { $adjustedDecimalPart += $integerPart * $adjustedDecimalDivisor; $value = "{$sign}{$adjustedDecimalPart}/{$adjustedDecimalDivisor}"; } } } else { // Handle the number itself // scale number $value = $value / $scale; // Strip # $format = preg_replace('/\\#/', '', $format); $n = "/\\[[^\\]]+\\]/"; $m = preg_replace($n, '', $format); $number_regex = "/(0+)(\\.?)(0*)/"; if (preg_match($number_regex, $m, $matches)) { $left = $matches[1]; $dec = $matches[2]; $right = $matches[3]; // minimun width of formatted number (including dot) $minWidth = strlen($left) + strlen($dec) + strlen($right); if ($useThousands) { $value = number_format($value, strlen($right), PHPExcel_Shared_String::getDecimalSeparator(), PHPExcel_Shared_String::getThousandsSeparator()); } else { $sprintf_pattern = "%0{$minWidth}." . strlen($right) . "f"; $value = sprintf($sprintf_pattern, $value); } $value = preg_replace($number_regex, $value, $format); } } if (preg_match('/\\[\\$(.*)\\]/u', $format, $m)) { // Currency or Accounting $currencyFormat = $m[0]; $currencyCode = $m[1]; list($currencyCode) = explode('-', $currencyCode); if ($currencyCode == '') { $currencyCode = PHPExcel_Shared_String::getCurrencyCode(); } $value = preg_replace('/\\[\\$([^\\]]*)\\]/u', $currencyCode, $value); } } } } // Additional formatting provided by callback function if ($callBack !== null) { list($writerInstance, $function) = $callBack; $value = $writerInstance->{$function}($value, $formatColor); } return $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); }
/** * Convert a value in a pre-defined format to a PHP string * * @param mixed $value Value to format * @param string $format Format code * @param array $callBack Callback function for additional formatting of string * @return string Formatted string */ public static function toFormattedString($value = '0', $format = PHPExcel_Style_NumberFormat::FORMAT_GENERAL, $callBack = null) { // For now we do not treat strings although section 4 of a format code affects strings if (!is_numeric($value)) { return $value; } // For 'General' format code, we just pass the value although this is not entirely the way Excel does it, // it seems to round numbers to a total of 10 digits. if ($format === PHPExcel_Style_NumberFormat::FORMAT_GENERAL || $format === PHPExcel_Style_NumberFormat::FORMAT_TEXT) { return $value; } // Get the sections, there can be up to four sections $sections = explode(';', $format); // Fetch the relevant section depending on whether number is positive, negative, or zero? // Text not supported yet. // Here is how the sections apply to various values in Excel: // 1 section: [POSITIVE/NEGATIVE/ZERO/TEXT] // 2 sections: [POSITIVE/ZERO/TEXT] [NEGATIVE] // 3 sections: [POSITIVE/TEXT] [NEGATIVE] [ZERO] // 4 sections: [POSITIVE] [NEGATIVE] [ZERO] [TEXT] switch (count($sections)) { case 1: $format = $sections[0]; break; case 2: $format = $value >= 0 ? $sections[0] : $sections[1]; $value = abs($value); // Use the absolute value break; case 3: $format = $value > 0 ? $sections[0] : ($value < 0 ? $sections[1] : $sections[2]); $value = abs($value); // Use the absolute value break; case 4: $format = $value > 0 ? $sections[0] : ($value < 0 ? $sections[1] : $sections[2]); $value = abs($value); // Use the absolute value break; default: // something is wrong, just use first section $format = $sections[0]; break; } // Save format with color information for later use below $formatColor = $format; // Strip color information $color_regex = '/^\\[[a-zA-Z]+\\]/'; $format = preg_replace($color_regex, '', $format); // Let's begin inspecting the format and converting the value to a formatted string if (preg_match('/^(\\[\\$[A-Z]*-[0-9A-F]*\\])*[hmsdy]/i', $format)) { // datetime format self::_formatAsDate($value, $format); } else { if (preg_match('/%$/', $format)) { // % number format self::_formatAsPercentage($value, $format); } else { if ($format === self::FORMAT_CURRENCY_EUR_SIMPLE) { $value = 'EUR ' . sprintf('%1.2f', $value); } else { // In Excel formats, "_" is used to add spacing, which we can't do in HTML $format = preg_replace('/_./', '', $format); // Some non-number characters are escaped with \, which we don't need $format = preg_replace("/\\\\/", '', $format); // Some non-number strings are quoted, so we'll get rid of the quotes, likewise any positional * symbols $format = str_replace(array('"', '*'), '', $format); // Find out if we need thousands separator // This is indicated by a comma enclosed by a digit placeholder: // #,# or 0,0 $useThousands = preg_match('/(#,#|0,0)/', $format); if ($useThousands) { $format = preg_replace('/0,0/', '00', $format); $format = preg_replace('/#,#/', '##', $format); } // Scale thousands, millions,... // This is indicated by a number of commas after a digit placeholder: // #, or 0.0,, $scale = 1; // same as no scale $matches = array(); if (preg_match('/(#|0)(,+)/', $format, $matches)) { $scale = pow(1000, strlen($matches[2])); // strip the commas $format = preg_replace('/0,+/', '0', $format); $format = preg_replace('/#,+/', '#', $format); } if (preg_match('/#?.*\\?\\/\\?/', $format, $m)) { //echo 'Format mask is fractional '.$format.' <br />'; if ($value != (int) $value) { self::_formatAsFraction($value, $format); } } else { // Handle the number itself // scale number $value = $value / $scale; // Strip # $format = preg_replace('/\\#/', '0', $format); $n = "/\\[[^\\]]+\\]/"; $m = preg_replace($n, '', $format); $number_regex = "/(0+)(\\.?)(0*)/"; if (preg_match($number_regex, $m, $matches)) { $left = $matches[1]; $dec = $matches[2]; $right = $matches[3]; // minimun width of formatted number (including dot) $minWidth = strlen($left) + strlen($dec) + strlen($right); if ($useThousands) { $value = number_format($value, strlen($right), PHPExcel_Shared_String::getDecimalSeparator(), PHPExcel_Shared_String::getThousandsSeparator()); $value = preg_replace($number_regex, $value, $format); } else { if (preg_match('/0([^\\d\\.]+)0/', $format, $matches)) { $value = self::_complexNumberFormatMask($value, $format); } else { $sprintf_pattern = "%0{$minWidth}." . strlen($right) . "f"; $value = sprintf($sprintf_pattern, $value); $value = preg_replace($number_regex, $value, $format); } } } } if (preg_match('/\\[\\$(.*)\\]/u', $format, $m)) { // Currency or Accounting $currencyFormat = $m[0]; $currencyCode = $m[1]; list($currencyCode) = explode('-', $currencyCode); if ($currencyCode == '') { $currencyCode = PHPExcel_Shared_String::getCurrencyCode(); } $value = preg_replace('/\\[\\$([^\\]]*)\\]/u', $currencyCode, $value); } } } } // Additional formatting provided by callback function if ($callBack !== null) { list($writerInstance, $function) = $callBack; $value = $writerInstance->{$function}($value, $formatColor); } return $value; }
/** * * @param DOMElement $element * @return PHPExcel_Cell */ protected function _addCell($element, $row = null) { $text = trim($element->nodeValue); $cell = $this->_mainSheet->getCellByColumnAndRow($this->_currentCell, $this->_currentRow); if (preg_match('/^\\$ *(\\d{1,3}(\\,\\d{3})*|(\\d+))(\\.\\d{2})?$/', $text)) { $currencyCode = PHPExcel_Shared_String::getCurrencyCode(); $decimalSeparator = PHPExcel_Shared_String::getDecimalSeparator(); $thousandsSeparator = PHPExcel_Shared_String::getThousandsSeparator(); $value = (double) trim(str_replace(array($currencyCode, $thousandsSeparator, $decimalSeparator), array('', '', '.'), $text)); $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_NUMERIC); //Style $cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(str_replace('$', $currencyCode, PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE)); } else { $cell->setValueExplicit($text, PHPExcel_Cell_DataType::TYPE_STRING); } $currentCell = $this->_currentCell; if ($element->hasAttribute('colspan')) { $colspan = (int) $element->getAttribute('colspan'); $cellTarget = $this->_currentCell + $colspan - 1; $this->_mainSheet->mergeCellsByColumnAndRow($this->_currentCell, $this->_currentRow, $cellTarget, $this->_currentRow); $this->_currentCell += $colspan; } else { $this->_currentCell++; } if ($element->hasAttribute('rowspan')) { $rowSpan = (int) $element->getAttribute('rowspan'); if ($rowSpan < 2) { return $cell; } $rowTarget = $this->_currentRow + ($rowSpan - 1); $this->_mainSheet->mergeCellsByColumnAndRow($currentCell, $this->_currentRow, $currentCell, $rowTarget); } return $cell; }