如何从 Java 调用 Excel 电子表格中的 VBA 代码? [英] How do I call VBA code in an Excel spreadsheet from Java?
问题描述
我有一个包含大量 VBA 代码的 Excel 文件.当在 Excel 中打开文档时，有 4 个不带参数的公共子例程可以由用户调用，这些子例程根据需要操作各种工作表中的数据.我们有一个大型 Java 应用程序，我们希望通过从 Java 环境调用宏来与该文档进行交互.关键是我们只需要编写一次 VBA 代码，然后 Java 就可以调用它来执行.此外，我们想假设 Java 应用程序的用户不一定可以立即访问 Excel，而是在 Windows 机器上操作.应该怎么做呢?
I have an Excel file with a large set of VBA code. There are 4 public subroutines that take no parameters that can be called by the user when the document is opened in Excel, these manipulate the data in the various sheets as needed. We have a large Java application that we would like to interact with this document by calling the Macros from the Java environment. The point is that we only have to write the VBA code once and then Java can call it for execution. Furthermore, we want to assume that the user of the Java application does not necessarily have immediate access to Excel, but is operating on a Windows machine. How should one go about doing this?
我们是否将 VBA 代码编译成 DLL，并从 Java 内部调用它?你如何编译 DLL，这是否需要使用 Visual Studio?我们如何从 Java 调用 DLL?我们应该尝试某种 COM 对象吗?
Do we compile the VBA code into a DLL, and call it from within Java? How do you compile the DLL, does that require the use of Visual Studio? How do we call the DLL from Java? Should we try some sort of COM object?
推荐答案
我基本上看到了从 Java 应用程序调用 Excel 中的 VBA 代码的三个选项:
I basically see three options for calling VBA code in Excel from a Java application:
Java COM Bridge: There are several tools available that allow you to call COM or COM Automation components from Java. Excel is such a component. I know of Jacob and JCom, but there might more such tools available.
Java/VBScript/COM 自动化:由于您显然不需要将数据传递给 VBA 代码，因此最简单的解决方案可能是编写一个 VBScript 来启动 Excel，打开文档，调用宏并关闭 Excel.这个脚本是从 Java 启动的 Runtime.getRuntime().exec("cmd/c start script.vbs");
Java / VBScript / COM Automation: Since you obviously don't need to pass data to the VBA code, the simplest solution is probably to write a VBScript that starts Excel, opens the document, calls the macro and closes Excel. This script is started from Java with Runtime.getRuntime().exec("cmd /c start script.vbs");
JNI:您可以为您的应用程序编写特定的 DLL.它实现了 JNI 接口，因此可以从 Java 调用.它的实现使用 COM 调用来处理 Excel.这样的 DLL 最好用 VisualStudio 编写，它支持 C++ 调用 COM 对象.
JNI: You could write a specific DLL for your applications. It implements the JNI interface so it can be called from Java. And its implementation uses COM calls to work with Excel. Such a DLL is best written with VisualStudio and it's support for C++ to call COM objects.
无论您的解决方案是什么，您基本上都希望在 Excel 自动化界面(VBScript 中的示例)上执行以下命令:
Whatever your solution will be, you basically want to execute the following commands on Excel automation interface (sample in VBScript):
Dim xl
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open ("workbook.xlsx")
xl.Application.Run "MyMacro"
xl.Application.Quit
Set xl = Nothing
您不能将 VBA 代码编译成 DLL.不存在用于此的工具(与完整的 Visual Basic 相比).
You cannot compile VBA code into a DLL. There exists no tool for that (in contrast to the full Visual Basic).
我希望这个答案有帮助，即使我不明白你的意思:我们想假设 Java 应用程序的用户不一定可以立即访问 Excel，而是在一个Windows 机器."
I hope this answer is helpful even though I didn't understand what you mean by: "we want to assume that the user of the Java application does not necessarily have immediate access to Excel, but is operating on a Windows machine."
这篇关于如何从 Java 调用 Excel 电子表格中的 VBA 代码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
