php - 无法使用PHPExcel将Excel中的数据导入到phpMySql

Question

我的代码无法将Excel文件内的数据导入到phpMySql。我花了很多时间来找出错误，但我找不到，所以拜托，有人能帮我度过这段糟糕的时光

运行此代码后，网络浏览器没有显示任何错误，但当我访问 phpMyAdmin 时，没有任何来自 Excel 的数据。

示例.php

<?php  

include ("PHPExcel/IOFactory.php");

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "dashboard_db";
$tbname_act = "average_cycle_time";

$inputFileType = 'Excel2007';
$inputFileName = 'example.xlsx';
$sheetnames = 'ACT' ;

$connect = mysqli_connect($servername, $username, $password, $dbname);
if ($connect -> connect_error){
die ("connection failed: " . $conn -> connect_error);
}
echo "Connection successful ! <br>" ;  

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
$objReader->setLoadSheetsOnly($sheetnames);


$html="<table border='1'>";  
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);  
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet)
{ 
    $highestRow = $worksheet->getHighestRow();  
  for ($row=2; $row<=$highestRow; $row++)  
  {        
       $html.="<tr>";  

       $TruckID = mysqli_real_escape_string($connect, $worksheet- >getCellByColumnAndRow(0, $row)->getValue());  
       $Date = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(1, $row)->getValue());  
       $ArrivalTime = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(2, $row)->getValue());  
       $DepartTime = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(3, $row)->getValue());  

       $sql = "INSERT INTO $tbname_act(excel_TruckID, excel_Date, excel_ArrivalTime, excel_DepartTime) 
                VALUES ('".$TruckID."', '".$Date."', '".$ArrivalTime."', '".$DepartTime."')";  

       mysqli_query($connect, $sql);  

       $html.= '<td>'.$TruckID.'</td>';  
       $html .= '<td>'.$Date.'</td>';
       $html .= '<td>'.$ArrivalTime.'</td>';  
       $html .= '<td>'.$DepartTime.'</td>';   
       $html .= "</tr>";  
   }  
   }  
  $html .= '</table>';  
  echo $html;  
  echo '<br />Time for calculation of Average Cycle Time ... Inserted !!';  
  ?>  

create.sql

 CREATE TABLE IF NOT EXISTS `average_cycle_time` (  
 `TruckID` int(10) NOT NULL,
 `Date` date NOT NULL,  
 `ArrivalTime` timestamp NOT NULL,  
 `DepartTime` timestamp NOT NULL,  
 PRIMARY KEY (`TruckID`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

Data in Excel file(.xslx)

Answer 1

可能是你的sql查询不正确。您可以通过显示 $sql 的值然后从 Phpmyadmin 运行 sql 查询来调试 sql 查询