phpexcelreader读取excel自动生成表跟字段-PHP Tutorial-php.cn

phpexcelreader读取excel自动生成表跟字段

WBOY

Release： 2016-06-13 10:40:11

Original

913 people have browsed it

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>

Copy after login

上传文件处理

<?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 "请选择上传文件！";} ?>

Copy after login

解析文件组装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;	}	}?>

Copy after login