php编程时如何将Excel中数据导出到MySQL数据库
savetomysql.php源代码如下：
<?php
$dbname=$_POST[dbname];
$tbname=$_POST[tbname];
mysql_connect("localhost","root","root");
mysql_query("set names gb2312");
mysql_query("drop database ".$dbname."if exists");
mysql_query("create database ".$dbname."");
mysql_query("use ".$dbname."");
mysql_query("drop table ".$tbname."if exists");
mysql_query("CREATE TABLE ".$tbname."(
`id` INT( 8 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`bookname` VARCHAR( 50 ) NOT NULL ,
`pubname` VARCHAR( 50 ) NOT NULL ,
`writer` VARCHAR( 50 ) NOT NULL
) ENGINE = MYISAM ;");
$conn=new com("adodb.connection");
$connstr="Driver={Microsoft Excel Driver (*.xls)};DBQ=".realpath("/book.xls");
//建立一个book.xls 文件
$conn->open($connstr);
$sql="select * from [Sheet1$]";
$rs=$conn->execute($sql);
while(!$rs->eof)
{
$fields=$rs->fields(id);
$id=$fields->value;
$fields=$rs->fields(bookname);
$bookname=$fields->value;
$fields=$rs->fields(pubname);
$pubname=$fields->value;
$fields=$rs->fields(writer);
$writer=$fields->value;
mysql_query("insert into ".$tbname."(id,bookname,pubname,writer)
values('$id','$bookname','$pubname','$writer')");
$rs->movenext;
}
echo "<script>alert('数据导出成功!');history.back();</script>";
?>
index.php源代码如下：
<!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>将Excel中数据导出到MySQL数据库</title>
<link rel="stylesheet" type="text/css" href="style.css">
</head>
<script language="javascript">
function chkinput(form){
if(form.dbname.value==""){
alert("请输入数据库名称!");
form.dbname.select();
return(false);
}
if(form.tbname.value==""){
alert("请输入表名!");
form.tbname.select();
return(false);
}
return(true);
}
</script>
<body>
<table width="200" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td><img src=http://gccde.com/"images/banner.gif" width="499" height="67" /></td>
</tr>
</table>
<table width="499" height="10" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td></td>
</tr>
</table>
<table width="499" height="50" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td height="34" bgcolor="#FBAD62">
<table width="499" height="50" border="0" align="center" cellpadding="0" cellspacing="1">
<tr>
<td width="85" height="25" bgcolor="#FECC9B"><div align="center"><strong>ID</strong></div></td>
<td width="190" bgcolor="#FECC9B"><div align="center"><strong>书名 </strong></div></td>
<td width="123" bgcolor="#FECC9B"><div align="center"><strong>出版社</strong></div></td>
<td width="96" bgcolor="#FECC9B"><div align="center"><strong>作者</strong></div></td>
</tr>
<?php
$conn=new com("adodb.connection");
$connstr="Driver={Microsoft Excel Driver (*.xls)};DBQ=".realpath("excel/book.xls");
$conn->open($connstr);
$sql="select * from [Sheet1$]";
$rs=$conn->execute($sql);
if($rs->eof || $rs->bof)
{
echo "<div align=center>暂无图书信息!</div>";
}
else
{
while(!$rs->eof)
{
?>
<tr>
<td height="25" bgcolor="#FFFFFF"><div align="center">
<?php $fields=$rs->fields(id);echo $fields->value;?></div></td>
<td height="25" bgcolor="#FFFFFF"><div align="center">
<?php $fields=$rs->fields(bookname);echo $fields->value;?></div></td>
<td height="25" bgcolor="#FFFFFF"><div align="center">
<?php $fields=$rs->fields(pubname);echo $fields->value;?></div></td>
<td height="25" bgcolor="#FFFFFF"><div align="center">
<?php $fields=$rs->fields(writer);echo $fields->value;?></div></td>
</tr>
<?php
$rs->movenext;
}
}
?>
</table></td>
</tr>
</table>
<table width="499" height="10" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td></td>
</tr>
</table>
<table width="499" height="25" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td bgcolor="#E79A47">
<table width="499" height="25" border="0" align="center" cellpadding="0" cellspacing="1">
<form name="form1" method="post" action="savetomysql.php" onsubmit="return chkinput(this)">
<tr>
<td bgcolor="#FFFFFF"><div align="center">数据库名：<input type="text" name="dbname" size="10">
表名：<input type="text" name="tbname" size="10">
<input type="submit" value="保存" class="buttoncss">
</div></td>
</tr>
</form>
</table></td>
</tr>
</table>
</body>
</html>
数据库的链接文件
<?php
$conn=mysql_connect("localhost","root","root");
mysql_select_db("db_databade19",$conn);
mysql_query("set names gb2312");
?>
