[phpstudy]PHP Excel类读取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;

上面看上去有点乱，下面我来写一个完整的类

 

 代码如下 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内容插入到数据库
$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.phpddt.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.111cn.net

[link] => http://www.phpddt.co
)
)
)
)
)
[1] => Array
(
[maxrow] => 0
[maxcol] => 0
[numRows] => 0
[numCols] => 0
)
[2] => Array
(
[maxrow] => 0
[maxcol] => 0
[numRows] => 0
[numCols] => 0
)
)

[phpstudy]PHP Excel类读取excel文件并且导入数据库

http://m.bbyears.com/jiaocheng/40975.html

推荐访问:phpstorm php面试题 php数组转字符串 php教程