搜索
查看: 56043|回复: 695

[技巧] EXCEL 调用SQL 取用友数据做表

    [复制链接]
发表于 2013-2-24 15:15:42 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有帐号?注册账号

x
刚在坛子上晃,见有用EXCEL连接SQL做的,但是那种方法不直观,现在我用多图的方式来演示下如何做一份现存量查询表,当然 这数据是可以更新的哦。
以U890为例:
首先,打开EXCEL表依次点击数据→获取数据→自其他来源→来自SQL SERVER
在接下来的数据连接向导中填上数据库的地址用户名和密码

3.png

4.png
2,选择用友的帐套数据库
5.png
在下面你将看到数据库中的好多表
7.png
选择现存量表,V_CURRENTSTOCK,
8.png
下一步→完成
9.png
完成后跳出导入数据的框框,因为之前打开电子表格的时候我默认选择了A1
所以这里直接确定
10.png
然后你就能看见已经把现存量的数据表拉取电子表格里了
11.png
隐藏掉我们不需要的列 只留下存货编码和现存量两列,由于这张表里没有存货名称和规格,我们还要另外再取一张表就是存货档案的表

12.png
打开SHEET2

13.png



再次重复上面的步骤取出存货档案的表在SHEET2中
18.png

19.png
已经取出的数据放在了SHEET2中了,接下来我们就要把存货名称和规格型号引到SHEET1中

20.png
在SHEET1中新建两列,用VLOOKUP 把存货名称和规格型号从SHEET2中引过来
21.png

做好的表就像下图一样
22.png
当然,如果数据有变更的话,直接在表上有数据的地方右击点击刷新,电子表格就和SQL SERVER再次连接取数了。
23.png
如果你发现你用EXCEL查找的时候 存货名称查不到。请在查询框框下面点击选项,然后在查找范围里选择“值”


24.png

嘎嘎,一张现存量查询的表就做完了 ,发给你们的仓库去用吧,除了做单人员以外的需要查现存量的就可以使用了。条件是他们的电脑网络能够连接到ERP的服务器。
想更新数据?右键刷新就行了哦,当然如果懒的话,可以在连接属性里设置打开表格自动更新数据,还可以设置多长时间刷新一次哦。

刚接触用友,最近在做REPORTING SERVER 报表,在论坛是第二次发帖,琢磨出来点东西就想个大伙分享,请各位有钱的砸钱没钱的捧人场,多图编辑好烦的。各位!!!!看你们的了。
14.png
15.png
17.png

评分

参与人数 9威望 +5 金币 +44 收起 理由
linrizi + 2 很给力!
simonxu1981 + 6 赞一个!
fy2010 + 1 很给力!
422025743 + 8
初学者3560604 + 1 赞一个!
maxw-1121 + 1 很给力!
mangnalia + 5
KEVIN_671210 + 20 很给力!
jojo_bj + 5 快速查询一些数据很实用

查看全部评分

发表于 2014-10-9 18:13:06 | 显示全部楼层
本帖最后由 APOLLOOOOO 于 2014-10-9 18:20 编辑

用友UFO的报表运行速度慢,公式编辑界面差,我们老早就不用UFO了,都是用Excel+SQL做报表,如现金流量表查询脚本代码:
  1. /***现金流量表凭证查询***/
  2. --USE UFDATA_201_2014 --连接指定数据库
  3. DECLARE @_YEAR smallint = 2014 --确定查询年度
  4. DECLARE @CodeLin varchar(20) =(SELECT  CODINGRULE
  5.                                                                 FROM GradeDef_Base
  6.                                                                 WHERE KEYWORD = 'code' AND iyear = @_YEAR)
  7. DECLARE @CodeLin1 int = LEFT (@CodeLin , 1)
  8. DECLARE @CodeLin2 int = SUBSTRING (@CodeLin , 2 ,1) + @CodeLin1
  9. DECLARE @CodeLin3 int = SUBSTRING (@CodeLin , 3 ,1) + @CodeLin2
  10. DECLARE @CodeLin4 int = SUBSTRING (@CodeLin , 4 ,1) + @CodeLin3
  11. WITH code_CTE AS (SELECT ccode, ccode_name, igrade, bcash | bbank AS 现金
  12.                                         FROM code
  13.                                         WHERE iyear = @_YEAR) --科目公用表表达式 (CTE)
  14.         ,GL_accvouch2 AS (SELECT GL_accvouch.iyear   AS 年
  15.                                                         ,GL_accvouch.iperiod AS 月
  16.                                                         ,DAY (GL_accvouch.dbill_date) AS 日
  17.                                                         ,GL_accvouch.csign AS 凭证类别
  18.                                                         ,GL_accvouch.i_id
  19.                                                         ,GL_accvouch.ino_id AS 凭证编号
  20.                                                         ,现金科目 = CAST((SELECT  code_CTE.现金
  21.                                                                                                 FROM code_CTE
  22.                                                                                                 WHERE  GL_accvouch.ccode = code_CTE.ccode)
  23.                                                                                         AS int)
  24.                                                 FROM  GL_accvouch
  25.                                                 WHERE  GL_accvouch.iperiod BETWEEN 1 AND 12
  26.                                                                 AND GL_accvouch.iflag IS NULL
  27.                                                                 AND GL_accvouch.iyear = @_YEAR
  28.                                                 ) --判断现金分录
  29. SELECT 凭证.iyear   AS 年
  30.           ,凭证.iperiod AS 月
  31.       ,DAY (凭证.dbill_date) AS 日
  32.       ,记帐标志 = CASE  WHEN  凭证.ibook = 1 THEN '√' ELSE NULL END
  33.       ,凭证.csign AS 凭证类别
  34.       ,凭证.ino_id AS 凭证编号
  35.       ,凭证.cdigest AS 摘要
  36.       ,凭证.ccode AS 科目编码
  37.       ,code.ccode_name AS 科目名称
  38.       ,LEFT (凭证.ccode , @CodeLin1) AS 一级编码
  39.       ,一级科目 = (SELECT  code_CTE.ccode_name
  40.                                         FROM code_CTE
  41.                                         WHERE  LEFT (凭证.ccode , @CodeLin1) = code_CTE.ccode)
  42.       ,二级编码 = CASE WHEN code.igrade > 1 THEN LEFT (凭证.ccode,@CodeLin2) ELSE NULL END
  43.       ,二级科目 = CASE WHEN code.igrade > 1
  44.                        THEN (SELECT  code_CTE.ccode_name
  45.                                                                 FROM code_CTE
  46.                                                                 WHERE  LEFT (凭证.ccode , @CodeLin2) = code_CTE.ccode)
  47.                        ELSE NULL END
  48.       ,现金科目 = CASE WHEN (SELECT  code_CTE.现金
  49.                                                                 FROM code_CTE
  50.                                                                 WHERE  凭证.ccode = code_CTE.ccode) = 1 THEN '√'
  51.                        ELSE NULL END
  52.       ,SIGN((SELECT SUM(现金科目)
  53.         FROM  GL_accvouch2
  54.         WHERE GL_accvouch2.年 = @_YEAR
  55.                                 AND GL_accvouch2.月 = 凭证.iperiod
  56.                                 AND GL_accvouch2.凭证类别 = 凭证.csign
  57.                                 AND GL_accvouch2.凭证编号 = 凭证.ino_id)) AS 流量标志
  58.           ,主表项目 = CASE WHEN ((SELECT SUM(现金科目)
  59.                                                                         FROM  GL_accvouch2
  60.                                                                         WHERE GL_accvouch2.年 = @_YEAR
  61.                                                                                         AND GL_accvouch2.月 = 凭证.iperiod
  62.                                                                                         AND GL_accvouch2.凭证类别 = 凭证.csign
  63.                                                                                         AND GL_accvouch2.凭证编号 = 凭证.ino_id)) = 0
  64.                                                         THEN NULL  --非流量凭证无主表项目
  65.         WHEN (SELECT  code_CTE.现金
  66.                         FROM code_CTE
  67.                         WHERE  凭证.ccode = code_CTE.ccode) = 1
  68.                 THEN '现金'
  69.         WHEN LEFT (凭证.ccode , @CodeLin3) IN (66018001,66018002)
  70.                 THEN '支付给职工以及为职工支付的现金'        --自营工资福利
  71.         WHEN LEFT (凭证.ccode , @CodeLin3) =  660303
  72.                 THEN '汇率变动对现金的影响额'  --汇兑损益
  73.         WHEN LEFT (凭证.ccode , @CodeLin3) BETWEEN 12210009 AND 12210011
  74.                 THEN '购买商品、接受劳务支付的现金'  --自营备用金与押金
  75.         WHEN LEFT (凭证.ccode , @CodeLin2) =  122100
  76.                 THEN '销售商品、提供劳务收到的现金'  --其他自营流水
  77.         WHEN LEFT (凭证.ccode , @CodeLin2) IN (660101,660102)
  78.                 THEN '支付给职工以及为职工支付的现金'  --工资福利
  79.         WHEN LEFT (凭证.ccode , @CodeLin2) IN (660302,660303)
  80.                 THEN '收到的其他与经营活动有关的现金'  --财务费用之利息收入、汇兑损益
  81.         WHEN LEFT (凭证.ccode , @CodeLin1) IN (1121,1122,6001,6041,6051)
  82.                 THEN '销售商品、提供劳务收到的现金'
  83.                         --应收票据、应收账款、主营收入、租赁收入、其他收入
  84.         WHEN LEFT (凭证.ccode , @CodeLin1) IN (1221,6301,6711)
  85.                 THEN '收到的其他与经营活动有关的现金'  --其他应收、营业外收支
  86.         WHEN LEFT (凭证.ccode , @CodeLin1) IN (1405,1801,2202,6401,6402,6601,6603)
  87.                 THEN '购买商品、接受劳务支付的现金'
  88.                         --库存商品、长期待摊、应付账款、主营与其他业务成本、销售与财务费用
  89.         WHEN LEFT (凭证.ccode , @CodeLin1) IN (2221,6403,6801)
  90.                 THEN '支付的各项税费'  --税费
  91.         WHEN LEFT (凭证.ccode , @CodeLin1) =  6111
  92.                 THEN '取得投资收益所收到的现金'
  93.         WHEN LEFT (凭证.ccode , @CodeLin1) IN (1601,1604,1701)
  94.                 THEN '购建固定资产、无形资产和其他长期资产所支付的现金'
  95.         WHEN LEFT (凭证.ccode , @CodeLin1) IN (4001,4002)
  96.                 THEN '吸收投资所收到的现金'
  97.         WHEN LEFT (凭证.ccode , @CodeLin1) =  4104
  98.                 THEN '分配股利利润或偿付利息所支付的现金'
  99.         ELSE NULL
  100.         END
  101.           ,附表项目 = CASE
  102.         WHEN LEFT (凭证.ccode , @CodeLin1) =  4103 THEN '净利润'
  103.         WHEN LEFT (凭证.ccode , @CodeLin1) =  1602 THEN '固定资产折旧'
  104.         WHEN LEFT (凭证.ccode , @CodeLin1) =  1801 THEN '待摊费用减少(减:增加)'
  105.         WHEN LEFT (凭证.ccode , @CodeLin1) =  6603 THEN '财务费用'
  106.         WHEN LEFT (凭证.ccode , @CodeLin1) =  6111 THEN '投资损失(减:收益)'
  107.         WHEN LEFT (凭证.ccode , @CodeLin1) =  1405 THEN '存货的减少(减:增加)'
  108.         WHEN LEFT (凭证.ccode , @CodeLin1) IN (1122,1221)
  109.                 THEN '经营性应收项目的减少(减:增加)'
  110.         WHEN LEFT (凭证.ccode , @CodeLin1) IN (2202,2221)
  111.                 THEN '经营性应付项目的增加(减:减少)'
  112.         ELSE NULL
  113.         END
  114.       ,凭证.md AS 借方金额
  115.       ,凭证.mc AS 贷方金额
  116. FROM    GL_accvouch AS 凭证
  117.                 LEFT OUTER JOIN code ON 凭证.ccode = code.ccode
  118. WHERE    凭证.iperiod BETWEEN 1 AND 12
  119.                 AND 凭证.iflag IS NULL
  120.                 AND 凭证.iyear = @_YEAR
  121.                 --过虑非法月分(期初数据可能会显示0月等)与作废凭证、筛选年度
  122.                 AND (((SELECT SUM(现金科目)
  123.                                 FROM  GL_accvouch2
  124.                                 WHERE GL_accvouch2.年 = @_YEAR
  125.                                                 AND GL_accvouch2.月 = 凭证.iperiod
  126.                                                 AND GL_accvouch2.凭证类别 = 凭证.csign
  127.                                                 AND GL_accvouch2.凭证编号 = 凭证.ino_id)) > 0
  128.                         OR LEFT (凭证.ccode , @CodeLin1)
  129.                                 IN (1122,1221,1405,1602,1801,2202,2221,4103,6111,6603))
  130. ORDER BY 1,2,5,6
复制代码


点评

掌握数据库代码确实很方便  详情 回复 发表于 2016-11-14 10:58
SQL  详情 回复 发表于 2015-7-9 17:17
这是什么方法?我会的是VBA,你这个??  详情 回复 发表于 2014-12-16 13:19
回复 支持 4 反对 0

使用道具 举报

发表于 2013-2-25 15:34:51 | 显示全部楼层

    学习了!

点评

用excel分析用友的数据,做了一些数据模型,有机会多交流:www.df-bi.com  详情 回复 发表于 2016-1-27 21:02
回复 支持 2 反对 0

使用道具 举报

发表于 2013-11-1 12:58:02 | 显示全部楼层
  楼主,首先感谢你做了一个这么好的教程,然后还是感谢你做了这么好的教程,最后还是感谢你做了这么好的教程{:soso_e104:}
  忽然之间我怎么发现,我毕业后从事了ERP的工作脑子好像变得死板多了,一点儿创意也没有了,汗颜啊,我也得争取多研究研究啊!
回复 支持 1 反对 0

使用道具 举报

发表于 2013-2-25 15:32:06 | 显示全部楼层

十分感谢楼主{:soso_e100:} 好人啊
回复 支持 1 反对 0

使用道具 举报

 楼主| 发表于 2013-2-24 15:20:56 | 显示全部楼层
本帖最后由 inpal 于 2013-2-24 16:47 编辑

沙发自己坐了。。。。。。顺便补充一下,这种方法可以运用到其他的各种报表,比如采购模块,生产模块,可以快速的查出自己想要的数据,可扩展性蛮强的,可以做好多辅助性的动作,可以缓解“加密盒已饱和”的情况。当然配合数据字典你就能做出好多好多好玩的。喜欢的就顶一下吧,新手不容易
回复 支持 反对

使用道具 举报

发表于 2013-2-24 16:54:54 | 显示全部楼层
嗯,不错,不过很早就知道了,还是顶一下
回复 支持 反对

使用道具 举报

发表于 2013-2-24 16:59:38 | 显示全部楼层
学习了
回复 支持 反对

使用道具 举报

发表于 2013-2-24 17:18:41 | 显示全部楼层
好东西,学习一下,收藏之。
回复 支持 反对

使用道具 举报

发表于 2013-2-24 18:00:38 | 显示全部楼层
嗯,不错,不过很早就知道了,还是顶一下
回复 支持 反对

使用道具 举报

发表于 2013-2-25 09:15:34 | 显示全部楼层
好想法啊,
回复 支持 反对

使用道具 举报

发表于 2013-2-25 10:48:33 | 显示全部楼层
好东西学习学习
回复 支持 反对

使用道具 举报

发表于 2013-2-25 11:16:36 | 显示全部楼层
学习了{:soso_e181:}
回复 支持 反对

使用道具 举报

发表于 2013-2-25 11:45:43 | 显示全部楼层
嗯,不错,非常感谢
回复 支持 反对

使用道具 举报

发表于 2013-2-25 13:15:32 | 显示全部楼层
学习了, 怎么刷新excel中的数据,楼主能不能提供一种方法

点评

在上面的图片里已经说明了 ,刷新数据只要在表格上有字的部分右击点刷新就行了,或者在EXCEL“数据”标签上有个连接属性,在里面设置刷新时间,或者设置打开表格自动刷新数据  详情 回复 发表于 2013-2-25 15:27
回复 支持 反对

使用道具 举报

发表于 2013-2-25 13:18:44 | 显示全部楼层
    学习了!
回复 支持 反对

使用道具 举报

 楼主| 发表于 2013-2-25 15:27:11 | 显示全部楼层
jia332 发表于 2013-2-25 13:15
学习了, 怎么刷新excel中的数据,楼主能不能提供一种方法

在上面的图片里已经说明了  ,刷新数据只要在表格上有字的部分右击点刷新就行了,或者在EXCEL“数据”标签上有个连接属性,在里面设置刷新时间,或者设置打开表格自动刷新数据

点评

十分感谢楼主 好人啊  详情 回复 发表于 2013-2-25 15:32
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 注册账号

本版积分规则

Archiver|手机版|用友之家 ( 蜀ICP备07505338号 ) 川公网安备 51072502110008号

GMT+8, 2017-7-26 08:40 , Processed in 0.393794 second(s), 29 queries , Gzip On, MemCache On.

Powered by Discuz! X3.3

© 2005-2017

快速回复 返回顶部 返回列表