phpexcel 将excel数据导入到mysql数据库_phpexcel导入excel到mysql数据库(示例)

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 student(typeId,name,sex,age) values ($strs[0],'$strs[1]','$strs[2]',$strs[3])";

echo $sql."

";

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."条数据！！！

";

echo "插入失败".$error_result."条数据！！！";

?>