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 ( '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(); // 取得总列数 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内容插入到数据库 //开源代码phpfensi测试数据 $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] => HdhCmsTestphpfensi测试数据 ) ) [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] => HdhCmsTestphpfensi测试数据 [link] => http://HdhCmsTestphpfensi测试数据 ) ) ) ) ) [1] => Array ( [maxrow] => 0 [maxcol] => 0 [numRows] => 0 [numCols] => 0 ) [2] => Array ( [maxrow] => 0 [maxcol] => 0 [numRows] => 0 [numCols] => 0 ) )查看更多关于PHP Excel类读取excel文件并且导入数据库 - php高级应的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did30468