如何使用php准确地从xls中读取2017年9月9日?

如果您正在使用 rangeToArray() 方法获取数据,然后查看 rangeToArray()

/**
 * Create array from a range of cells
 *
 * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1")
 * @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
 * @param boolean $calculateFormulas Should formulas be calculated?
 * @param boolean $formatData Should formatting be applied to cell values?
 * @param boolean $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero
 *                               True - Return rows and columns indexed by their actual row and column IDs
 * @return array
 */

因此,要获取 你需要用 $formatData 参数设置为 true

$rowData = $sheet->rangeToArray('A'.$row.':' . $highestColumn.$row,NULL,TRUE,TRUE);

float(42987) 当你呼叫时,PHPExcel返回 getValue() 是 单元格的确切值。。。。该浮点通过数字格式掩码转换为MS Excel中的日期/时间显示。您在MS Excel中看到的是 单元格的值,应用数字格式掩码,因此需要告诉phpexel获取格式化值,而不是精确的(原始)值。

只要您没有加载readDataOnly设置为true的文件(这告诉PHPExcel 不

 var_dump($objPHPExcel->getActiveSheet()->getCell('B2')->getFormattedValue());

float(42987) 是下面的确切值。Excel只是以您选择的任何格式将其显示为日期。

使用此类进行转换。

class OLEAutomationDateConverter
{
    /**
     * Get the OLE Automation Date epoch
     *
     * @return DateTimeImmutable
     */
    public static function BaseDate()
    {
        static $baseDate = null;
        if ($baseDate == null) {
            $baseDate = new DateTimeImmutable('1899-12-30 00:00:00');
        }
        return $baseDate;
    }
    /**
     * Convert a DateTime object to a float representing an OLE Automation Date
     *
     * @param DateTimeInterface $dateTime
     * @return float
     */
    public static function DateTimeToOADate(DateTimeInterface $dateTime)
    {
        $interval = self::BaseDate()->diff($dateTime);
        $mSecs = ($interval->h * 3600000)
            + ($interval->i * 60000)
            + ($interval->s * 1000)
            + floor($dateTime->format('u') / 1000);
        return $interval->days + ($mSecs / 86400000);
    }
    /**
     * Convert a float representing an OLE Automation Date to a DateTime object
     *
     * The returned value has a microsecond component, but resolution is millisecond and even
     * this should not be relied upon as it is subject to floating point precision errors
     *
     * @param float $oaDate
     * @return DateTime
     */
    public static function OADateToDateTime($oaDate)
    {
        $days = floor($oaDate);
        $msecsFloat = ($oaDate - $days) * 86400000;
        $msecs = floor($msecsFloat);
        $hours = floor($msecs / 3600000);
        $msecs %= 3600000;
        $mins = floor($msecs / 60000);
        $msecs %= 60000;
        $secs = floor($msecs / 1000);
        $msecs %= 1000;
        $dateTime = self::BaseDate()
            ->add(new DateInterval(sprintf('P%sDT%sH%sM%sS', $days, $hours, $mins, $secs)))
            ->format('Y-m-d H:i:s');
        return new DateTime("$dateTime.$msecs");
    }
}

或者,如果可以使用javascript,请使用矩库。有一个函数可以将OADates转换为和。

https://github.com/markitondemand/moment-msdate#about-ole-automation-dates

moment.fromOADate(41493) 退货 Wed Aug 07 2013 00:00:00 GMT-0600 (MDT)

对于准确的日期和时间(时间是小数点右边的值):

moment.fromOADate(41493.706892280097000) Wed Aug 07 2013 16:57:55 GMT-0600 (MDT)

默认情况下 moment.fromOADate()

moment.fromOADate(42754.835023148145, 360) Fri Jan 20 2017 02:02:25 GMT+0000 (UTC)

对于力矩格式:

//convert OA date into Moment (JavaScript date)
var momentDate = moment.fromOADate(41493.706892280097000);

//use Moment's awesomeness
var formattedDate = momentDate.format('MMM Do YY);

//formattedDate === "Aug 7th 13"

这可以很容易地链接在一起:

moment.fromOADate(41493.706892280097000).format('MMM Do YY); //Aug 7th 13

注意:OLE自动化日期未指定,这意味着默认情况下它们基于本地时区。

我认为有几种方法可以做到这一点,例如 ExcelToPHP() 和 toFormattedString()

$value 要查找的字符串,请执行以下操作:

$string = \PHPExcel_Style_NumberFormat::toFormattedString($value, 'DD/MM/YYYY');