最近项目上用到java 程序调用后缀名为xlsb的宏,并读出执行宏更新的数据,所以用到了jacob这个包,功能比较强大,我仅记录我使用到的部分
1. jacob使用方法
- 导包,pom.xml依赖如下
<dependency>
<groupId>com.jacob</groupId>
<artifactId>jacob</artifactId>
<version>1.19</version>
</dependency>
- 复制jacob-1.19-x64.dll(windows64 位 根据自己的电脑选择)到自己的jre下面,路径:C:\Program Files\Java\jdk1.8.0_151\jre\bin
**2. 代码示例
/* jacob 工具类*/
public class JacobExcelUtils {
/**
* 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();
ComThread.InitMTA(true);
// Excel对象
if (xl == null || xl.m_pDispatch == 0) {
xl = new ActiveXComponent("Excel.Application");
}
// 设置是否显示打开excel
xl.setProperty("Visible", new Variant(visible));
//是否打开excel的提示框
xl.setProperty("DisplayAlerts", new Variant(visible));
// 工作簿对象
if (workbooks == null || workbooks.m_pDispatch == 0) {
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");
}
public Dispatch getRange(String positon,Dispatch sheet){
return Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[]{positon}, new int[1]).toDispatch();
}
public Dispatch getRowOrColumn(Dispatch range, String name){
return Dispatch.call(range, name).toDispatch();
}
public int getRowOrColumnCount(Dispatch RowOrColumn){
return Dispatch.get(RowOrColumn,"Count").getInt();
}
/**
* 清空原始变量
*/
private void initComponents() {
workbook = null;
currentSheet = null;
sheets = null;
}
/*
测试方法
*/
public class Test {
@org.junit.Test
public void testMacro() {
JacobExcelUtils tool=new JacobExcelUtils();
try {
StopWatch sw = new StopWatch();
sw.start();
//打开
tool.OpenExcel("D:/录制宏复制粘贴1.xlsm", false, false);
Dispatch sheet = tool.getSheetByName("Sheet1");
for (int i = 2; i <= 7; i++) {
tool.setValue(sheet, "C" + i, "value", i * 1.2);
}
//调用Excel宏“Macro1”,可以不进行调用宏,最后closeexcel时也会触发宏
//tool.callMacro("Macro1");
sw.stop();
System.out.println("测试2耗时:"+sw.getTotalTimeMillis());
//保存并触发宏
tool.CloseExcel(true, true);
//重新打开
tool.OpenExcel("D:/录制宏复制粘贴1.xlsm", false, false);
Dispatch profitsheet = tool.getSheetByName("Sheet1");
//根据范围进行读excel的数据
Dispatch userRange = Dispatch.invoke(profitsheet, "Range", Dispatch.Get, new Object[]{"DY12:EH13"}, new int[1]).toDispatch();
//最后是一个二维数组,可以根据行和列读数据
SafeArray safeArray=Dispatch.call(userRange, "Value").toSafeArray();
Dispatch row=Dispatch.call(userRange, "Rows").toDispatch();
Dispatch columns=Dispatch.call(userRange, "Columns").toDispatch();
int rowCount=Dispatch.get(row,"Count").getInt();
int columnCount=Dispatch.get(columns,"Count").getInt();
for (int i = 1; i <= rowCount; i++) {
for (int j = 1; j <= columnCount; j++) {
System.out.println(safeArray.getString(i, j));
}
}
tool.CloseExcel(true, true);
} catch (FileNotFoundException e) {
e.printStackTrace();
//发生异常时关闭
tool.CloseExcel(true, true);
}
}
}
3. 注意事项
- jacob实际调用windows的microsoft
excel,如果未正确关闭excel的话,此进程还在,需要手动打开任务管理器进行关闭,不然下次运行时会报错 - 电脑上只有wps也可进行调用,文件的后缀要改为xlsm
- excel 提示下面的弹框,也可以在设置中关闭
4. 附件
https://download.csdn.net/download/u013401975/88320007