利用PHPExcel類實現Excel數據批量操作

胡必波

（廣州工商學院，廣東 廣州 510850）

1 引言

為滿足廣大師生的報修需求，我院后勤集團與信息化處整合了水、電、暖、網絡、固定電話、一卡通等維修類別中師生報修率最高的若干報修項目，推出了校園一站式服務網上“報修投訴平臺”，簡化了以往需要向不同單位申報故障的繁瑣過程，報修申請人只需登錄平臺，進入“我要報修”頁面，填寫信息申請報修。該系統中學生報修信息由一站式服務中心的管理人員以Excel表格的形式提供。本系統采用PHP+MySQL作為開發平臺，開發過程中MySQL數據庫與Excel表有進行導入導出等交互的開發需求。為此我們選用了微軟公司提供的類庫PHPExcel。本文就PHPExcel類庫中有關Excel導入數據庫及將數據庫中的內容導出的實現思路分別作介紹。

2 PHPExcel概述

PHPExcel是用來操作Office Excel文檔的一個PHP類庫，它基于微軟的OpenXML標準和PHP語言。可以使用它來讀取、寫入不同格式的電子表格，如Excel(BIFF).xls、Excel 2007(OfficeOpenXML).xlsx、CSV、Gnumeric、PDF、HTML等。更難得的是還可以在Excel中設置圖片、表格、字體大小、顏色等非常具體的格式。PHPExcel目前最高版本為1.8.0。

3 PHPExcel原理

圖1 PHPExcel基本架構

如圖1所示，PHPExcel在內存中是將數據以電子表格的形式來呈現的，我們只要編寫前端代碼調用PHPExcel對象模型，就可以很方便地創建一個Web電子表格。和我們使用桌面電子表格軟件一樣，PHPExcel所創建的電子表格包含一個或多個工作表，工作表的單元格又包含數據、公式、圖像等。

4 一站式服務系統中Excel批量數據操作功能實現

4.1 數據導入功能的實現

以網絡端口保修數據導出為例。該功能的實現是將網絡端口保修數據導入到MySQL中，將Excel表格中的數據導入數據庫使用的是PHPExcel類庫中的類PHPExcel_Reader_Excel5。具體的實現思路為首先創建PHPExcel_Reader_Excel5的對象$objReader，然后調用它的setReadDataOnly方法設置數據源為可讀，接著調用它的load方法設置要導入的Excel文件，最后通過調用getCellByColumnAndRow方法從Excel獲取數據并將數據插入到數據庫中。

4.2 數據導出功能的實現

該功能的實現是將某階段內的歷史數據從數據庫中導出到指定的Excel表格中。首先通過調用PHPExcel類庫中的PHPExcel類創建Excel文件，并通過調用其方法設置文檔屬性以及Sheet索引。然后通過調用活動單元格對象的方法setCellValue在表格的第一行設置字段名稱。最后通過調用數據庫的數據將數據寫入Excel文件，寫入方法同樣使用的是setCellValue方法。運行效果如圖2所示。

圖2 從一站式服務系統導出的報修數據

實現功能的核心代碼如下：

//生成數據

$column=2;

$objActSheet=$objPHPExcel-＞getActiveSheet();

foreach($data as$key=＞ $rows){//行寫入

$span=ord("B");

foreach($rows as$keyName=＞$value){//列寫入

$j=getExcelColumnValue($span-65);

$objActSheet-＞setCellValue("A".$column,$column-1);

$objActSheet-＞setCellValue($j.$column,$value);

$span++;

4.3 經驗與技巧

4.3.1 亂碼的處理

（1）解決生成文件名的導出Excel亂碼

亂碼原因：客戶使用的中文版Windows系統平臺，Windows平臺的文件名編碼為gb2312(gbk)，而我們網頁編碼一般都采用utf-8(國際化)編碼，這時當我們：header("Content-Disposition∶inline;filename="".$filename.".xls"")時就會出現亂碼，假如你的網頁編碼就是gb2312那就不用考慮編碼問題了。

解決辦法：對$filename轉碼，執行：iconv('utf-8","gb2312",$filename)。假如你的環境不支持iconv函數可以換別的函數，只要能將$filename的編碼轉為gbk就行。但是這樣問題又會來了，linux用戶又會出現文件名亂碼（因為linux平臺文件名不是gbk編碼）。考慮到這個問題采用兩個辦法：第一，放棄一部分客戶，畢竟windows系統用戶占絕大部分；第二，像gmail一樣，提供兩個下載地址：一個文件名gbk編碼，一個文件名utf-8編碼。

（2）解決Excel內數據的亂碼

PHP導出Excel亂碼的原因：網頁編碼與Excel編碼不一致。

解決辦法：定義Excel的字符集：header("Content-Type∶application/vnd.ms-excel;charset=UTF-8")，讓其與你的網頁編碼一致即可解決Excel內數據亂碼的問題。

PHPExcel導出到xls文件的時候出現內存亂碼，解決代碼如下：ob_end_clean();//清除緩沖區，避免亂碼，通過這個函數清除緩沖區，避免不必要的數據干擾生成的Excel文件導致Excel文件打不開。

4.3.2 分頁導出

實現分頁導出的思路是進入導出頁面時，傳遞查詢相關的參數。導出頁面再做相關的分頁查詢處理。

＜li＞＜a href="simple-download-xls.php?xiaoqu=＜?php echo$GLOBALS['xiaoqu_query'];?＞

&name=＜?php echo$GLOBALS['name_query'];?＞&start=＜?php echo$start_row;?＞"＞導出數據＜/a＞＜/li＞

4.3.3 內存溢出

云平臺下使用PHPExcel進行excel數據批量導入，小數據量下測試都沒有問題，可是一到正式環境下，數據超過千條（一行十列為一條數據），就報內存溢出。在不進行特殊設置的情況下，PHPExcel將讀取的單元格信息保存在內存中，我 們 可 以 通 過 代 碼 PHPExcel_Settings∶setCacheStorage-Method()來設置不同的緩存方式，已達到降低內存消耗的目的。可采用的處理方式如下：

（1）將單元格數據序列化后保存在內存中

PHPExcel_CachedObjectStorageFactory∶cache_in_memory_serialized;

（2）將單元格序列化后再進行Gzip壓縮，然后保存在內存中

PHPExcel_CachedObjectStorageFactory∶cache_in_memory_gzip;

（3）緩存在臨時的磁盤文件中，速度可能會慢一些

PHPExcel_CachedObjectStorageFactory∶cache_to_dis -cISAM;

（4）保存在php∶//temp

PHPExcel_CachedObjectStorageFactory∶cache_to_php -Temp;

（5）保存在memcache中

PHPExcel_CachedObjectStorageFactory∶cache_to_memcache

這里限于篇幅不再展開論述。

5 結語

本文以實際項目校園一站式服務系統導出的Excel表為計算基礎，介紹了PHPExcel的基本原理，分析了一站式服務系統中Excel批量數據導入導出的實現思路。并以導出具體操作為例，給出了實現其功能的相關算法。在項目實施過程中，針對可能出現的導出文件亂碼問題、內存溢出問題，進行了分析和研究，并提出了相應的解決辦法。在此基礎上還進行了功能拓展，介紹了實現分頁批量導出Excel表數據技巧。在此應用基礎上，還需要進一步研究的問題有：優化Excel批量數據導出效果，提高導出文件美化效果。

[1]鄭阿奇.PHP實用教程（第2版）[M].北京：電子工業出版社，2014.

[2]唐俊.PHP+MySQL網站開發技術（項目式）[M].北京：人民郵電出版社，2013.

[3]Maarten Balliauw，Mark Baker.PHPExcel developer documentation.2014.

[4]丁月光.PHPExcel文件讀寫[J].電腦編程技巧與維護，2013，(9)：14-18.