poi-excel-添加水印

1、官网快速指南

https://poi.apache.org/components/spreadsheet/quick-guide.html
访问如上地址可以查看到poi的相关操作方式：

How to create a new workbook
How to create a sheet
How to create cells
How to create date cells
Working with different types of cells
Iterate over rows and cells
Getting the cell contents
Text Extraction
Files vs InputStreams
Aligning cells
Working with borders
Fills and color
Merging cells
Working with fonts
......

https://archive.apache.org/dist/poi/release/src/

https://archive.apache.org/dist/poi/release/bin/

2、如何处理水印

在excel中没有水印的概念，只能通过其他形式视觉上达到水印的效果；
方式一：可以通过页眉页脚的方式：
优点：全文覆盖、打印预览可查看缺点：无法编辑、在非打印预览视图下无法查看
方式二：插入图片方式
优点：在非打印预览视图下可以查看缺点：打印的时候无法展示

3、实现方式

3.1、页眉页脚实现方式

3.1.1 pom依赖

<!-- Apache POI 核心库 -->
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.3</version>
</dependency><!-- OOXML 模式库（提供 CTLocking 等类） -->
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-lite</artifactId><version>5.2.3</version>
</dependency>

3.1.2 简单代码

  public static void buildHeaderAndFooter () throws IOException {// 创建文档Workbook wb = new HSSFWorkbook();// 创建sheet页Sheet sheet = wb.createSheet("sheet名称");// 创建行Row row = sheet.createRow(0);// 创建列Cell cell = row.createCell(0);// 设置单元格内容cell.setCellValue("测试测试");// 获取页眉Header header = sheet.getHeader();// 设置页眉header.setCenter("&\"微软雅黑,Bold\"&14&KAAAAAA 公司机密 - 严禁外传 - 页眉");sheet.setMargin(Sheet.HeaderMargin, 0.8);// 获取页脚Footer footer = sheet.getFooter();// 设置页脚footer.setCenter("&\"微软雅黑,Bold\"&14&KAAAAAA 公司机密 - 严禁外传 - 页脚");sheet.setMargin(Sheet.FooterMargin, 0.8);// TODO 需要根据自己的本地地址修改try (OutputStream fileOut = new FileOutputStream("D://workbook.xls")) {wb.write(fileOut);}}

3.1.3 效果

普通视图下：没有任何效果

预览模式下：页眉页脚展示咱们得水印

3.2 背景图片实现方式

3.2.1 pom依赖

<!-- Apache POI 核心库 -->
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.3</version>
</dependency><!-- OOXML 模式库（提供 CTLocking 等类） -->
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-lite</artifactId><version>5.2.3</version>
</dependency>

3.2.2 简单代码

 public static void picture() throws IOException{// 创建文档Workbook wb = new XSSFWorkbook();// 背景图片的位置 TODO 需要修改成本地地址InputStream is = new FileInputStream("D://a.png");byte[] bytes = IOUtils.toByteArray(is);// 设置成背景图片int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);is.close();CreationHelper helper = wb.getCreationHelper();// 创建sheet页Sheet sheet = wb.createSheet();// 创建绘图容器（drawing patriarch）这是 Excel 中所有形状（包括图片）的顶级容器。每个 sheet 只能有一个绘图容器。Drawing drawing = sheet.createDrawingPatriarch();// 创建锚点（ClientAnchor）对象。锚点用于确定图片在 sheet 中的位置和大小ClientAnchor anchor = helper.createClientAnchor();// 设置起始列（从0开始计数）anchor.setCol1(3);// 设置起始行（从0开始计数）anchor.setRow1(2);Picture pict = drawing.createPicture(anchor, pictureIdx);// 根据图片原始尺寸自动调整显示大小pict.resize();// 保存excel文件地址，TODO 需根据本地地址调整String file = "D://picture.xls";if (wb instanceof XSSFWorkbook) file += "x";try (OutputStream fileOut = new FileOutputStream(file)) {wb.write(fileOut);}}

3.2.3 效果

不足之处在于添加的背景图片可以被删除，这样水印就没啥意义了

3.2.4 针对水印可被删除的改进方式

代码调整

 public static void buildPicture() throws IOException{// 创建文档XSSFWorkbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();// 背景图片的位置 TODO 需要修改成本地地址InputStream is = new FileInputStream("D://a.png");byte[] bytes = IOUtils.toByteArray(is);// 添加图片到工作簿int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);//  返回工作簿中所有图片的列表POIXMLDocumentPart poixmlDocumentPart = wb.getAllPictures().get(pictureIdx);CreationHelper helper = wb.getCreationHelper();// 创建sheet页XSSFSheet sheet = wb.createSheet();/*** getPackagePart() 获取图片在 OOXML 包中的物理表示（即 ZIP 包中的一个文件）。* getPartName() 返回该文件的路径（如 /xl/media/image1.png）。*/PackagePartName ppn = poixmlDocumentPart.getPackagePart().getPartName();String relType = XSSFRelation.IMAGES.getRelation();PackageRelationship pr = sheet.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);// 设置图片为工作表背景sheet.getCTWorksheet().addNewPicture().setId(pr.getId());String file = "D://picture.xls";if (wb instanceof XSSFWorkbook) file += "x";try (OutputStream fileOut = new FileOutputStream(file)) {wb.write(fileOut);}is.close();}

效果如下: 水印无法选中也无法删除

4、完整示例

主要是使用背景图片的方式来实现excel的水印效果

4.1、pom依赖

<!-- Apache POI 核心库 -->
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.3</version>
</dependency><!-- OOXML 模式库（提供 CTLocking 等类） -->
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-lite</artifactId><version>5.2.3</version>
</dependency>

4.2、代码

package water1;import org.apache.poi.ooxml.POIXMLDocumentPart;
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.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRelation;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import javax.imageio.ImageIO;
import java.awt.Color;
import java.awt.Font;
import java.awt.*;
import java.awt.geom.AffineTransform;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;public class ExcelWatermarkGenerator {public static void main(String[] args) {try {String outputFile = "D://平铺水印数据表.xlsx";createTiledWatermarkExcel(outputFile);System.out.println("成功创建带平铺水印的Excel文件: " + new File(outputFile).getAbsolutePath());} catch (Exception e) {System.err.println("创建Excel文件时出错: " + e.getMessage());e.printStackTrace();}}public static void createTiledWatermarkExcel(String outputPath) throws Exception {// 1. 创建工作簿XSSFWorkbook workbook = new XSSFWorkbook();XSSFSheet sheet = (XSSFSheet)workbook.createSheet("销售数据");// 2. 添加标题行addTitleRow(sheet);// 3. 添加示例数据addSampleData(sheet);// 4. 设置列宽setColumnWidths(sheet);// 5. 创建平铺水印图片 TODO  “公司机密” 可替换成需要的水印文字byte[] watermarkImage = createTiledWatermarkImage("公司机密", 4, 3); // 4列3行平铺// 6. 添加水印到ExceladdWatermarkToSheet(workbook, sheet, watermarkImage);// 7. 添加WPS兼容的页眉文本水印addHeaderWatermarkForWPS(sheet);// 8. 设置打印选项setupPrintSettings(sheet);// 9. 保存文件try (FileOutputStream out = new FileOutputStream(outputPath)) {workbook.write(out);}workbook.close();}private static void addTitleRow(Sheet sheet) {Row titleRow = sheet.createRow(0);titleRow.setHeightInPoints(25);CellStyle titleStyle = createTitleStyle(sheet.getWorkbook());String[] titles = {"产品名称", "生产日期", "库存数量", "单价(元)", "总价值"};for (int i = 0; i < titles.length; i++) {Cell cell = titleRow.createCell(i);cell.setCellValue(titles[i]);cell.setCellStyle(titleStyle);}}private static CellStyle createTitleStyle(Workbook workbook) {CellStyle style = workbook.createCellStyle();style.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());style.setFillPattern(FillPatternType.SOLID_FOREGROUND);org.apache.poi.ss.usermodel.Font font = workbook.createFont();font.setBold(true);font.setFontHeightInPoints((short) 12);font.setColor(IndexedColors.WHITE.getIndex());style.setFont(font);style.setBorderTop(BorderStyle.THIN);style.setBorderBottom(BorderStyle.THIN);style.setBorderLeft(BorderStyle.THIN);style.setBorderRight(BorderStyle.THIN);style.setTopBorderColor(IndexedColors.BLUE.getIndex());style.setAlignment(HorizontalAlignment.CENTER);style.setVerticalAlignment(VerticalAlignment.CENTER);return style;}private static void addSampleData(Sheet sheet) {CellStyle dateStyle = createDateStyle(sheet.getWorkbook());CellStyle currencyStyle = createCurrencyStyle(sheet.getWorkbook());String[] products = {"智能手机", "笔记本电脑", "平板电脑", "智能手表", "蓝牙耳机"};SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");for (int i = 1; i <= 30; i++) {Row row = sheet.createRow(i);// 产品名称row.createCell(0).setCellValue(products[i % products.length] + " " + (i % 5 + 1) + "代");// 生产日期Cell dateCell = row.createCell(1);Date date = new Date(System.currentTimeMillis() - (i * 24 * 60 * 60 * 1000L));dateCell.setCellValue(date);dateCell.setCellStyle(dateStyle);// 库存数量row.createCell(2).setCellValue(100 + (int)(Math.random() * 500));// 单价Cell priceCell = row.createCell(3);double price = 500 + (Math.random() * 3000);priceCell.setCellValue(price);priceCell.setCellStyle(currencyStyle);// 总价值（公式计算）Cell valueCell = row.createCell(4);valueCell.setCellFormula("C" + (i+1) + "*D" + (i+1));valueCell.setCellStyle(currencyStyle);}}private static CellStyle createDateStyle(Workbook workbook) {CellStyle style = workbook.createCellStyle();style.setLocked(false); // 允许编辑CreationHelper createHelper = workbook.getCreationHelper();style.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd"));return style;}private static CellStyle createCurrencyStyle(Workbook workbook) {CellStyle style = workbook.createCellStyle();style.setLocked(false); // 允许编辑style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("¥#,##0.00"));return style;}private static void setColumnWidths(Sheet sheet) {sheet.setColumnWidth(0, 25 * 256);  // 产品名称sheet.setColumnWidth(1, 15 * 256);  // 生产日期sheet.setColumnWidth(2, 12 * 256);  // 库存数量sheet.setColumnWidth(3, 12 * 256);  // 单价sheet.setColumnWidth(4, 15 * 256);  // 总价值}private static byte[] createTiledWatermarkImage(String text, int cols, int rows) throws IOException {// 1. 设置单个水印单元尺寸int cellWidth = 300;int cellHeight = 200;// 2. 计算总图片尺寸int totalWidth = cellWidth * cols;int totalHeight = cellHeight * rows;// 3. 创建透明背景图片BufferedImage image = new BufferedImage(totalWidth, totalHeight, BufferedImage.TYPE_INT_ARGB);Graphics2D g2d = image.createGraphics();// 4. 设置透明背景g2d.setComposite(AlphaComposite.Clear);g2d.fillRect(0, 0, totalWidth, totalHeight);g2d.setComposite(AlphaComposite.Src);// 5. 设置水印样式g2d.setRenderingHint(RenderingHints.KEY_TEXT_ANTIALIASING, RenderingHints.VALUE_TEXT_ANTIALIAS_ON);g2d.setColor(new Color(180, 180, 180, 50)); // 半透明灰色// 6. 使用WPS兼容字体Font font = new Font("微软雅黑", Font.BOLD, 36);g2d.setFont(font);// 7. 在网格中平铺水印for (int row = 0; row < rows; row++) {for (int col = 0; col < cols; col++) {// 计算当前单元中心位置int centerX = col * cellWidth + cellWidth / 2;int centerY = row * cellHeight + cellHeight / 2;// 保存当前变换AffineTransform originalTransform = g2d.getTransform();// 移动到当前单元中心g2d.translate(centerX, centerY);// 旋转文本g2d.rotate(Math.toRadians(-30));// 计算文本位置（居中）FontMetrics metrics = g2d.getFontMetrics();int textWidth = metrics.stringWidth(text);int x = -textWidth / 2;int y = metrics.getHeight() / 4;// 绘制水印文本g2d.drawString(text, x, y);// 恢复原始变换g2d.setTransform(originalTransform);}}g2d.dispose();// 8. 将图片转换为字节数组ByteArrayOutputStream baos = new ByteArrayOutputStream();ImageIO.write(image, "PNG", baos);return baos.toByteArray();}private static void addWatermarkToSheet(XSSFWorkbook workbook, XSSFSheet sheet, byte[] watermarkImage) throws Exception {// 1. 添加图片到工作簿int pictureIdx = workbook.addPicture(watermarkImage, Workbook.PICTURE_TYPE_PNG);//  返回工作簿中所有图片的列表POIXMLDocumentPart poixmlDocumentPart = workbook.getAllPictures().get(pictureIdx);CreationHelper helper = workbook.getCreationHelper();/*** getPackagePart() 获取图片在 OOXML 包中的物理表示（即 ZIP 包中的一个文件）。* getPartName() 返回该文件的路径（如 /xl/media/image1.png）。*/PackagePartName ppn = poixmlDocumentPart.getPackagePart().getPartName();String relType = XSSFRelation.IMAGES.getRelation();PackageRelationship pr = sheet.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);// 设置图片为工作表背景sheet.getCTWorksheet().addNewPicture().setId(pr.getId());}private static void addHeaderWatermarkForWPS(Sheet sheet) {// WPS兼容性：添加文本水印到页眉Header header = sheet.getHeader();header.setCenter("&\"微软雅黑,Bold\"&14&KAAAAAA 公司机密 - 严禁外传");sheet.setMargin(Sheet.HeaderMargin, 0.8);}private static void setupPrintSettings(Sheet sheet) {// 1. 设置页面为横向sheet.getPrintSetup().setLandscape(true);// 2. 设置页边距sheet.setMargin(Sheet.TopMargin, 0.7);sheet.setMargin(Sheet.BottomMargin, 0.7);sheet.setMargin(Sheet.LeftMargin, 0.5);sheet.setMargin(Sheet.RightMargin, 0.5);// 3. 设置打印标题行sheet.setRepeatingRows(CellRangeAddress.valueOf("1:1"));// 4. 设置网格线sheet.setDisplayGridlines(true);sheet.setPrintGridlines(true);// 5. 设置缩放比例sheet.setZoom(90); // 90%缩放}
}