PHP讀取Excel文件方法總結(jié) 2009-02-19 15:02:02
這是我初次接觸用PHP程序來讀取Excel數(shù)據(jù)，剛開始可愁了，本來也沒用過，就開始上網(wǎng)搜索這方面的資料，網(wǎng)上的說法真是眾多風(fēng)云??！終于蒼天不負(fù)有心人，讓我找到了兩種方法與大家分享一下。
第一種方法是先在ACCESS中導(dǎo)入*.xls文件，這樣就可以從ACCESS數(shù)據(jù)庫中讀取數(shù)據(jù)，然后插入到mysql,sql server.....其它數(shù)據(jù)庫，就搞定了。（我主要是導(dǎo)入到MYSQL中
第一種方法源碼：
文件名：access-mysql.php
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "<html xmlns="<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>列表</title>
</head>
<body>
<?php
$filename = "access-mysql.php?";
$pagesize=100; //設(shè)置每一頁顯示的記錄數(shù)
$db = 'D:\web\text\xls_php\a.mdb';
$conn = new COM('ADODB.Connection');
$conn->Open("DRIVER={Driver do Microsoft Access (*.mdb)}; DBQ=$db");
$sql = 'select count(*) from Sheet1';
$res = $conn->Execute($sql);
while (!$res->EOF)
{
$numrows = $res->Fields[0]->Value;
$res->MoveNext();
}
$pages=intval($numrows/$pagesize);
if ($numrows % $pagesize){
$pages++;}
//判斷頁數(shù)設(shè)置
if (isset($_GET["page"]))
{
$page=intval($_GET["page"]);
}
else{
$page=1; //否則，設(shè)置為第一頁
}
//計算記錄偏移量
$offset=$pagesize*($page - 1);
//讀取指定記錄數(shù)
$sql="select * from Sheet1";
$res = $conn->Execute($sql);
echo $res->move($offset);
if (!$res->EOF)
{
$i=0;
?>
<table width="100%" border="1" cellspacing="0">
<tr><!--這一行是我需要的幾個字段名稱-->
<td width="3%">$n</td>
<td width="3%">id</td>
<td width="3%">wuli</td>
<td width="7%">bianhao</td>
<td width="9%">J</td>
<td width="4%">P</td>
<td width="4%">S</td>
<td width="7%">title</td>
<td width="9%">shen</td>
<td width="4%">jieshou</td>
<td width="5%">money</td>
<td width="21%">content</td>
</tr>
<?php
$i=0;
$n = ($page-1)*100;
if($n=($page-1) ."00")
$n = ($page-1)."00";
while (!$res->EOF) {
$n++;
$i++;
$id = $res->Fields["id"]->Value;
$snno = $res->Fields["wuli"]->Value;
$newid = $res->Fields["bianhao"]->Value;
$stepkey = $res->Fields["J"]->Value;
$secret1 = $res->Fields["P"]->Value;
$secret2 = $res->Fields["S"]->Value;
$memo = $res->Fields["title"]->Value;
$fax = $res->Fields["jieshou"]->Value;
$content = $res->Fields["content"]->Value;
$lianxiren = $res->Fields["shen"]->Value;
?>
<tr <?php if($i%2){echo "bgcolor='#ffffff'";}?> >
<td><?=$n?></td>
<td><?=$id?></td>
<td><?=$snno?></td>
<td><?=$newid?></td>
<td><?=$stepkey?></td>
<td><?=$secret1?></td>
<td><?php if($secret2!="") echo $secret2; else echo " ";?></td>
<td><?php if($memo!="") echo $memo; else echo " ";?></td>
<td><?php if($lianxiren!="") echo $lianxiren; else echo " ";?></td>
<td><?php if($fax!="") echo $fax; else echo " ";?></td>
<td><?php if($res->Fields["ru"]->Value!="") echo $res->Fields["ru"]->Value; else echo " ";?></td>
<td><?php if($content!="") echo $content; else echo " ";?></td>
</tr>
<?php
//以下是把讀取的信息導(dǎo)入到MYSQL數(shù)據(jù)庫中
mysql_connect("localhost","root","") or die ("連接數(shù)據(jù)庫失敗！");
mysql_select_db("test");
$inser_sql = "insert into stepentry(newid,ab,snno,stepkey,secret1,secret2,starttype,startdate,senddate,memo) values ('{$newid}','','{$snno}','{$stepkey}','{$secret1}','{$secret2}','','','','{$memo}')";
echo $inser_sql."<br />";
mysql_query($inser_sql);
if ($i>=$pagesize){break;}
$res->MoveNext();
}
}
$res->Close();
$conn->Close();
$res = null;
$conn = null;
?>
<tr>
<td colspan="16">
<?php
echo "<div align='center'>共有".$pages."頁(".$page."/".$pages.")</br></br>";
$first=1;
$prev=$page-1;
$next=$page+1;
$last=$pages;
if ($page>1)
{
echo "<a href='".$filename."page=".$first."'>[首頁]</a> ";
echo "<a href='".$filename."page=".$prev."'>[上一頁]</a> ";
}
if ($page<$pages)
{
echo "<a href='".$filename."page=".$next."'>[下一頁]</a> ";
echo "<a href='".$filename."page=".$last."'>[尾頁]</a> ";
}
echo "<select name=\"select\" onchange=\"location='".$filename."&page='+this.options[this.selectedIndex].value\">";
for($i=1;$i<=$pages;$i++)
{
echo "<option value='$i'";
if ($i==$page){echo "selected='selected'";}
echo " >$i</option>";
}
echo "</div>";
?>
</select></tr>
</table>
</body>
</html>
第二種方法源碼：
文件名稱：insertsql.php
<?php
session_start();
error_reporting(E_ALL ^ E_NOTICE);//這是屏蔽錯誤信息的
$file = file('a.csv');
$num = count($file);
for($i=0;$i <$num;$i++)
{
list($id1, $id2,$id3,$id4) = explode( ', ',$file[$i],4);
//echo($id1. ' <br /> ');
//echo($id2. ' <br /> ');
//echo($id3. ' <br /> ');
//echo($id4. ' <br /> ');
//echo($id5. ' <br /> ');
$id=explode(',',$id1);//print_r($id);
$id[0]=str_replace(" ","",$id[0]);
if($id[0]!="" && $id[0]!="物理編號")
{
$sql = mysql_query("select * from test.stepentry where snno='$id[0]'",$link);
//$count = mysql_num_rows($sql);
if(mysql_num_rows($sql)==0)
{
$q = "INSERT INTO test.stepentry(newid,originid,snno,stepkey,secret1,secret2,starttype,startdate,senddate,memo,fromman,toman) values ('$id[1]','','$id[0]','$id[2]','$id[3]','$id[4]','','','','$id[5]','$id[6]','$id[8]')";
echo $q."<br />";
mysql_query($q,$link);
}
else
{
$q = "update test.stepentry set fromman='$id[6]',toman='$id[8]' where snno='$id[0]'";
echo $q."<br />";
mysql_query($q,$link);
}
}
}
exit;
?>
