通用Excel共享及查詢系統(tǒng)設(shè)計(jì)與實(shí)現(xiàn)＊

王進(jìn)利，丁小寶，魯大前

（1.臺(tái)州學(xué)院 人文學(xué)院，浙江 臨海317000；2.臺(tái)州學(xué)院 電子與信息工程學(xué)院，浙江 臨海317000）

0 引言

隨著無(wú)紙化辦公程度越來(lái)越高，數(shù)據(jù)處理越來(lái)越依賴于Office 辦公軟件，特別是Excel 等電子表格程序，而越來(lái)越多的表格，催生了表格共享與查詢的需求。傳統(tǒng)的表格共享主要通過(guò)QQ、郵箱及使用局域網(wǎng)共享文件夾等方式，但這些方式無(wú)法適應(yīng)移動(dòng)互聯(lián)網(wǎng)發(fā)展。因此盧德山等［1］提出了基于PHP 與電子表格的通用數(shù)據(jù)查詢系統(tǒng)，該系統(tǒng)使用網(wǎng)頁(yè)+電子表格（文本）做數(shù)據(jù)庫(kù)的查詢方案進(jìn)行查詢，可以實(shí)現(xiàn)任意表格數(shù)據(jù)的查詢，但使用起來(lái)比較復(fù)雜、穩(wěn)定性差、安全性較弱、后期數(shù)據(jù)維護(hù)不便。李烜［2］、劉松［3］、全思皓［4］等提出采用VBA 直接聯(lián)接數(shù)據(jù)庫(kù)，實(shí)現(xiàn)數(shù)據(jù)的讀寫，以減少學(xué)習(xí)成本、簡(jiǎn)化工作，但這種方式存在很大的安全風(fēng)險(xiǎn)，并要求使用者具有編程能力，因此無(wú)法大規(guī)模推廣。

為發(fā)揮Excel 的強(qiáng)大的本地處理能力，也為適應(yīng)隨著移動(dòng)互聯(lián)網(wǎng)技術(shù)的發(fā)展，特別是H5 網(wǎng)頁(yè)技術(shù)的快速發(fā)展，對(duì)通用表格系統(tǒng)靈活性的更高要求，因此本文提出了采用“Excel+H5 網(wǎng)頁(yè)”的方式，來(lái)實(shí)現(xiàn)靈活的數(shù)據(jù)處理與查詢。

1 需求分析及解決方案

在信息化過(guò)程中，電子表格的共享與查詢是小微企業(yè)最迫切的需求。而要實(shí)現(xiàn)這個(gè)需求，必須在保證數(shù)據(jù)靈活性、可靠性、數(shù)據(jù)存儲(chǔ)安全性、低泄漏風(fēng)險(xiǎn)的前提下，盡量滿足管理成本、使用成本、系統(tǒng)成本低的實(shí)際需要，最好還要有權(quán)限管理、實(shí)時(shí)更新、本地導(dǎo)出、手機(jī)查詢等功能。

Excel+VBA+PHP+MySQL 的方案可以很好的滿足上述的要求。Excel 軟件有數(shù)據(jù)操作靈活、多表操作等特點(diǎn)，以Excel 為界面來(lái)構(gòu)建信息系統(tǒng)，可以減少終端用戶的使用成本，因此在全世界范圍內(nèi)被廣泛使用。

Visual Basic for Applications（VBA）是微軟開發(fā)出來(lái)在其桌面應(yīng)用程序中使用的編程語(yǔ)言，與Excel等緊密結(jié)合，能實(shí)現(xiàn)從“普通應(yīng)用軟件”到“軟件構(gòu)造模塊”的轉(zhuǎn)變。而國(guó)產(chǎn)的WPS 軟件也有兼容的VBA安裝包，可以實(shí)現(xiàn)表格編程。

PHP+MySQL 是最流行的組合，可以實(shí)現(xiàn)動(dòng)態(tài)網(wǎng)頁(yè)、復(fù)雜的網(wǎng)頁(yè)制作、權(quán)限管理，并能滿足現(xiàn)在移動(dòng)查詢、微信小程序、H5 網(wǎng)頁(yè)等的應(yīng)用構(gòu)建需求。

結(jié)合上文，基于Excel+VBA+PHP+MySQL 的信息系統(tǒng)開發(fā)，需要充分發(fā)揮各個(gè)軟件的優(yōu)勢(shì)，才能構(gòu)建出一個(gè)通用、易用、高效的適用于小微企業(yè)的信息系統(tǒng)。

2 數(shù)據(jù)共享的原理與實(shí)現(xiàn)

2.1 一般用戶使用方法

一鍵上傳：上傳時(shí)管理員只是把數(shù)據(jù)復(fù)制到指定位置，并設(shè)置相應(yīng)的參數(shù)，最后點(diǎn)擊上傳，即可完成數(shù)據(jù)上傳到網(wǎng)站的過(guò)程。

一鍵下載：只要設(shè)置相應(yīng)的參數(shù)，再點(diǎn)擊下載，即可實(shí)現(xiàn)數(shù)據(jù)從服務(wù)器到電子表格的全過(guò)程。全過(guò)程中管理員無(wú)需使用任何其它的工具，使用體驗(yàn)比較好。

2.2 工作原理介紹

如圖1，在本系統(tǒng)中，含宏的表格、PHP 代碼及MySQL 服務(wù)器都是專門設(shè)計(jì)出來(lái)為共享表格服務(wù)的，而小程序及公眾號(hào)則是設(shè)計(jì)出來(lái)為查詢數(shù)據(jù)服務(wù)的。

圖1 共享查詢系統(tǒng)工作原理

2.3 MySQL數(shù)據(jù)庫(kù)設(shè)計(jì)

為了達(dá)到能把任意的電子表格上傳到數(shù)據(jù)庫(kù)，并能進(jìn)行后期共享查詢的目的，除了數(shù)據(jù)庫(kù)中常見的用戶及權(quán)限控制數(shù)據(jù)表外，我們將所有的電子表格上傳到數(shù)據(jù)庫(kù)中的一張數(shù)據(jù)表中。在如圖2 的數(shù)據(jù)表中字段分兩類：一類是固定的參數(shù)字段，如數(shù)據(jù)表名、上傳密碼、查看密碼等；另一類字是數(shù)據(jù)字段，用戶上傳的數(shù)據(jù)全寫入到這里。

圖2 數(shù)據(jù)表設(shè)計(jì)

在第一類的參數(shù)字段中，數(shù)據(jù)表名、上傳密碼、查看密碼等直接寫在每一條記錄中，這樣在PHP 設(shè)計(jì)查詢數(shù)據(jù)表時(shí)能極大的簡(jiǎn)化代碼，減少工作量。

在第二類數(shù)據(jù)字段中，為了通用性，假定電子表格中所有的數(shù)據(jù)都是文本類型，并且假定數(shù)據(jù)共有50 條數(shù)據(jù)，所有數(shù)據(jù)字段都以d1，d2，d3，…，d50 來(lái)保存，所有數(shù)據(jù)字段都是text 類型。這樣我們可以把用戶上傳的所有數(shù)據(jù)都接收到同一張數(shù)據(jù)表中，保證數(shù)據(jù)庫(kù)的通用性，以方便后期的統(tǒng)一查詢。

上述的數(shù)據(jù)庫(kù)設(shè)計(jì)雖然不是最優(yōu)的，但這種設(shè)計(jì)邏輯清晰，方便后期處理。雖然有數(shù)據(jù)冗余、字段類型單一的缺點(diǎn)，但對(duì)于小微型企業(yè)而言，即使部分?jǐn)?shù)據(jù)重復(fù)，其性能損失也是可以接受的。而數(shù)據(jù)類型單一，無(wú)法進(jìn)行數(shù)值計(jì)算的缺點(diǎn)，也可以后期通過(guò)代碼智能識(shí)別而解決。

2.4 含VBA的電子表格設(shè)計(jì)

本文利用VBA 的強(qiáng)大功能，配置如圖3 所示的配置參數(shù)，即可實(shí)現(xiàn)數(shù)據(jù)表的上傳與下載。由于使用了PHP 網(wǎng)頁(yè)作為中介，電子表格實(shí)質(zhì)上類似于網(wǎng)頁(yè)，就是B/S 結(jié)構(gòu)，這可以避免將數(shù)據(jù)庫(kù)賬號(hào)與密碼都寫入Excel 中，從而能保證數(shù)據(jù)庫(kù)的安全性。

圖3 電子表格控制界面

上傳過(guò)程即為利用POST 方法提交數(shù)據(jù)的過(guò)程：

（1）VBA 將數(shù)據(jù)行中的數(shù)據(jù)自動(dòng)另存為如圖4 的CSV 文件；

（2）向配套的PHP 網(wǎng)址以POST 方式提交此文件及包括用戶名、密碼、指定查詢姓名字段等信息在內(nèi)的信息；

（3）除指定字段外，只要有查看密碼就能查看這個(gè)密碼對(duì)應(yīng)的所有的數(shù)據(jù)表。

上傳密碼僅用于上傳，用戶也可以更改查看密碼。姓名字段主要用于查詢時(shí)的自動(dòng)補(bǔ)全，查詢網(wǎng)址指定了數(shù)據(jù)上傳的位置。

圖4 導(dǎo)出的CSV 的樣式

下載過(guò)程即為讀取網(wǎng)頁(yè)中的表格的過(guò)程：

（1）點(diǎn)擊下載時(shí)，VBA 會(huì)向服務(wù)器提交一個(gè)含用戶名、密碼、數(shù)據(jù)表名、字段等查詢信息的POST 請(qǐng)求給查詢網(wǎng)址；

（2）配置的網(wǎng)址核對(duì)信息后，將符合條件的數(shù)據(jù)以表格的形式輸出到網(wǎng)頁(yè)；

（3）通過(guò)VBA 用QueryTables 方法讀取網(wǎng)頁(yè)中的數(shù)據(jù)并輸出到電子表格中，從而實(shí)現(xiàn)數(shù)據(jù)的共享。

2.5 上傳下載對(duì)應(yīng)的PHP控制器設(shè)計(jì)

PHP 代碼是本數(shù)據(jù)共享系統(tǒng)實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)訪問的關(guān)鍵，既要保證數(shù)據(jù)的安全，又要實(shí)現(xiàn)上傳下載及查詢的各種功能。

上傳過(guò)程中，PHP 控制器在接收到POST 請(qǐng)求及相應(yīng)的CSV 文件后：

（1）核對(duì)用戶名與密碼、IP 地址（可不要求），進(jìn)行登陸操作，不存在則返回出錯(cuò)信息；

（2）核對(duì)數(shù)據(jù)表與上傳密碼是否正確，不正確則返回出錯(cuò)信息；

（3）清空所有數(shù)據(jù)表下現(xiàn)有的所有數(shù)據(jù)；

（4）在每條記錄下的sheetname，name 等寫入相應(yīng)的信息，而排序、分類、縮略字段等信息，編碼成Json 格式后寫入custom1 中，然后把數(shù)據(jù)寫入到d1、d2 等字段中。這樣就完成了電子表格向MySQL 數(shù)據(jù)庫(kù)的寫入。

下載過(guò)程中，PHP 控制器在接收到要查詢的數(shù)據(jù)表名、字段名等查詢條件的POST 請(qǐng)求后：

（1）核對(duì)用戶名與密碼、IP 地址（可不要求），進(jìn)行登陸操作，不存在則返回出錯(cuò)信息；

（2）核對(duì)數(shù)據(jù)表與查看密碼是否正確，不正確則返回出錯(cuò)信息；

（3）根據(jù)POST 請(qǐng)求，進(jìn)行數(shù)據(jù)查詢，把查詢得到的二維數(shù)組輸出為表格顯示在網(wǎng)頁(yè)上。

2.6 網(wǎng)頁(yè)查詢的實(shí)現(xiàn)及優(yōu)化

由于數(shù)據(jù)已在MySQL 服務(wù)器上，也不涉及電子表格，只需在保證數(shù)據(jù)安全性的前提下，利用PHP 將數(shù)據(jù)輸出即可，并在輸出過(guò)程中做到盡量的美化及實(shí)用。

查詢流程如下：

（1）在如圖5 的H5 頁(yè)面中，用戶只需要在文本框內(nèi)輸入自己要查詢的信息即可；

（2）后臺(tái)優(yōu)先從指定的name、pid 字段中查詢對(duì)應(yīng)的值并輸出到圖6 的列表頁(yè)面中；

（3）用戶再?gòu)牧斜眄?yè)中點(diǎn)擊鏈接進(jìn)入如圖7 的詳情頁(yè)。

圖5 網(wǎng)頁(yè)查詢頁(yè)面設(shè)計(jì)

圖6 網(wǎng)頁(yè)查詢列表頁(yè)面設(shè)計(jì)

圖7 網(wǎng)頁(yè)詳情頁(yè)面設(shè)計(jì)

查詢頁(yè)面優(yōu)化：

（1）用戶進(jìn)入查詢界面后，直接輸入要查詢的信息，系統(tǒng)給出含這些信息的自動(dòng)補(bǔ)全信息，方便查詢；

（2）如果查詢不到信息時(shí)，歷遍所有字段進(jìn)行查詢；

（3）查詢結(jié)果中自動(dòng)識(shí)別網(wǎng)址、電話，方便使用；

（4）用戶在搜索結(jié)果中，可以方便地進(jìn)行這個(gè)字段的同類搜索。

3 安全性的分析

3.1 登陸安全防護(hù)

在傳統(tǒng)網(wǎng)站中，用戶登陸時(shí)，為了防止暴力破解，普遍采用了驗(yàn)證碼的方式來(lái)增加破解的難度。在本文的電子表格上傳及下載時(shí)，系統(tǒng)是處于未登陸的狀態(tài)的，電子表格需提交用戶名及密碼進(jìn)行用戶認(rèn)證登陸。而認(rèn)證的過(guò)程中，為了簡(jiǎn)單，暫時(shí)沒有加驗(yàn)證碼，而是采用IP 地址驗(yàn)證及用戶多次登陸失敗鎖定的機(jī)制來(lái)防止暴力破解。

3.2 上傳與下載安全防護(hù)

與傳統(tǒng)的網(wǎng)站相同，上傳與下載（即導(dǎo)入與導(dǎo)出）功能只有管理員用戶才能進(jìn)行操作，而一般的普通用戶是不具有此權(quán)限的。

3.3 網(wǎng)頁(yè)查詢安全防護(hù)

由于數(shù)據(jù)已存在MySQL 服務(wù)器上，也不涉及電子表格，只需在保證數(shù)據(jù)安全性的前提下，利用PHP將數(shù)據(jù)輸出即可，輸出過(guò)程中做到盡量的美化及實(shí)用。

4 結(jié)語(yǔ)

本數(shù)據(jù)共享系統(tǒng)，已經(jīng)在GitHub 上開源［5］。作為一個(gè)通用性系統(tǒng)，本系統(tǒng)著力于解決電子表格共享中的通用性及便捷性問題。現(xiàn)階段的系統(tǒng)已經(jīng)是一個(gè)普適性極強(qiáng)、使用方便的、安全可靠的一個(gè)電子表格解決方案，推廣后可用于小微企業(yè)的數(shù)據(jù)共享，推進(jìn)其信息化進(jìn)程。當(dāng)然該方案還存在效率不高和界面優(yōu)化等問題，仍需根據(jù)用戶反饋進(jìn)行進(jìn)一步的優(yōu)化。