php讀取EXCEL文件 php excelreader讀取excel文件

更新時間：2012年12月06日 11:11:46 作者：

php開發(fā)中肯定會遇到將excel文件內容導入到數(shù)據(jù)庫的需要，php-excel-reader可以很輕松的使用它讀取excel文件,本文將詳細介紹，需要了解的朋友可以參考下

php開發(fā)中肯定會遇到將excel文件內容導入到數(shù)據(jù)庫的需要，php-excel-reader是一個讀取excel的類，可以很輕松的使用它讀取excel文件非常方便。

php-excel-reader下載地址： http://www.dbjr.com.cn/codes/67223.html

我下載的是php-excel-reader-2.21版本，使用的時候還遇到幾個小問題，后面再細說，先奉上php實例：

我使用的excel如下圖：

php-excel-reader php代碼如下：

復制代碼代碼如下:

 
<?php 
/*by www.phpddt.com*/ 
header("Content-Type:text/html;charset=utf-8"); 
require_once 'excel_reader2.php'; 
//創(chuàng)建對象 
$data = new Spreadsheet_Excel_Reader(); 
//設置文本輸出編碼 
$data->setOutputEncoding('UTF-8'); 
//讀取Excel文件 
$data->read("example.xls"); 
//$data->sheets[0]['numRows']為Excel行數(shù) 
for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) { 
//$data->sheets[0]['numCols']為Excel列數(shù) 
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) { 
//顯示每個單元格內容 
echo $data->sheets[0]['cells'][$i][$j].' '; 
} 
echo '<br>'; 
} 
?> 

讀取結果截圖如下：

php-excel-reader讀取excel文件 再來說說這個類的小問題：

(1)出現(xiàn)Deprecated: Function split() is deprecated in 。。。錯誤

解決：將excel_reader2.php源碼中split改為explode,詳情點擊php中explode與split的區(qū)別介紹

(2)出現(xiàn)Deprecated: Assigning the return value of new by reference is deprecated in錯誤

解決：將excel_reader2.php源碼中$this->_ole =& new OLERead()中 &去掉，因為php5.3中廢除了=& 符號直接用=引用

(3)亂碼問題解決：

構造函數(shù)是function Spreadsheet_Excel_Reader($file='',$store_extended_info=true,$outputEncoding='')，它默認的編碼是utf-8,如果不指定，可能會出現(xiàn)亂碼問題，可通過$data->setOutputEncoding('GBK');指定，還有如果你使用dump()函數(shù)，dump()函數(shù)將excel內容一html格式輸出,使用htmlentities將字符轉化為html的,它默認使用ISO8559-1編碼的，所以你要將 excel_reader2.php源碼中 htmlentities($val)函數(shù)改為htmlentities($val,ENT_COMPAT,"GB2312");才行。

最后來說說，php-excel-reader操作excel中的兩個重要的方法：

1.dump(),它可以將excel內容以html格式輸出：

echo $data->dump(true,true);

2.將excel數(shù)據(jù)存入數(shù)組中，使用$data->sheets，打印下如下：

復制代碼代碼如下:

 
Array 
( 
[0] => Array 
( 
[maxrow] => 0 
[maxcol] => 0 
[numRows] => 5 
[numCols] => 4 
[cells] => Array 
( 
[1] => Array 
( 
[1] => 編號 
[2] => 姓名 
[3] => 年齡 
[4] => 學號 
) 
[2] => Array 
( 
[1] => 1 
[2] => 小紅 
[3] => 22 
[4] => a1000 
) 
[3] => Array 
( 
[1] => 2 
[2] => 小王 
[3] => 33 
[4] => a1001 
) 
[4] => Array 
( 
[1] => 3 
[2] => 小黑 
[3] => 44 
[4] => a1002 
) 
[5] => Array 
( 
[2] => by 
[3] => www.phpddt.com 
) 
) 
[cellsInfo] => Array 
( 
[1] => Array 
( 
[1] => Array 
( 
[xfIndex] => 15 
) 
[2] => Array 
( 
[xfIndex] => 15 
) 
[3] => Array 
( 
[xfIndex] => 15 
) 
[4] => Array 
( 
[xfIndex] => 15 
) 
) 
[2] => Array 
( 
[1] => Array 
( 
[string] => 1 
[raw] => 1 
[rectype] => unknown 
[format] => %s 
[formatIndex] => 0 
[fontIndex] => 0 
[formatColor] => 
[xfIndex] => 15 
) 
[2] => Array 
( 
[xfIndex] => 15 
) 
[3] => Array 
( 
[string] => 22 
[raw] => 22 
[rectype] => unknown 
[format] => %s 
[formatIndex] => 0 
[fontIndex] => 0 
[formatColor] => 
[xfIndex] => 15 
) 
[4] => Array 
( 
[xfIndex] => 15 
) 
) 
[3] => Array 
( 
[1] => Array 
( 
[string] => 2 
[raw] => 2 
[rectype] => unknown 
[format] => %s 
[formatIndex] => 0 
[fontIndex] => 6 
[formatColor] => 
[xfIndex] => 23 
) 
[2] => Array 
( 
[xfIndex] => 23 
) 
[3] => Array 
( 
[string] => 33 
[raw] => 33 
[rectype] => unknown 
[format] => %s 
[formatIndex] => 0 
[fontIndex] => 6 
[formatColor] => 
[xfIndex] => 23 
) 
[4] => Array 
( 
[xfIndex] => 23 
) 
) 
[4] => Array 
( 
[1] => Array 
( 
[string] => 3 
[raw] => 3 
[rectype] => unknown 
[format] => %s 
[formatIndex] => 0 
[fontIndex] => 0 
[formatColor] => 
[xfIndex] => 15 
) 
[2] => Array 
( 
[xfIndex] => 15 
) 
[3] => Array 
( 
[string] => 44 
[raw] => 44 
[rectype] => unknown 
[format] => %s 
[formatIndex] => 0 
[fontIndex] => 0 
[formatColor] => 
[xfIndex] => 15 
) 
[4] => Array 
( 
[xfIndex] => 15 
) 
) 
[5] => Array 
( 
[2] => Array 
( 
[xfIndex] => 15 
) 
[3] => Array 
( 
[xfIndex] => 24 
[hyperlink] => Array 
( 
[flags] => 23 
[desc] => www.phpddt.com 
[link] => http://www.phpddt.co 
) 
) 
) 
) 
) 
[1] => Array 
( 
[maxrow] => 0 
[maxcol] => 0 
[numRows] => 0 
[numCols] => 0 
) 
[2] => Array 
( 
[maxrow] => 0 
[maxcol] => 0 
[numRows] => 0 
[numCols] => 0 
) 
) 