做了一个phpExcel导入excel数据表的测试,有几个需要注意的细节:
1,PHPExcel_IOFactory::createReader('Excel2007')。注意参数,一般为Excel5,Excel2007
2,excel列与数据表字段的对应关系,以及数据类型
3,注意sql语句
HTML
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="utf-8" />
</head>
<body>
<form action="upload.php" method="post" enctype="multipart/form-data">
<input type="hidden" name="leadExcel" value="true">
<table align="center" width="90%" border="0">
<tr>
<td>
<input type="file" name="inputExcel"><input type="submit" value="导入数据">
</td>
</tr>
</table>
</form>
</body>
</html>
upload.php
<?
set_time_limit(0);
if($_POST['leadExcel'] == "true")
{
$filename = $_FILES['inputExcel']['name'];
$tmp_name = $_FILES['inputExcel']['tmp_name'];
$msg = uploadFile($filename,$tmp_name);
header("Content-type: text/html; charset=utf-8");
echo $msg;
}
//导入Excel文件
function uploadFile($file,$filetempname)
{
//自己设置的上传文件存放路径
$filePath = 'upFile/';
$msg = "";
//下面的路径按照你PHPExcel的路径来修改
require_once './PHPExcel.php';
require_once './PHPExcel/IOFactory.php';
require_once './PHPExcel/Reader/Excel2007.php';
//注意设置时区
$time=date("y-m-d-H-i-s");//去当前上传的时间
//获取上传文件的扩展名
$extend=strrchr ($file,'.');
//上传后的文件名
$name=$time.$extend;
$uploadfile=$filePath.$name;//上传后的文件名地址
$result=move_uploaded_file($filetempname,$uploadfile);//假如上传到当前目录下
if($result)
{
require_once './Pdo.php';
$db = new DbTemplate();
$objReader = PHPExcel_IOFactory::createReader('Excel2007');//use excel2007 for 2007 format
$objPHPExcel = $objReader->load($uploadfile);
//$sheet = $objPHPExcel->getSheet(0);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);//总列数
$headtitle=array();
for ($row = 2;$row <= $highestRow;$row++)
{
$strs=array();
//注意highestColumnIndex的列数索引从0开始
for ($col = 0;$col < $highestColumnIndex;$col++)
{
$strs[$col] =$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
}
$sql = "INSERT INTO tuniu_coupon(`coupon_no`, `coupon_pwd`, `coupon_money`, `expire_date`) VALUES (
'{$strs[0]}','{$strs[1]}',{$strs[3]},'{$strs[2]}')";
//die($sql);
if(!$db->query($sql))
{
return false;
$msg = 'sql语句有误';
}
if($row==2) break; //测试
}
$msg = "导入成功!";
}
else
{
$msg = "导入失败!";
}
unlink($uploadfile); //删除上传的excel文件
return $msg;
}
?>
本实现来使用PHPExcel version 1.7.9