用Excel展示SQL Server中的数据 (I): 数据库连接
2012-04-02 10:19 liangshi 阅读(34646) 评论(2) 编辑 收藏 举报在工作中,我常常撰写一些技术报告,以总结当前测试进度、分析测试结果。这些技术报告需要图表来展示数据,而这些数据大多存放在数据库中。经过尝试,我学习并总结出一种利用Excel展示SQL Server数据的方法。虽然是雕虫小技,却胜在快捷方便。本文将介绍该方法的基本技术:利用Excel导入SQL Server中的数据。
在本文中,我使用的是Excel 2007,连接的数据库是SQL Server 2008 Express。幸运的是,所介绍的技术同样适用于Excel 2010和SQL Server的其他版本。不过,本文使用的T-SQL脚本使用了一些SQL Sever 2008才支持的语法,需要修改之后才能用于SQL Server 2005。
1. 数据初始化
出于演示的目的,我在本机的SQL Server上创建了数据库bug_db。在该数据库中,我创建了表Bug和BugType,并填充了数据。完整的SQL脚本可以从这里下载。
-- create tables CREATE TABLE dbo.Bug ( ID INT IDENTITY, BugTypeID TINYINT NOT NULL, CreatedTime DATETIME NOT NULL ) ON [Primary] GO CREATE TABLE dbo.BugType ( ID TINYINT, TypeName NVARCHAR(16) ) ON [Primary] GO INSERT INTO dbo.BugType (ID, TypeName) VALUES (1, 'code defect'), (2, 'design'), (3, 'performance'), (4, 'configuration'), (5, 'document') GO -- insert bug data INSERT INTO dbo.Bug (BugTypeID, CreatedTime) VALUES (2, '3/1/2010 12:00:00 AM'), (3, '3/1/2010 12:00:00 AM'), …
Bug表记录了测试团队发现的Bug信息,包含唯一的标识符(ID)、Bug的类型信息(BugTypeID)和发现时间(CreatedTime)。为了简化演示,Bug表没有包含本文不需要的信息(它们在真实数据库中很重要),如标题(Title)、优先级(Priority)、严重性(Severity)等。此外,BugType表记录了BugTypeID对应的Bug类型名。
2. 创建视图以完成计算
假设我的任务是分析Bug类型的分布情况,那么我可以在数据库中创建如下视图。
CREATE SCHEMA [test] AUTHORIZATION [db_owner] GO CREATE VIEW test.Stat AS WITH T AS ( SELECT TypeName, COUNT(*) AS Cnt FROM dbo.Bug B INNER JOIN dbo.BugType T ON B.BugTypeID = T.ID GROUP BY T.TypeName ) SELECT TypeName, Cnt, 1.*Cnt/Total AS Rate FROM T CROSS JOIN (SELECT COUNT(*) AS Total FROM dbo.Bug) B GO
为了避免名字冲突,我首先创建了新的数据库架构(schema)test。在test架构中,创建了视图Stat。该视图利用SQL 2005引入的公共表达式计算出每一种Bug的个数,随后除以Bug总数以获得Bug类型的分布。运行SELECT * FROM test.Stat,可获得以下结果。
3. 连接视图以展示数据
在Excel 2007中,点击Data → From Other Sources → From SQL Server,打开数据连接向导。
在登录对话框中,输入待连接的数据库。
然后,选择数据库bug_db中的Stat视图。
然后,点击Finish以完成向导。这时,Excel会询问将导入的数据放置于何处。选择一个合适的单元格之后,点击OK。
这样视图Stat中的数据就呈现在Excel数据薄的表格中。
此时,Rate列的小数部分过长,显得不那么美观。可以右击列Rate所在列(即C列),选择Format Cell,在弹出的对话框中选择Percentage,然后点击OK。
这样,Rate列的数据被格式化为百分数。
这时,可以将该表拷贝复制到Word或Outlook邮件中,作为分析报告的一部分。不过,一图胜过千言万语。不妨利用Excel的作图功能,绘制出一幅反映数据分布的图,以辅助说明数据表。Excel的作图功能非常强大,在此不再赘述,只是展示一副基于该表的柱状图。从该图可以看出,大部分Bug属于code defect、design和performance。以此可以推测被测试程序有较多的性能问题。
4. 数据更新
如果表格和图需要反复使用,可以将数据薄保存。下次打开该数据薄,Excel会给出安全警告:数据连接被中断。这时,需要点击Options,并允许数据访问。之后,Excel会重新导入数据,并更新基于导入数据的图。数据库中的数据变化可以自动地在数据薄中得到体现,实在是非常方便。
此外,如果需要手动更新数据,可以利用Data下的Refresh All。
5. 小结
上述数据展示方法,是分层模型的一个实例。
- SQL Server是数据层,存储了待展示的数据。
- Excel是展示层,存储了数据格式化方法和图表。
- SQL Server的视图充当了逻辑层,存储了计算逻辑。实际上,Excel的数据连接支持SQL查询,但是我倾向于将SQL查询以视图的形式置于数据库之中。第一,在Excel中编写查询比较困难,在SQL Server Management Studio中编写查询非常便捷。第二,置于SQL Server的计算逻辑可以被多个Excel数据薄共享。
分层模型充分发挥了SQL Server在数据存储和操纵上的强大功能,以及Excel在数据表现和图表制作的灵活性。不使用传统意义上的“编程语言”,便快速地实现了数据获取与展示的自动化。
参考文献
- 韩小良,Excel+SQL Server数据库管理技术详解
- John Walkenbach,Excel 2007宝典