PHP导入Excel数据

周翔

　　在Sourceforge上提供了一个开源的PHP导入Excel数据的类，在下载的项目中也提供了简单的示例。下面我做了一些简易的包装，让PHP导入Excel数据用起来更为简便。
　　首先看看示例代码，了解一下Excel Reader的基本使用方式（按照下面的注释step by step）
<?php
// 1.引用ExcelReader类文件
require_once 'Excel/reader.php';
// 2.实例化读取Excel的类
$data = new Spreadsheet_Excel_Reader();
// 3.设置输出编码
$data->setOutputEncoding('utf-8');
// 4.读取指定的excel
$data->read('test.xls');

// 5.循环输出每一行数据，这里读取的是Excel的第一个Sheet表格
// sheets[0]['numRows']代表行数
// sheets[0]['numCols']代表列数
for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) { // 遍历行
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) { // 遍历列
echo &quot;\&quot;&quot;.$data->sheets[0]['cells'][$i][$j].&quot;\&quot;,&quot;;
}
echo &quot;\n&quot;;
}
?>
　　这个简单的例子只是输出了Excel中每个单元格的值，我们还需要拼接sql语句最终将这些值插入到数据库中，而且当Excel的第一行为列头时，这一行数据是不能够插入到数据表中的。
　　下面的步骤就来演示这一简单过程。
　　1. 创建数据库，数据表

--创建test数据库
create database test;
use test;
--创建books表
create table books
(
ISBN varchar(50) primary key,
Pubdate varchar(50)
);
　　2. 拼接sql

<?php
require_once 'Excel/reader.php';
@ $db = mysql_connect ( 'localhost', 'root', 'root' ) or die ( &quot;Could not connect to database.&quot; ); // 连接数据库
mysql_query ( &quot;set names 'utf-8'&quot; ); // 输出中文
mysql_select_db ( 'test' ); // 选择数据库
$data = new Spreadsheet_Excel_Reader ();
$data->setOutputEncoding ( 'utf-8' );
$data->read ( 'Books.xls' );
// 调用ImportExcelData方法
ImportExcelData ( $data, 'books', array ('ISBN', 'Pubdate' ), true );
/**
* 将读取到的Excel数据导入到数据库中
*
* @access public
* @param
*            hasColumnHeader    是否包含列头
* @param
*            columnArray        要插入的列
* @param
*            tableName 要插入的表
* @return bool
*/
function ImportExcelData($data, $tableName, $columnArray, $hasColumnHeader = false) {
// 默认不包含列头，起始行就为1
$start = 1;
if ($hasColumnHeader) {
// 如果包含列头，跳过列头，起始行为2
$start = 2;
}
// 生成insert语句的前半部分
// 形式如这种：insert into table_name('field1','field2'...) values
$insert_statement = CreateInsertStatement ( $tableName, $columnArray );
for($i = $start; $i <= $data->sheets [0] ['numRows']; $i ++) { // 遍历行
$sql = $insert_statement;
$sql .= &quot;(&quot;;
for($j = 1; $j <= $data->sheets [0] ['numCols']; $j ++) { // 遍历列
$sql .= &quot;'&quot; . $data->sheets [0] ['cells'] [$i] [$j] . &quot;',&quot;;
}
$sql = trimEnd ( $sql, &quot;,&quot; );
$sql .= &quot;);&quot;;
$res = mysql_query ( $sql );
}
}
/**
* 创建插入sql的语句
*
* @access public
* @param
*            tableName
* @param
*            columnArray
* @return string
*/
function CreateInsertStatement($tableName, $columnArray) {
$sql = &quot;insert into &quot;.$tableName.&quot;(&quot;;
foreach ( $columnArray as $c ) {
$sql .= &quot;&quot; . $c . &quot;,&quot;;
}
$sql = trimEnd ( $sql, &quot;,&quot; );
$sql .= &quot;) values&quot;;
return $sql;
}
/**
* 移除字符串中指定的尾部字符
*
* @access public
* @param
*            str
* @param
*            strEnd
* @return string
*/
function trimEnd($str, $strEnd) {
return substr ( $str, - (strlen ( $strEnd )) ) == $strEnd ? substr ( $str, 0, strlen ( $str ) - strlen ( $strEnd ) ) : $str;
}
?>

　　3. 优化
　　上面的方法有一个问题，每次生成一个insert语句，就执行一次插入操作，如果Excel的数据量很大，那么执行的插入操作也非常多。既然如此，何不生成一批sql语句后再执行插入操作呢？
　　我对ImportExcelData方法做了简单的优化，加了一个batchSize参数，表示每次执行多少条插入语句，也许这样的做法会更加节约资源。

/**
* 将读取到的Excel数据导入到数据库中
*
* @access public
* @param hasColumnHeader    是否包含列头
* @param columnArray        要插入的列
* @param tableName         要插入的表
* @param batchSize            每次执行插入语句的条数
* @return bool
*/
function ImportExcelData($data,$tableName,$columnArray,$hasColumnHeader = false, $batchSize = 100){
// 默认不包含列头，起始行就为1
$start = 1;
if($hasColumnHeader){
// 如果包含列头，跳过列头，起始行为2
$start = 2;
}
// 记录循环次数
$loop = 0;
$sql = &quot;&quot;;
// 生成insert语句的前半部分
// 形式如这种：insert into table_name('field1','field2'...) values
$insert_statement = CreateInsertStatement($tableName, $columnArray);
for($i = $start; $i <= $data->sheets[0]['numRows']; $i++){ // 遍历行
$sql .= $insert_statement;
$sql .= &quot;(&quot;;
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++){ // 遍历列
$sql .= &quot;'&quot;.$data->sheets[0]['cells'][$i][$j].&quot;',&quot;;
}
$sql = trimEnd($sql,&quot;,&quot;);
$sql .= &quot;);&quot;;
$loop ++;
// 当loop值等于batchSize时，执行插入操作
if($loop == $batchSize){
$res = mysql_query ( $sql );
$loop = 0;
            $sql = &quot;&quot;;
}
//echo $sql;
}
// 如果有950条记录，执行了前9个batch，剩余50条也应当执行
if($loop != 0){
$res = mysql_query ( $sql );
}
}
　　Books.xls文件的内容如图1：
【图1】

　　生成的sql语句如图2：
【图2】

　　执行后的结果如图3：
【图3】

　　当然Excel Reader类中提供的方法不止这些，大家可以参考Sourceforge上提供的example，也可以直接去查看或更改源代码获取自己需求的功能。
　　本文源代码下载：http://files.iyunv.com/keepfool/phpExcelReader.zip