Converteer het volledige seriële formaat van de Excel-datum naar Unix timestamp

Ik heb veel verwijzingen gezien naar het converteren van het "datum" -gedeelte van het Excel-seriedatumformaat, maar iedereen lijkt het "tijd" -gedeelte ervan over te slaan.

Dit is wat ik moet doen:

Ik heb een Excel-bestand dat ik importeer. PHP in gebruik.

Ik kom het Excel Date Serial-formaat tegen (dddddd.tttttt) en moet dit converteren naar een volledige Unix-tijdstempel.

Ik heb een paar verschillende dingen geprobeerd, maar ik word in een vloeiende beweging opgehangen hoe dit moet.

10
strtotime is niet gaat helpen met n excel-seriewaarde, het aantal dagen sinds 1/1/1900 (of mogelijk 1/1/1904 afhankelijk van of het de Windows 1900 of Mac 1904 gebruikt) kalender)
toegevoegd de auteur Mark Baker, de bron
wat geeft strtotime() jou?
toegevoegd de auteur arijeet, de bron

4 antwoord

Gebruik deze formule om van de datum van Excel naar de Unix-datum te veranderen. Gebruik vervolgens "gmdate" om de echte datum in PHP te krijgen:

UNIX_DATE = (EXCEL_DATE - 25569) * 86400

en om van Unix-datum naar Excel-datum te converteren, gebruik je deze formule:

EXCEL_DATE = 25569 + (UNIX_DATE/86400)

Nadat je deze formule in een variabele hebt omgezet, kun je de echte datum in PHP gebruiken met behulp van dit voorbeeld:

$UNIX_DATE = ($EXCEL_DATE - 25569) * 86400;
echo gmdate("d-m-Y H:i:s", $UNIX_DATE);

Bedankt.

30
toegevoegd
nogmaals ... mooi antwoord ...;)
toegevoegd de auteur deemi, de bron
Werkt als een charm functie ExcelDateToUnix ($ dateValue = 0) {return ($ dateValue - 25569) * 86400; }
toegevoegd de auteur vonUbisch, de bron

Deze oneliner werkte voor mij, natuurlijk met behulp van PHPExcel.

$date_formated = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($date_int_val));
12
toegevoegd
PhpExcel is nu verouderd. Zijn directe opvolger PhpSpreadsheet stelt ons in staat om de volgende methode te gebruiken: \ PhpOffice \ PhpSpreadsheet \ Shared \ Date :: excelToDateTimeObject & zwnj; ($ date_int_val) .
toegevoegd de auteur Guicara, de bron
dit werkt! bedankt
toegevoegd de auteur Yosi Azwan, de bron

Je hebt duidelijk niet heel hard gekeken:

Rechtstreeks overgenomen uit de PHPExcel Date-verwerkingscode:

public static function ExcelToPHP($dateValue = 0) {
    if (self::$ExcelBaseDate == self::CALENDAR_WINDOWS_1900) {
        $myExcelBaseDate = 25569;
       //   Adjust for the spurious 29-Feb-1900 (Day 60)
        if ($dateValue < 60) {
            --$myExcelBaseDate;
        }
    } else {
        $myExcelBaseDate = 24107;
    }

   //Perform conversion
    if ($dateValue >= 1) {
        $utcDays = $dateValue - $myExcelBaseDate;
        $returnValue = round($utcDays * 86400);
        if (($returnValue <= PHP_INT_MAX) && ($returnValue >= -PHP_INT_MAX)) {
            $returnValue = (integer) $returnValue;
        }
    } else {
        $hours = round($dateValue * 24);
        $mins = round($dateValue * 1440) - round($hours * 60);
        $secs = round($dateValue * 86400) - round($hours * 3600) - round($mins * 60);
        $returnValue = (integer) gmmktime($hours, $mins, $secs);
    }

   //Return
    return $returnValue;
}   //   function ExcelToPHP()

Stel zelf in :: $ ExcelBaseDate == self :: CALENDAR_WINDOWS_1900 als nodig om de Excel-basiskalender aan te geven die u gebruikt: Windows 1900 of Mac 1904

en als je in de plaats een PHP DateTime-object wilt:

public static function ExcelToPHPObject($dateValue = 0) {
    $dateTime = self::ExcelToPHP($dateValue);
    $days = floor($dateTime/86400);
    $time = round((($dateTime/86400) - $days) * 86400);
    $hours = round($time/3600);
    $minutes = round($time/60) - ($hours * 60);
    $seconds = round($time) - ($hours * 3600) - ($minutes * 60);

    $dateObj = date_create('1-Jan-1970+'.$days.' days');
    $dateObj->setTime($hours,$minutes,$seconds);

    return $dateObj;
}   //   function ExcelToPHPObject()
6
toegevoegd
Hoe lees je eigenlijk de Excel-werkmap?
toegevoegd de auteur Mark Baker, de bron
Als je PHPExcel gebruikt, doe dan geen moeite met een privé-functie: de code die ik hierboven geciteerd heb is ingebouwd in PHPExcel - PHPExcel_Shared_Date :: ExcelToPHP() of PHPExcel_Shared_Date :: ExcelToPHPObject() - noem die in plaats daarvan
toegevoegd de auteur Mark Baker, de bron
Bedankt Mark. Houd in gedachten dat ik misschien niet de diepte van ervaring heb, hoewel ik voor het grootste deel in staat ben om dingen te vinden. In dit geval, als ik door mijn spreadsheet parseer, roep ik de functie aan en geef ik de celwaarde door?
toegevoegd de auteur timebinder, de bron
openen met PHPExcel, itereren op elke rij, elke celwaarde krijgen, dus in het geval van kolom A, die de datumwaarde heeft, doe ik in principe $ cell = $ sheet-> getCell ('A'. $ rowIndex), dan $ array_data [$ rowIndex] ['A'] = $ cell-> getValue (); Vanaf daar wil ik het gewoon instellen op een variabele in Unix timestamp-indeling
toegevoegd de auteur timebinder, de bron
Mark, bedankt. Ik nam wat ik nodig had van het bovenstaande en creëerde een privéfunctie om te gebruiken waar ik het nodig had. Proost
toegevoegd de auteur timebinder, de bron

Ik had hetzelfde probleem met een project, ik was op zoek naar een PHP Class om Excel te lezen als PHP Array . Ik had het geluk dat ik 'SimpleXLSX' Class vond. Het kan uitstekend omgaan met excel-gegevens, maar ... maar ... ohh !! maar.... :( Ik heb me gerealiseerd dat er een probleem is met het veld Datum lezen van Excel . In excel-waarden zag er goed uit, maar toen we waren proberen om ze te importeren, de datumwaarden krijgen verschillende. Enige tijd kregen we soms enkele correcte waarden, alleen een aantal en soms een float-waarde. We waren op zoek naar oplossing '

WAAROM HET GEBEURT, WAAROM PHP NIET CORRECTE DATUM KRIJGT VAN EXCEL

' Toen vonden we na het goggles de reden:

@Bron: xls-datum in PHP lezen

Volgens Excel-formaat 41397 is 2013-05-03 Excel-winkels datums en   keer als een getal dat het aantal dagen sinds 1900-januari-0 weergeeft,   plus een fractioneel deel van een 24-urendag: ddddd.tttttt. Dit is   een seriële datum of seriële datum-tijd genoemd.

@bron: Excel-datumconversie met PHP Excel

Converteer Excel-datum naar Unix-datum en converteer vervolgens Unix-datum naar PHP-datum

Dus maakte ik een kleine hulpklasse om Excel Date te lezen voor gebruik in PHP. Ik hoop dat het iemand zal helpen en goggling en inspanningen zal verminderen.

Here is my code to Read Excel as PHP Array() and Parse Excel Date as PHP Date

Voor beginners:

  1. Download SimpleXLSX.php uit de gegeven voorbeeldcode
  2. Excel-gegevens (veld/waarde) converteren in PHP-array()
  3. Excel-datum converteren naar PHP
  4. Nu .. Ja !! Excel-gegevens zijn klaar als een PHP-array om naar toe te gaan MySQL-tabel ...

Dit is PHP-code:

<?php   
/*
  EXCEL DATA EXAMPLE:
  ----------------------------
  ID      Consumer_Date  Delivery_Date   Date_of_Dispatch    Gift_Date_Created   Active_Date              Approved_Date
  536     -No-Data-      9-Nov-15        7-Nov-15            -No-Data-           10/31/2015 12:00:00 AM   4/11/2015 10:21
  537     -No-Data-      -No-Data-       7-Nov-15            -No-Data-           10/23/2015 12:00:00 AM   3/11/2015 16:24

*/

/*
  EXCEL DATA IN PHP ARRAY FORMAT
  -------------------------------
  Array
  (
      [0] => Array
          (
              [ID] => 536
              [Consumer_Date] => -No-Data-
              [Delivery_Date] => 42317
              [Date_of_Dispatch] => 42315
              [Gift_Date_Created] => -No-Data-
              [Active_Date] => 10/31/2015 12:00:00 AM
              [Approved_Date] => 42105.431574074
          )
      [1] => Array
          (
              [ID] => 537
              [Consumer_Date] => -No-Data-
              [Delivery_Date] => -No-Data-
              [Date_of_Dispatch] => 42315
              [Gift_Date_Created] => -No-Data-
              [Active_Date] => 10/23/2015 12:00:00 AM
              [Approved_Date] => 42074.683958333
          )
  )

*/

/* ----------------- */
/* Excel_Date_Parser.php */
/* ----------------- */


// Numbers of days between January 1, 1900 and 1970 (including 19 leap years)
define("MIN_DATES_DIFF", 25569);

// Numbers of second in a day:
define("SEC_IN_DAY", 86400);

/** Set default timezone (will throw a notice otherwise) */
date_default_timezone_set('Asia/Kolkata');

/**
 * Class Excel_Date_Parser
 *
 * SetDateString($excel_date_value) : send excel date column value
 * GetDateString(): get your PHP date in Y-m-d format (MySQL)
 */
class Excel_Date_Parser
{

  /**
   * Send Excel Date String Value Here
   * @param [type] $date_from_excel [description]
   * @return instance Excel_Date_Parser
   */
  public function SetDateString($date_from_excel) {
    $this->date_from_excel = $date_from_excel;
    return $this;
  }

  /**
   * Set Date Format Here, default is "Y-m-d"
   * @param string $set_format_date [description]
   */
  public function SetDateFormat($set_format_date = "Y-m-d") {
    $this->set_format_date = $set_format_date;
  }

  /**
   * Get what format is set
   */
  public function GetDateFormat() {
    return $this->set_format_date;
  }

  /**
   * Return PHP date according to Set Format from Excel Date
   * @return string PHP date
   */
  public function GetDateString() {

   //if value is valid date string
    if (strtotime($this->date_from_excel)) {

      /**
       * Excel stores dates and times as a number representing the number of days since 1900-Jan-0,
       * plus a fractional portion of a 24 hour day: ddddd.tttttt.
       * This is called a serial date, or serial date-time.
       *
       * @source: https://stackoverflow.com/questions/17808750/reading-xls-date-in-php
       */
      if (is_float($this->date_from_excel)) {

       //date format 2015-25-12
        $this->SetDateFormat("Y-d-m");
        $this->date_from_excel = date($this->GetDateFormat() , (mktime(0, 0, -1, 1, $this->date_from_excel, 1900)));
      } 
      else {

       //date format 2015-12-25
        $this->SetDateFormat();

       //return converted date string in PHP format date format 2015-12-25
        $this->date_from_excel = date($this->GetDateFormat() , strtotime($this->date_from_excel));
      }
    }

    /**
     * Excel stores dates and times as a number representing the number of days since 1900-Jan-0,
     * plus a fractional portion of a 24 hour day: ddddd.tttttt .
     * This is called a serial date, or serial date-time.
     *
     * According to excel format 41397 is 2013-05-03
     * @source: https://stackoverflow.com/questions/17808750/reading-xls-date-in-php
     */
    else if (is_integer($this->date_from_excel)) {
      $this->SetDateFormat();
      $this->date_from_excel = gmdate($this->GetDateFormat() , (($this->date_from_excel - MIN_DATES_DIFF) * SEC_IN_DAY));
    }

   //return real value
    else {
      $this->date_from_excel = $this->date_from_excel;
    }

   //return date
    return $this->date_from_excel;
  }
}


/* ----------------- */
/* Excel_Reader.php */
/* ----------------- */

/* Show errors */
error_reporting(1);

/* display error */
ini_set('display_errors', 1);

/**
* Using class SimpleXLSX 
* 
* Big Thanks!!!! to Sergey Shuchkin, Who made Excel Reader Class
* 
* This class can be used to parse and retrieve data from Excel XLS spreadsheet files.
* It can parse a given Excel XLS file by extracting its contents files and parsing the 
* contained xml spreadsheet file.
*
* The class provides functions to retrieve data for the spreadsheet worksheets, rows and cells.
*
* @link: http://www.phpclasses.org/package/6279-PHP-Parse-and-retrieve-data-from-Excel-XLS-files.html
* @author: Sergey Shuchkin
* @download: http://www.phpclasses.org/browse/download/zip/package/6279/name/simple-xlsx-2013-10-13.zip
*/
require_once 'SimpleXLSX.php';


/* Adding my class Excel_Date_Parser */
require_once 'Excel_Date_Parser.php';


/**
 * [toPhpDate description]
 * @param [type] $array [description]
 */
function toPhpDate($array) {

 //create class object
  $ed = new Excel_Date_Parser();

 //parse array and set
  $array['Consumer_Date'] = $ed->SetDateString($array['Consumer_Date'])->GetDateString();
  $array['Delivery_Date'] = $ed->SetDateString($array['Delivery_Date'])->GetDateString();
  $array['Date_of_Dispatch'] = $ed->SetDateString($array['Date_of_Dispatch'])->GetDateString();
  $array['Gift_Date_Created'] = $ed->SetDateString($array['Gift_Date_Created'])->GetDateString();
  $array['Active_Date'] = $ed->SetDateString($array['Active_Date'])->GetDateString();
  $array['Approved_Date'] = $ed->SetDateString($array['Approved_Date'])->GetDateString();

 //return PHP array
  return $array;
}

// make xls object
$xlsx = new SimpleXLSX('Sony_RedemptionFormat 8-Dec-15.xlsx');

// get excel data as array
$Excel_Array_Data = $xlsx->rows();

// Get Column Name
$Excel_Column = $Excel_Array_Data[0];

// Remove Column Name From Array
unset($Excel_Array_Data[0]);

// Rest Data is Excel Data without Column
$Excel_Data = $Excel_Array_Data;

// Combine array for inserting in database
foreach ($Excel_Array_Data as $key => $Excel_Data) {
  $insert_data[] = array_combine($Excel_Column, $Excel_Data);
}

// show array data
echo "
", print_r($insert_data, true);

// update array excel date
$insert_data = array_map('toPhpDate', $insert_data);

// show array data after update date
echo "
", print_r($insert_data, true);

Ik hoop dat deze code iemand zal helpen. Ik worstelde hetzelfde probleem Dus ik heb dit kleine script gemaakt om anderen tijd te besparen.

Happy PHPING !!!! :)

0
toegevoegd
Gebruiker vraagt ​​om een ​​oplossing met PHPExcel. Het installeren van een hele nieuwe bibliotheek is geen oplossing.
toegevoegd de auteur Isidro Moran, de bron