代码改变世界

用Excel展示SQL Server中的数据 (I): 数据库连接

2012-04-02 10:19  liangshi  阅读(34543)  评论(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脚本可以从这里下载。

image3_thumb[1]

-- 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,可获得以下结果。

image63

3. 连接视图以展示数据

在Excel 2007中,点击Data → From Other Sources → From SQL Server,打开数据连接向导。

image26

在登录对话框中,输入待连接的数据库。

image25

然后,选择数据库bug_db中的Stat视图。

image24

然后,点击Finish以完成向导。这时,Excel会询问将导入的数据放置于何处。选择一个合适的单元格之后,点击OK。

image29

这样视图Stat中的数据就呈现在Excel数据薄的表格中。

image35

此时,Rate列的小数部分过长,显得不那么美观。可以右击列Rate所在列(即C列),选择Format Cell,在弹出的对话框中选择Percentage,然后点击OK。

image38

image41

这样,Rate列的数据被格式化为百分数。

image44

这时,可以将该表拷贝复制到Word或Outlook邮件中,作为分析报告的一部分。不过,一图胜过千言万语。不妨利用Excel的作图功能,绘制出一幅反映数据分布的图,以辅助说明数据表。Excel的作图功能非常强大,在此不再赘述,只是展示一副基于该表的柱状图。从该图可以看出,大部分Bug属于code defect、design和performance。以此可以推测被测试程序有较多的性能问题。

image47

4. 数据更新

如果表格和图需要反复使用,可以将数据薄保存。下次打开该数据薄,Excel会给出安全警告:数据连接被中断。这时,需要点击Options,并允许数据访问。之后,Excel会重新导入数据,并更新基于导入数据的图。数据库中的数据变化可以自动地在数据薄中得到体现,实在是非常方便。

image53

image62

此外,如果需要手动更新数据,可以利用Data下的Refresh All。

image66

5. 小结

上述数据展示方法,是分层模型的一个实例。

  1. SQL Server是数据层,存储了待展示的数据。
  2. Excel是展示层,存储了数据格式化方法和图表。
  3. SQL Server的视图充当了逻辑层,存储了计算逻辑。实际上,Excel的数据连接支持SQL查询,但是我倾向于将SQL查询以视图的形式置于数据库之中。第一,在Excel中编写查询比较困难,在SQL Server Management Studio中编写查询非常便捷。第二,置于SQL Server的计算逻辑可以被多个Excel数据薄共享。

分层模型充分发挥了SQL Server在数据存储和操纵上的强大功能,以及Excel在数据表现和图表制作的灵活性。不使用传统意义上的“编程语言”,便快速地实现了数据获取与展示的自动化。

参考文献

  1. 韩小良,Excel+SQL Server数据库管理技术详解
  2. John Walkenbach,Excel 2007宝典