ExcelServices--开发用户自定义函数

最新推荐文章于 2017-06-22 13:39:58 发布

weixin_30642029

最新推荐文章于 2017-06-22 13:39:58 发布

阅读量117

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/zhengyongfeng/archive/2009/08/29/ExcelService.html

在sharepoint2007中提供强大的ExcelService功能，它的到来的却给用户带来了强大的数据，报表展示功能（这里就不啰嗦了，想多些了解还是自己去查找些相关资料吧）的完美体验。但是Excelservice只能处理Excel自带的函数，这大家应该都明了。就单这一点也给实际的开发多变的客户需求制造了一些麻烦。想想如果局限于此，那么和强大的Excel客户端相比，在计算能力也就大打折扣了，所以针对实际需求，我们也可以找到相应的解决办法。那就是开发自己定义的函数。
具体的操作代码如下（笔者在上方标有相应注释，由于时间问题，就直接贴上加几张图片，不清楚的朋友可以联系我）：
打开VS2005，新建一个类库项目。添加C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI目录下的Microsoft.Office.Excel.Server.Udf组件。代码么如下：

using System;
2

using System.Collections.Generic;
3

using System.Text;
4

using Microsoft.Office.Excel.Server.Udf;
5

using System.Data;
6

using System.Data.SqlClient;
7

using System.Runtime.InteropServices;
8

using Microsoft.Win32;
9

namespace Getproductsalespercentudf
10

{
11

[UdfClass]//记住在类的上方进行[UdfClass]声明而且每个类只能有一个，作用就是让excel能够识别该类。
13

public class GetValue
14

{
15

[UdfMethod]//获取输入产品的当月销售量
17

public int getsingleproductnum(string Company, string Productname, string Month)
18

{
19

DataTable dt_single = new DataTable();
21

int productnum = 0;
22

using (SqlConnection con = new SqlConnection("Data Source=mosingserver;DataBase=ExcelServices;Integrated Security=true;"))
23

{
24

con.Open();
25

string strsql1 = "select Number from Productsales where Company='" + Company + "' and Productname='" + Productname + "' and Month='" + Month + "'";
26

dt_single = table_resualt(strsql1, con);
27

productnum = Convert.ToInt32(dt_single.Rows[0]["Number"]);
28

con.Close();
29

}
31

return productnum;
32

}
33

[UdfMethod]//获取输入产品的总销售量{ 在每个函数上都声明[UdfMethod]，表示该函数可以被excel识别，用户定义函数中可以包含多个[UdfMethod]}
35

public int getallproductnum(string Company, string Productname, string Month)
36

{
37

DataTable dt_total = new DataTable();
38

int producttotalnum = 0;
39

using (SqlConnection con = new SqlConnection("Data Source=mosingserver;DataBase=ExcelServices;Integrated Security=true;"))
40

{
41

con.Open();
42

string strsql2 = "select Sum(Number)as Total from Productsales where Productname='" + Productname + "' and Month='" + Month + "'";
43

dt_total = table_resualt(strsql2, con);
44

producttotalnum = Convert.ToInt32(dt_total.Rows[0]["Total"]);
45

con.Close();
46

}
48

return producttotalnum;
49

}
50

[UdfMethod]//获取当前产品所占销售总量的份额
54

public string Getsalespercent(string Company, string Productname, string Month)
55

{
56

float percent = 0.00F;
57

string percentresualt = "";
58

percent = (float)getsingleproductnum(Company, Productname, Month) / getallproductnum(Company, Productname, Month);
59

percentresualt = (percent * 100).ToString("#0.00") + "%";//四舍五入
60

return percentresualt;
61

}
62

//返回数据
64

public DataTable table_resualt(string strsql, SqlConnection con)
65

{
66

DataTable dt_resualt = new DataTable();
67

SqlDataAdapter sda = new SqlDataAdapter(strsql, con);
68

DataSet ds = new DataSet();
69

sda.Fill(ds);
70

dt_resualt = ds.Tables[0];
71

return dt_resualt;
72

}
74

}
78

}
79

然后点击生成。搞定
进入【管理中心】-【共享服务管理】-【ExcelService配置】-【用户定义函数程序集】

程序集位置有全局程序集和文件路径。看个人的实际操作，笔者这里就直接输入刚才项目的生成组件所在的路径，选择文件夹路径（如果勾选第一种，就得把程序集放入全局程序集（GAC），但是在生成项目组件时，记得强名称，方可放入其中）
完成以上步骤之后，我们在Excel客户端打开一个工作簿：

1.在上方找几个单元格（笔者在A4单元格输入【分公司名称】，然后点击B4单元格，在选择【工具栏】上面的【公式】，选择定义名称，笔者把他定义为分公司名称（具体定义名称由个人自己定义。。。），做这步的目的是为了把他当做参数使用为下面做准备。），同时在A6,B6,A8,B8采用类似的操作。。。这样子笔者就定义了三个参数。分别是（分公司名称，产品名称，销售月份）
2.在B10，B12，B14大家都可以看到单元格变成了#NAME?这是因为我们引用了上方自定义函数，因Excel客户端无法识别而提示的错误，不用管他。首先简述下这步的步骤。细心的朋友从头往下看下来这篇文章，应该知道我在项目里定义了三个函数（getsingleproductnum，getallproductnum，Getsalespercent分别表示求产品当前月份的销售量，所有子公司的同类产品当月销售量总和，该产品占总销售量的百分比，这三个函数都带三个参数，所以在前一步笔者也在工作簿上定义了三个名称就是为了这部做准备。）具体的操作步骤为：选中B10单元格，然后在上方输入=getsingleproductnum(分公司名称,产品名称,销售月份)输完函按Ctrl+Shift+Enter键，B10单元格就变成#NAME?{而B12,B14进行相同的操作。}