勤哲Excel服务器与用友ERP-U8的数据集成
添加时间: 2007-3-1 0:40:21 作者: 第九软件网 阅读次数:221 来源: http://www.d9soft.com
在一个企事业单位中，可能布置有多种软件，如财务软件、OA系统、ERP系统、自主开发的应用系统等等。这些应用软件各自都会积累大量的数据，若不能使它们彼此沟通整合起来，就会形成人们常说得“信息孤岛”的现象 —— 一个企业中有多种系统在运行；各系统有各系统的数据，互相不能访问；同一份数据要在不同的系统中重复录入；各系统的数据之间常常出现不一致；哪里都有数据，领导需要数据时却无法迅速找到，找到了也不知是否准确。
信息孤岛问题的解决需要从多个方面着手，从技术上说，应当有使得不同的系统能够互访数据的能力。
Excel服务器提供了“外部数据源”的功能。其目的是使得Excel服务器能够访问和操作其它应用系统的数据库中的数据。相对于Excel服务器而言，其它系统的数据库称为“外部数据源”。
Excel服务器目前支持的外部数据源类型有三种：SQL Server 2000、Access、Sybase。Excel服务器仅提供利用外部数据的方法。若要实际利用外部数据源的数据，需要具备以下条件：
1）您既要有Excel服务器的系统管理员权限，也要有对外部数据源的系统管理员权限。
2）您了解外部数据源的位置和访问方法。
3）您了解外部数据源中数据表和字段的含义。
现在以用友ERP-U8 为例，说明外部数据源的用法。您会看到，如何把用友ERP-U8的帐套数据库定义为外部数据源；如何通过Excel服务器的模版访问用友ERP-U8的数据。
本例子中所用的用友ERP-U8软件，来自人民邮电出版社《用友ERP----财务软件培训教程（第二版）》的随书附送V8.50演示版光盘。读者可自行购买得到。本例中的数据为演示版中的示例帐套，例子中用到了用友数据库中的两个表，对表名及其中字段含义的解释，为作者自行分析示例帐套中的数据所得，目的仅在于讲解Excel服务器的外部数据源的使用，而非对用友数据库的解读，不代表用友公司，亦不保证正确。
用友ERP-U8 V8.50使用的是SQL Server数据库。本章假设您具备关于SQL Server系统管理的一些基本知识，如SQL Server企业管理器的使用、表与视图的概念、SQL Server帐户及帐户权限设置等。关于这方面内容，读者可自行参考其它相关书籍。
安装好演示版后，引入示例帐套“[999]演示帐套”。熟悉SQL Server的用户可从SQL Server企业管理器中看到，出现了一个新的数据库“UFDATA-999-2002”，如下图所示：
若要在Excel服务器中引用“UFDATA_999_2002”中的数据，要经过两个步骤，首先，要在Excel服务器中把数据库“UFDATA_999_2002”登记为外部数据源。第二步，要指定“UFDATA_999_2002”数据库中的哪些表或视图可以在Excel服务器中被引用，这一步称为“注册数据表”。
这样做了之后，外部数据源中被注册过的表，就像通过Excel服务器模版定义的普通数据表一样，可以在表间公式和回写公式中使用。也可以让模版上的数据项“对应”到这些外部数据表，使得通过Excel服务器填报表单，能将表单数据直接存入外部数据库。
第一节 注册外部数据源
1.以Admin账户（其它具有Administrators角色的帐户亦可）登录到管理台。
2.在管理台主界面左侧树型中选中“外部数据源”，点击鼠标右间，选择弹出菜单“新建à数据源”，弹出“注册外部数据源”对话框，如下图所示。
1）数据源类型选择“SQL Server”，数据源名称您任意起，这里输入“用友”，服务器或数据文件处输入用友数据库所在的服务器名（不必和Excel服务器数据库安装在同一台机器上），作者把用友数据库安装在自己的计算机上，名称为Demo。数据库名称输入用友演示帐套的数据库名，为“UFDATA_999_2002”。
2）登录账户指的是用于连接用友数据库所在的数据库服务器的SQL Server帐户。作为系统管理员，您应当知道帐户和密码。在这里，我们输入SQL Server的超级用户sa，作者计算机的sa帐户口令也是“sa”。
3）按[确定]，完成外部数据源的注册。对话框关闭，管理台主界面右侧窗格中出现已注册的“用友”外部数据源，如下图所示。
第二节 注册外部数据源中的表
注册了“用友”外部数据源后，还需要对其中要用到的数据表或视图进行“注册”，才能实际访问其中的数据。在余下两节的例子中，我们要通过Excel服务器访问用友帐套数据库中的三个表：科目表、凭证表和科目总账表。所以先要对这三个表进行注册。
a. 注册科目表
1）在管理台主界面左侧树型上展开“外部数据源”，选中“用友”，在右侧窗格中显示出所有用友演示帐套数据库中的表和视图。
2）选中表code，点击鼠标右键，选择弹出菜单“注册数据表”。
3）弹出“数据表信息”对话框，将显示名称改为“用友_科目表”。
4）在“列信息”选项卡上修改若干列的“显示列名”：cclassà类别，ccodeà科目编码，ccode_nameà科目名称，igradeà级次，cexch_nameà外币币种，并选中实际列名[ccode]为主键。
修改后的结果如下图所示：
注：所谓数据表的“显示名称”和字段的“显示列名”，目的是表达出数据表和字段的含义，使人更容易理解。不改变也是可以的。
5）切换到“可读权限”选项卡，选择可读部门和角色，如下图：
6）按[确定]
b. 注册凭证表
用同样方法，注册用友帐套数据库中的凭证表。
表名：gl_accvouch
显示名称：用友_凭证表
修改部分字段的显示列名：
|
实际列名 |
显示列名 |
主键 |
|
i_id |
流水号 |
√ |
|
iperiod |
期间 |
|
|
csign |
凭证字 |
|
|
ino_id |
凭证号 |
|
|
inid |
明细行号 |
|
|
dbill_date |
制单日期 |
|
|
cbill |
制单人 |
|
|
ccheck |
审核人 |
|
|
cbook |
记账人 |
|
|
ibook |
是否记账 |
|
|
iflag |
作废标志 |
|
|
cdigest |
摘要 |
|
|
ccode |
科目编码 |
|
|
md |
借方金额 |
|
|
mc |
贷方金额 |
|
|
md_f |
外币借方金额 |
|
|
mc_f |
外币贷方金额 |
可读权限：
部门：阳光乳业、财务部
角色：Administrators、会计、财务部经理
可写权限：
部门：财务部
角色：会计
c. 注册科目总账表
表名：gl_accsum
显示名称：用友_科目总账表
修改部分字段的显示列名：
|
实际列名 |
显示列名 |
主键 |
|
i_id |
流水号 |
√ |
|
ccode |
科目编码 |
|
|
iperiod |
会计期间 |
|
|
cbegind_c |
期初方向 |
|
|
mb |
期初金额 |
|
|
md |
借方金额合计 |
|
|
mc |
贷方金额合计 |
|
|
cendd_c |
期末方向 |
|
|
me |
期末金额 |
可读权限：
部门：阳光乳业、财务部
角色：Administrators、会计、财务部经理
可写权限：无
第三节 表间公式引用外部数据
本节创建一个模版，从用友数据库中查询某科目在指定会计期间内的明细账。
a. 创建模版
新建模版《用友明细账查询》，信息如下：
|
项目 |
内容 | |
|
报表编号 |
FDS-001 | |
|
报表名称 |
用友明细账查询 | |
|
初始填报权限 |
部门：阳光乳业、财务部 角色： Administrators 、财务部经理、会计 | |
|
最终查阅权限 |
部门：无 角色：无 | |
|
其它 |
接受默认值 | |
|
表样： | ||
|
| ||
|
Excel 公式： | ||
|
单元地址 |
公式 | |
|
D7 |
=K7 & IF(L7=0,"",TEXT(L7,"0000")) 并复制到 D8:D17 | |
|
H7 |
=IF(D7="","",IF(H6=" 平 ",IF(F7>0," 借 "," 贷 "),IF(H6=" 借 ",IF(J7>0," 借 ",IF(J7<0," 贷 "," 平 ")),IF(J7>0," 贷 ",IF(J7<0," 借 "," 平 "))))) 并复制到 H8:H17 | |
|
J7 |
=IF(D7="",0,IF(H6=" 借 ",I6+F7-G7,I6+G7-F7)) 并复制到 J8:J17 | |
|
I7 |
=ABS(J7) 并复制到 I8:I17 | |
|
F18 |
=SUM(F7:F17) | |
|
G18 |
=SUM(G7:G17) | |
b. 定义数据规范
c. 定义数据表
|
表名 |
用友明细账查询_主表 |
所在工作表 |
Sheet1 | |||
|
样式 |
单一数据项 |
是否创建 |
否 | |||
|
主键 |
区域 |
字段名 |
数据类型 |
必填 |
填写规范 |
其它 |
|
C2 |
期间 |
整数 |
||||
|
C3:E3 |
科目 |
文字(100字以内) |
科目列表 |
|||
|
G3 |
科目编码 |
文字(20字以内) |
||||
|
B4 |
年 |
整数 |
||||
|
H6 |
期初方向 |
文字(20字以内) |
||||
|
I6 |
期初余额 |
金额 |
||||
|
F18 |
借方合计 |
金额 |
||||
|
G18 |
贷方合计 |
金额 |
||||
|
H18 |
期末方向 |
文字(20字以内) |
||||
|
I18 |
期末余额 |
金额 |
||||
|
表名 |
用友明细账查询_明细 |
所在工作表 |
Sheet1 | |||
|
样式 |
重复数据项、按行、可扩展 |
是否创建 |
否 | |||
|
主键 |
区域 |
字段名 |
数据类型 |
必填 |
填写规范 |
其它 |
|
B7:B17 |
月 |
整数 |
||||
|
C7:C17 |
日 |
整数 |
||||
|
D7:D17 |
凭证号数 |
文字(100字以内) |
||||
|
E7:E17 |
摘要 |
文字(100字以内) |
||||
|
F7:F17 |
借方 |
金额 |
||||
|
G7:G17 |
贷方 |
金额 |
||||
|
H7:H17 |
方向 |
文字(20字以内) |
||||
|
I7:I17 |
余额 |
金额 |
||||
|
K7:K17 |
凭证字 |
文字(20字以内) |
||||
|
L7:L17 |
凭证号 |
整数 |
||||
|
M7:M17 |
流水号 |
整数 |
||||
d. 定义表间公式
公式名称：查询科目编码
应用方式：筛选条件改变后自动执行
公式内容：
提取<用友_科目表>中满足下列条件的数据:
<数据筛选: 用友_科目表.科目名称 = 本报表.Sheet1:科目>
按以下方式填入<用友明细账查询_主表>:
用友_科目表.科目编码--->(填入值)科目编码
公式名称：查询期初余额及方向
应用方式：筛选条件改变后自动执行
公式内容：
提取<用友_科目总账表>中满足下列条件的数据:
<数据筛选: 用友_科目总账表.会计期间 = 本报表.Sheet1:期间
并且 用友_科目总账表.科目编码 = 本报表.Sheet1:科目编码>
按以下方式填入<用友明细账查询_主表>:
用友_科目总账表.期初方向--->(填入值)期初方向
用友_科目总账表.期初金额--->(填入值)期初余额
公式名称：查询明细
应用方式：筛选条件改变后自动执行
公式内容：
提取<用友_凭证表>中满足下列条件的数据:
<数据筛选: 用友_凭证表.期间 = 本报表.Sheet1:期间
并且 用友_凭证表.科目编码= 本报表.Sheet1:科目编码
并且 用友_凭证表.是否记账 =1>
按以下方式填入<用友明细账查询_明细>:
用友_凭证表.流水号--->(填入值)流水号
月份值(用友_凭证表.制单日期)--->(填入值)月
日期值(用友_凭证表.制单日期)--->(填入值)日
用友_凭证表.摘要--->(填入值)摘要
用友_凭证表.借方金额--->(填入值)借方
用友_凭证表.贷方金额--->(填入值)贷方
用友_凭证表.凭证字--->(填入值)凭证字
用友_凭证表.凭证号--->(填入值)凭证号
e. 查询用友财务数据
1）填报《用友明细账查询》
2）期间输入8、选择科目，查询出结果如下：
第四节 填报表单，数据写入外部数据库
除了能从外部数据源查询数据以外，还可以通过填报表单，直接将数据保存到外部数据源中的表。比如，我们可以创建一个《凭证录入》模版，定义数据项时不是创建新的数据表，而是“对应”到“用友_凭证表”。（参见下面的例子---多模板对应一个数据表），这样通过Excel服务器所录入的凭证将直接保存到用友数据库。
例子：多模版对应一个数据表
我们知到，针对同一个模版填制的若干表单对应于数据库中同一个表中的记录。再进一步，我们还可以使针对不同模版填制的若干表单对应于数据库中同一个表中的记录。
举例来说，我们可以定义两种模版：《入库单》和《发货单》，它们的格式不同，权限也不同，但可以使它们都对应于数据库中的同一个表----出入库单。填入入库单的结果是向“出入库单”表中写入数据，填发货单的结果也是向“出入库单”表中写入数据。
下面我们具体实现这个例子，从中您会对上节所述“数据录入与存储的分离”有更深刻的理解。
a. 《成品入库单》模版
新建模版《成品入库单》，信息如下：
|
项目 |
内容 |
|
报表编号 |
INV-001 |
|
报表名称 |
成品入库单 |
|
初始填报权限 |
部门：阳光乳业、储运部 角色：Administrator、保管员 |
|
最终查阅权限 |
部门：阳光乳业、储运部 角色：Administrator、保管员 |
|
其它 |
接受默认值 |
|
表样： | |
|
| |
|
Excel公式： | |
|
单元地址 |
公式 |
|
H5:H7 |
=F5*G5 |
|
H8 |
=SUM(H5:H7) |
定义两个数据表：出入库单_主表、出入库单_明细：
|
表名 |
出入库单_主表 |
所在工作表 |
Sheet1 | |||
|
样式 |
单一数据项 |
是否创建 |
是 | |||
|
主键 |
区域 |
字段名 |
数据类型 |
必填 |
填写规范 |
其它 |
|
√ |
H3 |
单号 |
文字(20字以内) |
√ |
出入库单号 |
|
|
C2 |
日期 |
日期 |
√ |
当前日期 |
||
|
C3 |
来源去向 |
文字(100字以内) |
√ |
部门名称 |
||
|
C9 |
制单人 |
文字(20字以内) |
√ |
当前用户姓名 |
||
|
C12 |
单据类型 |
文字(20字以内) |
√ |
不清空 | ||
|
C13 |
出入标志 |
整数 |
√ |
不清空 | ||
|
C8:F8 |
备注 |
文字(100字以内) |
||||
|
表名 |
出入库单_明细 |
所在工作表 |
Sheet1 | |||
|
样式 |
重复数据项、按行循环、可扩展 |
是否创建 |
是 | |||
|
主键 |
区域 |
字段名 |
数据类型 |
必填 |
填写规范 |
其它 |
|
B5:B7 |
物料编码 |
文字(20字以内) |
√ |
|||
|
C5:C7 |
物料名称 |
名称或地址 |
√ |
产品列表 |
||
|
D5:D7 |
规格型号 |
文字(100字以内) |
||||
|
E5:E7 |
单位 |
文字(20字以内) |
√ |
|||
|
F5:F7 |
数量 |
小数 |
√ |
|||
|
G5:G7 |
单价 |
价格 |
√ |
|||
|
H5:H7 |
金额 |
金额 |
||||
定义表间公式：
|
名称 |
应用时机 |
内容 |
|
提取产品信息 |
筛选条件改变后自动执行 |
提取<物料表>中满足下列条件的数据: <数据筛选: 物料表.物料名称=本报表.Sheet1:物料名称> 按以下方式填入<出入库单_明细>: 物料表.物料编码--->(填入值)物料编码 物料表.物料名称--->(按行(列)匹配)物料名称 物料表.规格型号--->(填入值)规格型号 物料表.计量单位--->(填入值)单位 物料表.计划价--->(填入值)单价 |
定义回写公式：（定义回写公式的操作方法参见第9章）
|
名称 |
应用时机 |
内容 |
|
保存时增加库存量 |
保存时 |
完成任务 [] 后刷新 [物料表] 中满足如下条件的数据: 物料表.物料编码 = 本报表.Sheet1:物料编码 刷新方法: 当前库存 = 物料表.当前库存 + 本报表.Sheet1:数量 |
|
删除时减少库存量 |
删除时 |
撤销任务 [] 后刷新 [物料表] 中满足如下条件的数据: 物料表.物料编码 = 本报表.Sheet1:物料编码 刷新方法: 当前库存 = 物料表.当前库存 - 本报表.Sheet1:数量 |
b.《发货单》模版
新建模版《发货单》，信息如下：
|
项目 |
内容 |
|
报表编号 |
INV-002 |
|
报表名称 |
发货单 |
|
初始填报权限 |
部门：阳光乳业、储运部 角色：Administrator、保管员 |
|
最终查阅权限 |
部门：阳光乳业、储运部 角色：Administrator、保管员 |
|
其它 |
接受默认值 |
|
表样： | |
|
| |
|
Excel公式： | |
|
单元地址 |
公式 |
按以下步骤操作定义数据项。
1）选中单元格H2,C3:E3,H3,C8:F8,C12,C13
2）右击鼠标，选择弹出菜单“定义单一数据项”。
3）按[左侧单元命名]，点击[下一步]，如图：
4）选中“对应到以下数据表中字段”，点击[下一步]，如图：
5）依次单击每行的字段名栏，从下拉列表中选择区域对应的字段名，结果如图：
6）点击[完成]。
7）选中C2单元格，右击鼠标，选择弹出菜单[定义单一数据项]。
8）选[左侧单元命名]，点击[下一步]。
9）选择“添加到以下数据表”、“出入库单_主表”，如下图所示：
10）单击表格中最后一行的字段名栏，将“订单编号”改为“相关单据编号”，数据类型选择“文字（20字以内）”，如下图：
11）点击[完成]
12）选择菜单“模版à管理数据表”，设置“出入库单_主表”的字段“单据类型”和“出入标志”为不清空。
按以下方法操作定义重复数据项：
1）选中单元区域B5:H7，右击鼠标，选择弹出菜单“定义重复数据项”。
2）在弹出窗口中选择标题模式“按行”，上方标题行数为1，数据区域可向下扩展，入下图所示，点击[下一步]。
3）选择“对应到以下数据表中字段”、“出入库单_明细”，如下图，点击[下一步]。
4）依次选择各个单元区域所对应得字段名，如下图：
5）点击[完成]。
定义表间公式：
|
名称 |
应用时机 |
内容 |
|
构造订单编号下拉列表 |
打开后自动执行 （初始填报） |
提取<订单_主表>中满足下列条件的数据: <数据筛选: 订单_主表.审批意见='同意' 并且 订单_主表.已发货='否'> 按以下方式填入<出入库单_主表>: 订单_主表.订单编号--->(构造下拉选项) 相关单据编号 |
|
提取订单客户名称 |
筛选条件改变后自动执行 |
提取<订单_主表>中满足下列条件的数据: <数据筛选: 订单_主表.订单编号=本报表.Sheet1:相关单据编号> 按以下方式填入<出入库单_主表>: 订单_主表.客户名称--->(填入值) 来源去向 |
|
提取订单明细 |
筛选条件改变后自动执行 |
提取<订单_明细>中满足下列条件的数据: <数据筛选: 订单_明细.订单号=本报表.Sheet1:相关单据编号> 按以下方式填入<出入库单_明细>: 订单_明细.产品编码--->(填入值) 物料编码 订单_明细.产品名称--->(填入值) 物料名称 订单_明细.规格--->(填入值) 规格型号 订单_明细.计量单位--->(填入值) 单位 订单_明细.数量--->(填入值) 数量 |
|
提取计划价 |
筛选条件改变后自动执行 |
提取<物料表>中满足下列条件的数据: <数据筛选: 物料表.物料编码=本报表.Sheet1:物料编码> 按以下方式填入<出入库单_明细>: 物料表.物料编码 --->(按行(列)匹配) 物料编码 物料表.计划价 --->(填入值) 单价 |
定义回写公式：（定义回写公式的操作方法参见第9章）
|
名称 |
应用时机 |
内容 |
|
保存时更新订单状态 |
保存时 |
完成任务 [] 后刷新 [订单_主表] 中满足如下条件的数据: 订单_主表.订单编号 = 本报表.Sheet1:相关单据编号 刷新方法: 已发货 = '是' |
|
删除时更新订单状态 |
删除时 |
撤销任务 [] 后刷新 [订单_主表] 中满足如下条件的数据: 订单_主表.订单编号 = 本报表.Sheet1:相关单据编号 刷新方法: 已发货 = '否' |
|
保存时减少库存 |
保存时 |
完成任务 [] 后刷新 [物料表] 中满足如下条件的数据: 物料表.物料编码 = 本报表.Sheet1:物料编码 刷新方法: 当前库存 = 物料表.当前库存 - 本报表.Sheet1:数量 |
|
删除时增加库存 |
删除时 |
撤销任务 [] 后刷新 [物料表] 中满足如下条件的数据: 物料表.物料编码 = 本报表.Sheet1:物料编码 刷新方法: 当前库存 = 物料表.当前库存 + 本报表.Sheet1:数量 |
第五节 如何保证外部数据的安全
通过Excel服务器引用外部数据源，某些用户或许有疑虑。比如在上面的例子中，财务人员可能就会发出这样的疑问：难道用友财务软件的数据能够被任意访问吗。事实上，如果采用恰当的安全措施，这样的担心就完全是多余的。
这些恰当的安全措施包括：
1）用友数据库中建立一个全新的SQL Server帐户，比如说叫visitor，设置该帐户仅对部分表或视图有权限。
2）在Excel服务器管理台中注册外部数据源时，以visitor帐户（而不是sa帐户）作为到用有数据库的登录账户。
3）在用友数据库中，可针对需要访问的数据建立视图，在Excel服务器中注册这些视图，而不是注册数据表本身。
4）注册数据库表或视图时，设定正确的可读权限，尽量不要设定可写权限。
上一篇文章: 最新恶意网站列表 下一篇文章: 自己的名片自己做—小试牛刀做vCard