excel宏的Java调用

下载jacob-1.19.zip

https://sourceforge.net/projects/jacob-project/

jacob-1.19-x64.dll放到C:\Windows\System32目录下

jacob.jar引入项目

excel需要保存为启用宏的工作簿,后缀为.xlsm
文档特定设置 宏
Sub 样式设置()
'
' 样式设置 宏
'

'
    Range("A1:C1").Select
    Selection.Font.Bold = True
    With Selection.Font
        .Name = "微软雅黑"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
        .Name = "微软雅黑"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    ActiveWindow.SmallScroll Down:=-9
    Range("A1:C8").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveWindow.SmallScroll Down:=-6
    Range("A1:C1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
    Range("G3").Select
End Sub
Sub 文档特定设置()
'
' 文档特定设置 宏
'

'
End Sub
样式设置 宏
Sub 样式设置()
'
' 样式设置 宏
'

'
    Range("A1:C1").Select
    Selection.Font.Bold = True
    With Selection.Font
        .Name = "微软雅黑"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
        .Name = "微软雅黑"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    ActiveWindow.SmallScroll Down:=-9
    Range("A1:C8").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveWindow.SmallScroll Down:=-6
    Range("A1:C1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
    Range("G3").Select
End Sub
Sub 文档特定设置()
'
' 文档特定设置 宏
'

'
End Sub
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("文档特定设置");
        //调用Excel宏
        tool.callMacro("样式设置");
        Variant num = tool.getValue("B8", sheet);
        System.out.println(num);
        //关闭并保存,释放对象
        tool.CloseExcel(true, true);
    }
}

 

### 如何使用 Java 实现 WPS Excel 功能 要通过 Java 实现 WPS Excel 的功能,主要涉及以下几个方面:读取 Excel 文件、启用、执行操作以及保存修改后的文件。以下是详细的说明和代码示例。 #### 1. 使用 Apache POI 处理 Excel 文件 Apache POI 是一个强大的开源库,支持对 Microsoft Office 文档的操作,包括 Excel 文件。它提供了丰富的 API 来处理工作簿、工作表、单元格等内容[^1]。 ```java import org.apache.poi.ss.usermodel.*; import java.io.FileInputStream; import java.io.FileOutputStream; public class ExcelMacroExample { public static void main(String[] args) throws Exception { // 加载现有的 Excel 文件 FileInputStream fileInputStream = new FileInputStream("example.xlsx"); Workbook workbook = WorkbookFactory.create(fileInputStream); // 获取第一个工作表 Sheet sheet = workbook.getSheetAt(0); // 启用(如果存在) if (workbook instanceof XSSFWorkbook && ((XSSFWorkbook) workbook).getVbaProject() != null) { System.out.println("此文档包含 VBA !"); } else { System.out.println("未找到任何!"); } // 执行特定的逻辑或调用函数 executeCustomLogic(sheet); // 保存更改到新的文件 FileOutputStream outputStream = new FileOutputStream("output_with_macro.xlsx"); workbook.write(outputStream); workbook.close(); fileInputStream.close(); outputStream.close(); System.out.println("完成!"); } private static void executeCustomLogic(Sheet sheet) { Row row = sheet.getRow(0); // 假设第 0 行有数据 Cell cell = row.getCell(0); String value = ""; if (cell != null) { switch (cell.getCellType()) { case STRING: value = cell.getStringCellValue(); break; default: value = "未知类型"; } } System.out.println("单元格 A1 的值:" + value); } } ``` 这段代码演示了如何加载 Excel 文件并检查是否存在。需要注意的是,Apache POI 并不直接支持运行脚本,但它允许访问与相关的元数据。 --- #### 2. 配合其他工具实现复杂操作 对于更高级的需求,比如嵌入图片资源或者转换为图片格式等功能,则可能需要额外的支持: - **嵌入图片** 如果目标是向 Excel 中写入图片或其他多媒体内容,可以参考 `cn.hutool.json` 或者类似的 JSON 解析器来辅助处理 XML 数据结构[^2]。 - **Excel 转换为图片** 对于将 Excel 工作表导出为图像文件的任务,可采用第三方图形渲染引擎(如 Aspose.Cells),其具备完整的跨平台兼容性和高质量输出能力[^5]。 --- #### 3. 结合 WPS Office 开发环境 虽然上述方法基于通用框架构建,但如果具体场景限定在 WPS 生态下开发插件或扩展程序时,还可以考虑引入 Lisp 编程语言作为补充手段之一[^3]。不过这通常适用于定制化程度较高的场合,在日常业务中较少见。 --- ### 注意事项 由于纯 Java 技术栈难以完全模拟原生办公软件的行为模式(例如触发内置事件处理器等动作),因此某些特殊情况下仍需借助外部服务端口转发机制或者其他专用 SDK 接口才能达成预期效果[^4]。
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值