如何用PHP将Excel导入数据库时转换5位日期数字？

将Excel文件导入数据库时遇到问题：前端读取Excel并转换为对象数组后，通过PHP批量插入数据库，原本应为日期的列被存储为5位数字。

前端读取Excel代码 #

let excelVar = ref([]);

let excelExport = (event) => {
  var input = event.target;
  var reader = new FileReader();
  reader.onload = () => {
    var fileData = reader.result;
    var wb = XLSX.read(fileData, { type: "binary" });
    wb.SheetNames.forEach((sheetName) => {
      var rowObj = XLSX.utils.sheet_to_json(wb.Sheets[sheetName]);
      excelVar.value = JSON.parse(JSON.stringify(rowObj));
       excelBulkUpload(excelVar.value);
      // window.location.reload();
    });
  };
  reader.readAsBinaryString(input.files[0]);
  $q.notify({
    spinner:QSpinnerGears,
    message: upload,
    color: 'positive',
    timeout:1000,
position:"right",

  })
};

PHP批量插入代码 #

else if (array_key_exists('excelDatas', $arr )) {
    //    BULKINSERT
    //    check the size of array

    $excel_rows = sizeof($arr['excelDatas']);
    $check_existed_data = $this->db->rawQuery("SELECT COUNT(*)
FROM(
        SELECT *  FROM tbl_emp_information) AS derived");
        // var_dump( $check_existed_data);
        

    for ($x = 0; $x < $excel_rows ; $x++) {

       
        $insert_data = $this->db->insert('tbl_emp_information', $arr['excelDatas'][$x]);
    }
    
            if($insert_data){
                $query = $this->db->rawQuery("DELETE t1 FROM tbl_emp_information t1, tbl_emp_information t2
                WHERE t1.employee_id > t2.employee_id AND t1.employee_ref_id = t2.employee_ref_id ");

                 $check_existed_updated = $this->db->rawQuery("SELECT COUNT(*)
            FROM(
                    SELECT *  FROM tbl_emp_information) AS derived");
                    // var_dump( $check_existed_data);
                    $existed = $check_existed_data[0]['COUNT(*)'];
                    $updated = $check_existed_updated[0]['COUNT(*)'];
                    $excel_rows = sizeof($arr['excelDatas']);
                    if($existed<$updated){
                            $result = $updated - $existed;
                            http_response_code(200);
                            echo $result . "/" . $excel_rows . " Record Saved";
                    }else{
                        
                        echo "All records existed!";
                        return;
                       
                    };
               
            }
            else{
                
                echo json_encode(array('msg' => "Failed: " . $db->getLastError()));
            }
            return;


        }

方案1：前端读取时直接转换日期（推荐） #

Excel的5位数字是其内置的日期序列化值（从1900年1月1日开始的天数），可以在前端使用XLSX库的配置参数自动转换为日期字符串，避免后端额外处理。

修改前端代码中sheet_to_json的调用，添加日期转换配置：

var rowObj = XLSX.utils.sheet_to_json(wb.Sheets[sheetName], {
    raw: false, // 关闭原始模式，启用自动类型转换
    dateNF: 'yyyy-mm-dd' // 指定输出的日期格式，可根据需求调整为'yyyy/mm/dd'等
});

修改后，前端传递给后端的对象数组中，日期列会直接是格式化后的字符串，PHP可直接插入数据库。

方案2：PHP后端转换日期数值 #

如果无法修改前端代码，可在PHP插入数据前，将Excel日期数值转换为标准日期格式。需要注意Excel的1900闰年bug（错误地将1900年视为闰年），处理逻辑如下：

修改PHP的循环插入部分：

for ($x = 0; $x < $excel_rows ; $x++) {
    $row = $arr['excelDatas'][$x];
    
    // 替换'date_column'为你实际的日期列键名，如有多个日期列需重复处理
    if (isset($row['date_column']) && is_numeric($row['date_column'])) {
        $excel_date = (int)$row['date_column'];
        // 处理Excel 1900闰年bug：数值≤59时无需减1，≥60时减1修正
        $days_to_add = $excel_date <= 59 ? $excel_date : $excel_date - 1;
        $php_date = date('Y-m-d', strtotime('1899-12-31 + ' . $days_to_add . ' days'));
        $row['date_column'] = $php_date;
    }
    
    $insert_data = $this->db->insert('tbl_emp_information', $row);
}

关键说明： #

• Excel日期系统以1900年1月1日为第1天，但实际上1900年不是闰年，Excel错误包含了1900年2月29日，因此数值≤59时直接计算，≥60时需要减1天修正。
• 转换后的日期格式Y-m-d符合MySQL等数据库的DATE类型存储要求，可根据数据库类型调整格式。

内容的提问来源于stack exchange，提问作者Michael Angelo Corral