Excel文件數據挖掘研究綜述

鄭 平

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

企業以前每月用excel做數據匯總和分析,隨著數據量增大,以及對所有excel文件做匯總分析的需求,需要把excel文件中的數據抓取并存儲到SQL server 數據庫中,用SQL server數據庫擔當數據的管理、備份、倉庫。考慮到軟件的趨勢是BS結構,本軟件項目研究采用BS結構的方式,用網頁從excel中抓取數據。

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

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

數據流程

圖1

業務流程

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

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

分類對應的數據庫表

圖2

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

挖掘方式對應的數據庫表

圖3

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

Excel文件對應的數據庫表

圖4

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

圖5

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

挖掘數據所對應的數據庫表

圖6

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

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

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