PHPExcel读取excel并导入数据库
PHPExcel是一款php对于excel数据表读写的一个非常棒的插件了,下面我来给大家介绍利用PHPExcel读取excel并导入mysql数据库方法.
例1,代码示例,代码如下:
require_once 'phpexcel/Classes/PHPExcel.php' ; require_once 'phpexcel/Classes/PHPExcel/IOFactory.php' ; require_once 'phpexcel/Classes/PHPExcel/Reader/Excel5.php' ; $objReader = PHPExcel_IOFactory::createReader( 'Excel5' ); //use excel2007 for 2007 format $objPHPExcel = $objReader ->load( $filename ); //$filename可以是上传的文件，或者是指定的文件 $sheet = $objPHPExcel ->getSheet(0); $highestRow = $sheet ->getHighestRow(); // 取得总行数 $highestColumn = $sheet ->getHighestColumn(); // 取得总列数 $k = 0; //循环读取excel文件,读取一条,插入一条 for ( $j =2; $j <= $highestRow ; $j ++) { $a = $objPHPExcel ->getActiveSheet()->getCell( "A" . $j )->getValue(); //获取A列的值 $b = $objPHPExcel ->getActiveSheet()->getCell( "B" . $j )->getValue(); //获取B列的值 $sql = "INSERT INTO table VALUES(" . $a . "," . $b . ")" ; mysql_query( $sql ); }例2,代码如下:
<?php set_time_limit(20000); ini_set ( 'memory_limit' , '-1' ); require_once './PHPExcel.php' ; require_once './PHPExcel/IOFactory.php' ; require_once './PHPExcel/Reader/Excel5.php' ; //使用pdo连接数据库 $dsn = "mysql:host=localhost;dbname=alumni;" ; $user = "root" ; $password = "" ; try{ $dbh = new PDO( $dsn , $user , $password ); $dbh ->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(); // 取得总列数 //开源代码phpfensi测试数据 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(); ?>例3,新建数据库表如下:
-- 数据库: `alumni` -- 表的结构 `alumni` CREATE TABLE IF NOT EXISTS `alumni` ( `id` bigint (20) NOT NULL AUTO_INCREMENT, `gid` varchar (20) DEFAULT NULL COMMENT '档案编号' , `student_no` varchar (20) DEFAULT NULL COMMENT '学号' , ` name ` varchar (32) DEFAULT NULL , PRIMARY KEY (`id`), KEY `gid` (`gid`), KEY ` name ` (` name `) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;php程序,代码如下:
<?php header( "Content-Type:text/html;charset=utf-8" ); require_once 'excel_reader2.php' ; set_time_limit(20000); ini_set ( "memory_limit" , "2000M" ); //使用pdo连接数据库 $dsn = "mysql:host=localhost;dbname=alumni;" ; $user = "root" ; $password = "" ; try{ $dbh = new PDO( $dsn , $user , $password ); $dbh ->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); //使用php-excel-reader读取excel内容 $data = new Spreadsheet_Excel_Reader(); $data ->setOutputEncoding( 'UTF-8' ); $data ->read( "stu.xls" ); for ( $i = 1; $i <= $data ->sheets[0][ 'numRows' ]; $i ++) { for ( $j = 1; $j <= 3; $j ++) { $student_no = $data ->sheets[0][ 'cells' ][ $i ][1]; $name = $data ->sheets[0][ 'cells' ][ $i ][2]; $gid = $data ->sheets[0][ 'cells' ][ $i ][3]; } //将获取的excel内容插入到数据库 $stmt ->execute(); } echo "执行成功" ; echo "最后插入的ID：" . $dbh ->lastInsertId(); ?>查看更多关于PHPExcel读取excel并导入数据库 - php高级应用的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did30459