百分百能用,我用的POI版本是5.2.3,效果如下
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.opc.PackagePartName;
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.TargetMode;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetProtection;import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;/*** Excel表格添加水印*/
@Slf4j
public class WaterMarkUtil {/*** Excel添加水印** @param workbook XSSFWorkbook* @param waterMarkText 水印文字内容*/public static void insertWaterMarkTextToXlsx(XSSFWorkbook workbook, String waterMarkText) throws IOException {BufferedImage image = createWatermarkImage(waterMarkText);ByteArrayOutputStream imageOs = new ByteArrayOutputStream();ImageIO.write(image, "png", imageOs);int pictureIdx = workbook.addPicture(imageOs.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG);XSSFPictureData pictureData = workbook.getAllPictures().get(pictureIdx);for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表XSSFSheet sheet = workbook.getSheetAt(i);PackagePartName ppn = pictureData.getPackagePart().getPartName();String relType = XSSFRelation.IMAGES.getRelation();PackageRelationship pr = sheet.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);sheet.getCTWorksheet().addNewPicture().setId(pr.getId());}}/*** 创建水印图片** @param waterMark 水印文字*/public static BufferedImage createWatermarkImage(String waterMark) {String[] textArray = waterMark.split("\n");Font font = new Font(null, Font.PLAIN, 30);try {//指定字体位置 (宋体:simsun.ttf)String fontFilePath = "D:\\qxt\\feihuasongti.ttf";FileInputStream fontInputStream = new FileInputStream(fontFilePath);font = Font.createFont(Font.TRUETYPE_FONT, fontInputStream);font = font.deriveFont(30f); // 设置字体大小为30} catch (Exception e) {log.error("指定使用汉字字体位置:", e);font = new Font("simsun", Font.PLAIN, 30);}//设置水印的密集程度,可以用调节图片大小来控制int width = 350;int height = 250;BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);// 背景透明 开始Graphics2D g = image.createGraphics();image = g.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);// 背景透明 结束g = image.createGraphics();g.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER, 0.5f));//设置水印透明度(0-1)之间g.setColor(new Color(Color.lightGray.getRGB()));// 设定画笔颜色g.setFont(font);// 设置画笔字体//g.shear(0.1, -0.26);// 设定倾斜度//设置字体平滑g.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);//文字从中心开始输入,算出文字宽度,左移动一半的宽度,即居中FontMetrics fontMetrics = g.getFontMetrics(font);// 水印位置int x = width / 2;int y = height / 2;// 设置水印旋转g.rotate(Math.toRadians(-40), x, y);for (String s : textArray) {// 文字宽度int textWidth = fontMetrics.stringWidth(s);g.drawString(s, x - (textWidth / 2), y);// 画出字符串y = y + font.getSize();}g.dispose();// 释放画笔return image;}/*** 保护工作表以及设置保护选项*/public static void protectWorksheet(XSSFWorkbook excel) {for (int i = 0; i < excel.getNumberOfSheets(); i++) {XSSFSheet sheet = excel.getSheetAt(i);// 保护工作表并设置密码sheet.protectSheet("8246");// 获取CTSheetProtection对象以进行更精细的控制CTSheetProtection protection = sheet.getCTWorksheet().getSheetProtection();// 设置保护选项(例如,允许用户选择未锁定的单元格)protection.setSheet(true);//控制是否保护整个工作表。默认为false,设置为true`后,用户不能修改工作表内容,除非解除了保护protection.setObjects(true);//如果为true,则禁止插入、删除或移动图表、图片等对象protection.setFormatCells(true);//是否允许用户格式化单元格protection.setFormatColumns(true);//是否允许用户格式化列protection.setFormatRows(true);//是否允许用户格式化行protection.setInsertColumns(true);//是否允许用户插入列protection.setInsertRows(true);//是否允许用户插入行protection.setDeleteColumns(true);//是否允许用户删除列protection.setDeleteRows(true);//是否允许用户删除行protection.setSelectLockedCells(true);//如果为false,则不允许选择锁定的单元格,即使整体工作表保护已被解除protection.setSelectUnlockedCells(true);//如果为false,则不允许选择未锁定的单元格,即使整体工作表保护已被解除protection.setSort(true);//如果设置为true,允许用户对受保护的工作表进行排序操作protection.setAutoFilter(true);//如果设置为true,允许用户在受保护的工作表上应用或编辑自动过滤器。protection.setPivotTables(true);//如果设置为true,允许用户在受保护的工作表上创建、修改或删除透视表protection.setInsertHyperlinks(true);//控制是否允许插入超链接}}}
public static void main(String[] args) throws IOException {XSSFWorkbook workbook = new XSSFWorkbook();XSSFSheet sheet = workbook.createSheet("Sheet1");SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");String waterMarkText = "这是个水印\n" + dateFormat.format(new Date());WaterMarkUtil.insertWaterMarkTextToXlsx(workbook, waterMarkText);try (FileOutputStream fileOut = new FileOutputStream("watermark_example.xlsx")) {workbook.write(fileOut);} catch (Exception e) {e.printStackTrace();}}