Excel文件數(shù)據(jù)挖掘研究綜述

鄭 平

紅塔煙草集團(tuán)有限責(zé)任公司楚雄卷煙廠 云南 楚雄 675000

企業(yè)以前每月用excel做數(shù)據(jù)匯總和分析,隨著數(shù)據(jù)量增大,以及對所有excel文件做匯總分析的需求,需要把excel文件中的數(shù)據(jù)抓取并存儲到SQL server 數(shù)據(jù)庫中,用SQL server數(shù)據(jù)庫擔(dān)當(dāng)數(shù)據(jù)的管理、備份、倉庫?？紤]到軟件的趨勢是BS結(jié)構(gòu),本軟件項(xiàng)目研究采用BS結(jié)構(gòu)的方式,用網(wǎng)頁從excel中抓取數(shù)據(jù)。

本研究涉及的軟件有操作系統(tǒng)Windows Server 2008 R2、數(shù)據(jù)庫SQL server 2008R2、開發(fā)軟件Visual Studio 2015、文本編輯軟件Note++(可以脫離VS開發(fā)環(huán)境,編輯前端)。

本研究采用微軟MVC4模板開發(fā),實(shí)現(xiàn)前后端分離。MVC中的Model和View由C#開發(fā),接收WEB發(fā)來的數(shù)據(jù),并存儲到SQL server。考慮到Html5的主流地位,MVC的View部分,采用Html5開發(fā),進(jìn)一步分離前后端,取消C#在前端的應(yīng)用,前端只使用Java Script語言。最終通過IIS發(fā)布出來。

數(shù)據(jù)流程

圖1

業(yè)務(wù)流程

1.用戶通過WEB頁面與本軟件模塊實(shí)現(xiàn)功能交互。

首先定義目標(biāo)excel文件的分類,分類的名稱應(yīng)該與Excel文件的核心內(nèi)容相對應(yīng)。比如：分類名稱是動(dòng)力車間指標(biāo)分析,Excel文件名可以是2018年4月動(dòng)力車間指標(biāo)分析純數(shù).xlsx、2018年4月動(dòng)力車間指標(biāo)分析.xlsx、動(dòng)力車間指標(biāo)分析純數(shù).xlsx。

分類對應(yīng)的數(shù)據(jù)庫表

圖2

然后再定義挖掘方式,包括：單元格的功能名稱、所屬類別、起始單個(gè)單元格、按列排列單元格、按行排列單元格。列排列和行排列,用于一次從年底的文件中抓取全年12個(gè)月的數(shù)據(jù)。這樣就不用上傳每個(gè)月的數(shù)據(jù),減少了文檔上傳次數(shù)和抓取次數(shù)

挖掘方式對應(yīng)的數(shù)據(jù)庫表

圖3

2.隨后通過WEB頁面上傳EXCEL數(shù)據(jù)文檔。由于以前的文檔有跨文件引用,該功能在web上無法實(shí)現(xiàn)。所以該文檔必須被文字化,公式和引用都會(huì)變成實(shí)際的字符串或數(shù)字。前端JavaScript腳本會(huì)把EXCEL文檔轉(zhuǎn)成二進(jìn)制流,用XHR 方式傳遞給后臺,同時(shí)還傳輸報(bào)表名稱,起始日期等參數(shù)給后臺。后臺接收二進(jìn)制流并儲存在SQL 數(shù)據(jù)庫中的Image類型字段,同時(shí)儲存報(bào)表名稱,起始日期等參數(shù)到相應(yīng)的字段。

Excel文件對應(yīng)的數(shù)據(jù)庫表

圖4

3用戶篩選、查詢存儲在SQL 中的excel文檔,用戶看見的EXCEL文檔是把SQL 數(shù)據(jù)庫中二進(jìn)制文件在WEB 頁面上渲染而成,并不是EXCEL 文檔通過WEB 頁面上的組件打開。當(dāng)然,用戶點(diǎn)擊導(dǎo)出后,依然會(huì)得到能夠被EXCEL 軟件打開的文檔,但不含公式和引用,是個(gè)純文字的EXCEL文檔。

圖5

4點(diǎn)擊抓取后,服務(wù)器腳本會(huì)按照用戶定義抓取方式來抓取數(shù)據(jù),并把抓取的數(shù)據(jù)儲存到數(shù)據(jù)庫。數(shù)據(jù)挖掘由前端用JavaScript實(shí)現(xiàn),最終把抓取的數(shù)據(jù)連同數(shù)據(jù)的日期、名稱一并發(fā)送到后臺,后臺儲存數(shù)據(jù)到數(shù)據(jù)庫。

挖掘數(shù)據(jù)所對應(yīng)的數(shù)據(jù)庫表

圖6

用戶可以查詢上傳的EXCEL 文檔,用戶看見的EXCEL文檔是把SQL 數(shù)據(jù)庫中二進(jìn)制文件在WEB 頁面上渲染而成,并不是EXCEL文檔通過WEB頁面上的組件打開。當(dāng)然,用戶點(diǎn)擊導(dǎo)出后,依然會(huì)得到能夠被EXCEL 軟件打開的文檔,但不含公式和引用,是個(gè)純文字的EXCEL文檔。

本研究實(shí)施后,順利地將以前的105個(gè)excel文檔儲存到數(shù)據(jù)庫中,含數(shù)據(jù)的單元格總數(shù)為123522,從中挖取到14043個(gè)數(shù)據(jù),圖10就是挖掘數(shù)據(jù)的部分截圖。其中3510個(gè)數(shù)據(jù)是手工錄入數(shù)據(jù),完善了數(shù)據(jù)庫的原始記錄。6470個(gè)數(shù)據(jù)是普通求和數(shù)據(jù),挖取之后可以直接使用,無需用SQL 語句再進(jìn)行計(jì)算,實(shí)現(xiàn)了資源的重復(fù)利用。還有4063個(gè)單元格是復(fù)雜的Excel統(tǒng)計(jì),這部分工作用EXCEL實(shí)現(xiàn)很輕松,用SQL+C#開發(fā)Excel的功能既多余,實(shí)現(xiàn)難度又很高。

所有Excel的數(shù)據(jù)功能無需再用其他方式來呈現(xiàn),用戶依然可以按以前的習(xí)慣使用EXCEL,無需學(xué)習(xí)新圖表。這樣既減輕了開發(fā)工作量,又降低了學(xué)習(xí)成本。最后,通過對4063個(gè)Excel統(tǒng)計(jì)數(shù)據(jù)的分析,完善了報(bào)表系統(tǒng),發(fā)現(xiàn)了新的知識,對業(yè)務(wù)管理提出了新的思路。