通过java调用VBS,再用VBS执行Excel中的宏的例子

(一)  首先需要降低客户端及服务器端对于Excel的宏的安全级别的要求,按照下述的图示进行操作:

(1)进入宏的安全级别设置功能

 

(2) 降低宏的安全级别

(二)  按照下面代码编写VBS代码

Option Explicit

Dim objXLApp

Dim objXLBook

Set objXLApp = WScript.CreateObject("Excel.Application")

‘注意以下的“e/data_test.xls”的位置需要修改为包含宏的那个excel的实际位置

‘位置需要设置为绝对路径

Set objXLBook = objXLApp.Workbooks.Open("e:/data_test.xls" )

 

‘doChangeExcel即为excel中的宏方法的名称

objXLApp.Run "doChangeExcel"

'objXLBook.Saved = True

objXLBook.Save

objXLBook.Close

Set objXLBook = Nothing

objXLApp.Quit

Set objXLApp = Nothing

WScript.Quit

(三)  以下是excel中的宏,仅需要复制到excel的宏中即可:

'author guoqiang

'date 2007-12-19

Sub doChangeExcel ()

    Dim count

    count = Sheets.count

    Debug.Print count

    For i = 1 To count

        'get the assigned sheet

        Sheets.Item(i).Select

       

        'get the name of the sheet

        SheetName = ActiveSheet.Name

        protected = ActiveSheet.ProtectContents

        'ptotected = True

        ActiveSheet.Select

       

        '---------------------------------------

        'if it is Trans_Xcpt sheet, then row 5 is autofilter

        If SheetName = "Trans_Xcpt" Then

            If protected = True Then

                ActiveSheet.Select

                ActiveSheet.Unprotect

                Rows("5:5").Select

                Selection.AutoFilter

            Else

                Rows("5:5").Select

                Selection.AutoFilter

                ActiveSheet.Protect

            End If

        '---------------------------------------

        'if it is MTD_IN sheet, then row 4 is autofilter

        ElseIf SheetName = "MTD_IN" Then

            If protected = True Then

                ActiveSheet.Select

                ActiveSheet.Unprotect

                Rows("4:4").Select

                Selection.AutoFilter

            Else

                Rows("4:4").Select

                Selection.AutoFilter

                ActiveSheet.Protect

            End If

        '---------------------------------------

        'if it is Net_Demand sheet, then row 4 is autofilter

        ElseIf SheetName = "Net_Demand" Then

            If protected = True Then

                ActiveSheet.Select

                ActiveSheet.Unprotect

                Rows("4:4").Select

                Selection.AutoFilter

            Else

                Rows("4:4").Select

                Selection.AutoFilter

                ActiveSheet.Protect

            End If

        '---------------------------------------

        'if it is A_Supply sheet, then row 4 is autofilter

        ElseIf SheetName = "A_Supply" Then

            If protected = True Then

                ActiveSheet.Select

                ActiveSheet.Unprotect

                Rows("4:4").Select

                Selection.AutoFilter

            Else

                Rows("4:4").Select

                Selection.AutoFilter

                ActiveSheet.Protect

            End If

        '---------------------------------------

        'if it is Special_Cell sheet, then row 4 is autofilter

        ElseIf SheetName = "Special_Cell" Then

            If protected = True Then

                ActiveSheet.Select

                ActiveSheet.Unprotect

                Rows("4:4").Select

                Selection.AutoFilter

            Else

                Rows("4:4").Select

                Selection.AutoFilter

                ActiveSheet.Protect

            End If

        '---------------------------------------

        'else, only protect or unprotect

        Else

            If protected = True Then

                ActiveSheet.Select

                ActiveSheet.Unprotect

            Else

                ActiveSheet.Select

                ActiveSheet.Protect

            End If

        End If

        Next i

End Sub

(四)  在java代码中进行执行(注意:仅仅支持windows2000以上的系统):

//TODO:其他的代码

//以下是执行VBS代码

//注意“E:/Temp/vbs.vbs”应该是正确的存储的VBS代码的位置

//方式一: 直接调用,脚本出错不会报错。

//Process process=Runtime.getRuntime().exec("cmd /c CScript E:/Temp/vbs.vbs");

//方式二: 脚本出错会提示

String[] cpCmd  = new String[]{"wscript", "vbs的绝对路径"};

Process process=Runtime.getRuntime().exec(cpCmd);

//等待VBS执行完毕

process.waitFor();

//TODO: 其他的代码

 

注意:该宏会自动判断是否需要进行protect(宏程序中假定protect和autofilter是一致的,即同时为true或者false)。




java 调用并传值给vb执行excel的宏

test.vbs文件中vb代码如下:

dim args

set args = wscript.arguments

Dim objXL

Set objXL = CreateObject("Excel.Application")

With objXL

    .Workbooks.Open (args(0))

    .Application.Run "Bar"

    .ActiveWorkbook.Save

    .Workbooks.Close

    .Application.Quit

End With

Set objXL = Nothing

解释: args(0)是接收下表为0的参数,也就是第一个参数

.Application.Run "Bar"的 bar 是excel文件中宏的名称

 

java调用vb

Runtime.getRuntime().exec("cmd /c start f:/test.vbs f:/test.xls")

解释: 运行f盘下test.vbs文件,参数为f盘test.xls,在vb文件中.Workbooks.Open (args(0))读取的args(0)则是test.xls。

 

运行后发现有个问题,图表上会多出几个系列,如本来应该显示6个系列,但是图上却显示12个,翻倍的。由于对vb不熟,花了1天时间才搞明白,先看下边代码吧,这是excel中的宏,生成的图为柱状图:

Sub Bar(chartype As String, title As String)

    ActiveSheet.Shapes.AddChart.Select

    If chartype = "3D" Or chartype = "3d" Then

        ActiveChart.ChartType = xl3DColumn

    End If

    If IsNull(title) Or IsEmpty(title) Then

    

    Else

        ActiveChart.HasTitle = True

        ActiveChart.ChartTitle.Text = title

    End If

    

    Dim columnNum

    columnNum = Range("A1").End(xlToRight).Columns.Column

    

    ‘下边 这个循环加上去以后就不会出现系列翻倍的问题了

    For a = 1 To ActiveChart.SeriesCollection.Count

        ActiveChart.SeriesCollection(1).Select

        Selection.Delete

    Next a

 

    For i = 2 To columnNum

        ActiveChart.SeriesCollection.NewSeries

        ActiveChart.SeriesCollection(i - 1).Name = "=" & ActiveSheet.Name & "!$" & Chr(i + 64) & "$" & 1

        ActiveChart.SeriesCollection(i - 1).Values = Range(Cells(2, i), Cells(Range(Chr(i + 64) & "2").End(xlDown).Row, i))

    Next i

    ActiveChart.SeriesCollection(columnNum - 1).XValues = Range(Cells(2, 1), Cells(Range("A2").End(xlDown).Row, 1))

End Sub

 

可能比较啰嗦,但是本人研究了一段时间才搞出来,网上比较难找


  • 1
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值