首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 网站开发 > PHP >

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

2012-08-21 
phpexcelreader读取excel自动生成表和字段这个主要是采用phpexcelreader的功能实现,根据excel的文件名和ex

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 name="upfile" id="files" />          </label>          <label>            <input type="submit" name="button" id="button" value="上传" />          </label>      </span>        <label></label>      <span name="code"><?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;}}?>

热点排行