概述
目前企业应用系统使用的大多数据库都是关系型数据库，关系数据库依赖的理论就是针对集合运算的关系代数。关系代数是一种抽象的查询语言，是关系数据操纵语言的一种传统表达方式。不过我们在工作中发现，很多人在面对复杂的数据库运算逻辑时会采用游标、循环、自定义函数等方式处理，因为游标是一种比较熟悉和舒适的面向过程的编程方式，很符合我们一般的逻辑思维习惯，可很不幸，这会导致糟糕的性能。显然，SQL的总体目的是你要实现什么，而不是怎样实现。大道至简，我们在工作与学习的过程中经常会发现，更好的解决方案往往是简单的，是高效的，是优雅的。
本人曾经用T-SQL重写了一个基于游标的存储过程，那个表只有100,000条记录，原来的存储过程用了40分钟才执行完毕，而新的存储过程只用了不到1秒。在这里，我想将自己遇到和收集到的关于集合运算与游标操作的对比展现给大家，以供参考。
问题描述
我们有时会遇到这样一个问题，类似于某一列的值累计求和（即本条记录的某个值=前几列该值的合计）。我将解决的核心部分抽取出来。
--- 原始数据如下：
OID | Period | Amount | Balance |
1 | 2009 | 3500.00 | 0.00 |
2 | 2009 | 5100.00 | 0.00 |
3 | 2009 | 10000.00 | 0.00 |
4 | 2010 | 2560.00 | 0.00 |
5 | 2010 | 4700.00 | 0.00 |
-- 预期结果如下（求Balance的值）：
OID | Period | Amount | Balance |
1 | 2009 | 3500.00 | 3500.00 |
2 | 2009 | 5100.00 | 8600.00 |
3 | 2009 | 10000.00 | 18600.00 |
4 | 2010 | 2560.00 | 2560.00 |
5 | 2010 | 4700.00 | 7260.00 |
创建测试数据的SQL脚本
(
OID INT IDENTITY PRIMARY KEY
, Period NVARCHAR(20)
, Amount DECIMAL(18, 2) DEFAULT 0
, Balance DECIMAL(18, 2) DEFAULT 0
, Balance2 DECIMAL(18, 2) DEFAULT 0
, Balance3 DECIMAL(18, 2) DEFAULT 0
)
GO
DECLARE @i INT
SET @i = 1900
WHILE @i <= 2013
BEGIN
INSERT INTO tPeriod(Period, Amount)
SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
UNION ALL SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
UNION ALL SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
UNION ALL SELECT CAST(@i AS NVARCHAR), ROUND(RAND() * 10000, -2)
.NET实现Office Excel自定义公式 广泛应用于报表与数据分析 - James Li 阅读原文»
在管理软件开发的功能点中，有相当一部分功能是与Excel做数据交互，产生Excel 数据报表。如果Excel报表的数据计算方法很有规律可循，则可以通过自定义公式来解决。比如常见的资产负债表，利润表，取数都非常有规律。
Excel DNA是一套用.NET框架实现的Excel自定义公式引擎，互联网上有很多Excel财务方面的报表是基于此技术实现。Excel DNA完全开放源代码，可到网上下载它的完整源代码和例子工程，地址是https://exceldna.codeplex.com
开发自定义公式
1 创建用户自定义公式(Visual Basic)
用记事本创建一个Test.dna的文本文件，参考下面的代码例子，实现一个简单的加法公式。
<DnaLibrary>
<![CDATA[
Public Module MyFunctions
Function AddThem(x, y)
AddThem = x + y
End Function
End Module
]]>
</DnaLibrary>
打开Excel，输入公式=AddThem(4,2)，可以看到Excel计算后的公式结果是6。
2 创建自定义公式(C#)
创建一个Test.dna的文本文件，参考如下所示的例子代码，实现字符串与双精度类型数值相加的公式。
<DnaLibrary Language="CS">
<![CDATA[
using ExcelDna.Integration;
public class MyFunctions
{
[ExcelFunction(Description="Joins a string to a number", Category="My functions")]
public static string JoinThem(string str, double val)
{
return str + val;
}
}
]]>
</DnaLibrary>
可以打开Excel，输入公式=JoinThem("James",1983)，可看到实际的运算结果。
3 通过.NET程序集创建自定义公式
Visual Basic,C#都是.NET的一等公民，用自己熟悉的语言创建一个Class Library类库项目TestLib，参考如下的代码例子，实现两个双精度数值相加的公式。
using ExcelDna.Integration;
public class MyFunctions
{
[ExcelFunction(Description="Multiplies two numbers", Category="Useful functions")]
public static double MultiplyThem(double x, double y)
{
return x * y;
}
}
再来创建一个Test.dna的文本文件，添加如下的文本片段，用于声明前面创建的公式。
<DnaLibrary>如果.NET程序集编译成.NET 4.0，则需要稍微修改一下Test.dna的文本内容，参考下面的例子。
<ExternalLibrary Path="TestLib.dll" />
</DnaLibrary>打开Excel，输入公式=MultiplyThem(2,3) 或=MultiplyThem(2; 3)，看到计算结果是5。
<DnaLibrary RuntimeVersion="v4.0" >
<ExternalLibrary Path="TestLib.dll" />
</DnaLibrary>
部署 Deployment
拷贝一份文件ExcelDna.xll到需要的目录中，重命名为需要的名字，比如上面的Test.xll，将上面的Test.dna文件也放到同一个文件中。双击xll文件会打开Excel程序，
提示安全声明，选为本会话启用此加载项。
然后就可以使用自定义的公式，打开或新建一个Excel文件，输入公式，回车后执行公式。
TestClassLibrary程序集中自定义公式的方法的源代码。要编译必须引用程序集ExcelDna.Integration.dll。
[ExcelFunction(Description="My first Excel-DNA function")]
public static string MyFirstFunction(string name)
{
return ("Hello " + name);
}
Test.dna文件的内容如下，只是简单的声明上面的程序集中的方法。
<DnaLibrary Name="First Add-In" RuntimeVersion="v4.0">
<ExternalLibrary Path="TestClassLibrary.dll" />
</DnaLibrary>
打包 Packing
注意在部署时，必须同时存在dna文件和xll文件，而且这两个文件的名字要完全相同。Excel DNA支持将这两个文件合并打包在一个文件中。
运行程序ExcelDna\Distribution\ExcelDnaPack.exe，并传入参数Test.dna。如下的命令所示例的：
ExcelDnaPack.exe， Test.dna
执行完成之后，会生成一个Test-packed.xll文件，这个文件可以拷贝到其它电脑中直接执行。Test-packed.xll中已经包含Test.dna和TestLibrary.dll文件，运行时会主动被加载。
抛开复杂的Visual Studio Tools for Office, 凭借这几天简单的例子，我想读者应该可以快速的上手，高效率的创作Excel中的自定义公式。
界面(Ribbon,Panel)与COM服务器支持
Excel DNA支持创建一个自定义的面板，添加一个WinForms自定义控件，继承于System.Windows.Forms.UserControl，再添加下面的码调用即可。
CustomTaskPane myCTP = CustomTaskPaneFactory.CreateCustomTaskPane(typeof(MyUserControl), myTitle更多内容可参考下载的源代码包，有详细的例子和说明文档。
本文链接：.NET实现Office Excel自定义公式 广泛应用于报表与数据分析，转载请注明。
没有评论:
发表评论