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;
}
}
Top comments (0)