用于运行 Java 程序的 Microsoft Excel 宏 [英] Microsoft Excel Macro to run Java program

问题描述

我已经学会了在 Jxl 和 POI API 的帮助下使用 Java 程序读取和编写 Excel 文件.是否可以在宏的帮助下运行 Java 程序?

I have learnt to read and write an Excel file using a Java program with the help of Jxl and POI API. Is it possible to run a Java program with the help of macros?

推荐答案

是的，这是可能的.

实际上有很多方法，我希望你喜欢我的例子.

There are quite a few ways actually and I hope you like my examples.

为了证明这一点，我创建了一个程序，其中一些文本作为参数发送，程序以更改后的版本进行响应.我做了一个可运行的罐子.第一个示例从 args 读取参数，从标准输入读取其他参数.

To demonstrate this, I create a program where some text is send as arguments and program responds with an altered version of it. I made a runnable jar of it. First example reads the argument from args and other from standard input.

文件Hello.java和H1.jar:

public class Hello {
    public static void main(String[] args) {
        StringBuilder sb = new StringBuilder("Hello");
        
        if (args.length > 0) 
            sb.append(' ').append(args[0]);
        System.out.println(sb.append('.').toString());
    }
}

文件Hello2.java和H2.jar:

import java.util.Scanner;

public class Hello2 {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        StringBuilder sb = new StringBuilder("Hello");
        
        sb.append(' ').append(sc.nextLine());
        System.out.println(sb.append('.').toString());
    }
}

您可以将它们保存在一个 jar 中，但随后您需要创建和 使用清单(这有点矫枉过正).

You can save them in a single jar, but then you need create and use a manifest (that's a bit overkill).

现在在 Excel 中，我添加了一个模块和对 Windows 脚本宿主对象的引用.如果你不喜欢 sleep，那么你可以用 DoEvents 替换它:

Now in Excel I add a module and a reference to Windows Script Host Object. If you do not like the sleep, then you can replace it with DoEvents:

'add a reference to Windows Script Host Object Model
'for example : Tools-References
Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Sub RunSleep( _
    exec As WshExec, _
    Optional timeSegment As Long = 20 _
)
    Do While exec.Status = WshRunning
        Sleep timeSegment
    Loop
End Sub

Private Function RunProgram( _
    program As String, _
    Optional command As String = "" _
) As WshExec
    Dim wsh As New WshShell
    Dim exec As WshExec

    Set exec = wsh.exec(program)
    Call exec.StdIn.WriteLine(command)
    Call RunSleep(exec)
    Set RunProgram = exec
End Function

为了测试它，我将文件保存到 c: 驱动器并使用代码:

And to test it I saved the files to c: drive and used the code:

Public Sub Run()
    Dim program As WshExec
    Set program = RunProgram("java -jar ""C:\H1.jar"" Margus")
    Debug.Print "STDOUT: " & program.StdOut.ReadAll

    Set program = RunProgram("java -jar ""C:\H2.jar", "Margus")
    Debug.Print "STDOUT: " & program.StdOut.ReadAll
End Sub

就我而言，我得到的回应是:

In my case I get a responce of :

标准输出:你好，马格斯.

STDOUT: Hello Margus.

标准输出:你好，马格斯.

STDOUT: Hello Margus.

这篇关于用于运行 Java 程序的 Microsoft Excel 宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！