本文整理汇总了PHP中PHPExcel_Shared_Date::FormattedPHPToExcel方法的典型用法代码示例。如果您正苦于以下问题：PHP PHPExcel_Shared_Date::FormattedPHPToExcel方法的具体用法？PHP PHPExcel_Shared_Date::FormattedPHPToExcel怎么用？PHP PHPExcel_Shared_Date::FormattedPHPToExcel使用的例子？那么, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类PHPExcel_Shared_Date的用法示例。
在下文中一共展示了PHPExcel_Shared_Date::FormattedPHPToExcel方法的10个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的PHP代码示例。
示例1: TIMEVALUE
/**
* TIMEVALUE
*
* @param string $timeValue
* @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object,
* depending on the value of the ReturnDateType flag
*/
public static function TIMEVALUE($timeValue)
{
$timeValue = self::flattenSingleValue($timeValue);
if (($PHPDateArray = date_parse($timeValue)) !== False && $PHPDateArray['error_count'] == 0) {
if (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE) {
$excelDateValue = PHPExcel_Shared_Date::FormattedPHPToExcel($PHPDateArray['year'], $PHPDateArray['month'], $PHPDateArray['day'], $PHPDateArray['hour'], $PHPDateArray['minute'], $PHPDateArray['second']);
} else {
$excelDateValue = PHPExcel_Shared_Date::FormattedPHPToExcel(1900, 1, 1, $PHPDateArray['hour'], $PHPDateArray['minute'], $PHPDateArray['second']) - 1;
}
switch (self::getReturnDateType()) {
case self::RETURNDATE_EXCEL:
return (double) $excelDateValue;
break;
case self::RETURNDATE_PHP_NUMERIC:
return (int) ($phpDateValue = PHPExcel_Shared_Date::ExcelToPHP($excelDateValue + 25569) - 3600);
break;
case self::RETURNDATE_PHP_OBJECT:
return new DateTime('1900-01-01 ' . $PHPDateArray['hour'] . ':' . $PHPDateArray['minute'] . ':' . $PHPDateArray['second']);
break;
}
}
return self::$_errorCodes['value'];
}示例2: loadIntoExisting
//.........这里部分代码省略.........
break;
case 'percentage':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$dataValue = (double) $cellDataOfficeAttributes['value'];
if (floor($dataValue) == $dataValue) {
$dataValue = (int) $dataValue;
}
$formatting = PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00;
break;
case 'currency':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$dataValue = (double) $cellDataOfficeAttributes['value'];
if (floor($dataValue) == $dataValue) {
$dataValue = (int) $dataValue;
}
$formatting = PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE;
break;
case 'float':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$dataValue = (double) $cellDataOfficeAttributes['value'];
if (floor($dataValue) == $dataValue) {
if ($dataValue == (int) $dataValue) {
$dataValue = (int) $dataValue;
} else {
$dataValue = (double) $dataValue;
}
}
break;
case 'date':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$dateObj = new DateTime($cellDataOfficeAttributes['date-value'], $GMT);
$dateObj->setTimeZone($timezoneObj);
list($year, $month, $day, $hour, $minute, $second) = explode(' ', $dateObj->format('Y m d H i s'));
$dataValue = PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hour, $minute, $second);
if ($dataValue != floor($dataValue)) {
$formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15 . ' ' . PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4;
} else {
$formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15;
}
break;
case 'time':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$dataValue = PHPExcel_Shared_Date::PHPToExcel(strtotime('01-01-1970 ' . implode(':', sscanf($cellDataOfficeAttributes['time-value'], 'PT%dH%dM%dS'))));
$formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4;
break;
}
// echo 'Data value is '.$dataValue.'<br />';
// if ($hyperlink !== NULL) {
// echo 'Hyperlink is '.$hyperlink.'<br />';
// }
} else {
$type = PHPExcel_Cell_DataType::TYPE_NULL;
$dataValue = NULL;
}
if ($hasCalculatedValue) {
$type = PHPExcel_Cell_DataType::TYPE_FORMULA;
// echo 'Formula: ', $cellDataFormula, PHP_EOL;
$cellDataFormula = substr($cellDataFormula, strpos($cellDataFormula, ':=') + 1);
$temp = explode('"', $cellDataFormula);
$tKey = false;
foreach ($temp as &$value) {
// Only replace in alternate array entries (i.e. non-quoted blocks)
if ($tKey = !$tKey) {
$value = preg_replace('/\\[([^\\.]+)\\.([^\\.]+):\\.([^\\.]+)\\]/Ui', '$1!$2:$3', $value);
// Cell range reference in another sheet
$value = preg_replace('/\\[([^\\.]+)\\.([^\\.]+)\\]/Ui', '$1!$2', $value);示例3: TIMEVALUE
/**
* TIMEVALUE
*
* @param string $timeValue
* @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object,
* depending on the value of the ReturnDateType flag
*/
public static function TIMEVALUE($timeValue)
{
$timeValue = trim(PHPExcel_Calculation_Functions::flattenSingleValue($timeValue), '"');
$timeValue = str_replace(array('/', '.'), array('-', '-'), $timeValue);
$PHPDateArray = date_parse($timeValue);
if ($PHPDateArray !== False && $PHPDateArray['error_count'] == 0) {
if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
$excelDateValue = PHPExcel_Shared_Date::FormattedPHPToExcel($PHPDateArray['year'], $PHPDateArray['month'], $PHPDateArray['day'], $PHPDateArray['hour'], $PHPDateArray['minute'], $PHPDateArray['second']);
} else {
$excelDateValue = PHPExcel_Shared_Date::FormattedPHPToExcel(1900, 1, 1, $PHPDateArray['hour'], $PHPDateArray['minute'], $PHPDateArray['second']) - 1;
}
switch (PHPExcel_Calculation_Functions::getReturnDateType()) {
case PHPExcel_Calculation_Functions::RETURNDATE_EXCEL:
return (double) $excelDateValue;
break;
case PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC:
return (int) ($phpDateValue = PHPExcel_Shared_Date::ExcelToPHP($excelDateValue + 25569) - 3600);
break;
case PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT:
return new DateTime('1900-01-01 ' . $PHPDateArray['hour'] . ':' . $PHPDateArray['minute'] . ':' . $PHPDateArray['second']);
break;
}
}
return PHPExcel_Calculation_Functions::VALUE();
}示例4: date
echo date('H:i:s') . ' Add data' . EOL;
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Financial Year')->setCellValue('B1', 'Financial Period')->setCellValue('C1', 'Country')->setCellValue('D1', 'Date')->setCellValue('E1', 'Sales Value')->setCellValue('F1', 'Expenditure');
$startYear = $endYear = $currentYear = date('Y');
$startYear--;
$endYear++;
$years = range($startYear, $endYear);
$periods = range(1, 12);
$countries = array('United States', 'UK', 'France', 'Germany', 'Italy', 'Spain', 'Portugal', 'Japan');
$row = 2;
foreach ($years as $year) {
foreach ($periods as $period) {
foreach ($countries as $country) {
$endDays = date('t', mktime(0, 0, 0, $period, 1, $year));
for ($i = 1; $i <= $endDays; ++$i) {
$eDate = PHPExcel_Shared_Date::FormattedPHPToExcel($year, $period, $i);
$value = rand(500, 1000) * (1 + rand(-0.25, +0.25));
$salesValue = $invoiceValue = NULL;
$incomeOrExpenditure = rand(-1, 1);
if ($incomeOrExpenditure == -1) {
$expenditure = rand(-500, -1000) * (1 + rand(-0.25, +0.25));
$income = NULL;
} elseif ($incomeOrExpenditure == 1) {
$expenditure = rand(-500, -1000) * (1 + rand(-0.25, +0.25));
$income = rand(500, 1000) * (1 + rand(-0.25, +0.25));
} else {
$expenditure = NULL;
$income = rand(500, 1000) * (1 + rand(-0.25, +0.25));
}
$dataArray = array($year, $period, $country, $eDate, $income, $expenditure);
$objPHPExcel->getActiveSheet()->fromArray($dataArray, NULL, 'A' . $row++);示例5: loadIntoExisting
//.........这里部分代码省略.........
$cellDataFormula = '';
if (isset($cellDataTableAttributes['formula'])) {
$cellDataFormula = $cellDataTableAttributes['formula'];
$hasCalculatedValue = true;
}
if (isset($cellDataText->p)) {
// echo 'Value Type is '.$cellDataOfficeAttributes['value-type'].'<br />';
switch ($cellDataOfficeAttributes['value-type']) {
case 'string':
$type = PHPExcel_Cell_DataType::TYPE_STRING;
$dataValue = $cellDataText->p;
if (isset($dataValue->a)) {
$dataValue = $dataValue->a;
$cellXLinkAttributes = $dataValue->attributes($namespacesContent['xlink']);
$hyperlink = $cellXLinkAttributes['href'];
}
break;
case 'boolean':
$type = PHPExcel_Cell_DataType::TYPE_BOOL;
$dataValue = $cellDataText->p == 'TRUE' ? True : False;
break;
case 'float':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$dataValue = (double) $cellDataOfficeAttributes['value'];
if (floor($dataValue) == $dataValue) {
$dataValue = (int) $dataValue;
}
break;
case 'date':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$dateObj = new DateTime($cellDataOfficeAttributes['date-value'], $GMT);
$dateObj->setTimeZone($timezoneObj);
list($year, $month, $day, $hour, $minute, $second) = explode(' ', $dateObj->format('Y m d H i s'));
$dataValue = PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hour, $minute, $second);
if ($dataValue != floor($dataValue)) {
$formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15 . ' ' . PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4;
} else {
$formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15;
}
break;
case 'time':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$dataValue = PHPExcel_Shared_Date::PHPToExcel(strtotime('01-01-1970 ' . implode(':', sscanf($cellDataOfficeAttributes['time-value'], 'PT%dH%dM%dS'))));
$formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4;
break;
}
// echo 'Data value is '.$dataValue.'<br />';
// if (!is_null($hyperlink)) {
// echo 'Hyperlink is '.$hyperlink.'<br />';
// }
}
if ($hasCalculatedValue) {
$type = PHPExcel_Cell_DataType::TYPE_FORMULA;
// echo 'Formula: '.$cellDataFormula.'<br />';
$cellDataFormula = substr($cellDataFormula, strpos($cellDataFormula, ':=') + 1);
$temp = explode('"', $cellDataFormula);
foreach ($temp as $key => &$value) {
// Only replace in alternate array entries (i.e. non-quoted blocks)
if ($key % 2 == 0) {
$value = preg_replace('/\\[\\.(.*):\\.(.*)\\]/Ui', '$1:$2', $value);
$value = preg_replace('/\\[\\.(.*)\\]/Ui', '$1', $value);
$value = PHPExcel_Calculation::_translateSeparator(';', ',', $value, $inBraces);
}
}
unset($value);
// Then rebuild the formula string示例6: write_date
/**
* Write one date somewhere in the worksheet.
* @param integer $row Zero indexed row
* @param integer $col Zero indexed column
* @param string $date The date to write in UNIX timestamp format
* @param mixed $format The XF format for the cell
*/
public function write_date($row, $col, $date, $format = null)
{
$getdate = usergetdate($date);
$exceldate = PHPExcel_Shared_Date::FormattedPHPToExcel($getdate['year'], $getdate['mon'], $getdate['mday'], $getdate['hours'], $getdate['minutes'], $getdate['seconds']);
$this->worksheet->setCellValueByColumnAndRow($col, $row + 1, $exceldate);
$this->worksheet->getStyleByColumnAndRow($col, $row + 1)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX22);
$this->apply_format($row, $col, $format);
}示例7: stringToExcel
/**
* Convert a date/time string to Excel time
*
* @param string $dateValue Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10'
* @return float|false Excel date/time serial value
*/
public static function stringToExcel($dateValue = '')
{
// restrict to dates and times like these because date_parse accepts too many strings
// '2009-12-31'
// '2009-12-31 15:59'
// '2009-12-31 15:59:10'
if (!preg_match('/^\\d{4}\\-\\d{1,2}\\-\\d{1,2}( \\d{1,2}:\\d{1,2}(:\\d{1,2})?)?$/', $dateValue)) {
return false;
}
// now try with date_parse
$PHPDateArray = date_parse($dateValue);
if ($PHPDateArray['error_count'] == 0) {
$year = $PHPDateArray['year'] !== false ? $PHPDateArray['year'] : self::getExcelCalendar();
$month = $PHPDateArray['month'] !== false ? $PHPDateArray['month'] : 1;
$day = $PHPDateArray['day'] !== false ? $PHPDateArray['day'] : 0;
$hour = $PHPDateArray['hour'] !== false ? $PHPDateArray['hour'] : 0;
$minute = $PHPDateArray['minute'] !== false ? $PHPDateArray['minute'] : 0;
$second = $PHPDateArray['second'] !== false ? $PHPDateArray['second'] : 0;
$excelDateValue = PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hour, $minute, $second);
return $excelDateValue;
}
return false;
}示例8: export
public function export($competition, $exportFormsts, $all = false, $xlsx = false, $extra = false, $order = 'date')
{
$registrations = $this->getRegistrations($competition, $all, $order);
$template = PHPExcel_IOFactory::load(Yii::getPathOfAlias('application.data.results') . '.xls');
$export = new PHPExcel();
$export->getProperties()->setCreator(Yii::app()->params->author)->setLastModifiedBy(Yii::app()->params->author)->setTitle($competition->wca_competition_id ?: $competition->name)->setSubject($competition->name);
$export->removeSheetByIndex(0);
//注册页
$sheet = $template->getSheet(0);
$sheet->setCellValue('A1', $competition->wca_competition_id ?: $competition->name);
$events = $competition->getRegistrationEvents();
$col = 'J';
$cubecompsEvents = array('333' => '3x3', '444' => '4x4', '555' => '5x5', '666' => '6x6', '777' => '7x7', '222' => '2x2', '333bf' => '333bld', '333fm' => 'fmc', 'minx' => 'mega', 'pyram' => 'pyra', '444bf' => '444bld', '555bf' => '555bld', '333mbf' => '333mlt');
foreach ($events as $event => $data) {
$sheet->setCellValue($col . 2, "=SUM({$col}4:{$col}" . (count($registrations) + 4) . ')');
$sheet->setCellValue($col . 3, isset($cubecompsEvents[$event]) ? $cubecompsEvents[$event] : $event);
$sheet->getColumnDimension($col)->setWidth(5.5);
$col++;
}
foreach ($registrations as $key => $registration) {
$user = $registration->user;
$row = $key + 4;
$sheet->setCellValue('A' . $row, $registration->number)->setCellValue('B' . $row, $user->getCompetitionName())->setCellValue('C' . $row, $user->country->name)->setCellValue('D' . $row, $user->wcaid)->setCellValue('E' . $row, $user->getWcaGender())->setCellValue('F' . $row, PHPExcel_Shared_Date::FormattedPHPToExcel(date('Y', $user->birthday), date('m', $user->birthday), date('d', $user->birthday)));
$col = 'J';
foreach ($events as $event => $data) {
if (in_array("{$event}", $registration->events)) {
$sheet->setCellValue($col . $row, 1);
}
$col++;
}
if ($extra) {
$col++;
$fee = $registration->getTotalFee();
if ($registration->isPaid()) {
$fee .= Yii::t('common', ' (paid)');
}
$sheet->setCellValue($col . $row, $fee);
$col++;
$sheet->setCellValue($col . $row, $user->mobile);
$col++;
$sheet->setCellValue($col . $row, $user->email);
$col++;
$sheet->setCellValue($col . $row, $registration->comments);
}
if (!$registration->isAccepted()) {
$sheet->getStyle("A{$row}:D{$row}")->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFFFFF00'))));
}
}
$export->addExternalSheet($sheet);
//各个项目
foreach ($exportFormsts as $event => $rounds) {
$count = count($rounds);
foreach ($rounds as $round => $format) {
if ($round == $count - 1) {
$round = 'f';
} else {
$round++;
}
$sheet = $template->getSheetByName($format);
if ($sheet === null) {
continue;
}
$sheet = clone $sheet;
$sheet->setTitle("{$event}-{$round}");
$template->addSheet($sheet);
$sheet->setCellValue('A1', Events::getFullEventName($event) . ' - ' . Rounds::getFullRoundName($round));
if ($round == 1 || $count == 1) {
$row = 5;
foreach ($registrations as $registration) {
if (!in_array("{$event}", $registration->events)) {
continue;
}
$user = $registration->user;
$sheet->setCellValue('B' . $row, $user->getCompetitionName())->setCellValue('C' . $row, $user->country->name)->setCellValue('D' . $row, $user->wcaid);
if ($row > 5) {
$formula = $sheet->getCell('A' . ($row - 1))->getValue();
$formula = strtr($formula, array('-4' => '_temp_', $row - 1 => $row, $row - 2 => $row - 1, $row => $row + 1));
$formula = str_replace('_temp_', '-4', $formula);
$sheet->setCellValue('A' . $row, $formula);
}
if (!$registration->isAccepted()) {
$sheet->getStyle("A{$row}:D{$row}")->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFFFFF00'))));
}
$row++;
}
}
$export->addExternalSheet($sheet);
}
}
$this->exportToExcel($export, 'php://output', $competition->name, $xlsx);
}示例9: FormatDate
function FormatDate($date, $format_id = false)
{
$date = $this->_ensureTimeStamp($date);
return PHPExcel_Shared_Date::FormattedPHPToExcel(date("Y", $date), date("m", $date), date("d", $date));
}示例10: TIMEVALUE
/**
* TIMEVALUE
*
* @param string $timeValue
* @return long Excel time serial value
*/
public static function TIMEVALUE($timeValue)
{
$timeValue = self::flattenSingleValue($timeValue);
if (($PHPDateArray = date_parse($timeValue)) !== False && $PHPDateArray['error_count'] == 0) {
if (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE) {
return PHPExcel_Shared_Date::FormattedPHPToExcel($PHPDateArray['year'], $PHPDateArray['month'], $PHPDateArray['day'], $PHPDateArray['hour'], $PHPDateArray['minute'], $PHPDateArray['second']);
} else {
return PHPExcel_Shared_Date::FormattedPHPToExcel(1900, 1, 1, $PHPDateArray['hour'], $PHPDateArray['minute'], $PHPDateArray['second']) - 1;
}
}
return self::$_errorCodes['value'];
}