我已经学会使用Jxl和POI API编写Java程序读写Excel文件。是否可以使用宏来帮助运行Java程序?
我已经学会使用Jxl和POI API编写Java程序读写Excel文件。是否可以使用宏来帮助运行Java程序?
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脚本主机对象的引用。如果你不喜欢sleep
,那么你可以用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:\
驱动器并使用以下代码:
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
STDOUT: 你好 Margus。
STDOUT: 你好 Margus。
Dim result As String
Dim commandstr As String
commandstr = "javaw -jar somejar someparameter"
' try with or without cast to string
result = CStr( shellRun(commandstr) )
'somewhere from SO but forget.. sorry for missing credits
Public Function ShellRun(sCmd As String) As String
'Run a shell command, returning the output as a string'
Dim oShell As Object
Set oShell = CreateObject("WScript.Shell")
'run command'
Dim oExec As Object
Dim oOutput As Object
Set oExec = oShell.exec(sCmd)
Set oOutput = oExec.StdOut
'handle the results as they are written to and read from the StdOut object'
Dim s As String
Dim sLine As String
While Not oOutput.AtEndOfStream
sLine = oOutput.ReadLine
If sLine <> "" Then s = s & sLine & vbCrLf
Wend
ShellRun = s
End Function
您的VBA可以将输出写入文件,Java可以定期轮询文件修改并从文件中读取数据。然后通过另一个文件将数据写回VBA。除非您只想通过System.execute(...)从shell中启动Java程序,否则实现VBA-Java集成几乎不可能。