PHP导入Excel数据
在Sourceforge上提供了一个开源的PHP 导入 Excel 数据 的类,在下载的项目中也提供了简单的示例。下面我做了一些简易的包装,让PHP 导入 Excel 数据 用起来更为简便。 首先看看示例代码,了解一下Excel Reader的基本使用方式(按照下面的注释step by step)
在Sourceforge上提供了一个开源的PHP导入Excel数据的类,在下载的项目中也提供了简单的示例。下面我做了一些简易的包装,让PHP导入Excel数据用起来更为简便。
首先看看示例代码,了解一下Excel Reader的基本使用方式(按照下面的注释step by step)
// 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 sheets[0]['numRows']; $i++) { // 遍历行
for ($j = 1; $j sheets[0]['numCols']; $j++) { // 遍历列
echo "\"".$data->sheets[0]['cells'][$i][$j]."\",";
}
echo "\n";
}
?>
这个简单的例子只是输出了Excel中每个单元格的值,我们还需要拼接sql语句最终将这些值插入到数据库中,而且当Excel的第一行为列头时,这一行数据是不能够插入到数据表中的。
下面的步骤就来演示这一简单过程。
1. 创建数据库,数据表
--创建test数据库 create database test; use test; --创建books表 create table books ( ISBN varchar(50) primary key, Pubdate varchar(50) );
2. 拼接sql
require_once 'Excel/reader.php'; @ $db = mysql_connect ( 'localhost', 'root', 'root' ) or die ( "Could not connect to database." ); // 连接数据库 mysql_query ( "set names 'utf-8'" ); // 输出中文 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 >sheets [0] ['numRows']; $i ++) { // 遍历行 $sql = $insert_statement; $sql .= "("; for($j = 1; $j sheets [0] ['numCols']; $j ++) { // 遍历列 $sql .= "'" . $data->sheets [0] ['cells'] [$i] [$j] . "',"; } $sql = trimEnd ( $sql, "," ); $sql .= ");"; $res = mysql_query ( $sql ); } } /** * 创建插入sql的语句 * * @access public * @param * tableName * @param * columnArray * @return string */ function CreateInsertStatement($tableName, $columnArray) { $sql = "insert into ".$tableName."("; foreach ( $columnArray as $c ) { $sql .= "" . $c . ","; } $sql = trimEnd ( $sql, "," ); $sql .= ") values"; 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 = ""; // 生成insert语句的前半部分 // 形式如这种:insert into table_name('field1','field2'...) values $insert_statement = CreateInsertStatement($tableName, $columnArray); for($i = $start; $i sheets[0]['numRows']; $i++){ // 遍历行 $sql .= $insert_statement; $sql .= "("; for ($j = 1; $j sheets[0]['numCols']; $j++){ // 遍历列 $sql .= "'".$data->sheets[0]['cells'][$i][$j]."',"; } $sql = trimEnd($sql,","); $sql .= ");"; $loop ++; // 当loop值等于batchSize时,执行插入操作 if($loop == $batchSize){ $res = mysql_query ( $sql ); $loop = 0;
$sql = ""; } //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.cnblogs.com/keepfool/phpExcelReader.zip
下一篇: 类不需要实例化也能直接用
推荐阅读