PHP PHPExcel_Shared_Date类代码示例

 public static function exportXlsx($data, $keys)
 {
     // Create new PHPExcel object
     $objPHPExcel = new \PHPExcel();
     // Set document properties
     $objPHPExcel->getProperties()->setCreator("Roadiz CMS")->setLastModifiedBy("Roadiz CMS")->setCategory("");
     $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
     $cacheSettings = ['memoryCacheSize' => '8MB'];
     \PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
     $objPHPExcel->setActiveSheetIndex(0);
     foreach ($keys as $key => $value) {
         $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($key, 1, $value);
     }
     foreach ($data as $key => $answer) {
         foreach ($answer as $k => $value) {
             $columnAlpha = \PHPExcel_Cell::stringFromColumnIndex($k);
             if ($value instanceof \DateTime) {
                 $value = \PHPExcel_Shared_Date::PHPToExcel($value);
                 $objPHPExcel->getActiveSheet()->getStyle($columnAlpha . (2 + $key))->getNumberFormat()->setFormatCode('dd.mm.yyyy hh:MM:ss');
             }
             $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($k, 2 + $key, $value);
         }
     }
     // Set active sheet index to the first sheet, so Excel opens this as the first sheet
     $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
     ob_start();
     $objWriter->save('php://output');
     $return = ob_get_clean();
     return $return;
 }

 public function ProcessFileContent()
 {
     $objPHPExcel = PHPExcel_IOFactory::load($this->file);
     // Format is as follows:
     // (gray bg)    [ <description of data> ], <relation1>,    <relationN>
     //              <srcConcept>,              <tgtConcept1>,  <tgtConceptN>
     //              <srcAtomA>,                <tgtAtom1A>,    <tgtAtomNA>
     //              <srcAtomB>,                <tgtAtom1B>,    <tgtAtomNB>
     //              <srcAtomC>,                <tgtAtom1C>,    <tgtAtomNC>
     // Output is function call:
     // InsPair($relation,$srcConcept,$srcAtom,$tgtConcept,$tgtAtom)
     // Loop over all worksheets
     foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
         // Loop through all rows
         $highestrow = $worksheet->getHighestRow();
         $highestcolumn = $worksheet->getHighestColumn();
         $highestcolumnnr = PHPExcel_Cell::columnIndexFromString($highestcolumn);
         $row = 1;
         // Go to the first row where a table starts.
         for ($i = $row; $i <= $highestrow; $i++) {
             $row = $i;
             if (substr($worksheet->getCell('A' . $row)->getValue(), 0, 1) === '[') {
                 break;
             }
         }
         // We are now at the beginning of a table or at the end of the file.
         $line = array();
         // Line is a buffer of one or more related (subsequent) excel rows
         while ($row <= $highestrow) {
             // Read this line as an array of values
             $values = array();
             // values is a buffer containing the cells in a single excel row
             for ($columnnr = 0; $columnnr < $highestcolumnnr; $columnnr++) {
                 $columnletter = PHPExcel_Cell::stringFromColumnIndex($columnnr);
                 $cell = $worksheet->getCell($columnletter . $row);
                 $cellvalue = (string) $cell->getCalculatedValue();
                 // overwrite $cellvalue in case of datetime
                 // the @ is a php indicator for a unix timestamp (http://php.net/manual/en/datetime.formats.compound.php), later used for typeConversion
                 if (PHPExcel_Shared_Date::isDateTime($cell) && !empty($cellvalue)) {
                     $cellvalue = '@' . (string) PHPExcel_Shared_Date::ExcelToPHP($cellvalue);
                 }
                 $values[] = $cellvalue;
             }
             $line[] = $values;
             // add line (array of values) to the line buffer
             $row++;
             // Is this relation table done? Then we parse the current values into function calls and reset it
             $firstCellInRow = (string) $worksheet->getCell('A' . $row)->getCalculatedValue();
             if (substr($firstCellInRow, 0, 1) === '[') {
                 // Relation table is complete, so it can be processed.
                 $this->ParseLines($line);
                 $line = array();
             }
         }
         // Last relation table remains to be processed.
         $this->ParseLines($line);
         $line = array();
     }
 }

//.........这里部分代码省略.........
						}
					}

					$dxfs = array();
					if (!$this->_readDataOnly && $xmlStyles) {
						if ($xmlStyles->dxfs) {
							foreach ($xmlStyles->dxfs->dxf as $dxf) {
								$style = new PHPExcel_Style;
								$this->_readStyle($style, $dxf);
								$dxfs[] = $style;
							}
						}

						if ($xmlStyles->cellStyles)
						{
							foreach ($xmlStyles->cellStyles->cellStyle as $cellStyle) {
								if (intval($cellStyle['builtinId']) == 0) {
									if (isset($cellStyles[intval($cellStyle['xfId'])])) {
										// Set default style
										$style = new PHPExcel_Style;
										$this->_readStyle($style, $cellStyles[intval($cellStyle['xfId'])]);

										// normal style, currently not using it for anything
									}
								}
							}
						}
					}

					$xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));  //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");

					// Set base date
					if ($xmlWorkbook->workbookPr) {
						PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900);
						if (isset($xmlWorkbook->workbookPr['date1904'])) {
							$date1904 = (string)$xmlWorkbook->workbookPr['date1904'];
							if ($date1904 == "true" || $date1904 == "1") {
								PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
							}
						}
					}

					$sheetId = 0; // keep track of new sheet id in final workbook
					$oldSheetId = -1; // keep track of old sheet id in final workbook
					$countSkippedSheets = 0; // keep track of number of skipped sheets
					$mapSheetId = array(); // mapping of sheet ids from old to new

					if ($xmlWorkbook->sheets)
					{
						foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
							++$oldSheetId;

							// Check if sheet should be skipped
							if (isset($this->_loadSheetsOnly) && !in_array((string) $eleSheet["name"], $this->_loadSheetsOnly)) {
								++$countSkippedSheets;
								$mapSheetId[$oldSheetId] = null;
								continue;
							}

							// Map old sheet id in original workbook to new sheet id.
							// They will differ if loadSheetsOnly() is being used
							$mapSheetId[$oldSheetId] = $oldSheetId - $countSkippedSheets;

							// Load sheet
							$docSheet = $excel->createSheet();
							$docSheet->setTitle((string) $eleSheet["name"]);

 public function testDATEwith1904CalendarError()
 {
     PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
     $result = PHPExcel_Calculation_DateTime::DATE(1901, 1, 31);
     PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900);
     $this->assertEquals($result, '#NUM!');
 }

 function insertarCargarExcepciones($array)
 {
     $uploadOk = 1;
     $time = time();
     $fecha = date("Y-m-d", $time);
     $target_dir = "../documents/";
     $target_file = $target_dir . basename($_FILES["archivoExcepcion"]["name"]);
     move_uploaded_file($array["archivoExcepcion"]["tmp_name"], $target_file);
     set_include_path(get_include_path() . PATH_SEPARATOR . '../complements/PHPExcel-1.8/Classes/');
     $inputFileType = 'Excel2007';
     include 'PHPExcel/IOFactory.php';
     $inputFileName = $target_file;
     $objReader = PHPExcel_IOFactory::createReader($inputFileType);
     $objReader->setReadDataOnly(true);
     $objPHPExcel = $objReader->load($inputFileName);
     $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
     require_once "../db/conexiones.php";
     $consulta = new Conexion();
     foreach ($sheetData as $key => $datos) {
         $nombreSinAcentos = sanear_string($datos['B']);
         $nombre = strtoupper(trim($nombreSinAcentos));
         $datosEmpleado = $consulta->Conectar("postgres", "SELECT * FROM userinfo WHERE UPPER(name)='" . $nombre . "'");
         if ($datosEmpleado) {
             $sqlInsert = $this->invoco->Conectar("postgres", "INSERT INTO horario_excepcion (user_id, desde, hasta, banda_id) VALUES (" . $datosEmpleado[0]['userid'] . ",'" . date("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($objPHPExcel->getActiveSheet()->getCell('D' . $key)->getvalue())) . "', '" . date("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($objPHPExcel->getActiveSheet()->getCell('E' . $key)->getvalue())) . "'," . $datos['C'] . ")");
         }
     }
     return "Se insertaron los datos Exitosamente!";
 }

 /**
  * Format cell data type
  * @param  \PHPExcel_Cell $cell 
  * @return string               
  */
 protected function reformatCellDataType(\PHPExcel_Cell $cell)
 {
     $value = $cell->getValue();
     //datetime
     if (\PHPExcel_Shared_Date::isDateTime($cell)) {
         $format = $this->cellFormat['dateTime'];
         return date($format, \PHPExcel_Shared_Date::ExcelToPHP($value));
     }
     return $value;
 }

function getImportExcelData($data, $fields)
{
    global $total_records, $cCharset, $columnIndex;
    foreach ($data->getWorksheetIterator() as $worksheet) {
        $highestRow = $worksheet->getHighestRow();
        for ($row = 2; $row <= $highestRow; ++$row) {
            for ($col = 0; $col < $columnIndex; ++$col) {
                $cell = $worksheet->getCellByColumnAndRow($col, $row);
                if (PHPExcel_Shared_Date::isDateTime($cell)) {
                    $date_format = $cell->getParent()->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode();
                    $value = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $date_format);
                    if (is_a($value, 'PHPExcel_RichText')) {
                        $value = $value->getPlainText();
                    }
                    if ($value) {
                        $time = array();
                        if (strtotime($value)) {
                            $value = strtotime($value);
                        } else {
                            $d_format = "";
                            for ($i = 0; $i < strlen($date_format); $i++) {
                                $letter = substr(strtolower($date_format), $i, 1);
                                if ($letter == "d" || $letter == "m" || $letter == "y") {
                                    if (strpos($d_format, $letter) === false) {
                                        $d_format .= $letter;
                                    }
                                }
                            }
                            $value = strtotime(localdatetime2db($value, $d_format));
                        }
                        //							$value = PHPExcel_Shared_Date::ExcelToPHP($value);
                        $time = localtime($value, true);
                        $val = $time["tm_year"] + 1900 . "-" . ($time["tm_mon"] + 1) . "-" . $time["tm_mday"] . " " . $time["tm_hour"] . ":" . $time["tm_min"] . ":" . $time["tm_sec"];
                    } else {
                        $val = NULL;
                    }
                } else {
                    $error_handler = set_error_handler("empty_error_handler");
                    $val = PHPExcel_Shared_String::ConvertEncoding($cell->getValue(), $cCharset, 'UTF-8');
                    if (is_a($val, 'PHPExcel_RichText')) {
                        $val = $val->getPlainText();
                    }
                    if ($error_handler) {
                        set_error_handler($error_handler);
                    }
                }
                $arr[$fields[$col]] = $val;
            }
            $ret = InsertRecord($arr, $row - 2);
            $total_records++;
        }
        break;
    }
}

 public function convert($input)
 {
     if (!$input) {
         return;
     }
     if (is_numeric($input) && $input < 100000) {
         //Date may be 42338 (=> 30.11.2015
         $date = \PHPExcel_Shared_Date::ExcelToPHPObject($input);
         return $this->formatOutput($date);
     }
     return parent::convert($input);
 }

 /**
  * 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 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);
 }

 private static function couponFirstPeriodDate($settlement, $maturity, $frequency, $next)
 {
     $months = 12 / $frequency;
     $result = PHPExcel_Shared_Date::ExcelToPHPObject($maturity);
     $eom = self::isLastDayOfMonth($result);
     while ($settlement < PHPExcel_Shared_Date::PHPToExcel($result)) {
         $result->modify('-' . $months . ' months');
     }
     if ($next) {
         $result->modify('+' . $months . ' months');
     }
     if ($eom) {
         $result->modify('-1 day');
     }
     return PHPExcel_Shared_Date::PHPToExcel($result);
 }

 /**
  * Fill worksheet from values in array
  *
  * @param array $source Source array
  * @param mixed $nullValue Value in source array that stands for blank cell
  * @param string $startCell Insert array starting from this cell address as the top left coordinate
  * @param bool $strictNullComparison Apply strict comparison when testing for null values in the array
  * @throws PhpExcelException
  * @return Worksheet
  */
 public function fromArray(array $source = null, $nullValue = null, $startCell = 'A1', $strictNullComparison = false)
 {
     foreach ($source as &$row) {
         if (is_array($row)) {
             foreach ($row as $key => $value) {
                 if ($value instanceof DateTime) {
                     $row[$key] = PhpOffice_PHPExcel_Shared_Date::PHPToExcel($value);
                 }
             }
         }
     }
     try {
         $this->sheet->fromArray($source, $nullValue, $startCell, $strictNullComparison);
     } catch (PhpOffice_PHPExcel_Exception $ex) {
         throw new PhpExcelException('Unable to paste the array to worksheet', $ex->getCode(), $ex);
     }
     return $this;
 }

 protected function renderFields($key, $value)
 {
     $renderTimeArray = array('p_loadTimeSlot', 'p_unloadTimeSlot', 'p_shipperEta', 'p_consigneeEta', 'p_shipperAta', 'p_shipperAtd', 'p_consigneeAta', 'p_consigneeAtd', 'to_shipperEta');
     $securityArray = array('p_securityFlag', 'p_stackFlag');
     if (in_array($key, $renderTimeArray)) {
         if (is_object($value)) {
             return \PHPExcel_Shared_Date::PHPToExcel($value);
         }
     } else {
         if (in_array($key, $securityArray)) {
             if ($value == true) {
                 return 'ДА';
             } else {
                 return 'НЕТ';
             }
         }
     }
     return $value;
 }

 /**
  * @return array
  */
 protected function makeExportData()
 {
     $rows = array();
     $rows[] = $this->excelHeader();
     /** @var $em \Doctrine\ORM\EntityManager */
     $em = \AppKernel::getStaticContainer()->get('doctrine');
     /** @var $query \Doctrine\ORM\QueryBuilder */
     $object = $em->getRepository('ApplicationSonataClientBundle:Garantie')->createQueryBuilder('g')->orderBy('g.date_decheance', 'ASC')->getQuery()->execute();
     $nom_de_la_banques = Garantie::getNomDeLaBanques();
     foreach ($object as $key => $row) {
         $date_decheance = $row->getDateDecheance() ? \PHPExcel_Shared_Date::PHPToExcel($row->getDateDecheance()) : '';
         /** @var $row Garantie */
         $cell = array('client' => (string) $row->getClient(), 'date_decheance' => $date_decheance, 'montant' => $row->getMontant(), 'nom_de_la_banques_id' => isset($nom_de_la_banques[$row->getNomDeLaBanquesId()]) ? $nom_de_la_banques[$row->getNomDeLaBanquesId()] : '', 'num_de_ganrantie' => $row->getNumDeGanrantie(), 'note' => $row->getNote(), 'client_date_fin_mission' => $row->getClient()->getDateFinMission() ? \PHPExcel_Shared_Date::PHPToExcel($row->getClient()->getDateFinMission()) : '', 'calc_day_date_decheance' => $date_decheance ? '=B' . ($key + 2) . '-TODAY()' : '');
         //format
         $this->excelCellFormat($key + 2, $row);
         $rows[] = $cell;
     }
     return $rows;
 }

function excel_to_date($id)
{
    $value = $id->getValue();
    if ($value != '') {
        if (PHPExcel_Shared_Date::isDateTime($id)) {
            $data = PHPExcel_Shared_Date::ExcelToPHP($value);
            $date = date('Y-m-d', $data);
        } else {
            if (date_create($value)) {
                $date = date_format(date_create($value), 'Y-m-d');
            } else {
                $date = '0000-00-00';
            }
        }
    } else {
        $date = '0000-00-00';
    }
    return $date;
}

 public function getAllData()
 {
     $this->excel_data = array();
     $objWorksheet = $this->excel->getActiveSheet();
     // Get the highest row number and column letter referenced in the worksheet
     $highestRow = $objWorksheet->getHighestRow();
     // e.g. 10
     $highestColumn = $objWorksheet->getHighestColumn();
     // e.g 'F'
     if ($this->maxColumn != null) {
         $highestColumn = $this->maxColumn;
     }
     // Increment the highest column letter
     $highestColumn++;
     for ($row = 1; $row <= $highestRow; ++$row) {
         $line = array();
         for ($col = 'A'; $col != $highestColumn; ++$col) {
             //   	var_dump($objWorksheet->getCell($col . $row)
             // ->getValue());
             // print "<br/>\r\n";
             // continue;
             $cell = $objWorksheet->getCell($col . $row);
             $val = "" . $cell->getValue();
             // var_dump($col, $row,$val);
             if ($row == $this->titleLine && $val == "") {
                 $highestColumn = $col++;
                 break;
             }
             if (PHPExcel_Shared_Date::isDateTime($cell)) {
                 $val = PHPExcel_Shared_Date::ExcelToPHP($val);
             } else {
                 $val = "" . $val;
             }
             $line[] = $val;
         }
         $this->excel_data[] = $line;
     }
     return $this->excel_data;
 }