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%縮放}
}