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

鄭 平

紅塔煙草集團有限責任公司楚雄卷煙廠 云南 楚雄 675000

企業(yè)以前每月用excel做數(shù)據(jù)匯總和分析,隨著數(shù)據(jù)量增大,以及對所有excel文件做匯總分析的需求,需要把excel文件中的數(shù)據(jù)抓取并存儲到SQL server 數(shù)據(jù)庫中,用SQL server數(shù)據(jù)庫擔當數(shù)據(jù)的管理、備份、倉庫。考慮到軟件的趨勢是BS結構,本軟件項目研究采用BS結構的方式,用網頁從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ā),實現(xiàn)前后端分離。MVC中的Model和View由C#開發(fā),接收WEB發(fā)來的數(shù)據(jù),并存儲到SQL server。考慮到Html5的主流地位,MVC的View部分,采用Html5開發(fā),進一步分離前后端,取消C#在前端的應用,前端只使用Java Script語言。最終通過IIS發(fā)布出來。

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

圖1

業(yè)務流程

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

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

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

圖2

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

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

圖3

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

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

圖4

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

圖5

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

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

圖6

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

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

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