使用 PHP 将 Excel 数据导入 MySQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39891908/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Import Excel Data into MySQL Database using PHP
提问by 0xCFkZr47
I have a website. The website shows a schedule of school.
我有一个网站。该网站显示了学校的时间表。
Every day might have some changes, for example: John calls and informs he is sick so he can't arrive tomorrow. His students should have a substitute teacher.
每天可能会有一些变化，例如：约翰打电话通知他生病了，所以他明天不能来。他的学生应该有一个代课老师。
So, I have an email address that should get a mail containing the Excel file of the changes. and what I want is create a PHP code that take the file from the eMail and import it to SQL Database, and show it in the website. of course - it's should be done automatically and done on a daily basis.
所以，我有一个电子邮件地址，应该会收到一封包含更改的 Excel 文件的邮件。我想要的是创建一个 PHP 代码，从电子邮件中获取文件并将其导入 SQL 数据库，并在网站上显示它。当然 - 它应该自动完成并每天完成。
If you have a better idea or a better way for how to "code" the site - I will be happy to hear it.
如果您对如何“编码”网站有更好的想法或更好的方法 - 我会很高兴听到的。
BTW I found a little piece of code in PHP from Webslesson
顺便说一句，我从Webslesson找到了一小段 PHP 代码
Database Table
数据库表
CREATE TABLE IF NOT EXISTS `tbl_excel` (
`excel_id` INT(11) NOT NULL AUTO_INCREMENT,
`excel_name` VARCHAR(250) NOT NULL,
`excel_email` VARCHAR(300) NOT NULL,
PRIMARY KEY (`excel_id`)
)
ENGINE = MyISAM
DEFAULT CHARSET = latin1
AUTO_INCREMENT = 1;
index.php
索引.php
<?php
$connect = mysqli_connect("localhost", "root", "", "test_db");
include ("PHPExcel/IOFactory.php");
$html="<table border='1'>";
$objPHPExcel = PHPExcel_IOFactory::load('example.xls');
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet)
{
$highestRow = $worksheet->getHighestRow();
for ($row=2; $row<=$highestRow; $row++)
{
$html.="<tr>";
$name = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(0, $row)->getValue());
$email = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(1, $row)->getValue());
$sql = "INSERT INTO tbl_excel(excel_name, excel_email) VALUES ('".$name."', '".$email."')";
mysqli_query($connect, $sql);
$html.= '<td>'.$name.'</td>';
$html .= '<td>'.$email.'</td>';
$html .= "</tr>";
}
}
$html .= '</table>';
echo $html;
echo '<br />Data Inserted';
?>
回答by pawan sen
this code working fine
这段代码工作正常
$file = "your-file.xls";
$handle = fopen($file, "r");
$c = 0;
while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
{
$name = $filesop[0];
$email = $filesop[1];
$sql = mysql_query("INSERT INTO xls (name, email) VALUES ('$name','$email')");
}
if($sql){
echo "You database has imported successfully";
}else{
echo "Sorry! There is some problem.";
}
Try this
尝试这个
