PHP导入Excel到MySQL的方法
时间:2022-03-13 23:20
PHP-ExcelReader,下载地址:
注意点:
reader.php 中的下面这行要修改
<?php
header("Content-Type:text/html;charset=utf-8"); //设置字体编码,避免中文乱码
?>
<!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=gb2312" />
<title>导入测试</title>
</head>
<body>
<script>
function import_check(){
var f_content = form1.file.value;
var fileext=f_content.substring(f_content.lastIndexOf("."),f_content.length)
fileext=fileext.toLowerCase()
if (fileext!='.xls')
{
alert("对不起,导入数据格式必须是xls格式文件哦,请您调整格式后重新上传,谢谢 !");
return false;
}
}
</script>
<table width="98%" border="0" align="center" style="margin-top:20px; border:1px solid #9abcde;">
<form id="form1" name="form1" enctype="multipart/form-data" method="post" action="insert.php">
<tr>
<td width="18%" height="50"> 选择你要导入的数据表:</td>
<td width="82%">
<label>
<input name="file" type="file" id="file" size="50" />
</label>
<label>
<input name="button" type="submit" class="nnt_submit" id="button" value="导入数据" onclick="import_check();"/>
</label>
</td>
</tr>
</form>
</table>
</body>
</html>
<?php header("Content-Type:text/html;charset=utf-8"); //设置字体编码,避免中文乱码 require_once("../db_config.php"); require_once 'Excel/reader.php'; error_reporting(E_ALL ^ E_NOTICE); //ini_set('max_execution_time', '100'); //php运行时间为30秒,当数据量大时,会出现超时而导致无法全部导入的情况。 if($_POST) { $Import_TmpFile = $_FILES['file']['tmp_name']; // $Import_TmpFile = 'http://test998-merchphoto.stor.sinaapp.com/test.xls'; $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('utf-8'); $data->read($Import_TmpFile); $count =0; for($i= 1; $i<= $data->sheets[0]['numRows']; $i++) { $sql= "INSERT INTO test_xls(id,tm,name) VALUES('". $data->sheets[0]['cells'][$i][1]."','". $data->sheets[0]['cells'][$i][2]."','". $data->sheets[0]['cells'][$i][3]."')"; echo $sql."</br>"; if(mysql_query($sql)) { $count++; } } echo "<script>alert('成功导入".$count."条数据');</script>"; } ?>
mysql 表:
excel:
参考资料:
1、http://jason2016.blog.51cto.com/892969/289411
2、http://www.cnblogs.com/phpzxh/archive/2009/09/16/1568133.html
3、http://blog.csdn.net/china_skag/article/details/7098473