search

Home >Backend Development >PHP Tutorial > phpexcelreader读取excel自动生成表跟字段

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

WBOY
WBOYOriginal
2016-06-13 13:20:46919browse

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>


上传文件处理

<?php
if (! 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;
	}
	
}


?>

Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article： php 中 怎么使用 mysqli连接 获取mysql的进程列表，就是类似 show processlist Next article： php 正则形式修正符

Related articles

See more