VSTO学习笔记(十四)Excel数据透视表与PowerPivot-阿里云开发者社区

开发者社区> 杰克.陈> 正文

VSTO学习笔记(十四)Excel数据透视表与PowerPivot

简介: 原文:VSTO学习笔记(十四)Excel数据透视表与PowerPivot 近期公司内部在做一种通用查询报表,方便人力资源分析、统计数据。由于之前公司系统中有一个类似的查询使用Excel数据透视表完成的,故我也打算借鉴一下。
+关注继续查看
原文:VSTO学习笔记(十四)Excel数据透视表与PowerPivot

近期公司内部在做一种通用查询报表,方便人力资源分析、统计数据。由于之前公司系统中有一个类似的查询使用Excel数据透视表完成的,故我也打算借鉴一下。

测试代码下载 

 

原有系统是使用VBA编写的,难以维护,且对新的操作系统如Windows 7、64位架构不支持,我准备用VSTO进行重写。

数据透视表是一种交互式的表,可以进行某些计算,如求和与计数等。所进行的计算与数据跟数据透视表中的排列有关。

数据透视表是一种完全自助式的报表,功能很强大,对于提取、分析数据来说非常方便。

下面我们添加一张数据透视表。

1、在【数据】中,选择从SQL Server中提取数据:

我从CodePlex上下载了SQL Server 2012的官方示例数据库:AdventureWorks2012

CodePlex网站也改版了,大量Metro风格 J

 

2、连接数据库:

 

3、当前仅仅是测试,选择一个表。当然也可以自己写SQL语句进行筛选数据:

 

4、点击【完成】结束向导:

 

5、这里选择【数据透视表报表】:

 

6、这样一张数据透视表就完成了:

 

7、在右边拖动我们想要分析的字段:

行维度选择了产品模型和产品,列维度选择了尺寸,最终统计了价格。

双击价格的单元格可以看到明细,相当于钻取报表:

 

8、除了表格展现,数据透视表还支持各种图表。

在【数据】中点击【已存在的连接】:

 

9、选择刚刚创建的数据库连接:

 

10、选择图表类型:

 

11、类似刚才一样拖动字段,一个图表就生成了,是不是很强大?

右键可以更改图表类型,如饼形图、锥形图等:

 

 

 

12、我打算把数据透视表功能集成到系统中,需要用代码来生成。

通过跟踪宏代码,可以很容易用代码来生成:

 

Sub Macro1() 

' 

' Macro1 Macro 

' 

 

' 

Workbooks("123.xlsx").Connections.Add ". AdventureWorks2012 Product"""Array _ 

( _ 

"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=.;Use Procedure for Prepare=1;Auto Translate" _ 

, _ 

"=True;Packet Size=4096;Workstation ID=WINDOWS8NB;Use Encryption for Data=False;Tag with column collation when possible=False;Ini" _ 

"tial Catalog=AdventureWorks2012"), Array( _ 

"""AdventureWorks2012"".""Production"".""Product"""), 3 

ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _ 

ActiveWorkbook.Connections(". AdventureWorks2012 Product"), Version:= _ 

xlPivotTableVersion14).CreatePivotTable TableDestination:="Sheet1!R1C1", _ 

TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion14 

Cells(11).Select 

With ActiveSheet.PivotTables("PivotTable1").PivotFields("ProductID"

.Orientation = xlRowField 

.Position = 1 

End With 

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ 

"PivotTable1").PivotFields("ListPrice"), "Sum of ListPrice", xlSum 

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Size"

.Orientation = xlColumnField 

.Position = 1 

End With 

With ActiveSheet.PivotTables("PivotTable1").PivotFields("ProductModelID"

.Orientation = xlRowField 

.Position = 2 

End With 

With ActiveSheet.PivotTables("PivotTable1").PivotFields("ProductID"

.Orientation = xlRowField 

.Position = 2 

End With 

End Sub 

 

 

        private void fnExcel数据透视表测试()
        {
            Excel.Application __app = new Excel.Application();
            __app.DisplayAlerts = false;
            Excel.Workbook __book = __app.Workbooks.Add();

            //创建OleDB连接
            Excel.WorkbookConnection __conn = __book.Connections.Add("Test""PivotTable Test"
                , "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=.;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WINDOWS8NB;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=AdventureWorks2012"
                , "AdventureWorks2012.Production.Product", Excel.XlCmdType.xlCmdTable);

            //创建数据透视表
            Excel.PivotTable __table = __book.PivotCaches().Create(Excel.XlPivotTableSourceType.xlExternal
                , __conn, Excel.XlPivotTableVersionList.xlPivotTableVersion14)
                .CreatePivotTable("Sheet1!R1C1""PivotTable1", __conn, Excel.XlPivotTableVersionList.xlPivotTableVersion14);

            //添加行维度
            __table.PivotFields("ProductModelID").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            __table.PivotFields("ProductModelID").Position = 1;

            __table.PivotFields("ProductID").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            __table.PivotFields("ProductID").Position = 2;

            //添加列维度
            __table.PivotFields("Size").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
            __table.PivotFields("Size").Position = 1;

            //添加值维度
            __table.AddDataField("ListPrice""SUM(ListPrice)", Excel.XlConsolidationFunction.xlSum);

            __book.SaveAs(@"D:\test.xlsx", Excel.XlFileFormat.xlOpenXMLWorkbook);

            __app.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(__app);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(__book);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(__conn);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(__table);

            __app = null;
            __book = null;
            __conn = null;
            __table = null;
        }

 

 

13、这样我们就可以借助于Excel的数据透视表来展示、分析数据。

正因为PivotTable如此好用,微软将PivotTable功能进行了升级,叫做PowerPivot,作为Excel 2010的一个免费插件发布,定位于企业级自助式的商务智能。

目前最新版本是Microsoft® SQL Server® 2012 PowerPivot® for Microsoft® Excel® 2010,下载地址

 

与PowerPivot类似,微软还推出了:

Microsoft® SQL Server® 2012 Data Mining Add-ins for Microsoft® Office® 2010

Microsoft® SQL Server® 2012 Master Data Services Add-in For Microsoft® Excel®

 

PowerPivot也是微软商务智能的主推技术之一,官方网站

 

以下摘自百度百科

PowerPivot for Excel 是用于在 Excel 工作簿中创建 PowerPivot 数据的创作工具。您将使用数据透视表和数据透视图等 Excel 数据可视化对象来显示您在 Excel 工作簿 (.xlsx) 文件中嵌入或引用的 PowerPivot 数据。

  PowerPivot for Excel 通过下列方式来支持自助商业智能。

  取消当前 Excel 中的行和列限制,以便可以导入更多的数据。

  通过数据关系层,您可以集成来自不同数据源的数据并全面处理所有数据。可以输入数据、复制其他工作表中的数据或从企业数据库中导入数据。可以在数据之间建立关系以分析数据,就好像所有数据都来自一个数据源一样。

  创建可移植、可重用的数据。数据保留在工作簿内。您无需管理外部数据连接。如果您发布、移动、复制或共享工作簿,所有的数据都会和工作簿在一起。

  工作簿的其余部分可以立即使用所有的 PowerPivot 数据。可以在 Excel 和 PowerPivot 窗口之间切换,从而以交互方式处理数据及其在数据透视表或数据透视图中的表示形式。处理数据或其表示形式不是单独的任务。可以在同一个 Excel 环境中一起处理数据及其表示形式。

  PowerPivot for Excel 可以让您导入、筛选数百万行数据以及对这些数据进行排序,远远超过 Excel 中一百万行的限制。排序和筛选操作都非常快,因为它们是由在 Excel 内部运行的本地 Analysis Services VertiPaq 处理器执行的。

  更重要的是,通过使用 PowerPivot for Excel,您可以在来自完全不同的数据源的数据之间建立关系,具体方法是映射包含类似或相同数据的列。在数据之间建立关系时,您是在 Excel 中创建了可在数据透视表、数据透视图或任意 Excel 数据表示对象中使用的全新内容。

  保存的数据存储在 Excel 工作簿内部。数据经过高度压缩,生成的文件的大小适合在客户端工作站上进行管理。

  最后,用户会获得一个包含嵌入数据的工作簿 (.xlsx) 文件,这些数据由内部处理器提取和处理,但完全通过 Excel 呈现。压缩和处理是由 Analysis Services VertiPaq 引擎完成的。查询处理在后台透明地运行,以便在 Excel 中提供海量数据支持。因为由本地 Analysis VertiPaq 引擎执行,排序和筛选操作都非常快。

 

14、安装完PowerPivot后打开Excel会多了一个Ribbon:

 

打开PowerPivot Windows后可以看到是一个增强的PivotTable:

 

结合数据挖掘插件已经可以做很多分析了:

 

小结:

Office 已经从办公平台开始转换为了微软的数据平台、商务智能平台,SQL Server 2012的发布加剧了这一过程。

PivotTable和PowerPivot可以很方便的做自助式商务智能分析,相较于SQL Server 分析服务和Oracle、IBM的重量级解决方案,是一种轻量级的解决方案,特别适合于业务、管理人员分析、提取需要的数据。

目前PowerPivot还没有开放API,我也只是从表面了解下基本用途,期待Office 15的登场吧。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
4009 0
怎么设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程
6362 0
阿里云服务器ECS远程登录用户名密码查询方法
阿里云服务器ECS远程连接登录输入用户名和密码,阿里云没有默认密码,如果购买时没设置需要先重置实例密码,Windows用户名是administrator,Linux账号是root,阿小云来详细说下阿里云服务器远程登录连接用户名和密码查询方法
2171 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
5676 0
windows server 2008阿里云ECS服务器安全设置
最近我们Sinesafe安全公司在为客户使用阿里云ecs服务器做安全的过程中,发现服务器基础安全性都没有做。为了为站长们提供更加有效的安全基础解决方案,我们Sinesafe将对阿里云服务器win2008 系统进行基础安全部署实战过程! 比较重要的几部分 1.
4963 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
10712 0
如何设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云安全组设置详细图文教程(收藏起来) 阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程。阿里云会要求客户设置安全组,如果不设置,阿里云会指定默认的安全组。那么,这个安全组是什么呢?顾名思义,就是为了服务器安全设置的。安全组其实就是一个虚拟的防火墙,可以让用户从端口、IP的维度来筛选对应服务器的访问者,从而形成一个云上的安全域。
3807 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,云吞铺子总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系统盘、创建快照、配置安全组等操作如何登录ECS云服务器控制台? 1、先登录到阿里云ECS服务器控制台 2、点击顶部的“控制台” 3、通过左侧栏,切换到“云服务器ECS”即可,如下图所示 通过ECS控制台的远程连接来登录到云服务器 阿里云ECS云服务器自带远程连接功能,使用该功能可以登录到云服务器,简单且方便,如下图:点击“远程连接”,第一次连接会自动生成6位数字密码,输入密码即可登录到云服务器上。
16274 0
阿里云服务器ECS登录用户名是什么?系统不同默认账号也不同
阿里云服务器Windows系统默认用户名administrator,Linux镜像服务器用户名root
721 0
+关注
杰克.陈
一个安静的程序猿~
9798
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载