利用PHPExcel類實(shí)現(xiàn)Excel數(shù)據(jù)批量操作

胡必波

（廣州工商學(xué)院，廣東 廣州 510850）

1 引言

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

2 PHPExcel概述

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

3 PHPExcel原理

圖1 PHPExcel基本架構(gòu)

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

4 一站式服務(wù)系統(tǒng)中Excel批量數(shù)據(jù)操作功能實(shí)現(xiàn)

4.1 數(shù)據(jù)導(dǎo)入功能的實(shí)現(xiàn)

以網(wǎng)絡(luò)端口保修數(shù)據(jù)導(dǎo)出為例。該功能的實(shí)現(xiàn)是將網(wǎng)絡(luò)端口保修數(shù)據(jù)導(dǎo)入到MySQL中，將Excel表格中的數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫使用的是PHPExcel類庫中的類PHPExcel_Reader_Excel5。具體的實(shí)現(xiàn)思路為首先創(chuàng)建PHPExcel_Reader_Excel5的對象$objReader，然后調(diào)用它的setReadDataOnly方法設(shè)置數(shù)據(jù)源為可讀，接著調(diào)用它的load方法設(shè)置要導(dǎo)入的Excel文件，最后通過調(diào)用getCellByColumnAndRow方法從Excel獲取數(shù)據(jù)并將數(shù)據(jù)插入到數(shù)據(jù)庫中。

4.2 數(shù)據(jù)導(dǎo)出功能的實(shí)現(xiàn)

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

圖2 從一站式服務(wù)系統(tǒng)導(dǎo)出的報(bào)修數(shù)據(jù)

實(shí)現(xiàn)功能的核心代碼如下：

//生成數(shù)據(jù)

$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 經(jīng)驗(yàn)與技巧

4.3.1 亂碼的處理

（1）解決生成文件名的導(dǎo)出Excel亂碼

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

解決辦法：對$filename轉(zhuǎn)碼，執(zhí)行：iconv('utf-8","gb2312",$filename)。假如你的環(huán)境不支持iconv函數(shù)可以換別的函數(shù)，只要能將$filename的編碼轉(zhuǎn)為gbk就行。但是這樣問題又會(huì)來了，linux用戶又會(huì)出現(xiàn)文件名亂碼（因?yàn)閘inux平臺(tái)文件名不是gbk編碼）?？紤]到這個(gè)問題采用兩個(gè)辦法：第一，放棄一部分客戶，畢竟windows系統(tǒng)用戶占絕大部分；第二，像gmail一樣，提供兩個(gè)下載地址：一個(gè)文件名gbk編碼，一個(gè)文件名utf-8編碼。

（2）解決Excel內(nèi)數(shù)據(jù)的亂碼

PHP導(dǎo)出Excel亂碼的原因：網(wǎng)頁編碼與Excel編碼不一致。

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

PHPExcel導(dǎo)出到xls文件的時(shí)候出現(xiàn)內(nèi)存亂碼，解決代碼如下：ob_end_clean();//清除緩沖區(qū)，避免亂碼，通過這個(gè)函數(shù)清除緩沖區(qū)，避免不必要的數(shù)據(jù)干擾生成的Excel文件導(dǎo)致Excel文件打不開。

4.3.2 分頁導(dǎo)出

實(shí)現(xiàn)分頁導(dǎo)出的思路是進(jìn)入導(dǎo)出頁面時(shí)，傳遞查詢相關(guān)的參數(shù)。導(dǎo)出頁面再做相關(guān)的分頁查詢處理。

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

&name=＜?php echo$GLOBALS['name_query'];?＞&start=＜?php echo$start_row;?＞"＞導(dǎo)出數(shù)據(jù)＜/a＞＜/li＞

4.3.3 內(nèi)存溢出

云平臺(tái)下使用PHPExcel進(jìn)行excel數(shù)據(jù)批量導(dǎo)入，小數(shù)據(jù)量下測試都沒有問題，可是一到正式環(huán)境下，數(shù)據(jù)超過千條（一行十列為一條數(shù)據(jù)），就報(bào)內(nèi)存溢出。在不進(jìn)行特殊設(shè)置的情況下，PHPExcel將讀取的單元格信息保存在內(nèi)存中，我 們 可 以 通 過 代 碼 PHPExcel_Settings∶setCacheStorage-Method()來設(shè)置不同的緩存方式，已達(dá)到降低內(nèi)存消耗的目的?？刹捎玫奶幚矸绞饺缦拢?/p>

（1）將單元格數(shù)據(jù)序列化后保存在內(nèi)存中

PHPExcel_CachedObjectStorageFactory∶cache_in_memory_serialized;

（2）將單元格序列化后再進(jìn)行Gzip壓縮，然后保存在內(nèi)存中

PHPExcel_CachedObjectStorageFactory∶cache_in_memory_gzip;

（3）緩存在臨時(shí)的磁盤文件中，速度可能會(huì)慢一些

PHPExcel_CachedObjectStorageFactory∶cache_to_dis -cISAM;

（4）保存在php∶//temp

PHPExcel_CachedObjectStorageFactory∶cache_to_php -Temp;

（5）保存在memcache中

PHPExcel_CachedObjectStorageFactory∶cache_to_memcache

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

5 結(jié)語

本文以實(shí)際項(xiàng)目校園一站式服務(wù)系統(tǒng)導(dǎo)出的Excel表為計(jì)算基礎(chǔ)，介紹了PHPExcel的基本原理，分析了一站式服務(wù)系統(tǒng)中Excel批量數(shù)據(jù)導(dǎo)入導(dǎo)出的實(shí)現(xiàn)思路。并以導(dǎo)出具體操作為例，給出了實(shí)現(xiàn)其功能的相關(guān)算法。在項(xiàng)目實(shí)施過程中，針對可能出現(xiàn)的導(dǎo)出文件亂碼問題、內(nèi)存溢出問題，進(jìn)行了分析和研究，并提出了相應(yīng)的解決辦法。在此基礎(chǔ)上還進(jìn)行了功能拓展，介紹了實(shí)現(xiàn)分頁批量導(dǎo)出Excel表數(shù)據(jù)技巧。在此應(yīng)用基礎(chǔ)上，還需要進(jìn)一步研究的問題有：優(yōu)化Excel批量數(shù)據(jù)導(dǎo)出效果，提高導(dǎo)出文件美化效果。

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

[2]唐俊.PHP+MySQL網(wǎng)站開發(fā)技術(shù)（項(xiàng)目式）[M].北京：人民郵電出版社，2013.

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

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