Actually, Microsoft Excel doesn’t have a built-in feature to add watermark in Excel worksheets. But there are still several ways to do it, such as adding header image or WordArt to worksheets to simulate the look of a watermark. This article will show you how to create and insert a header image in Excel to mimic a watermark using Java codes.
In addition to JDK and Intellij IDEA, you need a third-party library to build our test environment. Here I’ll recommend a free API called Free Spire.XLS for Java, which is a free and professional Java Excel API that enables developers to create, manipulate, convert and print Excel worksheets without using Microsoft Office.
Add Spire.Xls.jar as a dependency
There
are two ways to add Spire.Xls.jar located in the free API to your project as a
dependency. If you’re creating a Maven project in IDEA, the best choice is
specifying the following configuration in the pom.xml file, and clicking the
button “Import Changes”.
<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.free</artifactId>
<version>3.9.1</version>
</dependency>
</dependencies>
If you are a non-maven user, then download the package from the website page, and add Spire.Xls.jar
to run the sample code given in this tutorial.
The screenshot below is shown what it finally looks like.
Using the codeHere are some steps to add text watermark to Excel worksheets in Java using Free Spire.XLS for Java.
Please note that the watermark can only be shown when the view mode is Layout, so don’t forget to
change the view mode.
Step 1: Define a custom function called DrawText() to create an image based on the text content of a
string. The string can be “Confidential”, “draft”, “Sample” or any text which you want to be shown as
watermark.
Step 2: Initialize a Workbook instance and load the test file.
Step 3: Call DrawText() method to create an image, set the image as left header image.
Step 4: Save the resulting document.
import com.spire.xls.*;
import java.awt.*;
import java.awt.image.BufferedImage;
import static java.awt.image.BufferedImage.TYPE_INT_ARGB;
public class AddTextWatermark {
public static void main(String[] args) {
//Initialize a Workbook instance and load the test file
Workbook workbook = new Workbook();
workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Test.xlsx");
//Set text content of watermark and its size
Font font = new Font("Arial", Font.PLAIN, 50);
String watermark = "Draft";
for (Worksheet sheet : (Iterable<Worksheet>) workbook.getWorksheets()) {
//Call DrawText() method to insert the image
BufferedImage imgWtrmrk = drawText(watermark, font, Color.pink, Color.white, sheet.getPageSetup().getPageHeight(), sheet.getPageSetup().getPageWidth());
//Set the image as left header
sheet.getPageSetup().setLeftHeaderImage(imgWtrmrk);
sheet.getPageSetup().setLeftHeader("&G");
//Set the Viewmode as Layout
sheet.setViewMode(ViewMode.Layout);
}
//Save the document
workbook.saveToFile("output/AddWatermark.xlsx", ExcelVersion.Version2010);
}
private static BufferedImage drawText (String text, Font font, Color textColor, Color backColor,double height, double width)
{
//Create an image with specified width and height
BufferedImage img = new BufferedImage((int) width, (int) height, TYPE_INT_ARGB);
Graphics2D loGraphic = img.createGraphics();
//set the font size
FontMetrics loFontMetrics = loGraphic.getFontMetrics(font);
int liStrWidth = loFontMetrics.stringWidth(text);
int liStrHeight = loFontMetrics.getHeight();
//set the text format
loGraphic.setColor(backColor);//paint the background
loGraphic.fillRect(0, 0, (int) width, (int) height);
loGraphic.translate(((int) width - liStrWidth) / 2, ((int) height - liStrHeight) / 2);
loGraphic.rotate(Math.toRadians(-35)); //Rotate text
//reset translate transform
loGraphic.translate(-((int) width - liStrWidth) / 2, -((int) height - liStrHeight) / 2);
loGraphic.setFont(font);
loGraphic.setColor(textColor);
//draw text on the image at center position
loGraphic.drawString(text, ((int) width - liStrWidth) / 2, ((int) height - liStrHeight) / 2);
loGraphic.dispose();
return img;
}
}Output
