PHP导入Excel数据-php手册-PHP中文網

首頁

php教程

php手册

PHP导入Excel数据

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 06, 2016 pm 07:36 PM

excelphp導入提供數據

在Sourceforge上提供了一个开源的PHP 导入 Excel 数据的类，在下载的项目中也提供了简单的示例。下面我做了一些简易的包装，让PHP 导入 Excel 数据用起来更为简便。首先看看示例代码，了解一下Excel Reader的基本使用方式（按照下面的注释step by step）

在Sourceforge上提供了一个开源的PHP导入Excel数据的类，在下载的项目中也提供了简单的示例。下面我做了一些简易的包装，让PHP导入Excel数据用起来更为简便。

首先看看示例代码，了解一下Excel Reader的基本使用方式（按照下面的注释step by step）

<span><?php </span><span>// 1.引用ExcelReader类文件
</span><span>require_once </span><span>'Excel/reader.php'</span>;
<span>// 2.实例化读取Excel的类
</span>$data = <span>new </span>Spreadsheet_Excel_Reader();
<span>// 3.设置输出编码
</span>$data->setOutputEncoding(<span>'utf-8'</span>);
<span>// 4.读取指定的excel
</span>$data->read(<span>'test.xls'</span>);


<span>// 5.循环输出每一行<strong>数据</strong>，这里读取的是Excel的第一个Sheet表格
// sheets[0]['numRows']代表行数
// </span><span>sheet</span><span>s[0]['numCols']代表列数
</span><span>for </span>($i = 1; $i sheets[0][<span>'numRows'</span>]; $i++) { <span>// 遍历行
    </span><span>for </span>($j = 1; $j shee<span>ts[0]['numCols'</span>]; $j++) { <span>// 遍历列
        </span><span>echo </span><span>"\""</span>.$data->sheets[0][<span>'cells'</span>][$i][$j].<span>"\","</span>;
    }
    <span>echo </span><span>"\n"</span>;
}

<span>?></span></span>

这个简单的例子只是输出了Excel中每个单元格的值，我们还需要拼接sql语句最终将这些值插入到数据库中，而且当Excel的第一行为列头时，这一行数据是不能够插入到数据表中的。

下面的步骤就来演示这一简单过程。

1. 创建数据库，数据表

<span>--创建test<strong>数据</strong>库
</span><span>create </span>database test;
use test;
<span>--创建books表
</span><span>create table </span>books
(
ISBN <span>varchar</span>(50) <span>primary key</span>,
Pubdate <span>varchar</span>(50)
);

2. 拼接sql

<span><?php </span><span>require_once </span><span>'Excel/reader.php'</span>;

@ $db = mysql_connect ( <span>'localhost'</span>, <span>'root'</span>, <span>'root' </span>) or <span>die </span>( <span>"Could not connect to database." </span>); <span>// 连接<strong>数据</strong>库
</span>mysql_query ( <span>"set names 'utf-8'" </span>); <span>// 输出中文
</span>mysql_select_db ( <span>'test' </span>); <span>// 选择<strong>数据</strong>库

</span>$data = <span>new </span>Spreadsheet_Excel_Reader ();
$data->setOutputEncoding ( <span>'utf-8' </span>);
$data->read ( <span>'Books.xls' </span>);

<span>// 调用ImportExcelData方法
</span><font>ImportExcelData ( $data, <span>'books'</span>, <span>array </span>(<span>'ISBN'</span>, <span>'Pubdate' </span>), <span>true </span>);</font>

<span>/**
 * 将读取到的Excel<strong>数据</strong><strong>导入</strong>到<strong>数据</strong>库中
 *
 * </span><span>@access </span><span>public
 * </span><span>@param
 </span><span>*            hasColumnHeader    是否包含列头
 * </span><span>@param
 </span><span>*            columnArray        要插入的列
 * </span><span>@param
 </span><span>*            tableName 要插入的表
 * </span><span>@return </span><span>bool
 */
</span><span>function </span>ImportExcelData($data, $tableName, $columnArray, $hasColumnHeader = <span>false</span>) {
    <span>// 默认不包含列头，起始行就为1
    </span>$start = 1;
    
    <span>if </span>($hasColumnHeader) {
        <span>// 如果包含列头，跳过列头，起始行为2
        </span>$start = 2;
    }
    <span>// 生成insert语句的前半部分
    // 形式如这种：insert into table_name('field1','field2'...) va</span><span>lues
    </span>$insert_statement = CreateInsertStatement ( $tableName, $columnArray );
    <span>for</span>($i = $start; $i >sheet</span>s [0] [<span>'numRows'</span>]; $i ++) { <span>// 遍历行
        </span>$sql = $insert_<span>statem</span>ent;
        $sql .= <span>"("</span>;
        <span>for</span>($j = 1; $j sheets [0] [<span>'numCols'</span>]; $j ++) { <span>// 遍历列
            </span>$sql .= <span>"'" </span>. $data->sheets [0] [<span>'cells'</span>] [$i] [$j] . <span>"',"</span>;
        }
        $sql = trimEnd ( $sql, <span>"," </span>);
        $sql .= <span>");"</span>;
        $res = mysql_query ( $sql );
    }
}

<span>/**
 * 创建插入sql的语句
 *
 * </span><span>@access </span><span>public
 * </span><span>@param
 </span><span>*            tableName
 * </span><span>@param
 </span><span>*            columnArray
 * </span><span>@return </span><span>string
 */
</span><span>function </span>CreateInsertStatement($tableName, $columnArray) {
    $sql = <span>"insert into ".<font>$tableName</font>."("</span>;
    <span>foreach </span>( $columnArray <span>as </span>$c ) {
        $sql .= <span>"" </span>. $c . <span>","</span>;
    }
    $sql = trimEnd ( $sql, <span>"," </span>);
    $sql .= <span>") values"</span>;
    <span>return </span>$sql;
}

<span>/**
 * 移除字符串中指定的尾部字符
 *
 * </span><span>@access </span><span>public
 * </span><span>@param
 </span><span>*            str
 * </span><span>@param
 </span><span>*            strEnd
 * </span><span>@return </span><span>string
 */
</span><span>function </span>trimEnd($str, $strEnd) {
    <span>return </span>substr ( $str, - (strlen ( $strEnd )) ) == $strEnd ? substr ( $str, 0, strlen ( $str ) - strlen ( $strEnd ) ) : $str;
}
<span>?></span>

3. 优化

上面的方法有一个问题，每次生成一个insert语句，就执行一次插入操作，如果Excel的数据量很大，那么执行的插入操作也非常多。既然如此，何不生成一批sql语句后再执行插入操作呢？

我对ImportExcelData方法做了简单的优化，加了一个batchSize参数，表示每次执行多少条插入语句，也许这样的做法会更加节约资源。

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

Books.xls文件的内容如图1：
【图1】

PHP导入Excel数据