PHP PHPExcel_Shared_String::getThousandsSeparator方法代码示例

//.........这里部分代码省略.........
                     $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;
 }

//.........这里部分代码省略.........
             $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;
 }

 /**
  * 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;
//.........这里部分代码省略.........

 /**
  * VALUE
  *
  * @param    mixed    $value    Value to check
  * @return    boolean
  */
 public static function VALUE($value = '')
 {
     $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
     if (!is_numeric($value)) {
         $numberValue = str_replace(PHPExcel_Shared_String::getThousandsSeparator(), '', trim($value, " \t\n\r\v" . PHPExcel_Shared_String::getCurrencyCode()));
         if (is_numeric($numberValue)) {
             return (double) $numberValue;
         }
         $dateSetting = PHPExcel_Calculation_Functions::getReturnDateType();
         PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
         if (strpos($value, ':') !== false) {
             $timeValue = PHPExcel_Calculation_DateTime::TIMEVALUE($value);
             if ($timeValue !== PHPExcel_Calculation_Functions::VALUE()) {
                 PHPExcel_Calculation_Functions::setReturnDateType($dateSetting);
                 return $timeValue;
             }
         }
         $dateValue = PHPExcel_Calculation_DateTime::DATEVALUE($value);
         if ($dateValue !== PHPExcel_Calculation_Functions::VALUE()) {
             PHPExcel_Calculation_Functions::setReturnDateType($dateSetting);
             return $dateValue;
         }
         PHPExcel_Calculation_Functions::setReturnDateType($dateSetting);
         return PHPExcel_Calculation_Functions::VALUE();
     }
     return (double) $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;
 }