欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  php教程

PHP导入Excel数据

程序员文章站 2022-05-23 16:02:23
...

在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】

PHP导入Excel数据 

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

PHP导入Excel数据

执行后的结果如图3:
【图3】
PHP导入Excel数据

当然Excel Reader类中提供的方法不止这些,大家可以参考Sourceforge上提供的example,也可以直接去查看或更改源代码获取自己需求的功能。

本文源代码下载:http://files.cnblogs.com/keepfool/phpExcelReader.zip