下载jacob-1.19.zip
https://sourceforge.net/projects/jacob-project/
jacob-1.19-x64.dll放到C:\Windows\System32目录下
jacob.jar引入项目
Sub test1()
Worksheets("Sheet1").Range("A5").Value = 22
MsgBox "工作表Sheet1内单元格A5中的值为" _
& Worksheets("Sheet1").Range("A5").Value
End Subexcel需要保存为启用宏的工作簿，后缀为.xlsm
文档特定设置 宏
JacobExcelTool.java
/*
* Copyright © 2019 bjfansr@cn.ibm.com Inc. All rights reserved
* @package: com.ibm.jacob
* @version V1.0
*/
package com.ibm.jacob;
import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.ComThread;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;
/**
* @author Moses *
* @Date 2019/4/1
*/
public class JacobExcelTool {
/**
* Excel对象
*/
private ActiveXComponent xl = null;
/**
* 工作簿对象
*/
private Dispatch workbooks = null;
/**
* 具体工作簿
*/
private Dispatch workbook = null;
/**
* 获得sheets集合对象
*/
private Dispatch sheets = null;
/**
* 当前sheet
*/
private Dispatch currentSheet = null;
public ActiveXComponent getXl() {
return xl;
}
public Dispatch getWorkbooks() {
return workbooks;
}
public Dispatch getWorkbook() {
return workbook;
}
/**
* 打开excel文件
*
* @param filepath 文件路径名称
* @param visible 是否显示打开
* @param readonly 是否只读方式打开
*/
public void OpenExcel(String filepath, boolean visible, boolean readonly) {
try {
// 清空原始变量
initComponents();
//仅允许同时运行一个线程,其他线程锁住,ComThread.InitMTA(true);可同时运行多个
ComThread.InitSTA();
// Excel对象
if (xl == null) {
xl = new ActiveXComponent("Excel.Application");
}
// 设置是否显示打开excel
xl.setProperty("Visible", new Variant(visible));
// 工作簿对象
if (workbooks == null) {
workbooks = xl.getProperty("Workbooks").toDispatch();
}
// 打开具体工作簿
workbook = Dispatch.invoke(
workbooks, "Open", Dispatch.Method,
new Object[]{filepath, new Variant(false), new Variant(readonly)},
new int[1]).toDispatch();
} catch (Exception e) {
e.printStackTrace();
releaseSource();
}
}
/**
* 工作簿另存为
*
* @param filePath 另存为的路径
*/
public void SaveAs(String filePath) {
Dispatch.invoke(workbook, "SaveAs", Dispatch.Method, new Object[]{filePath, new Variant(44)}, new int[1]);
}
/**
* 关闭excel文档
*
* @param f 含义不明 （关闭是否保存？默认false）
*/
public void CloseExcel(boolean f, boolean quitXl) {
try {
Dispatch.call(workbook, "Save");
Dispatch.call(workbook, "Close", new Variant(f));
} catch (Exception e) {
e.printStackTrace();
} finally {
if (quitXl) {
releaseSource();
}
}
}
/**
* 释放资源
*/
public void releaseSource() {
if (xl != null) {
xl.invoke("Quit", new Variant[]{});
xl = null;
}
workbooks = null;
ComThread.Release();
System.gc();
}
/**
* 添加新的工作表(sheet)，（添加后为默认为当前激活的工作表）
*/
public Dispatch addSheet() {
return Dispatch.get(Dispatch.get(workbook, "sheets").toDispatch(), "add").toDispatch();
}
/**
* 修改当前工作表的名字
*
* @param newName
*/
public void modifyCurrentSheetName(String newName) {
Dispatch.put(getCurrentSheet(), "name", newName);
}
/**
* 得到当前工作表的名字
*
* @return
*/
public String getCurrentSheetName() {
return Dispatch.get(getCurrentSheet(), "name").toString();
}
/**
* 得到工作薄的名字
*
* @return
*/
public String getWorkbookName() {
if (workbook == null) {
return null;
}
return Dispatch.get(workbook, "name").toString();
}
/**
* 得到sheets的集合对象
*
* @return
*/
public Dispatch getSheets() {
if (sheets == null) {
sheets = Dispatch.get(workbook, "sheets").toDispatch();
}
return sheets;
}
/**
* 得到当前sheet
*
* @return
*/
public Dispatch getCurrentSheet() {
currentSheet = Dispatch.get(workbook, "ActiveSheet").toDispatch();
return currentSheet;
}
/**
* 通过工作表名字得到工作表
*
* @param name sheetName
* @return
*/
public Dispatch getSheetByName(String name) {
return Dispatch.invoke(getSheets(), "Item", Dispatch.Get, new Object[]{name}, new int[1]).toDispatch();
}
/**
* 通过工作表索引得到工作表(第一个工作簿index为1)
*
* @param index
* @return sheet对象
*/
public Dispatch getSheetByIndex(Integer index) {
return Dispatch.invoke(getSheets(), "Item", Dispatch.Get, new Object[]{index}, new int[1]).toDispatch();
}
/**
* 得到sheet的总数
*
* @return
*/
public int getSheetCount() {
int count = Dispatch.get(getSheets(), "count").toInt();
return count;
}
/**
* 调用excel宏
*
* @param macroName 宏名
*/
public void callMacro(String macroName) {
Dispatch.call(xl, "Run", new Variant(macroName));
}
/**
* 调用excel宏
*
* @param macroName 宏名
* @param param 传递参数
*/
public void callMacro(String macroName, Object param) {
Dispatch.call(xl, "Run", new Variant(macroName), new Variant(param));
}
/**
* 单元格写入值
*
* @param sheet 被操作的sheet
* @param position 单元格位置，如：C1
* @param type 值的属性 如：value
* @param value
*/
public void setValue(Dispatch sheet, String position, String type, Object value) {
Dispatch cell = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[]{position}, new int[1])
.toDispatch();
Dispatch.put(cell, type, value);
}
/**
* 单元格读取值
*
* @param position 单元格位置，如： C1
* @param sheet
* @return
*/
public Variant getValue(String position, Dispatch sheet) {
Dispatch cell = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[]{position}, new int[1])
.toDispatch();
return Dispatch.get(cell, "Value");
}
/**
* 清空原始变量
*/
private void initComponents() {
workbook = null;
currentSheet = null;
sheets = null;
}
}TestJacob.java
/*
* Copyright © 2019 bjfansr@cn.ibm.com Inc. All rights reserved
* @package: com.ibm.jacob
* @version V1.0
*/
package com.ibm.jacob;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;
import org.junit.Test;
/**
* @author Moses *
* @Date 2019/4/1
*/
public class TestJacob {
@Test
public void testMacro() {
JacobExcelTool tool = new JacobExcelTool();
//打开
tool.OpenExcel("D:/temp/宏测试.xlsm", false, false);
Dispatch sheet = tool.getSheetByName("Sheet1");
for (int i = 2; i <= 7; i++) {
tool.setValue(sheet, "B" + i, "value", i * 1.2);
}
//调用Excel宏
tool.callMacro("test1");
//调用Excel宏
//tool.callMacro("样式设置");
Variant num = tool.getValue("A5", sheet);
System.out.println(num);
//关闭并保存，释放对象
tool.CloseExcel(true, true);
}
}最终在屏幕上输出 22
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
