jacob调用Excel VBA宏

jacob调用Excel VBA宏

1，下载jacob

下载最新版即可

2，本地配置

下载完成后，会得到这几个文件

jacob.jar可引入工程供调用

jacob-xxxxx.dll需放置对应的系统文件夹（C:\Windows\System32），32位系统放入x86.dll文件，64位系统放入x64.dll文件

3，代码调用

将jacob.jar引入工程后，即可开始使用，可直接导入下面的包装方法

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");}// 设置是否显示打开excelxl.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;}
}

其中重点介绍几个方法

单元格写入

/*** 单元格写入值** @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);
}

 宏程序调用

/*** 调用excel宏** @param macroName 宏名*/
public void callMacro(String macroName) {Dispatch.call(xl, "Run", new Variant(macroName));
}

 这套代码亲测有效，大家使用过程中有啥问题欢迎评论