在一般情况下如果我们要把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 ) )
本文地址：
转载随意，但请附上文章地址:-)