欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  php教程

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

程序员文章站 2022-05-08 20:59:08
...
在一般情况下如果我们要把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; 
上面看上去有点乱,下面我来写一个完整的类,代码如下:
query('set names utf8;');  
}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('bks.xls'); //指定的文件 
$sheet = $objPHPExcel->getSheet(0); 
$highestRow = $sheet->getHighestRow(); // 取得总行数 
$highestColumn = $sheet->getHighestColumn(); // 取得总列数 
 
for($j=1;$jgetActiveSheet()->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  
)  
)


本文地址:

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