excel宏 java,Microsoft 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?

解决方案

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