第一步：建立資料庫和資料表（按照自己的Excel資料設立欄位）。
[sql] view plain copy print?
CREATE DATABASE php_excel;
USE php_excel;
CREATE TABLE IF NOT EXISTS php_excel(
id int(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
gid varchar(20) NOT NULL,
stu_no varchar(20) NOT NULL,
name varchar(45) NOT NULL,
age int(4) NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
第二步：前臺
index.php
[html] view plain copy print?
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>phpexcel匯入excel資料到MYSQL資料庫</title>
</head>
<body>
<form name="frm1" action="insertdb.php" method="post" enctype="multipart/form-data">
<input name="filename" type="file" />
<input name="submit" type="submit" value="import" />
</form>
</body>
</html>
第三步：向資料庫插入資料的
insertdb.php
[php] view plain copy print?
session_start();
header("Content-type:text/html;charset:utf-8");
//全域性變數
$succ_result=0;
$error_result=0;
$file=$_FILES["filename"];
$max_size="2000000"; //最大檔案限制（單位：byte）
$fname=$file["name"];
$ftype=strtolower(substr(strrchr($fname,"."),1));
//檔案格式
$uploadfile=$file["tmp_name"];
if($_SERVER["REQUEST_METHOD"]=="POST"){
if(is_uploaded_file($uploadfile)){
if($file["size"]>$max_size){
echo "Import file is too large";
exit;
}
if($ftype!="xls"){
echo "Import file type is error";
}else{
echo "The file is not empty!";
require("./conn.php"); //連線mysql資料庫
//呼叫phpexcel類庫
require_once "PHPExcel.php";
require_once "PHPExcel\IOFactory.php";
require_once "PHPExcel\Reader\Excel5.php";
$objReader = PHPExcel_IOFactory::createReader("Excel5");//use excel2007 for 2007 format
$objPHPExcel = $objReader->load($uploadfile);
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow(); // 取得總行數
$highestColumn = $sheet->getHighestColumn(); // 取得總列數
$arr_result=array();
$strs=array();
for($j=2;$j<=$highestRow;$j++)
{
unset($arr_result);
unset($strs);
for($k="A";$k<= $highestColumn;$k++)
//讀取單元格
$arr_result .= $objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue().",";
$strs=explode(",",$arr_result);
$sql="insert into php_excel(gid,stu_no,name,age) values ($strs[0],"$strs[1]","$strs[2]",$strs[3])";
echo $sql."<br/>";
mysql_query("set names utf8");
$result=mysql_query($sql) or die("執行錯誤");
$insert_num=mysql_affected_rows();
if($insert_num>0){
$succ_result+=1;
$error_result+=1;
echo "插入成功".$succ_result."條資料！！！<br>";
echo "插入失敗".$error_result."條資料！！！";
其中
conn.php
$mysql=mysql_connect("localhost","root","") or die("資料庫連線失敗！");
mysql_select_db("php_excel",$mysql);
我的匯入效果如下：
至此，從Excel檔案讀取資料批次匯入到Mysql資料庫完成。
第一步：建立資料庫和資料表（按照自己的Excel資料設立欄位）。
[sql] view plain copy print?
CREATE DATABASE php_excel;
USE php_excel;
CREATE TABLE IF NOT EXISTS php_excel(
id int(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
gid varchar(20) NOT NULL,
stu_no varchar(20) NOT NULL,
name varchar(45) NOT NULL,
age int(4) NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
第二步：前臺
index.php
檔案。[html] view plain copy print?
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>phpexcel匯入excel資料到MYSQL資料庫</title>
</head>
<body>
<form name="frm1" action="insertdb.php" method="post" enctype="multipart/form-data">
<input name="filename" type="file" />
<input name="submit" type="submit" value="import" />
</form>
</body>
</html>
第三步：向資料庫插入資料的
insertdb.php
檔案。[php] view plain copy print?
session_start();
header("Content-type:text/html;charset:utf-8");
//全域性變數
$succ_result=0;
$error_result=0;
$file=$_FILES["filename"];
$max_size="2000000"; //最大檔案限制（單位：byte）
$fname=$file["name"];
$ftype=strtolower(substr(strrchr($fname,"."),1));
//檔案格式
$uploadfile=$file["tmp_name"];
if($_SERVER["REQUEST_METHOD"]=="POST"){
if(is_uploaded_file($uploadfile)){
if($file["size"]>$max_size){
echo "Import file is too large";
exit;
}
if($ftype!="xls"){
echo "Import file type is error";
exit;
}
}else{
echo "The file is not empty!";
exit;
}
}
require("./conn.php"); //連線mysql資料庫
//呼叫phpexcel類庫
require_once "PHPExcel.php";
require_once "PHPExcel\IOFactory.php";
require_once "PHPExcel\Reader\Excel5.php";
$objReader = PHPExcel_IOFactory::createReader("Excel5");//use excel2007 for 2007 format
$objPHPExcel = $objReader->load($uploadfile);
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow(); // 取得總行數
$highestColumn = $sheet->getHighestColumn(); // 取得總列數
$arr_result=array();
$strs=array();
for($j=2;$j<=$highestRow;$j++)
{
unset($arr_result);
unset($strs);
for($k="A";$k<= $highestColumn;$k++)
{
//讀取單元格
$arr_result .= $objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue().",";
}
$strs=explode(",",$arr_result);
$sql="insert into php_excel(gid,stu_no,name,age) values ($strs[0],"$strs[1]","$strs[2]",$strs[3])";
echo $sql."<br/>";
mysql_query("set names utf8");
$result=mysql_query($sql) or die("執行錯誤");
$insert_num=mysql_affected_rows();
if($insert_num>0){
$succ_result+=1;
}else{
$error_result+=1;
}
}
echo "插入成功".$succ_result."條資料！！！<br>";
echo "插入失敗".$error_result."條資料！！！";
其中
conn.php
程式碼如下：[php] view plain copy print?
$mysql=mysql_connect("localhost","root","") or die("資料庫連線失敗！");
mysql_select_db("php_excel",$mysql);
mysql_query("set names utf8");
我的匯入效果如下：
至此，從Excel檔案讀取資料批次匯入到Mysql資料庫完成。