phpexcelreader读取excel自动生成表跟字段
- WBOY原創
- 2016-06-13 10:40:11889瀏覽
phpexcelreader读取excel自动生成表和字段
这个主要是采用phpexcelreader的功能实现，根据excel的文件名和excel第一行生成表名及字段，数据从第二行开始依次导入。
phpexcelreader下载地址 ： http://sourceforge.net/projects/phpexcelreader/
下载下来有个 “phpExcelReader.zip”的压缩包。解压，我们需要的只有 Excel 文件夹的内容，首先把 “oleread.inc” 改为 “oleread.inc.php”
修改“reader.php”文件
修改第31行 require_once 'Spreadsheet/Excel/Reader/OLERead.php';
改为 ” ： require_once 'oleread.inc.php';
第 261行 =& 改为 = 号就 OK 了
源码及示例文件，以及流程都在下载文件里！
本人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=utf-8" /><title>Excel管理</title><link href="css.css" rel="stylesheet" type="text/css" /></head><body><form action="processExcel.php" method="post" enctype="multipart/form-data" name="form1" id="form1"> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td bgcolor="#eeeeee"><span class="text">请上传你的excel文件: <label> <input type="file" name="upfile" id="files" /> </label> <label> <input type="submit" name="button" id="button" value="上传" /> </label> </span> <label></label> <span class="text"> (注:excel的格式只能是xls) </span></td> </tr> </table></form></body></html>
上传文件处理
<?phpif (! empty ( $_FILES ['upfile'] ['name'] )) { if ($_FILES ['upfile'] ['error'] > 0) { switch ($_FILES ['upfile'] ['error']) { case 1 : $errorMsg = "上传文件超过限制！"; break; case 2 : $errorMsg = "上传文件超过前台指定大小！"; break; case 3 : $errorMsg = "上传文件不完整！"; break; case 4 : $errorMsg = "没有上传文件！"; break; } echo $errorMsg; } else { if (! is_dir ( 'upload' )) { mkdir ( 'upload' ); } //判断文件格式是否正确 $imgType = array ('application/octet-stream' ,'application/vnd.ms-excel'); if (! in_array ( $_FILES ['upfile'] ['type'], $imgType )) { echo $_FILES ['upfile'] ['type'] . "不符合文件类型"; exit (); } if (is_uploaded_file ( $_FILES ['upfile'] ['tmp_name'] )) { $toFileName = 'upload/' . $_FILES ['upfile'] ['name']; //移动临时文件到指定文件夹 if (move_uploaded_file ( $_FILES ['upfile'] ['tmp_name'], $toFileName )) { $excelFileName = $toFileName; } else { echo "文件上传失败！"; } } else { echo "不是上传文件！"; } }} else { echo "请选择上传文件！";} ?>解析文件组装sql
<?php$excelFileName = '';require_once 'uploadfile.php';echo "Excel文件路径：".$excelFileName."<br>";require_once 'reader.php';$data = new Spreadsheet_Excel_Reader();$data->setOutputEncoding('utf8'); //设置数据库连接的用户名及密码$conn= mysql_connect('localhost','root','root') or die("不能连接到数据库！."); //设置编码 mysql_query("set names 'utf8'");//数据库名mysql_select_db('excel'); //echo "是否文件=".is_file($excelFileName)."<br>";if($_POST['button'] && is_file($excelFileName)){ $data->read($excelFileName); $str = explode(".",$excelFileName); $strname = explode("/",$str[0]); $tablename = $strname[1]; echo "表名：".$tablename."<br>"; $existsTable = existsTable($tablename); //echo $existsTable."<br>"; //echo print_r($data->sheets[0]['cells'][1])."<br>"; echo "<hr>"; $fieldCount = count($data->sheets[0]['cells'][1]); if ($existsTable == 0) { $sql = "create table if not exists ".$tablename." (id int primary key auto_increment,"; for ($i = 1; $i<=$fieldCount; $i++) { if (!empty($data->sheets[0]['cells'][1][$i])) { $sql .= $data->sheets[0]['cells'][1][$i]." varchar(255) null,"; } } //去掉最后一个逗号 $sql = substr($sql,0,strlen($sql)-1); $sql .= ")"; echo "创建表sql=".$sql."<br>"; $query=mysql_query($sql); if($query){ $sign = 1; echo "创建表成功！<br>"; }else{ $sign = 0; echo "创建表失败！<br>"; return; } if ($sign == 1) { for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) { /* print_r($data->sheets[0]['cells'][$i]); echo "<br>"; */ $sql = "insert into ".$tablename." values(null,"; for ($j = 1; $j <= count($data->sheets[0]['cells'][$i]); $j++) { $sql .= "'".$data->sheets[0]['cells'][$i][$j]."'".","; } //去掉最后一个逗号 $sql = substr($sql,0,strlen($sql)-1); $sql .= ")"; echo "插入表sql=".$sql."<br>"; $query = mysql_query($sql); if($query){ echo "插入表成功！<br>"; }else{ echo "插入表失败！<br>"; break; } } } } else { for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) { /* print_r($data->sheets[0]['cells'][$i]); echo "<br>"; */ $sql = "insert into ".$tablename." values(null,"; for ($j = 1; $j <= count($data->sheets[0]['cells'][$i]); $j++) { $sql .= "'".$data->sheets[0]['cells'][$i][$j]."'".","; } //去掉最后一个逗号 $sql = substr($sql,0,strlen($sql)-1); $sql .= ")"; echo "插入表sql=".$sql."<br>"; $query = mysql_query($sql); if($query){ echo "插入表成功！<br>"; }else{ echo "插入表失败！<br>"; break; } } } echo "<hr>";}function insertData(){ }//查找是否存在表function existsTable($tablename){ $sql="select * from ".$tablename; $query=mysql_query($sql); if($query){ return 1; }else{ return 0; } }?>
陳述：
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn