首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >我从Excel文件导入到Mysql,但结果是1970-01-01

问我从Excel文件导入到Mysql,但结果是1970-01-01
EN

Stack Overflow用户
提问于 2016-07-06 04:12:10
回答 2查看 137关注 0票数 0

我从Excel文件导入到Mysql,但结果是1970-01-01,这是我的代码:

代码语言:javascript
复制
// server info
$server = 'localhost';
$user = 'root';
$pass = '';
$db = 'agenda';

// connect to the database
$mysqli = new mysqli($server, $user, $pass, $db);

// show errors (remove this line if on a live site)
mysqli_report(MYSQLI_REPORT_ERROR);

?>

<?php
require 'classes/phpexcel.php';
require_once 'classes/phpexcel/iofactory.php';
$inputFileName  = 'database/sql/agenda.xlsx';
$inputFileType  = 'Excel2007';
$objReader      = PHPExcel_IOFactory::createReader("$inputFileType");
$objReader->setReadDataOnly(true);
$objPHPExcel    = $objReader->load("$inputFileName");
$objWorksheet   = $objPHPExcel->getActiveSheet();
$highestRow     = $objWorksheet->getHighestRow();
$highestColumn  = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);

// view in table
echo '<table>' . "\n";
for ($row = 1; $row <= $highestRow; ++$row) {
    echo '<tr>' . "\n";
    for ($col = 0; $col <= $highestColumnIndex; ++$col) {
        echo '<td>' . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . '</td>' . "\n";
    }
    echo '</tr>' . "\n";
}
echo '</table>' . "\n";

//input to mysql
for($row = 2; $row <= $highestRow; ++$row) {
    for($col = 0; $col < $highestColumnIndex; ++$col) {  
        $rows[$col] = $objWorksheet->getCellByColumnAndRow($col, $row); 
    }
    $id   ="$rows[0]";
    $ts   ="$rows[1]";
    $pd   ="$rows[2]";

    $get_data1  = "$rows[3]";
    $st   = date("Y-m-d", strtotime ($get_data1));

    $stm  ="$rows[4]";

    $get_data2  ="$rows[5]";
    $et   = date("Y-m-d", strtotime ($get_data2));

    $etm  ="$rows[6]";
    $g    ="$rows[7]";
    $w    ="$rows[8]";
    // Prepared Statement
    $stmt = $mysqli->prepare("INSERT INTO `keyin` VALUES ('$id', '$ts', '$pd', '$st', '$stm', '$et', '$etm', '$g', '$w')");
    //Prepared Statement Bound$stmt->bind_param('sssssssss', $id, $ts, $pd, $st, $stm, $et, $etm, $g, $w);     


    //Prepared Statement Executed 
    $stmt->execute();
    printf("%s Row Inserted.\n", $stmt->affected_rows);
    //Prepared Statement Closed
    $stmt->close();

    // If you don't want to use prepared statement, you can use this one

    $mysqli->query("INSERT INTO users (Username,Email,Gender,Country) VALUES ('$rows[0]', '$rows[1]', '$rows[2]', '$rows[3]')");
}
$mysqli->close();
EN

回答 2

Stack Overflow用户

发布于 2016-07-06 04:37:58

由于excel计算自1900-01-01以来的天数,因此不能使用strtotime()。

您需要做的是将excel天数转换为秒。

代码语言:javascript
复制
$unix = ($get_data1 - 25569) * 86400; 
$st = date("Y-m-d", $unix);

您的excel值~42000不到一天的秒数(86400),因此在php中得到1970-01-01。

如果您将时间添加到php代码中,您将看到时间将在1970年1月1日中午左右。

https://3v4l.org/teI1F

票数 1
EN

Stack Overflow用户

发布于 2016-07-06 04:42:42

试试这个:

代码语言:javascript
复制
$st = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($get_data1));
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/38211921

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档