PHP Excel类读取excel文件并且导入数据库

在一般情况下如果我们要把excel数据库中的数据导入到mysql数据库我们没什么好办法实现了,但有了PHP Excel失控这后一切都变得简单了.

本代码是在thinkphp中开始使用的.

1.引入类,代码如下:

Vendor('PHPExcel.PHPExcel');//引入扩展类.就是/ 
Vendor('PHPExcel.PHPExcel.IOFactory'); 
Vendor('PHPExcel.PHPExcel.Reader.Excel5'); 
 
$excel_file= ROOT_PATH."/public/Uploads/".$publicity_bankdata_mod->where("id=".$data['id'])->getField('excel_file'); 
//dump($excel_file);exit; 
$objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format 
$objPHPExcel = $objReader->load($excel_file);//$uploadfile 
$sheet = $objPHPExcel->getSheet(0); 
$highestRow = $sheet->getHighestRow(); // 取得总行数 
$highestColumn = $sheet->getHighestColumn(); // 取得总列数 
$arr_result = array(); 
$strs=array(); 
$strs_datas=array(); 
$succ_result=0; 
$error_result=0; 
上面看上去有点乱,下面我来写一个完整的类,代码如下:
<?php 
set_time_limit(20000); 
ini_set(&#39;memory_limit&#39;,&#39;-1&#39;); 
require_once &#39;./PHPExcel.php&#39;; 
require_once &#39;./PHPExcel/IOFactory.php&#39;; 
require_once &#39;./PHPExcel/Reader/Excel5.php&#39;; 
 
//使用pdo连接数据库 
$dsn = "mysql:host=localhost;dbname=alumni;"; 
$user = "root"; 
$password = ""; 
try{ 
 $dbh = new PDO($dsn,$user,$password); 
 $dbh->query(&#39;set names utf8;&#39;);  
}catch(PDOException $e){ 
 echo "连接失败".$e->getMessage(); 
} 
//pdo绑定参数操作 
$stmt = $dbh->prepare("insert into alumni(gid,student_no,name) values (:gid,:student_no,:name) "); 
$stmt->bindParam(":gid", $gid,PDO::PARAM_STR); 
$stmt->bindParam(":student_no", $student_no,PDO::PARAM_STR); 
$stmt->bindParam(":name", $name,PDO::PARAM_STR); 
 
$objReader = new PHPExcel_Reader_Excel5(); //use excel2007 
$objPHPExcel = $objReader->load(&#39;bks.xls&#39;); //指定的文件 
$sheet = $objPHPExcel->getSheet(0); 
$highestRow = $sheet->getHighestRow(); // 取得总行数 
$highestColumn = $sheet->getHighestColumn(); // 取得总列数 
 
for($j=1;$j<=10;$j++) 
{ 
 
$student_no = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();//第一列学号 
$name = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();//第二列姓名 
$gid = $objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();//第三列gid 
} 
//将获取的excel内容插入到数据库 
$stmt->execute();

php-excel-reader操作excel中的两个重要的方法:

1.dump(),它可以将excel内容以html格式输出:echo $data->dump(true,true);

2.将excel数据存入数组中，使用$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.phprm.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.phprm.com 
 
[link] => http://www.phprm.com  
)  
)  
)  
)  
)  
[1] => Array  
(  
[maxrow] => 0  
[maxcol] => 0  
[numRows] => 0  
[numCols] => 0  
)  
[2] => Array  
(  
[maxrow] => 0  
[maxcol] => 0  
[numRows] => 0  
[numCols] => 0  
)  
)

本文地址：

转载随意，但请附上文章地址:-)