vba 从 Java 中使用宏读取 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4698812/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Read Excel with Macro from Java
提问by Adi Sembiring
I have excel. and I create macro to the excel file to read data from other resources. the macro run every second and update its excel cells.
我有优秀的。我为excel文件创建宏以从其他资源读取数据。宏每秒运行一次并更新其 excel 单元格。
Now, I want to build java program to read the excel data every seconds to. I have try Apache POI, but after I check the documentationti doesn't support reading excel file with macro.
现在，我想构建 java 程序来每秒读取 excel 数据。我尝试过 Apache POI，但在我检查文档后，ti 不支持使用宏读取 excel 文件。
I read from some resources Java Com Bridge (JCOB) can be used to read excel with macro. I've try, But the cell value still not updated every times I try my code.
我从一些资源中读取了 Java Com Bridge ( JCOB) 可用于读取带有宏的 excel。我试过了，但是每次我尝试我的代码时，单元格值仍然没有更新。
import com.jacob.com.*;
import com.jacob.activeX.*;
public class ExcelTest {
private static ActiveXComponent xl;
private static Dispatch workbooks = null;
private static Dispatch workbook = null;
private static Dispatch sheet = null;
private static String filename = null;
private static boolean readonly = false;
public static void main(String[] args) {
String file = "D:\tutorial\ApachePoi\ratesource.xls";
OpenExcel(file, false); // do not show false to open Excel
System.out.println(GetValue("B46"));
}
private static void OpenExcel(String file, boolean f) {
try {
filename = file;
xl = new ActiveXComponent("Excel.Application");
xl.setProperty("Visible", new Variant(f));
workbooks = xl.getProperty("Workbooks").toDispatch();
workbook = Dispatch.invoke(
workbooks,
"Open",
Dispatch.Method,
new Object[] { filename, new Variant(false),
new Variant(readonly) },// whether to open read-only
new int[1]).toDispatch();
} catch (Exception e) {
e.printStackTrace();
}
}
// Read value
private static String GetValue(String position) {
if (workbook == null) {
System.out.println("workbook is null");
}
sheet = Dispatch.get(workbook, "ActiveSheet").toDispatch();
Object cell = Dispatch.invoke(sheet, "Range", Dispatch.Get,
new Object[]{position}, new int[1]).toDispatch();
String value = Dispatch.get((Dispatch) cell, "Value").toString();
return value;
}
//1.3638356164383563
//1.3638356164383563
private static void SetValue (String position, String type, String value)
{
}
}
回答by Thorbj?rn Ravn Andersen
I am unfamiliar with an Excel engine capable of doing what you describe.
我不熟悉能够执行您描述的操作的 Excel 引擎。
Have you considered talking to Excel instead and ask it for its values when running your spread-sheet? I believe you can do so with ODBC.
您是否考虑过与 Excel 交谈并在运行电子表格时询问其值？我相信你可以用 ODBC 做到这一点。
Another approach might be creating an OpenOffice version of your sheet and talk to OpenOffice instead.
另一种方法可能是创建您的工作表的 OpenOffice 版本并改为与 OpenOffice 交谈。
回答by Thorbj?rn Ravn Andersen
One pitfall is poi don't change the value of the existing excel write to another file and you can see the difference
一个陷阱是 poi 不要更改现有 excel 写入另一个文件的值，您可以看到差异
workbook.getSheet().getRow(1).getCell(0).setValue("test");
and write this(changed) workbook to another file
并将此（更改后的）工作簿写入另一个文件
public void writeFile(String fileName) {
FileOutputStream fos;
try {
fos = new FileOutputStream(fileName);
getWorkbook().write(fos);
fos.close();
} catch (IOException e) {
System.out.println("IOException occurs");
e.printStackTrace();
}
}
