excel宏的Java调用

末蓝、 2022-02-25 10:20 444阅读 0赞

下载jacob-1.19.zip

[https://sourceforge.net/projects/jacob-project/][https_sourceforge.net_projects_jacob-project]

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

jacob.jar引入项目

![1649587-20190401165805013-1655938046.png][]

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

样式设置 宏

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);
        }
    }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70][]

[https_sourceforge.net_projects_jacob-project]: https://sourceforge.net/projects/jacob-project/
[1649587-20190401165805013-1655938046.png]: /images/20220225/abfa3c13f5634fe6b20de7a3adfe0053.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70]: /images/20220225/f0f2aebcf9304564b4eea9dbe72a2aff.png