Excle plus watermark

excle Add watermark

The effect is as follows ： The transparency of the watermark can be set , See the code for details

dependent jar package ：jacob-1.17-x64.rar

Pay attention to distinguish between 32 Bit and 64 System

Copy jacob-1.17-M4-x64.dll Put it in java jdk bin Under the table of contents 、 System catalog /WINDOWS/system32 Under the table of contents

The code is as follows ：

package com.solex.waterPrint;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;
import com.jacob.com.ComThread;

public class TestWaterExcel {
    
    // Testing capabilities 
        public static void main(String[] argv) {
            TestWaterExcel d = TestWaterExcel.getInstance();
            try {
                if (d.initExcelApp()) {
                    Date date=Calendar.getInstance().getTime();
                    SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
                    String dateStr=sdf.format(date);
                    String imgPath="C:/Users/solexit06/Desktop/Border.png";
                    String filePath="C:/Users/solexit06/Desktop/testExcel.xlsx";

                    d.openWorkbook(filePath,true,false);
                    d.getCurrentSheet();
                    d.setFooter(dateStr);// The footer 
// d.setHeader(dateStr);// header 
                    d.setWater(imgPath);// Set image watermark 

// Dispatch curSheet=d.getSheetByIndex(2);// Get the second table 
// d.setValue(curSheet, "A1", "value", "test2");// To 2 Tabular A1 writes 

                    d.closeDocument();
                } else{
                    System.out.println(" Failed to open object ！");
                }
            } catch (Exception e) {
                System.out.println(e);
            }finally{
                d.closeWordObj();
            }
        }


    public TestWaterExcel() {}
    private static TestWaterExcel instance;
    private ActiveXComponent excelApp;//Word object 
    private Dispatch workbooks  = null;// Store all documents 
    private Dispatch workbook  = null;// Used to store a document ： For example, when adding a new document, it returns , New document 
    private Dispatch sheets = null;//  get sheets A collection of objects 
    private Dispatch currentSheet = null;//  At present sheet


    public final static synchronized TestWaterExcel getInstance() {
        if (instance == null){
            instance = new TestWaterExcel();
        }
        return instance;
    }

    private void initComponents(){
        workbook = null;
        currentSheet = null;
        sheets = null;
    }

    /** *  initialization Excel object */
    public boolean initExcelApp() {
        boolean retFlag = false;
        initComponents();
        ComThread.InitSTA();// Initialization thread 、 Close the thread after use 
        try {
            excelApp = new ActiveXComponent("Excel.Application");// Initialize the table 
            excelApp.setProperty("Visible", new Variant(true));// Configuration to start word Whether to display execution or implicit execution 
            workbooks  = excelApp.getProperty("Workbooks").toDispatch();//  obtain word All document objects 
            retFlag = true;
        } catch (Exception e) {
            retFlag = false;
            e.printStackTrace();
        }
        return retFlag;
    }

    /** Open one workbook*/
    public void openWorkbook(String filename,boolean visible, boolean readonly) {
        if (this.workbook != null) {
            this.closeDocument();
        }
        workbook = Dispatch.invoke(workbooks,"Open",Dispatch.Method,
                new Object[] { filename, new Variant(visible),new Variant(readonly) },//  Whether to open as read-only 
                new int[1]).toDispatch();
    }

    /** *  obtain workbook Name  * @return */
    public String getWorkbookName() {
        if(workbook==null)
            return null;
        return Dispatch.get(workbook, "name").toString();
    }

    /** *  obtain sheets Collection object for  * @return */
    public Dispatch getSheets() {
        if(sheets==null)
            sheets = Dispatch.get(workbook, "sheets").toDispatch();
        return sheets;
    }

    /** *  Add a new worksheet (sheet),（ After adding, it defaults to the currently active worksheet ） */
    public Dispatch addSheet() {
        return Dispatch.get(sheets,"add").toDispatch();
    }

    /** *  Get the present sheet * @return */
    public Dispatch getCurrentSheet() {
        currentSheet = Dispatch.get(workbook, "ActiveSheet").toDispatch();
        return currentSheet;
    }

    /** *  Get the present sheet Name  * @return */
    public String getCurrentSheetName() {
        return Dispatch.get(getCurrentSheet(), "name").toString();
    }

    /** *  adopt sheetName obtain sheet * @param name sheetName * @return */
    public Dispatch getSheetByName(String name) {
        return Dispatch.invoke(getSheets(),"Item",Dispatch.Get,
                new Object[]{name}, new int[1]).toDispatch();
    }

    /** *  Get the worksheet through the worksheet index ( First Workbook index by 1) * @param index * @return sheet object  */
    public Dispatch getSheetByIndex(Integer index) {
        return Dispatch.invoke(getSheets(), "Item", Dispatch.Get, 
                new Object[]{index}, new int[1]).toDispatch();
    }

    /** *  obtain sheet Total of  * @return */
    public int getSheetCount() {
        int count = Integer.parseInt(Dispatch.get(getSheets(), "count").toString());
    return count;

    }

    /** *  Write values to cells  * @param sheet  Operated sheet * @param position  Cell location , Such as ：C1 * @param type  The attribute of value   Such as ：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);
    }

    /** *  Cell read value  * @param position  Cell location , Such as ： 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();
        Variant value = Dispatch.get(cell, "Value");
        return value;
    }

    /** *  Set footer information  */
    public void setFooter(String foot) {
        currentSheet=this.getCurrentSheet();
        Dispatch PageSetup=Dispatch.get(currentSheet,"PageSetup").toDispatch();
        // typeface :&10 [ Note blank space ], bold :&B, Color :&KFF0000, date ：&D, Time ：&T
        Dispatch.put(PageSetup,"CenterFooter",
            "&13 &B&KF08080&D&");
    } 

    /** *  Get footer information  */ 
    public String getFooter() {
        currentSheet=this.getCurrentSheet();
        Dispatch PageSetup=Dispatch.get(currentSheet,"PageSetup").toDispatch();
        return Dispatch.get(PageSetup,"CenterFooter").toString();
    } 

    /** *  Set header information  */
    public void setHeader(String header) {
        currentSheet=this.getCurrentSheet();
        Dispatch PageSetup=Dispatch.get(currentSheet,"PageSetup").toDispatch();
        Dispatch.put(PageSetup,"CenterHeader",header);
    } 

    /** *  Get header information  */
    public String getHeader() {
        currentSheet=this.getCurrentSheet();
        Dispatch PageSetup=Dispatch.get(currentSheet,"PageSetup").toDispatch();
        return Dispatch.get(PageSetup,"CenterHeader").toString();
    } 

    /** *  Set the watermark through the header  */
    public void setWater(String filePath) {
        currentSheet=this.getCurrentSheet();
        Dispatch PageSetup=Dispatch.get(currentSheet,"PageSetup").toDispatch();
        Dispatch.put(PageSetup,"CenterHeader",
            "\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n&G");// Show picture objects ,\n\r It's line breaking ,&G Show pictures 

        Dispatch header=Dispatch.get(PageSetup,"CenterHeaderPicture").toDispatch();// Get the picture object 
        Dispatch.put(header,"Filename",filePath);
        Dispatch.put(header,"Height",105);
        Dispatch.put(header,"Width",170);
        Dispatch.put(header,"Brightness",0.75);// Set brightness 
        Dispatch.put(header,"Contrast",0.25);// Set comparison 
    } 


    /** *  Save the workbook as  * @param fileNewPath  Save as path  */
    public void SaveAs(String fileNewPath){
    Dispatch.invoke(workbook, "SaveAs", Dispatch.Method,
        new Object[] { fileNewPath,new Variant(44) }, new int[1]);
    }

    /** *  Save and close the current document  */
    public void closeDocument() {
        if (workbook  != null) {
            Dispatch.call(workbook , "Save");// preservation 
            Dispatch.call(workbook , "Close", new Variant(0));// close 
            workbook  = null;
        }
    }

    /** *  Release resources */
    public void closeWordObj() {
        if(excelApp!=null){
            excelApp.invoke("Quit", new Variant[] {});//  Close file 
            excelApp=null;
        }
        workbooks = null;
        ComThread.Release();//  Release thread 
    }


}