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 ) )
本文地址:
转载随意,但请附上文章地址:-)
上一篇: PHP Cookie中保存数组
下一篇: 身份证 中提取生日
推荐阅读
-
php读取EXCEL文件 php excelreader读取excel文件
-
php excel reader读取excel内容存入数据库实现代码
-
php读取excel文件的简单实例
-
如何将一个EXCEL文件作为二进制文件存入数据库,再把它读取打开?
-
Excel导入数据库(php版)
-
php导出中文内容excel文件类实例
-
php读取excel文件示例分享(更新修改excel)
-
C#_Excel数据读取与写入_自定义解析封装类_支持设置标题行位置&使用excel表达式收集数据&单元格映射&标题映射&模板文件的参数数据替换(第二版-增加深度读取和更新功能)
-
php读取EXCEL文件 php excelreader读取excel文件
-
基于PHP读取TXT文件向数据库导入海量数据的方法