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;$jgetactivesheet()->getcell(a.$j)->getvalue();//获取a列的值 $b = $objphpexcel->getactivesheet()->getcell(b.$j)->getvalue();//获取b列的值 $sql = insert into table values(.$a.,.$b.); mysql_query($sql); }
例2,代码如下:
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 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`createtableif notexists`alumni`(`id`bigint(20) notnullauto_increment, `gid`varchar(20)default nullcomment'档案编号', `student_no`varchar(20)default nullcomment'学号', `name`varchar(32)default null, primarykey(`id`) , key`gid`(`gid`) , key`name`(`name`)) engine = myisamdefault charset = utf8;
php程序, 代码如下:
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 sheets[0]['numrows']; $i++) {for ($j = 1; $j 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();
永久地址：
转载随意~请带上教程地址吧^^