Add Watermark to Excel Spreadsheets in Java

Microsoft Excel doesn't have a built-in feature to add watermark in Excel sheet. But there are several tricky ways such as adding header image or WordArt to worksheets to simulate the look of a watermark. In this article, I am going to introduce how to create an image based on the worksheet size and set it as the header image by using Spire.XLS for Java.

Kindly note that the header image will be covered if the cells are filled with a solid color. Thus, adding a header image to simulate the appearance of a watermark requires that the cells where watermark appears do not have a background color.

Installing Spire.Xls.jar

If you use Maven, you can easily import the jar in your application by adding the following code to your project’s pom.xml file. For non-Maven projects, please download the jar file from this link and manually add it as a dependency in your program.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId> e-iceblue </groupId>
        <artifactId>spire.xls</artifactId>
        <version>4.8.1</version>
    </dependency>
</dependencies>

Using the code

import com.spire.xls.*;

import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.IOException;

import static java.awt.image.BufferedImage.TYPE_INT_ARGB;

public class AddWatermark {

    public static void main(String[] args) throws IOException {

        //Create a Workbook object and load the sample Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

        //Create a font
        Font font = new Font("Arial", Font.PLAIN, 50);

        //Define watermark text
        String watermarkText = "Internal Use";

        //Loop through the worksheets
        for (Object object : (Iterable) workbook.getWorksheets()) {

            //Get the specific worksheet
            Worksheet sheet = (Worksheet) object;

            //Call createWatermarkImage() method to create a watermark image
            BufferedImage image= createWatermarkImage(sheet, watermarkText, font, Color.pink);

            //Set the image as header image
            sheet.getPageSetup().setLeftHeaderImage(image);
            sheet.getPageSetup().setLeftHeader("&G");

            //Set the view mode as Layout
            sheet.setViewMode(ViewMode.Layout);
        }

        //Save the document
        workbook.saveToFile("output/Watermark.xlsx", ExcelVersion.Version2013);
    }

    private static BufferedImage createWatermarkImage(Worksheet sheet, String text, Font font, Color textColor) {

        //Create a Graphics2D object
        double width = 1.3 * (sheet.getPageSetup().getPageWidth() - 2 * 72 * sheet.getPageSetup().getLeftMargin());
        double height = 1.3 * (sheet.getPageSetup().getPageHeight() - 72 * sheet.getPageSetup().getBottomMargin());
        BufferedImage img = new BufferedImage((int) width, (int) height, TYPE_INT_ARGB);
        Graphics2D graphic = img.createGraphics();

        //Measure the string size
        FontMetrics fontMetrics = graphic.getFontMetrics(font);
        int strWidth = fontMetrics.stringWidth(text);
        int strHeight = fontMetrics.getAscent();

        //Draw string on the graphic
        graphic.setFont(font);
        graphic.setColor(textColor);
        graphic.drawString(text, ((int) width - strWidth) / 2, ((int) height + strHeight) / 2);

        graphic.dispose();
        return img;
    }
}

Output