Laravel迁移中Excel日期导入MySQL的格式问题解决问询

问题描述 #

将Excel中的日期以datetime/timestamp类型存入MySQL时，触发格式错误：

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '44839.319074074' for column it-graph.backlogs.opened at row 1

改为string类型存储后，数据库中显示的是上述数字，而非Excel里的YYYY.MM.DD HH:MM:SS格式。

现有代码 #

迁移文件（migrate）

public function up()
{
    Schema::create('backlogs', function (Blueprint $table) {
        $table->id();
        $table->string('number');
        $table->string('opened');
        $table->string('updated');
        $table->string('description');
        $table->string('caller');
        $table->string('configuration');
        $table->string('state');
        $table->string('creatorGroup');
        $table->string('assignmentGroup')->nullable();
        $table->string('assignedTo')->nullable();
        $table->string('priority');
        $table->string('updatedBy');
        $table->float('elapsedPercentage',8,2);
    });
}

Controller导入代码

function importData(Request $request){

   $the_file = $request->file('uploaded_file');
   try{
       $spreadsheet = IOFactory::load($the_file->getRealPath());
       $sheet        = $spreadsheet->getActiveSheet();
       $row_limit    = $sheet->getHighestDataRow();
       $column_limit = $sheet->getHighestDataColumn();
       $row_range    = range( 2, $row_limit );
       $column_range = range( 'M', $column_limit );
       $startcount = 2;
       $data = array();
       foreach ( $row_range as $row ) {
           $data[] = [
               'number' =>$sheet->getCell( 'A' . $row )->getValue(),
               'opened' => $sheet->getCell( 'B' . $row )->getValue(),
               'updated' => $sheet->getCell( 'C' . $row )->getValue(),
               'description' => $sheet->getCell( 'D' . $row )->getValue(),
               'caller' => $sheet->getCell( 'E' . $row )->getValue(),
               'configuration' =>$sheet->getCell( 'F' . $row )->getValue(),
               'state' =>$sheet->getCell( 'G' . $row )->getValue(),
               'creatorGroup' =>$sheet->getCell( 'H' . $row )->getValue(),
               'assignmentGroup' =>$sheet->getCell( 'I' . $row )->getValue(),
               'assignedTo' =>$sheet->getCell( 'J' . $row )->getValue(),
               'priority' =>$sheet->getCell( 'K' . $row )->getValue(),
               'updatedBy' =>$sheet->getCell( 'L' . $row )->getValue(),
               'elapsedPercentage' =>$sheet->getCell( 'M' . $row )->getValue(),
           ];
           $startcount++;
       }
       DB::table('backlogs')->insert($data);
   } catch (Exception $e) {
       $error_code = $e->errorInfo[1];
       return back()->withErrors('There was a problem uploading the data!');
   }
   return back()->withSuccess('Great! Data has been successfully uploaded.');
}

原因分析 #

Excel中的日期本质是浮点数（从1900-01-00开始的累计天数+小数部分代表时分秒），直接用getValue()获取的是原始数值，而非格式化后的日期字符串。因此存入datetime字段会因格式不匹配报错，存入string字段则会保留原始数字。

解决方案 #

1. 修改数据库字段类型 #

将opened和updated字段改为datetime类型，这是存储日期时间的标准类型，支持后续的日期查询和计算。

若表已创建，生成修改迁移：

执行命令创建迁移文件：

php artisan make:migration modify_backlogs_dates --table=backlogs

修改新迁移文件内容：

public function up()
{
    Schema::table('backlogs', function (Blueprint $table) {
        $table->datetime('opened')->change();
        $table->datetime('updated')->change();
    });
}

public function down()
{
    Schema::table('backlogs', function (Blueprint $table) {
        $table->string('opened')->change();
        $table->string('updated')->change();
    });
}

执行迁移：

php artisan migrate

2. 调整Controller中的日期转换逻辑 #

使用PhpSpreadsheet的工具类将Excel日期数值转换为MySQL支持的标准日期格式：

use Carbon\Carbon;
use PhpOffice\PhpSpreadsheet\Shared\Date;

function importData(Request $request){

    $the_file = $request->file('uploaded_file');
    try{
        $spreadsheet = IOFactory::load($the_file->getRealPath());
        $sheet        = $spreadsheet->getActiveSheet();
        $row_limit    = $sheet->getHighestDataRow();
        $row_range    = range( 2, $row_limit );
        $data = array();
        foreach ( $row_range as $row ) {
            // 转换Excel日期数值为标准格式
            $openedValue = $sheet->getCell( 'B' . $row )->getValue();
            $openedDate = Date::excelToDateTimeObject($openedValue)->format('Y-m-d H:i:s');

            $updatedValue = $sheet->getCell( 'C' . $row )->getValue();
            $updatedDate = Date::excelToDateTimeObject($updatedValue)->format('Y-m-d H:i:s');

            $data[] = [
                'number' =>$sheet->getCell( 'A' . $row )->getValue(),
                'opened' => $openedDate,
                'updated' => $updatedDate,
                'description' => $sheet->getCell( 'D' . $row )->getValue(),
                'caller' => $sheet->getCell( 'E' . $row )->getValue(),
                'configuration' =>$sheet->getCell( 'F' . $row )->getValue(),
                'state' =>$sheet->getCell( 'G' . $row )->getValue(),
                'creatorGroup' =>$sheet->getCell( 'H' . $row )->getValue(),
                'assignmentGroup' =>$sheet->getCell( 'I' . $row )->getValue(),
                'assignedTo' =>$sheet->getCell( 'J' . $row )->getValue(),
                'priority' =>$sheet->getCell( 'K' . $row )->getValue(),
                'updatedBy' =>$sheet->getCell( 'L' . $row )->getValue(),
                'elapsedPercentage' =>$sheet->getCell( 'M' . $row )->getValue(),
            ];
        }
        DB::table('backlogs')->insert($data);
    } catch (Exception $e) {
        return back()->withErrors('数据上传失败：'.$e->getMessage());
    }
    return back()->withSuccess('数据已成功上传！');
}

3. 验证效果 #

重新导入Excel文件，此时opened和updated字段会以YYYY-MM-DD HH:MM:SS格式存入MySQL，查询时可正常显示或进行日期相关操作。

内容的提问来源于stack exchange，提问作者senozanAleyna