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统计数据的分析,完善了报表系统,发现了新的知识,对业务管理提出了新的思路。