php开发中肯定会遇到将excel文件内容导入到数据库的需要，php-excel-reader是一个读取excel的类，可以很轻松的使用它读取excel文件非常方便。
php-excel-reader下载地址： http://sourceforge.net/projects/phpexcelreader
我下载的是php-excel-reader-2.21版本，使用的时候还遇到几个小问题，后面再细说，先奉上php实例：
我使用的excel如下图：
php代码如下：
<?php
header(“Content-Type:text/html;charset=utf-8”);
require_once ‘excel_reader2.php’;
//创建对象
$data = new Spreadsheet_Excel_Reader();
//设置文本输出编码
$data->setOutputEncoding(‘UTF-8’);
//读取Excel文件
$data->read(“example.xls”);
//$data->sheets[0][‘numRows’]为Excel行数
for ($i = 1; $i <= $data->sheets[0][‘numRows’]; $i++) {
//$data->sheets[0][‘numCols’]为Excel列数
for ($j = 1; $j <= $data->sheets[0][‘numCols’]; $j++) {
//显示每个单元格内容
echo $data->sheets[0][‘cells’][$i][$j].’ ‘;
}
echo ‘<br>’;
}
?>
读取结果截图如下：
再来说说这个类的小问题：
(1)出现Deprecated: Function split() is deprecated in 。。。错误
解决：将excel_reader2.php源码中split改为explode,详情点击php中explode与split的区别介绍
(2)出现Deprecated: Assigning the return value of new by reference is deprecated in错误
解决：将excel_reader2.php源码中$this->_ole =& new OLERead()中 &去掉，因为php5.3中废除了=& 符号直接用=引用
(3)乱码问题解决：
构造函数是function Spreadsheet_Excel_Reader($file=”,$store_extended_info=true,$outputEncoding=”)， 它默认的编码是utf-8,如果不指定，可能会出现乱码问题，可通过$data->setOutputEncoding(‘GBK’);指定，还有 如果你使用dump()函数，dump()函数将excel内容一html格式输出,使用htmlentities将字符转化为html的,它默认使用 ISO8559-1编码的，所以你要将 excel_reader2.php源码中 htmlentities($val)函数改为htmlentities($val,ENT_COMPAT,”GB2312″);才行。
最后来说说，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.phpddt.com
[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
)
)
这样你应该知道怎么取excel中的数据了，好了，使用php-excel-reader读取excel文件就是这么简单!
因为需要，将excel这样的数据：
新建数据库表如下：
— 数据库: `test`
— 表的结构 `alumni`
CREATE TABLE IF NOT EXISTS `alumni` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`gid` varchar(20) DEFAULT NULL COMMENT ‘档案编号’,
`student_no` varchar(20) DEFAULT NULL COMMENT ‘学号’,
`name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `gid` (`gid`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
导入后数据库结果如下：
php源码如下:
<?php
header(“Content-Type:text/html;charset=utf-8”);
require_once ‘excel_reader2.php’;
set_time_limit(20000);
ini_set(“memory_limit”,”2000M”);
//使用pdo连接数据库
$dsn = “mysql:host=localhost;dbname=test;”;
$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);
//使用php-excel-reader读取excel内容
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding(‘UTF-8’);
$data->read(“stu.xls”);
for ($i = 1; $i <= $data->sheets[0][‘numRows’]; $i++) {
for ($j = 1; $j <= 3; $j++) {
$student_no = $data->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();
?>
考虑到Excel的量比较大，使用了PDO的绑定操作！
评论