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

问题描述

在Jxl和POI API的帮助下，我学会了使用Java程序读取和写入Excel文件。借助宏可以运行Java程序吗？

解决方案

是的，可以。

实际上几乎没有办法，我希望你喜欢我的例子。

为了演示这个，我创建一个程序，其中一些文本作为参数发送，并且程序以其更改的版本作出响应。我做了一个可运行的罐子。第一个例子从args和其他标准输入读取参数。

文件 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中，但是您需要创建并使用清单（这有点过度）。

现在在Excel中添加一个模块和对Windows Script Host Object的引用。如果你不喜欢 sleep ，那么你可以用 DoEvents 替换它：

 '添加对Windows Script Host Object Model 
'的引用，例如：Tools-References 
 Option Explicit 
私有声明Sub Sleep Libkernel32（ByVal dwMilliseconds As Long）
 
 Private Sub RunSleep（_ 
 exec As WshExec，_ 
可选timeSegment As Long = 20 _ 
）
尽管exec.Status = WshRunning 
睡眠时间段
循环
结束子
 
私有函数RunProgram（_ 
程序As String，_ 
可选命令As String =_ 
）As WshExec 
 Dim wsh As New WshShell 
 Dim exec As WshExec 
 
设置exec = wsh.exec（程序）
调用exec.StdIn.WriteLine（命令）
调用RunSleep（exec）
设置RunProgram = exec 
结束函数
  

要测试它，我将文件保存到 c：\ 驱动器并使用代码：

  Public Sub Run（）
 Dim程序作为WshExec 
设置程序= RunProgram（java -jarC：\\\H1.jarMargus）
调试。打印STDOUT：& program.StdOut.ReadAll 
 
设置程序= RunProgram（java -jarC：\\\H2.jar，Margus）
 Debug.PrintSTDOUT： &安培; program.StdOut.ReadAll 
 End Sub 
  

在我的情况下，我得到一个响应： / p>

STDOUT：你好Margus。

STDOUT：你好Margus。

如果您发现这有用，不要忘记upvote：D

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?

解决方案

Yes, it is possible.

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

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.

File Hello.java and 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());
    }
}

File Hello2.java and 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());
    }
}

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

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

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.

If you found this useful, do not forget to upvote :D

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