php 读取excel 中数据库数据库,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;

上面看上去有点乱,下面我来写一个完整的类,代码如下:

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内容插入到数据库

$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

)

)

本文地址：

转载随意，但请附上文章地址:-)