excel与用友结合开发报表

hner

用习惯excel软件，感觉用友报表系统太落后了，如果能将excel功能在用友报表中体现出来，我想报表开发就方便很多了！

我目前每月将软件中的科目发生额导入excel中，然后利用excel函数进行编制各种报表，大家有没有这方面的经验！在这里分享一下吧！

谢谢了！

xiaohuogai

excel与用友结合开发报表,强烈支持！！

he.marx

谢谢您的无私分享！我正在寻找呢！

topunion009

excel与用友结合开发报表,强烈支持！！

qwayy

还没用到~~~~~~多多留意~~~~

cpmodel

我一般是用EXCEL VBA和SQL 直接到用友数据库取数到数组，全内存运算生成报表之后，直接粘贴到EXCEL表中，整个过程估计不超过1秒！等下我发个例子上来！

zhua

我也尝试了，用VBA来处理，简单用做了程序，获取客户档案表：不用登录用友就能直接在Excel中方便查询客户资料。

hner

这是我这两天写的代码，主要是在excel中获取数据库中的数据。


Sub QuerySalebillVouch()
'销售发票主表
ActiveSheet.Range("a8:iv65536").ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER=SQL Server;SERVER=10.3.0.98;UID=saWD=;APP=Microsoft Office XP;WSID=FIN-RENH;DATABASE=UFDATA_001_2008" _
        , Destination:=Range("A8"))
        .CommandText = Array( _
        "select * from UFDATA_001_2008.dbo.salebillvouch salebillvouch where sbvid>=4526 order by sbvid desc")
       .Refresh BackgroundQuery:=False
End With
End Sub



Sub QuerySalebillVouchs()
'销售发票子表
ActiveSheet.Range("a8:iv65536").ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER=SQL Server;SERVER=10.3.0.98;UID=saWD=;APP=Microsoft Office XP;WSID=FIN-RENH;DATABASE=UFDATA_001_2008" _
        , Destination:=Range("A8"))
        .CommandText = Array( _
        "select * from UFDATA_001_2008.dbo.salebillvouchs salebillvouchs where autoid>=121700 order by autoid desc")
       .Refresh BackgroundQuery:=False
      
End With
End Sub
Sub MaxAutoID()
'查询销售发票子表的最大标识号
With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER=SQL Server;SERVER=10.3.0.98;UID=saWD=;APP=Microsoft Office XP;WSID=FIN-RENH;DATABASE=UFDATA_001_2008" _
        , Destination:=Range("E3"))
        .CommandText = Array( _
        "select MAX(AUTOID) from UFDATA_001_2008.dbo.salebillvouchs salebillvouchs ")
       .Refresh BackgroundQuery:=False
      
End With
End Sub
Sub Inventory()
'存货编码
ActiveSheet.Range("a8:iv65536").ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER=SQL Server;SERVER=10.3.0.98;UID=sa;PWD=;APP=Microsoft Office XP;WSID=FIN-RENH;DATABASE=UFDATA_001_2008" _
        , Destination:=Range("A8"))
        .CommandText = Array( _
        "select * from UFDATA_001_2008.dbo.inventory inventory")
       .Refresh BackgroundQuery:=False
      
End With
End Sub

Sub Code()
'科目代码表
ActiveSheet.Range("a8:iv65536").ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER=SQL Server;SERVER=10.3.0.98;UID=sa;PWD=;APP=Microsoft Office XP;WSID=FIN-RENH;DATABASE=UFDATA_001_2008" _
        , Destination:=Range("A8"))
        .CommandText = Array( _
        "select * from UFDATA_001_2008.dbo.code code order by ccode")
       .Refresh BackgroundQuery:=False
      
End With
End Sub
Sub AP_InvCode()
'应收应付购销科目设置表
ActiveSheet.Range("a8:iv65536").ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER=SQL Server;SERVER=10.3.0.98;UID=sa;PWD=;APP=Microsoft Office XP;WSID=FIN-RENH;DATABASE=UFDATA_001_2008" _
        , Destination:=Range("A8"))
        .CommandText = Array( _
        "select * from UFDATA_001_2008.dbo.AP_invcode ap_invcode")
       .Refresh BackgroundQuery:=False
End With
End Sub
Sub AP_Detail()
'应收应付明细表
ActiveSheet.Range("a8:iv65536").ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER=SQL Server;SERVER=10.3.0.98;UID=sa;PWD=;APP=Microsoft Office XP;WSID=FIN-RENH;DATABASE=UFDATA_001_2008" _
        , Destination:=Range("A8"))
        .CommandText = Array( _
        "select * from UFDATA_001_2008.dbo.ap_detail ap_detail where auto_id>=307520 order by auto_id desc")
       .Refresh BackgroundQuery:=False
End With
End Sub

Sub 查询收款明细()
'查询收款明细
ActiveSheet.Range("a8:iv65536").ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER=SQL Server;SERVER=10.3.0.98;UID=sa;PWD=;APP=Microsoft Office XP;WSID=FIN-RENH;DATABASE=UFDATA_001_2008" _
        , Destination:=Range("A8"))
        .CommandText = Array( _
        "select * from UFDATA_001_2008.dbo.ap_detail ap_detail where iperiod=5 and ap_detail.cVouchType=48 and ap_detail.cInvCode is not null order by auto_id desc")
       .Refresh BackgroundQuery:=False
End With
End Sub
Sub cGLSign()
'查询转账凭证贷方明细
On Error Resume Next
Dim N As Integer
N = Range("b3")
m = Range("b4")

ActiveSheet.Range("a8:iv65536").ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER=SQL Server;SERVER=10.3.0.98;UID=sa;PWD=;APP=Microsoft Office XP;WSID=FIN-RENH;DATABASE=UFDATA_001_2008" _
        , Destination:=Range("A8"))
        .CommandText = Array( _
        "select iperiod 会计期,iGLno_id 凭证号,cvouchid 发票号,dvouchdate 发票日期,cdwcode 客户代码,sum(idamount) 发票金额  from UFDATA_001_2008.dbo.ap_detail ap_detail where  cGLSign='转' and iperiod=" & N & "and iGLno_id=" & m & "group by cvouchid,dvouchdate,cdwcode,iperiod,iglno_id")
        
        
       .Refresh BackgroundQuery:=False
End With
Range("a65536").End(xlUp).Offset(1, 0) = "合计"
Range("a65536").End(xlUp).Offset(0, 5) = "=sum(f9:f" & Range("f65536").End(xlUp).Row & ")"
End Sub

[ 本帖最后由 hner 于 2008-5-19 13:50 编辑 ]

潮涨潮落

楼上高手啊,可惜我不会用

vsop

用f417不是挺好吗

jimoshier

没看明白。。。。。。

jimoshier

我们的UFO报表只出资产负债表和利润表

hner

利用UFO编制报表麻烦，利用EXCEL+VBA结合SQL语言或者EXCEL函数完全可以编制出很多个性化的报表！

ae0044boy

本人觉得说到取数问题UFO是挺好用的,不用那么多专业知识

smallcase

看了此贴更知得抓紧学VBA了